从简单需求到OLAP的RANK系列函数
同事问了一个非常简单的问题，怎么取出每个partition里面另外一个列的最小值？
create table t1 (int c1, int c2);
假如按照c2分区，0-10，10-20，20-30，30-40，40-50
insert into t1 values(101, 1); insert into t1 values(102, 2); insert into t1 values(111, 11); insert into t1 values(112, 12); insert into t1 values(121, 21); insert into t1 values(122, 22); insert into t1 values(131, 31); insert into t1 values(132, 32); insert into t1 values(133, 32); insert into t1 values(132, 33); insert into t1 values(141, 41); insert into t1 values(142, 42); insert into t1 values(142, 43);
本来这个问题非常简单,但今天死活想不出来了，居然绕着去看RANK系列的OLAP函数，学习了好长时间也发现没办法搞定。
其实只需要按照分区表的范围取整数就可以了
select cast(c2/10 as integer), min(c1),max(c1) from t1 group by cast(c2/10 as integer);
输出如下：
1 2 3 - --- --- 0 101 102 1 111 112 2 121 122 3 131 133 4 141 142
不过倒是又温习了一下RANK系列函数，简单总结一下：
RANK() 排名函数 ，返回值是不连续的，如果有两个相同的第一名，则第三个值为3
DENSE_RANK() 奥运冠军排名函数，返回值是连续的，可以并列第一，然后第二名，第三名
ROW_NUMBER() 连续值，基本可以看作ORACLE里的ROWNUM
OVER（）可以加分区列或者列表达式，这样在输出max(c1)的时候如果分在一个区，所有的值都是一样的
SELECT c1,c2,CAST(C2/10 AS INTEGER),MAX(C1) OVER( PARTITION BY CAST(C2/10 AS INTEGER)) FROM T1;
得到如下输出：
C1 C2 3 4 --- -- - --- 101 1 0 102 102 2 0 102 111 11 1 112 112 12 1 112 121 21 2 122 122 22 2 122 131 31 3 133 132 33 3 133 133 32 3 133 132 32 3 133 141 41 4 142 142 43 4 142 142 42 4 142
OVER 内还可以加ORDER BY 用来指定排序的时候用那个column
另外一个非常有用的功能是窗口函数可以在OVER()内指定rows或者range来指定以当前行为范围的一个窗口，在这个窗口内来进行聚集函数的计算。
比如， 我想看看某商品当前日期的价格和此前30天的平均价格：
SELECT c1 as price,c2 as days,avg(C1) OVER( order by c2 range 30 PRECEDING ) as avg_price FROM T1;
结果如下：
PRICE DAYS AVG_PRICE ----- ---- --------- 101 1 101 102 2 101 111 11 104 112 12 106 121 21 109 122 22 111 131 31 114 132 32 120 133 32 120 132 33 124 141 41 126 142 42 129 142 43 132
在我想来，窗口函数最有用的功能是看当前股票价格和此前一段时间的平均价格。
这就是OLAP之RANK系列函数的简单介绍，有了这些知识之后应该可以很快的写出更多更加有用的SQL。我的感觉是RANK系列函数相当于在处理每一行的数据的同时都可以为某其它列带着group by，非常强大。但千万注意，这里面的任何函数都会导致扫面非常多的数据，所以这类SQL的性能不会太好，千万在online的程序中慎用。
