sql之每天一个小函数之几个实用小函数

今天讲
业务中常见的处理连续活跃同类型问题
这类问题常用的函数为lag窗口函数,datadiff日期函数等,今天就介绍这两类函数

lag与lead函数可以返回上下行的数据
语法:lead(col,n,default) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

日期比较函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

下面通过案例来演示

建表sql如下:
create table active AS
  SELECT 100 UID,
              '2021-04-01'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-01'dt
   UNION ALL SELECT 100 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-02'dt
   UNION ALL SELECT 100 UID,
                        '2021-04-03'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-03'dt
   UNION ALL SELECT 101 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 103 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 104 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 105 UID,
                        '2021-04-04'dt
   UNION ALL SELECT 102 UID,
                        '2021-04-03'dt

需求为:
（1） 某APP用户活跃记录表active，有uid（用户id）、dt（活跃日期）字段，求出连续出勤3天及以上的用户数

分析:
1.首先要找出每个员工对应的UID和该员工的工作时间, 筛选出用户的连续工作的日期
2.判断每个UID下的连续时间为三天的
这里就要用到lag开窗函数,需要保证员工该日期前两天有数据才证明有连续工作的情况,因此lag(col,n,default)中的字段应该为dt,n为2
代码如下:

第一步:
SELECT UID,
               dt,
               lag(dt,2)over(PARTITION BY UID ORDER BY dt)dt2
        FROM active

跑出的数据如下图

第二步

select
count(DISTINCT uid)
from(
        SELECT UID,
               dt,
               lag(dt,2)over(PARTITION BY UID ORDER BY dt)dt2
        FROM active
        )x
where datediff(dt,dt2)=2

最后的判断条件,datediff函数,结束日期为dt2,开始日期为dt(参照上图内层函数跑出的结果理解)
跑出的结果如下图

（2） 某APP用户活跃记录表active，有uid（用户id）、dt（活跃日期）字段，求每个用户的最大连续活跃天数

惯例分析:
1.求最大连续活跃天数,这里我们用lag或者lead函数就很简单了lag(dt,1),每个日期往前或者往后一天,判断日期前后的差值,为 1,则说明前后两天连续,否则不连续(切记不能直接从建好的表里找数据,这只是案例,如果又十万条数据呢?看表的目的只能是获取字段)

第一步与上题同理,略过了,直接第二步

select
        UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue
        from(
                SELECT UID,
                       dt,
                       lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2
                FROM active
                )x

结果如下图
这里注意最后一个新生字段if_continue 说明了 if 和datediff这两个函数结合之后的功能,判断是否连续(即差值为1),如果是,返回1,否则为0,有点像case when 的原理

第三步:

select
    UID,dt,max(if(if_continue=0,dt,null))over(PARTITION BY UID ORDER BY dt)start_dt
    from(
        select
        UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue
        from(
                SELECT UID,
                       dt,
                       lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2
                FROM active
         )x
    )y

这里尤其要注意这个max的用法,日期的比较靠前的为大的,这里很多人应该不明白为什么,解释起来很麻烦,要考虑到同一个人有断开的现象,比如上图中的101号的就有这种情况,所以我们要拿到连续日期的最早的一天
结果如下
第四步:

select  UID,max(datediff(dt,start_dt))days
from(
    select
    UID,dt,max(if(if_continue=0,dt,null))over(PARTITION BY UID ORDER BY dt)start_dt
    from(
        select
        UID,dt,dt2,if(datediff(dt,dt2)=1,1,0)if_continue
        from(
                SELECT UID,
                       dt,
                       lag(dt,1)over(PARTITION BY UID ORDER BY dt)dt2
                FROM active
         )x
    )y
)z
group by UID

结果为
差不多就是这样了,筒子们zaiwei!