5． GROUPING SETS、GROUPING＿＿ID、CUBE、ROLLUP

這幾個分析函數(shù)通常用于OLAP中，不能累加，而且需要根據(jù)不同維度上鉆和下鉆的指標統(tǒng)計，比如，分小時、天、月的UV數(shù)。

還是先創(chuàng)建一個用戶訪問表：user＿date

CREATE TABLE user＿date （
month STRING，
day STRING，
cookieid STRING
）；

表中加入如下數(shù)據(jù)：

2021－03，2021－03－10，cookie1
2021－03，2021－03－10，cookie5
2021－03，2021－03－12，cookie7
2021－04，2021－04－12，cookie3
2021－04，2021－04－13，cookie2
2021－04，2021－04－13，cookie4
2021－04，2021－04－16，cookie4
2021－03，2021－03－10，cookie2
2021－03，2021－03－10，cookie3
2021－04，2021－04－12，cookie5
2021－04，2021－04－13，cookie6
2021－04，2021－04－15，cookie3
2021－04，2021－04－15，cookie2
2021－04，2021－04－16，cookie1

GROUPING SETS的使用：

grouping sets是一種將多個group by 邏輯寫在一個sql語句中的便利寫法。

等價于將不同維度的GROUP BY結(jié)果集進行UNION ALL。

SELECT
month，
day，
COUNT（DISTINCT cookieid） AS uv，
GROUPING＿＿ID
FROM user＿date
GROUP BY month，day
GROUPING SETS （month，day）
ORDER BY GROUPING＿＿ID；

注：上述SQL中的GROUPING＿＿ID，是個關(guān)鍵字，表示結(jié)果屬于哪一個分組集合，根據(jù)grouping sets中的分組條件month，day，1是代表month，2是代表day。

結(jié)果如下：

上述SQL等價于：

SELECT month，
NULL as day，
COUNT（DISTINCT cookieid） AS uv，
1 AS GROUPING＿＿ID
FROM user＿date
GROUP BY month
UNION ALL
SELECT NULL as month，
day，
COUNT（DISTINCT cookieid） AS uv，
2 AS GROUPING＿＿ID
FROM user＿date
GROUP BY day；
CUBE的使用：

根據(jù)GROUP BY的維度的所有組合進行聚合。

SELECT
month，
day，
COUNT（DISTINCT cookieid） AS uv，
GROUPING＿＿ID
FROM user＿date
GROUP BY month，day
WITH CUBE
ORDER BY GROUPING＿＿ID；

結(jié)果如下：

上述SQL等價于：

SELECT NULL，NULL，COUNT（DISTINCT cookieid） AS uv，0 AS GROUPING＿＿ID FROM user＿date
UNION ALL
SELECT month，NULL，COUNT（DISTINCT cookieid） AS uv，1 AS GROUPING＿＿ID FROM user＿date GROUP BY month
UNION ALL
SELECT NULL，day，COUNT（DISTINCT cookieid） AS uv，2 AS GROUPING＿＿ID FROM user＿date GROUP BY day
UNION ALL
SELECT month，day，COUNT（DISTINCT cookieid） AS uv，3 AS GROUPING＿＿ID FROM user＿date GROUP BY month，day；
ROLLUP的使用：

是CUBE的子集，以最左側(cè)的維度為主，從該維度進行層級聚合。

比如，以month維度進行層級聚合：

SELECT
month，
day，
COUNT（DISTINCT cookieid） AS uv，
GROUPING＿＿ID  
FROM user＿date
GROUP BY month，day
WITH ROLLUP
ORDER BY GROUPING＿＿ID；

結(jié)果如下：

把month和day調(diào)換順序，則以day維度進行層級聚合：

SELECT
day，
month，
COUNT（DISTINCT cookieid） AS uv，
GROUPING＿＿ID  
FROM user＿date
GROUP BY day，month
WITH ROLLUP
ORDER BY GROUPING＿＿ID；

結(jié)果如下：

這里，根據(jù)日和月進行聚合，和根據(jù)日聚合結(jié)果一樣，因為有父子關(guān)系，如果是其他維度組合的話，就會不一樣。

窗口函數(shù)實際應(yīng)用

1． 第二高的薪水

難度簡單。

編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary）。

