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

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

還是先創(chuàng)建一個(gè)用戶訪問表：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是一種將多個(gè)group by 邏輯寫在一個(gè)sql語句中的便利寫法。

等價(jià)于將不同維度的GROUP BY結(jié)果集進(jìn)行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，是個(gè)關(guān)鍵字，表示結(jié)果屬于哪一個(gè)分組集合，根據(jù)grouping sets中的分組條件month，day，1是代表month，2是代表day。

結(jié)果如下：

上述SQL等價(jià)于：

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的維度的所有組合進(jìn)行聚合。

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等價(jià)于：

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è)的維度為主，從該維度進(jìn)行層級(jí)聚合。

比如，以month維度進(jìn)行層級(jí)聚合：

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維度進(jìn)行層級(jí)聚合：

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ìn)行聚合，和根據(jù)日聚合結(jié)果一樣，因?yàn)橛懈缸雨P(guān)系，如果是其他維度組合的話，就會(huì)不一樣。

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

1． 第二高的薪水

難度簡(jiǎn)單。

編寫一個(gè) 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；

更簡(jiǎn)單的代碼：

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

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

2． 分?jǐn)?shù)排名

難度簡(jiǎn)單。

編寫一個(gè) SQL 查詢來實(shí)現(xiàn)分?jǐn)?shù)排名。

如果兩個(gè)分?jǐn)?shù)相同，則兩個(gè)分?jǐn)?shù)排名（Rank）相同。請(qǐng)注意，平分后的下一個(gè)名次應(yīng)該是下一個(gè)連續(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)該返回（按分?jǐn)?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ù)字

難度中等。

編寫一個(gè) 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天登錄

難度困難。

寫一個(gè) 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  ｜
＋－－－－＋－－－－－－－－－－－＋

思路：

去重：由于每個(gè)人可能一天可能不止登陸一次，需要去重排序：對(duì)每個(gè)ID的登錄日期排序差值：計(jì)算登錄日期與排序之間的差值，找到連續(xù)登陸的記錄連續(xù)登錄天數(shù)計(jì)算：select id， count（＊） group by id， 差值（偽代碼）取出登錄5天以上的記錄通過表合并，取出id對(duì)應(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

注意點(diǎn)：

DATE＿SUB的應(yīng)用：DATE＿SUB （DATE， X），注意，X為正數(shù)表示當(dāng)前日期的前X天；如何找連續(xù)日期：通過排序與登錄日期之間的差值，因?yàn)榕判蜻B續(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 ｜
＋－－－－－－－－＋

請(qǐng)編寫一個(gè)查詢來查找所有數(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）