SQL学习（10）：微观到宏观的困难题目解答 | 大厂真题实例-每天新用户的次日留存率

Algebra幸运E

已于 2022-07-07 13:58:42 修改

阅读量362

点赞数

分类专栏： SQL学习文章标签： sql 学习

于 2022-07-06 17:05:56 首次发布

本文链接：https://blog.csdn.net/UoB1887858/article/details/125639256

版权

经过前几个章节的学习和对应题目的锻炼，相信文章所描述的解题思路和代码框架都已经被掌握。总结来看，先书写select-from-where代码框架，再分解题目，逐步填充的解题方法具有宏观到微观的特点，适合思路清晰的简单题目和部分中等题目，解答清晰、书写方便、不容易出现语法遗漏问题。

但当题目比较复杂，尤其需要多层子查询时，这种方法带来的代码书写思路却往往容易出现遗漏，导致错误。原因在于，我们解决困难问题的常见方法是微观到宏观的，即将困难问题拆分为简单的小问题再进行组合。 如果还使用前几章提到的框架，则在宏观下找微观时容易出现遗漏。

本章开始，将从微观到宏观介绍适合困难问题的新解题思路和代码框架。

用户行为日志表tb_user_log：

uid-用户ID
artical_id-文章ID
in_time-进入时间
out_time-离开时间
sign_in-是否签到

问题：统计2021年11月每天新用户的次日留存率（保留2位小数），命名为uv_left_rate

注：次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。

困难问题通常需要使用子查询，涉及join、union等函数。我们需要根据最终要求的提取数据，反推其来自的查询条件、子查询部分等。

问题拆分：

2021年11月——新用户和次日留存都需要在11月内，即用户第一次进入和再次进入都需要在11月。如果使用min得到的新用户子查询中使用where条件，只会得到11月中首次进入，并不等于在11月首次注册；需要使用having
新用户——2021年11月之前未进入过
次日留存——只关注新用户注册后的次日。题目注意事项中说明，如果首次进入停留时间跨天，则直接记录为次日留存，因此需要离开时间的数据，不能舍去
留存率——新用户中次日留存人数/前一天新用户，共同点在于都来自于新用户集合

问题思路总结：

筛选新注册用户——子查询
筛选活跃时间——子查询。

问题层面看需要再次进入的时间和注册时间相差1天，特殊情况下离开时间和注册时间差1，总体来看相当于寻找比注册时间多1天的对应数据，此时in和out无实际差别，因此使用union将in_time和out_time合为一体
子查询join——需要使用外连接，将新用户子查询全部保留。注意可能出现NULL，总体查询时需要注意处理。条件：uid相同和时间差࿰