互联网大厂SQL真题 （一）

题目：国庆期间每类视频点赞量和转发量

 问题：统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

Solution:

 题目要求的维度为tag和dt，指标为近一周总点赞量和单周最大当天转发量。

由题目所给的两个表可得连接的维度为video_id。由于有近一周这个限制，所以我们不能简单地使用group by。近一周的概念不是静止的，10-01的近一周和10-02的近一周涉及的区间显然不同，所以这道题里我们需要用到窗口函数来解决这个问题。它的基本语法为：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    ) 

其中partition by用于定义分组，frame可用于定义选定的行范围。需要特别注意的，窗口函数在sql中是在group by对应函数之后执行的。

在这道题中，我们可以先统计出每天的点赞量和转发量，根据这两个子指标，我们可以进而计算出近一周的点赞量和单日最大转发量。计算子指标代码如下：

select
    tag,
    date(start_time) as dt,
    sum(if_like),
    sum(if_retweet)
from
    tb_user_video_log a
    left join
    tb_video_info b
    on a.video_id = b.video_id
    where year(start_time) = '2021'

之后就是窗口函数的部分了，我们可以根据日期升序排序即order by dt，以及row preceding 6 and current row来获取近一周内每日的子指标从而求得题目的两个指标。因此可将上述代码改为：

select
    tag,
    date(start_time) as dt,
    sum(sum(if_like)) over (partition by tag order by date(start_time) rows between 
                            6 preceding and current row)
    as sum_like_cnt_max_retweet,
    max(sum(if_retweet)) over (partition by tag order by date(start_time) 
                               rows between 6 preceding and current row) 
    as max_retweet_cnt_7d
from
    tb_user_video_log a
    join
    tb_video_info b
    on a.video_id = b.video_id
    where year(start_time) = '2021'
group by
    tag, date(start_time)

这一步以后，就离答案很近了。由于题目要求的是国庆前三天的指标，所以我们可以将上述代码包装成一个子表，然后再限定dt范围为10-01和10-03之间，于是最终的代码如下：

select
    tag,
    dt,
    sum_like_cnt_max_retweet,
    max_retweet_cnt_7d
from
    (
   select
    tag,
    date(start_time) as dt,
    sum(sum(if_like)) over (partition by tag order by date(start_time) rows between 
                            6 preceding and current row)
    as sum_like_cnt_max_retweet,
    max(sum(if_retweet)) over (partition by tag order by date(start_time) 
                               rows between 6 preceding and current row) 
    as max_retweet_cnt_7d
from
    tb_user_video_log a
    join
    tb_video_info b
    on a.video_id = b.video_id
    where year(start_time) = '2021'
group by
    tag, date(start_time)
)t
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt asc;