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

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

还是先创建一个用户访问表：user＿date

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

表中加入如下数据：

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结果集进行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，是个关键字，表示结果属于哪一个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day。

结果如下：

上述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的使用：

根据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；

结果如下：

上述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的子集，以最左侧的维度为主，从该维度进行层级聚合。

比如，以month维度进行层级聚合：

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

结果如下：

把month和day调换顺序，则以day维度进行层级聚合：

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

结果如下：

这里，根据日和月进行聚合，和根据日聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样。

窗口函数实际应用

1． 第二高的薪水

难度简单。

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

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

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

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

这道题可以用 row＿number 函数解决。

参考代码：

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：偏移量，表示从第几条数据开始取，0代表第1条数据。

2． 分数排名

难度简单。

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

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

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

＋－－－－－－－＋－－－－－－＋
｜ 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． 连续出现的数字

难度中等。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

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

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

＋－－－－－－－－－－－－－－－－－＋
｜ 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． 连续N天登录

难度困难。

写一个 SQL 查询，  找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户，返回的结果表按照 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表，至少连续 5 天登录账户的是id＝7的用户

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

思路：

去重：由于每个人可能一天可能不止登陆一次，需要去重排序：对每个ID的登录日期排序差值：计算登录日期与排序之间的差值，找到连续登陆的记录连续登录天数计算：select id， count（＊） group by id， 差值（伪代码）取出登录5天以上的记录通过表合并，取出id对应用户名

参考代码：

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的应用：DATE＿SUB （DATE， X），注意，X为正数表示当前日期的前X天；如何找连续日期：通过排序与登录日期之间的差值，因为排序连续，因此若登录日期连续，则差值一致；GROUP BY和HAVING的应用：通过id和差值的GROUP BY，用COUNT找到连续天数大于5天的id，注意COUNT不是一定要出现在SELECT后，可以直接用在HAVING中

5． 给定数字的频率查询中位数

难度困难。

Numbers 表保存数字的值及其频率。

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

在此表中，数字为 0， 0， 0， 0， 0， 0， 0， 1， 2， 2， 2， 3，所以中位数是 （0 ＋ 0） ／ 2 ＝ 0。

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

请编写一个查询来查找所有数字的中位数并将结果命名为 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）