同事問了一個非常簡單的問題，怎么取出每個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的程序中慎用。

從簡單需求到OLAP的RANK系列函數