【Hive】SQL | 窗口函數詳解

前一篇文章【Hive】SQL | 窗口函數入門，對 Hive SQL 中的窗口函數進行一個初步的介紹，本文對窗口函數進行更加系統的介紹。

假設有如下表格（loan）。表中包含貸款人的唯一標識，貸款日期，以及貸款金額。

1.SUM(), MIN(),MAX(),AVG()等聚合函數，可以直接使用 over() 進行分區計算。

SELECT *,
/*前三次貸款的金額之和*/
SUM(amount) OVER (PARTITION BY name ORDER BY orderdate ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv1,
/*歷史所有貸款 累加到下一次貸款 的金額之和*/
SUM(amount) OVER (PARTITION BY name ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS pv2
FROM loan ;

其中，窗口函數over()使得聚合函數sum()可以在限定的窗口中進行聚合。本例子中，第一條語句計算每個人當前記錄的前三條貸款金額之和。第二條語句計算截至到下一次貸款，客戶貸款的總額。

窗口的限定語法為：ROWS BETWEEN 一個時間點 AND 一個時間點。時間節點可以使用：

n PRECEDING : 前n行

n FOLLOWING：後n行

CURRENT ROW ：當前行

如果不想限制具體的行數，可以將 n 替換為 UNBOUNDED.比如從起始到當前，可以寫為:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

窗口函數over()和group by 的最大區別，在於group by之後其餘列也必須按照此分區進行計算，而over()函數使得單個特徵可以進行分區。

2.NTILE(), ROW_NUMBER(), RANK(), DENSE_RANK()，可以為數據集新增加序列號。

SELECT *, NTILE(10) OVER (PARTITION BY name ORDER BY orderdate) AS f1,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY orderdate) AS f2,
RANK() OVER (PARTITION BY name ORDER BY orderdate) AS f3,
DENSE_RANK() OVER (PARTITION BY name ORDER BY orderdate) AS f4
FROM loan;
其中第一個函數是將數據按name切分成10區，並返回屬於第幾個分區。後面的三個函數的功能看起來很相似。區別在於當數據中出現相同值得時候，如何編號。
ROW_NUMBER()返回的是一列連續的序號。
RANK()對於數值相同的這一項會標記為相同的序號，而下一個序號跳過。比如{4，5，6}變成了{4，4，6}.
DENSE_RANK()對於數值相同的這一項，也會標記為相同的序號，但下一個序號並不會跳過。比如{4，5，6}變成了{4，4，5}.
3.LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()函數返回一系列指定的點
SELECT *,LAG(orderdate, 1) OVER(PARTITION BY name ORDER BY orderdate) AS last_dt,
LEAD(orderdate, 1,'1970-1-1') OVER(PARTITION BY name ORDER BY orderdate) AS next_dt,
FIRST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS first_dt,
LAST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS latest_dt
FROM loan;
LAG(n)將數據向前錯位 n 行。LEAD()將數據向後錯位 n 行。FIRST_VALUE()取當前分區中的第一個值。LAST_VALUE()取當前分區最後一個值。
4.GROUPING SET(),with CUBE, with ROLLUP 對 group by 進行限制
SELECT A,B,CFROM loan
GROUP BY substring(orderdate,1,7),orderdate
GROUPING SETS(substring(orderdate,1,7), orderdate)ORDER BY GROUPING__ID;
GROUPING__ID是GROUPING_SET()的操作之後自動生成的。生成GROUPING__ID是為了區分每條輸出結果是屬於哪一個group by的數據。
它是根據group by後面聲明的順序欄位，是否存在於當前group by中的一個二進位位組合數據。GROUPING SETS()必須先做GROUP BY操作。
比如（A,C）的group_id：group_id(A,C) = grouping(A)+grouping(B)+grouping (C) 的結果就是：二進位：101 也就是5.
如果解釋器發現group by A,C 但是select A,B,C 那麼運行時會將所有from 表取出的結果複製一份，B都置為null，也就是在結果中，B都為null.
SELECTA,B,CFROM loan
GROUP BY substring(orderdate,1,7),orderdate
with CUBEORDER BY GROUPING__ID;
with CUBE 和GROUPING_SET()的區別就是，with CUBE 返回的是group by 欄位的笛卡爾積。
SELECTA,B,CFROM loan
GROUP BY substring(orderdate,1,7),orderdate
with ROLLUPORDER BY GROUPING__ID;
with ROLLUP則不會產生第二列為鍵的聚合結果，在本例子中，只按照 substring(orderdate,1,7)進行展示。所以使用with ROLLUP時，要注意group by 後面欄位的順序。