一道SQL题的前世今生
- 2019 年 10 月 8 日
- 笔记
本文转载自超哥的杂货铺，详情可以扫描下方二维码：
以清净心看世界；
用欢喜心过生活。
超哥的杂货铺，你值得拥有~
来看一道SQL题目：
注：以下讨论核心在于解释原理，所涉及到的数据和表结构均为虚构。本文代码较多，如果看不清楚，可以在后台回复“sql”获取本文PDF版本。
假设某APP场景下，有以下数据需求： 表1，新增用户表，t_new_user 字段：uid 用户id，reg_date新增日期 表2，收藏行为表，t_favorite_act 字段：uid 用户id，fav_date 收藏日期 表3，加购物车行为，下面简称“加购”，t_add_cart_act 字段：uid 用户id，add_date 加购日期 表4，新安装用户表(包括前文的新增用户，还包含卸载重装的用户)， t_new_install_user 字段，uid 用户id，install_date 安装日期 目标：求2019-07-01新增用户数和新装用户数以及当日新增用户和新装用户在当天，接下来三天，接下来七天产生收藏行为和加购物车行为的人数(请确保你理解了需求)。输出格式如下：
点击图片查看细节
题目中涉及到了两类用户两种行为三种日期，直观的想法是可以化整为零，先缩小范围，看一种用户一个日期的行为，然后类推解决。(比如先看下新增用户三日收藏用户数，加购用户数)。我们看一下这种思路的SQL写法。
提取2019-07-01新增用户数，三日收藏用户数，三日加购用户数的代码如下：
select a.reg_date, count(distinct a.uid) as new_usr_cnt, count(distinct b.uid) as 3day_fav_usr_cnt,--3日收藏用户数 count(distinct c.uid) as 3day_add_cart_usr_cnt--3日加购用户数 from ( select reg_date, uid from t_new_user where reg_date = '2019-07-01' group by reg_date, uid ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
提取2019-07-01新装用户数(包含新增)，三日收藏用户数，三日加购用户数的代码如下(注意和之前的差别)
select a.reg_date, count(distinct a.uid) as new_install_usr_cnt, count(distinct b.uid) as new_install_3day_fav_usr_cnt, --3日收藏用户数 count(distinct c.uid) as new_install_3day_add_cart_usr_cnt--3日加购用户数 from ( select reg_date as new_date, uid from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date, uid from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
按照这种思路，我们需要写好多段的SQL(需要修改日期相关的代码)：新增用户当日，新增用户3日，新增用户7日，新装用户当日，新装用户3日，新装用户7日。 分别运行之后6次，经过整理就能得到最终的结果。也可以外面载嵌套一个left join，设法把这些小段的SQL合并起来(可以思考一下怎么做)，这样减少运行的次数，但是代码会非常长，而且有很多重复的部分。显然，这样做不够“明智”。
有没有更简单一点的写法呢？答案当然是肯定的。
变式1：新装和新增合并起来写
有没有发现“同一个日期”，如上面的“三日内行为”，对于新装用户和新增用户的代码，b表和c表的大段SQL是一样的！而且后面的代码包含了前面的。因为都要从这两个表里取收藏和加购行为，而且日期还一样。如果按照上面的写法，将新增用户和新装用户两段用日期关联，放到一起执行，会重复扫描b表和c表。显然这样在一定程度上会影响效率。考虑到大段代码的重复性，我们可以巧妙的合并一下，如下：
select a.reg_date, count(distinct case when a.type='new_user' then a.uid else null end) as new_usr_cnt, count(distinct case when a.type='new_user' then b.uid else null end) as fav_usr_cnt, count(distinct case when a.type='new_user' then c.uid else null end) as add_cart_usr_cnt, count(distinct a.uid) as new_install_usr_cnt, count(distinct b.uid) as new_install_3day_fav_usr_cnt, count(distinct c.uid) as new_install_3day_add_cart_usr_cnt from ( select reg_date as new_date, uid, 'new_user' as type from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date, uid, 'reinstall' as type from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 2) group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 2) group by add_date, uid ) c on a.uid = c.uid group by a.reg_date
这样是把新增和新装的合并起来了，但是当日，三日，七日还是需要分开写。还有办法优化吗？当然可以！(想象一下，如果需求忽然增加了五日，十日，十五日怎么办？)（题外话，其实新装表是包含新增表的，如果做表的时候能用一个标识区分安装时是否是首次，可能会更合理一些。）
变式2：巧用datediff
datediff函数可以求两个日期的时间差。虽然目前的需求是求当日，三日，七日，也就是日期差分别是0，2，6(注意不是1，3，7)的情况。如果我们能求出所有的时间差diff_day，再对diff_day进行判断，不仅能处理当前的需求，就是再来几个别的不同的日期，那也可以只用一个case when就解决了，代码如下，显然比原来更清晰简洁。
select a.reg_date, count(distinct case when fav_datediff = 0 and a.type='new_user' then a.uid) as new_install_user_current_day, count(distinct case when fav_datediff = 0 and a.type='new_user' then b.uid else null end) as new_user_current_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 2 and a.type='new_user' then b.uid else null end) as new_user_3_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 6 and a.type='new_user' then b.uid else null end) as new_user_7_day_fav, count(distinct case when add_datediff = 0 and a.type='new_user' then c.uid else null end) as new_user_current_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 2 and a.type='new_user' then c.uid else null end) as new_user_3_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 6 and a.type='new_user' then c.uid else null end) as new_user_7_day_add, count(distinct case when fav_datediff = 0 then a.uid) as new_install_user_current_day, count(distinct case when fav_datediff = 0 then b.uid else null end) as new_install_user_current_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 2 then b.uid else null end) as new_install_user_3_day_fav, count(distinct case when fav_datediff > 0 and fav_datediff <= 6 then b.uid else null end) as new_install_user_7_day_fav, count(distinct case when add_datediff = 0 and then c.uid else null end) as new_install_user_current_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 2 then c.uid else null end) as new_install_user_3_day_add, count(distinct case when add_datediff > 0 and add_datediff <= 6 then c.uid else null end) as new_install_user_7_day_add from ( select a.reg_date, a.uid, a.type, b.uid, c.uid, datediff(b.fav_date, a.reg_date) as fav_datediff, datediff(b.add_date, a.reg_date) as add_datediff, from ( select reg_date as new_date, uid, 'new_user' as type from t_new_user where reg_date = '2019-07-01' union all select install_date as new_date , uid, 'reinstall' as type from t_new_install_user where install_date = '2019-07-01' ) a left join ( select fav_date, uid from t_favorite_act where fav_date between '2019-07-01' and date_add('2019-07-01', 7) --注意这里要取能满足datediff=6的日期范围 group by fav_date, uid ) b on a.uid = b.uid left join ( select add_date, uid from t_add_cart_act where add_date between '2019-07-01'and date_add('2019-07-01', 7) --注意这里要取能满足datediff=6的日期范围 group by add_date, uid ) c on a.uid = c.uid ) d
看完上面的代码，是不是感觉豁然开朗了许多？我们巧妙的将之前的代码缩减并且减少了冗余。对比一开始的想法，不仅思路更加清晰，代码量也精简不少，而且更便捷，执行的效率也更高。不过我们继续来思考这端代码，看有没有哪些细节是可能会忽略的。
变式3：如果不是2019-07-01怎么办？
如果是别的固定的日期，当然只需要复制一下代码，改变一下日期就好了。但工作中会有这样的场景，不仅仅只是临时取一个数据，而是要开发报表，这需要让写好的SQL根据不同的日期变量，每天执行一下，获得相应的数据。这里有几个问题。SQL的定时执行通常需要依赖于shell脚本，我们需要把日期作为一个变量，它需要每天都自动更新成最新的时间(通常会有一天延迟，因此最新的日期就是当前日期昨天的时间)。另一个是数据更新的问题。我们用实例来说明，假设今天是0809，那我们应该能得到0808以及之前的数据。对于0802以及之前的数据，它的当日，三日，七日的转化情况已经固定了，不会随着时间进一步更新。对于0806以及之前的数据，它的当日和三日转化也已经确定。而0803-0808这些天，他们的七日转化数据还没有“到位”，0806-0808，他们的三日转化数据也还没有“到位”，因为时间周期还没到。我们可以选择将当前最新的数据呈现出来(例如0808的数据，当日，三日，七日是一样的，因为只有当日的数据)，也可以选择如果日期还没到可以计算数据的时候，在相应的数据置为0。还有一个需要考虑的点：当最新的一天任务执行的时候，都伴随着有一些天的计算时间得到了满足(原来的1天变成2天，2天变成3天，以此类推)，所以就需要同时更新当前时间及之前七天的数据。在调度任务的代码里需要有相应的处理逻辑。例如每天写入的时候都需要删除之前七天，写进最新七天等。(这里使用的是不置0的方式)。具体在这里就不展开了。
我们继续讨论。
变式4：留存计算
回到我们开篇的问题，有没有觉得，需求的逻辑和留存很像。留存通常是指之前有过某种行为，后续依然有。但这里前后两种行为是不一样的。其实可以看做是另一种意义上的留存。因此上面的思路也可以用来计算留存。拿次日留存来举例，我们算一下0801-0807的次日留存。这里额外需要一张活跃表。t_active_user(uid 用户id，act_date 活跃日期)
--次留 select a.reg_date, count(distinct a.uid) new_user, count(distinct b.uid) act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190807' group by reg_date, uid --每日每组新增用户 ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保证0807有留存数据 group by act_date, uid --每日每组日活用户 ) b on a.uid = b.uid where datediff(b.act_date, a.reg_date) = 1 group by a.reg_date
你认为这段SQL有问题吗？（我们把留存的分子分母分开来计算，先不做相除）
乍一看可能不觉得有什么问题。但运行之后会发现new_user和act_user的结果是一样的。问题出在where条件已经把没有不符合条件的活跃用户过滤掉了。正确的写法应该将判断条件写到count函数中(可以仔细思考一下)，如下：
--次留 select a.reg_date, count(distinct a.uid) new_user, count(distinct case when datediff(b.act_date, a.reg_date) = 1 then b.uid else null end) act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190807' group by reg_date, uid ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保证0807有留存数据 group by act_date, uid ) b on a.uid = b.uid group by a.reg_date
同样的，7日留存也可以在此基础上稍加改动得到。但在多日留存的时候，依然也要考虑前面说的数据更新的问题。
select a.reg_date, count(distinct a.uid) new_user, count(distinct case when datediff(b.act_date, a.reg_date) = 1 then b.uid else null end) 2day_act_user， count(distinct case when datediff(b.act_date, a.reg_date) = 6 then b.uid else null end) 7day_act_user from ( select reg_date, uid from t_new_user where reg_date >='20190801' and reg_date <='20190802'--有些天还不能计算7留的数据 group by reg_date, uid ) a left join ( select act_date, uid from t_active_user where act_date >='20190801' and act_date <='20190808'--至少要到0808才能保证0802有7日留存数据 group by act_date, uid ) b on a.uid = b.uid group by a.reg_date
关于活跃留存的计算，还可能存在活跃表是全量表的情况，即每个最新分区有历史每一天的活跃用户。那写法可能又有不同了。我们可以使用下面的方法计算留存。累计活跃表：t_user_active_accu(uid 用户id，act_date 活跃日期)。这种情况我们依然可以请datediff帮助我们计算留存。只需要按照日期差进行分组，统计各个日期差的活跃用户数，最后将相同日期差的用户数求和就能算出“留存的用户”了。如下面代码计算了20190801的留存。
select sum(case when datediff=0 then user_cnt else 0 end), sum(case when datediff=1 then user_cnt else 0 end) from ( select datediff, count(distinct uid) as user_cnt from ( select uid, datediff(act_date，'2019-08-01') as datediff from t_user_active_accu where ds = '20190808'--ds表示分区，取一个最新的即可 and datediff('2019-08-01', act_date) >=0 ) a group by datediff ) b
上面两种计算留存的方式都比较好理解，使用了datediff函数，但需要注意使用的场景，一种是增量表，一种是全量表。
总结
本文我们从一道SQL题目出发，讨论了使用union all 和datediff简化代码的过程，以及由此衍生出来的相关问题。由于表是虚构的，代码并未经过完全测试，主要在于理解其中的技巧和原理。希望对读到这里的你有所帮助。如有问题欢迎指出，也欢迎交流讨论~
