来看一道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的留存。
上面两种计算留存的方式都比较好理解,使用了datediff函数,但需要注意使用的场景,一种是增量表,一种是全量表。
总结
本文我们从一道SQL题目出发,讨论了使用union all 和datediff简化代码的过程,以及由此衍生出来的相关问题。由于表是虚构的,代码并未经过完全测试,主要在于理解其中的技巧和原理。希望对读到这里的你有所帮助。如有问题欢迎指出,也欢迎加群交流讨论~
觉得内容还不错的话,请分享给更多的朋友哦
01. 公众号后台回复「06」领取「数据仓库」「数据治理」等经典电子书籍或视频赠送。
02. 如要获取《大牛带你从0到1建设数据仓库》实战高清PPT或数仓实战视频,请关注公众号后添加小助手微信[ ID:iom1128 昵称:紫霞仙子],备注:PPT。
03. 高手如云,拉您入群「数仓BI」,「Python」、「资料分享」,公众号后台回复:加群。技术大佬们在等你,各种资源定期分享~
Q: 关于数据仓库,你还想了解什么?
欢迎加群与大家分享
觉得不错,请把这篇文章分享给你的朋友哦
加群请联系小助手:iom1128『紫霞仙子』
更多干货、福利,请在后台点击“紫霞秘籍”查看
!关注不迷路~ 各种福利、资源定期分享!