＋－－－－＋－－－－－－－－＋
｜ Id ｜ Salary ｜
＋－－－－＋－－－－－－－－＋
｜ 1  ｜ 100    ｜
｜ 2  ｜ 200    ｜
｜ 3  ｜ 300    ｜
＋－－－－＋－－－－－－－－＋

例如上述 Employee 表，SQL查詢應(yīng)該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那么查詢應(yīng)返回 null。

＋－－－－－－－－－－－－－－－－－－－－－＋
｜ SecondHighestSalary ｜
＋－－－－－－－－－－－－－－－－－－－－－＋
｜ 200                 ｜
＋－－－－－－－－－－－－－－－－－－－－－＋

這道題可以用 row＿number 函數(shù)解決。

參考代碼：

SELECT
 ＊
 FROM（
   SELECT Salary， row＿number（） over（order by Salary desc） rk
   FROM Employee
 ） t WHERE t．rk ＝ 2；

更簡單的代碼：

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

OFFSET：偏移量，表示從第幾條數(shù)據(jù)開始取，0代表第1條數(shù)據(jù)。

2． 分數(shù)排名

難度簡單。

編寫一個 SQL 查詢來實現(xiàn)分數(shù)排名。

如果兩個分數(shù)相同，則兩個分數(shù)排名（Rank）相同。請注意，平分后的下一個名次應(yīng)該是下一個連續(xù)的整數(shù)值。換句話說，名次之間不應(yīng)該有“間隔”。

＋－－－－＋－－－－－－－＋
｜ Id ｜ Score ｜
＋－－－－＋－－－－－－－＋
｜ 1  ｜ 3．50  ｜
｜ 2  ｜ 3．65  ｜
｜ 3  ｜ 4．00  ｜
｜ 4  ｜ 3．85  ｜
｜ 5  ｜ 4．00  ｜
｜ 6  ｜ 3．65  ｜
＋－－－－＋－－－－－－－＋

例如，根據(jù)上述給定的 Scores 表，你的查詢應(yīng)該返回（按分數(shù)從高到低排列）：

＋－－－－－－－＋－－－－－－＋
｜ Score ｜ Rank ｜
＋－－－－－－－＋－－－－－－＋
｜ 4．00  ｜ 1    ｜
｜ 4．00  ｜ 1    ｜
｜ 3．85  ｜ 2    ｜
｜ 3．65  ｜ 3    ｜
｜ 3．65  ｜ 3    ｜
｜ 3．50  ｜ 4    ｜
＋－－－－－－－＋－－－－－－＋

參考代碼：

SELECT Score，
dense＿rank（） over（order by Score desc） as ｀Rank｀
FROM Scores；

3． 連續(xù)出現(xiàn)的數(shù)字

難度中等。

編寫一個 SQL 查詢，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字。

＋－－－－＋－－－－－＋
｜ Id ｜ Num ｜
＋－－－－＋－－－－－＋
｜ 1  ｜  1  ｜
｜ 2  ｜  1  ｜
｜ 3  ｜  1  ｜
｜ 4  ｜  2  ｜
｜ 5  ｜  1  ｜
｜ 6  ｜  2  ｜
｜ 7  ｜  2  ｜
＋－－－－＋－－－－－＋

例如，給定上面的 Logs 表， 1 是唯一連續(xù)出現(xiàn)至少三次的數(shù)字。

＋－－－－－－－－－－－－－－－－－＋
｜ ConsecutiveNums ｜
＋－－－－－－－－－－－－－－－－－＋
｜ 1               ｜
＋－－－－－－－－－－－－－－－－－＋

參考代碼：

SELECT DISTINCT ｀Num｀ as ConsecutiveNums
FROM
 （
   SELECT Num，
   lead（Num， 1， null） over（order by id） n2，
   lead（Num， 2， null） over（order by id） n3
   FROM Logs
 ） t1
WHERE Num ＝ n2 and Num ＝ n3

4． 連續(xù)N天登錄

難度困難。

寫一個 SQL 查詢，  找到活躍用戶的 id 和 name，活躍用戶是指那些至少連續(xù) 5 天登錄賬戶的用戶，返回的結(jié)果表按照 id 排序。

表 Accounts：

