一道SQL题的前世今生

点击上方“超哥的杂货铺”,轻松关注

640?wx_fmt=gif

来看一道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新增用户数和新装用户数以及当日新增用户和新装用户在当天,接下来三天,接下来七天产生收藏行为和加购物车行为的人数(请确保你理解了需求)。输出格式如下:

640?wx_fmt=png 点击图片查看细节

题目中涉及到了两类用户两种行为三种日期,直观的想法是可以化整为零,先缩小范围,看一种用户一个日期的行为,然后类推解决。(比如先看下新增用户三日收藏用户数,加购用户数)。我们看一下这种思路的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简化代码的过程,以及由此衍生出来的相关问题。由于表是虚构的,代码并未经过完全测试,主要在于理解其中的技巧和原理。希望对读到这里的你有所帮助。如有问题欢迎指出,也欢迎交流讨论~

640?wx_fmt=png

640?wx_fmt=jpeg

以清净心看世界;

用欢喜心过生活。

超哥的杂货铺,你值得拥有~

长按二维码关注我们

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值