专栏首页数据森麟一道SQL题的前世今生

一道SQL题的前世今生

本文转载自超哥的杂货铺,详情可以扫描下方二维码:

以清净心看世界;

用欢喜心过生活。

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

来看一道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简化代码的过程,以及由此衍生出来的相关问题。由于表是虚构的,代码并未经过完全测试,主要在于理解其中的技巧和原理。希望对读到这里的你有所帮助。如有问题欢迎指出,也欢迎交流讨论~

本文分享自微信公众号 - 数据森麟(shujusenlin)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python 3.9 也要来了?

    Python 发布了版本号为 3.9.0b3 的 beta 版,后续即将发布 Python 3.9 的正式版。该版本包含了一些令人兴奋的新特性,预计正式版发布以...

    数据森麟
  • 你的灵魂画作都去哪儿了?“猜画小歌”背后的5000万组数据

    自从退出中国的搜索引擎市场,谷歌大概无时不刻都想”卷土重来“。昨天,这家以搜索引擎著称的巨头公司用一款微信小程序“猜画小歌”占领了朋友圈“C位”,刷足存在感的同...

    数据森麟
  • Python 爬取 4027 条脉脉职言,解读互联网人的苦与难!

    脉脉是一个实名职场社交平台。之前爬了脉脉职言版块,大概爬了4027条评论,本文对爬取过程给出详细说明,对于评论内容仅做可视化分析。

    数据森麟
  • 字符串与编码

    在最新的Python 3版本中,字符串是以Unicode编码的,即Python的字符串支持多语言

    py3study
  • 松哥手把手带你捋一遍 Spring Security 登录流程

    为什么想和大家捋一捋 Spring Security 登录流程呢?这是因为之前小伙伴们的一个提问:如何在 Spring Security 中动态修改用户信息?

    江南一点雨
  • 重构 - 你为什么要对参数赋值?

    对于学过多门语言的开发者来说,应该都明白在不同语言中对参数赋值有着不同的意义, 总的来说参数有值传递和引用传递两种, 而在Java中只有值传递的概念。

    PhoenixZheng
  • Rainbond-Java源码构建自定义JDK版本

    Rainbond官方提供了多个版本的OpenJDK供用户使用。这些OpenJDK的安装包托管于好雨科技官方的OSS(对象存储)中。能够接入互联网的Rainbon...

    Rainbond开源
  • #算法基础#选择和插入排序

    算法是基础,小蓝同学准备些总结一系列算法分享给大家,这是第二篇《选择和插入排序》,非常赞!希望对大家有帮助,大家会喜欢! 系列文章: 由快速排序到分治思想 ...

    大数据和云计算技术
  • 号称完全自主研发的国产编程语言「木兰」实为 Python 套壳,官方致歉!

    1 月 15 日,一篇题为「面向智能物联 中国科研团队发布 “木兰” 编程语言体系」的文章刷爆了朋友圈。

    iMike
  • UWP 手绘视频创作工具技术分享系列 - 手绘视频导出

    手绘视频最终的生成物是视频文件,前面几篇主要讲的是手绘视频的创作部分,今天讲一下手绘视频的导出问题。主要以 UWP 为例,另外会介绍一些 Web 端遇到的问题和...

    Shao Meng

扫码关注云+社区

领取腾讯云代金券