＋－－－－＋－－－－－－－－－－－＋
｜ id ｜ name      ｜
＋－－－－＋－－－－－－－－－－－＋
｜ 1  ｜ Winston   ｜
｜ 7  ｜ Jonathan  ｜
＋－－－－＋－－－－－－－－－－－＋

表 Logins：

＋－－－－＋－－－－－－－－－－－－－＋
｜ id ｜ login＿date  ｜
＋－－－－＋－－－－－－－－－－－－－＋
｜ 7  ｜ 2020－05－30  ｜
｜ 1  ｜ 2020－05－30  ｜
｜ 7  ｜ 2020－05－31  ｜
｜ 7  ｜ 2020－06－01  ｜
｜ 7  ｜ 2020－06－02  ｜
｜ 7  ｜ 2020－06－02  ｜
｜ 7  ｜ 2020－06－03  ｜
｜ 1  ｜ 2020－06－07  ｜
｜ 7  ｜ 2020－06－10  ｜
＋－－－－＋－－－－－－－－－－－－－＋

例如，給定上面的Accounts和Logins表，至少連續(xù) 5 天登錄賬戶的是id＝7的用戶

＋－－－－＋－－－－－－－－－－－＋
｜ id ｜ name      ｜
＋－－－－＋－－－－－－－－－－－＋
｜ 7  ｜ Jonathan  ｜
＋－－－－＋－－－－－－－－－－－＋

思路：

去重：由于每個人可能一天可能不止登陸一次，需要去重排序：對每個ID的登錄日期排序差值：計算登錄日期與排序之間的差值，找到連續(xù)登陸的記錄連續(xù)登錄天數(shù)計算：select id， count（＊） group by id， 差值（偽代碼）取出登錄5天以上的記錄通過表合并，取出id對應(yīng)用戶名

參考代碼：

SELECT DISTINCT b．id， name
FROM
 （SELECT id， login＿date，
   DATE＿SUB（login＿date， ROW＿NUMBER（） OVER（PARTITION BY id ORDER BY login＿date）） AS diff
  FROM（SELECT DISTINCT id， login＿date FROM Logins） a） b
INNER JOIN Accounts ac
ON b．id ＝ ac．id
GROUP BY b．id， diff
HAVING COUNT（b．id） ＞＝ 5

注意點：

DATE＿SUB的應(yīng)用：DATE＿SUB （DATE， X），注意，X為正數(shù)表示當前日期的前X天；如何找連續(xù)日期：通過排序與登錄日期之間的差值，因為排序連續(xù)，因此若登錄日期連續(xù)，則差值一致；GROUP BY和HAVING的應(yīng)用：通過id和差值的GROUP BY，用COUNT找到連續(xù)天數(shù)大于5天的id，注意COUNT不是一定要出現(xiàn)在SELECT后，可以直接用在HAVING中

5． 給定數(shù)字的頻率查詢中位數(shù)

難度困難。

Numbers 表保存數(shù)字的值及其頻率。

＋－－－－－－－－－－＋－－－－－－－－－－－－－＋
｜  Number  ｜  Frequency  ｜
＋－－－－－－－－－－＋－－－－－－－－－－－－－｜
｜  0       ｜  7          ｜
｜  1       ｜  1          ｜
｜  2       ｜  3          ｜
｜  3       ｜  1          ｜
＋－－－－－－－－－－＋－－－－－－－－－－－－－＋

在此表中，數(shù)字為 0， 0， 0， 0， 0， 0， 0， 1， 2， 2， 2， 3，所以中位數(shù)是 （0 ＋ 0） ／ 2 ＝ 0。

＋－－－－－－－－＋
｜ median ｜
＋－－－－－－－－｜
｜ 0．0000 ｜
＋－－－－－－－－＋

請編寫一個查詢來查找所有數(shù)字的中位數(shù)并將結(jié)果命名為 median 。

參考代碼：

select
avg（cast（number as float）） as median
from
 （
   select Number，
   Frequency，
   sum（Frequency） over（order by Number） － Frequency as prev＿sum，
   sum（Frequency） over（order by Number） as curr＿sum
   from Numbers
 ） t1， （
   select sum（Frequency） as total＿sum
   from Numbers
 ） t2
where
t1．prev＿sum ＜＝ （cast（t2．total＿sum as float） ／ 2）
and
t1．curr＿sum ＞＝ （cast（t2．total＿sum as float） ／ 2）