SQL刷题有感-持续

问题一：求次日留存率

SELECT COUNT(DISTINCT a.user_id)*100.0/COUNT(DISTINCT b.user_id) as retention_rate
FROM login_log a
LEFT JOIN login_log b ON a.user_id=b.user_id
AND DATEDIFF(b.login_date,a.login_date)=1
WHERE a.login_date='2022-10-01'
AND b.login_date='2022-10-02';

解释：
该SQL语句中，首先通过LEFT JOIN将当天和次日的登录记录进行关联，并筛选出当天和次日均有登录记录的用户。

然后使用COUNT(DISTINCT b.user_id)计算当天有登录记录的用户数，使用COUNT(DISTINCT a.user_id)计算次日有登录记录的用户数。

最后将次日有登录记录的用户数除以当天有登录记录的用户数并乘以100，得到次日留存率。

空值的判断

当表中某一字段数据有 2，1，nu l l这样的数据形式的时候，要判断字段内容不为2的时候，要先吧null值转换为非null值

IFNULL(actl_val,0) <> 2

问题二：

SELECT table_t.tag, concat( round (AVG( case when  table_t.bf_rat >=1 then 1 else table_t.bf_rat end )*100,2),'%')
FROM (
    SELECT tu.video_id,
     TIMESTAMPDIFF(SECOND, tu.start_time, tu.end_time) /tv.duration bf_rat, 
    tv.tag
    FROM tb_user_video_log tu
    JOIN tb_video_info tv ON tu.video_id = tv.video_id
) table_t
GROUP BY table_t.tag
HAVING AVG(table_t.bf_rat) > 0.6
ORDER BY AVG(case when  table_t.bf_rat >=1 then 1 else table_t.bf_rat end ) DESC;

题目：
错误原因：播放时间大于视频时间的数据应该视为1，1代表百分百

问题三

select tv.author,date_format(tu.start_time,'%Y-%m') month,
round(avg(case when tu.if_follow = 1 then 1
         when tu.if_follow = 2 then -1
         else 0 end),3) fans_growth_rate,
sum(sum(case when tu.if_follow = 1 then 1
         when tu.if_follow = 2 then -1
         else 0 end) ) over 
         (partition by tv.author order by date_format(tu.start_time,'%Y-%m')) fans_total
from tb_user_video_log tu 
inner join tb_video_info tv on tv.video_id=tu.video_id
where year(tu.start_time)=2021
and year(tu.end_time)=2021
group by tv.author,month
order by tv.author,fans_total

sum 窗口，根据创作者ID分组，然后根据开始时间生序排列即可
原题目

问题四：

依然是sum 窗口的应用，这道题的思路是，先根据明细表的聚合分组，然后使用sum窗口来计算特定时间下的sum窗口回溯6天

select * 
from
    (select tag, date(start_time) dt,
    sum(sum(if_like)) over(partition by tag order by date(start_time) rows 6 preceding) sum_like_cnt_7d,
    max(sum(if_retweet)) over(partition by tag order by date(start_time) rows 6 preceding) max_retweet_cnt_7d
    from tb_user_video_log tuvl, tb_video_info tvi
    where tuvl.video_id = tvi.video_id and (date(start_time) between '2021-09-25' and '2021-10-03')
    group by tag, dt) t
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt

原题目地址
解析：
1.首先，在内层查询中，我们从 tb_user_video_log 和 tb_video_info 两张表中筛选出符合条件的记录。这里的条件是 tuvl.video_id = tvi.video_id，以及 start_time 在 ‘2021-09-25’ 和 ‘2021-10-03’ 之间。通过这个筛选，我们得到了每个视频在这个时间范围内的点赞和转发数据。
2.然后，我们使用 group by 对每个标签（tag）和日期（dt）进行分组。这里的日期是将 start_time 转换为日期格式
3.接下来，我们计算每个标签在过去7天内的累计点赞数（sum_like_cnt_7d）和最大转发数（max_retweet_cnt_7d）。我们使用 sum() 函数计算累计点赞数，并使用窗口函数 over() 定义窗口范围为6行之前（即包括当前行在内的7天数据）。对于最大转发数，我们使用 max() 函数和同样的窗口范围。
4.在外层查询中，我们筛选出日期（dt）在 ‘2021-10-01’ 和 ‘2021-10-03’ 之间的记录。
5.最后，我们按标签（tag）降序和日期（dt）排序，得到最终的查询结果。