同事問了一個非常簡單的問題，怎么取出每個partition里面另外一個列的最小值？

    create table t1 (int c1, int c2);
  

?

假如按照c2分區(qū)，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函數(shù)，學(xué)習(xí)了好長時間也發(fā)現(xiàn)沒辦法搞定。

其實(shí)只需要按照分區(qū)表的范圍取整數(shù)就可以了

?

    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
  

不過倒是又溫習(xí)了一下RANK系列函數(shù)，簡單總結(jié)一下：

RANK() 排名函數(shù) ，返回值是不連續(xù)的，如果有兩個相同的第一名，則第三個值為3

DENSE_RANK () 奧運(yùn)冠軍排名函數(shù)，返回值是連續(xù)的，可以并列第一，然后第二名，第三名

ROW_NUMBER() 連續(xù)值，基本可以看作ORACLE里的ROWNUM

OVER（）可以加分區(qū)列或者列表達(dá)式，這樣在輸出max(c1)的時候如果分在一個區(qū)，所有的值都是一樣的

?

    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 內(nèi)還可以加ORDER BY 用來指定排序的時候用那個column

另外一個非常有用的功能是窗口函數(shù)可以在OVER()內(nèi)指定rows或者range來指定以當(dāng)前行為范圍的一個窗口，在這個窗口內(nèi)來進(jìn)行聚集函數(shù)的計(jì)算。

比如， 我想看看某商品當(dāng)前日期的價(jià)格和此前30天的平均價(jià)格：

    ?
  

    SELECT c1 as price,c2 as days,avg(C1)  OVER(

order by c2 range  30 PRECEDING 

) as avg_price

FROM T1;
  

    ?
  

結(jié)果如下：

     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
  

在我想來，窗口函數(shù)最有用的功能是看當(dāng)前股票價(jià)格和此前一段時間的平均價(jià)格。

這就是OLAP之RANK系列函數(shù)的簡單介紹，有了這些知識之后應(yīng)該可以很快的寫出更多更加有用的SQL。我的感覺是RANK系列函數(shù)相當(dāng)于在處理每一行的數(shù)據(jù)的同時都可以為某其它列帶著group by，非常強(qiáng)大。但千萬注意，這里面的任何函數(shù)都會導(dǎo)致掃面非常多的數(shù)據(jù)，所以這類SQL的性能不會太好，千萬在online的程序中慎用。

從簡單需求到OLAP的RANK系列函數(shù)