【SQL】滴滴DS数据分析笔试SQL+编程部分

一、计算连续3天登录的用户数

表t_user中有两个字段user_id和login_date，要求求出连续3天登录的用户数。

思路：
本题的重点在于如何体现“连续”。
使用窗口函数row_number()，如果用登陆日期减去对应的行号，若是连续的话，得到的值应该是一致的

SELECT user_id
FROM (
    SELECT 
        user_id, 
        login_date,
        row_number() OVER(PARTITION BY user_id ORDER BY login_date) AS rn,
        date_sub(login_date, rn) AS flag_date
    FROM  t_user
) tmp
GROUP BY tmp.user_id, date_sub(login_date, rn) -- 每个用户每次连续登录的分块
HAVING COUNT(1) >= 3

注：上述代码仅求出了连续登录3天以上的所有user_id，并非user_id数。

二、已知频数求中位数

给定表t_numbers，包含两列，number和frequence，求中位数。

思路：
计算每一个数字的上累积（小于等于该数的数字个数）、下累积（大于等于该数的数字个数），然后筛选出上下累积都超过频数一半的数字（剩余一个或两个），然后取平均。

SELECT AVG(tmp.number) AS median
FROM (
    SELECT 
        number,
        SUM(frequence) OVER(ORDER BY number ASC) AS up_sum, -- 上累积
        SUM(frequence) OVER(ORDER BY number DESC) AS down_sum, -- 下累积
        SUM(frequence) OVER()/2  AS half
    FROM t_numbers
) tmp
WHERE tmp.up_sum >= tmp.half
AND tmp.down_sum >= tmp.half;

需要注意OVER()函数的几点：

某个函数() OVER(partition by 列名  -- 分组
                order by 列名      -- 排序
                rows between unbounded preceding and unbounded following  -- 窗口
               )

（1）如果同时省略窗口和排序语句，那么窗口默认为between unbounded preceding and unbounded following（即整个组）；
（2）如果只省略窗口语句而不省略排序语句，那么窗口默认为between unbounded preceding and current row（即当前组的第一行到当前行）；
（3）如果省略分组语句而不省略排序语句，那么窗口默认为between unbounded preceding and current row（即当前组的第一行到当前行）；
（4）如果同时省略分组语句和排序语句，那么窗口默认为between unbounded preceding and unbounded following（即整个组）。

总结：
一句话，有排序语句时，窗口默认为between unbounded preceding and current row（即当前组的第一行到当前行）。

三、挡板储水