连续记录统计查询

最近在做一些运营活动时，发现经常希望查询连续操作行为，例如对于下单业务，希望查询近一周连续下单的用户ID，用户下单天数，共下单数，最大连续下单天数，当前已连续下单天数。

针对这样的需求，之前一直很头疼，根本不知道如何捕捉连续性，为了满足这样的需求，有时甚至引入缓存来去重记录每个用户每日的下单，然后根据缓存值判断连续，但这种做法相当消耗成本，每次都得定制开发，很多时候运营在策划时看到需要的成本如此之大，往往便舍去了这个活动方案。

后来在一些大神的指引下，对于连续性了解到一种比较巧妙的SQL查询方式，以oracle查询举例

计算时间差：

(to_date('20210128', 'yyyyMMdd')-to_date(days, 'yyyyMMdd')) diff

利用分区分组函数获取rn

row_number() over(partition by userid ORDER BY days DESC) rn

若diff与rn的差值不变，则说明操作在连续发生

下面我们一步步来解决上述举例的需求（查询近一周连续下单的用户ID，用户下单天数，共下单数，最大连续下单天数，当前已连续下单天数）：

1、根据用户ID分组查询出用户总体下单情况(用户ID，用户下单天数，共下单数)：

--根据时间过滤基础数据
ticket as (
SELECT * FROM LOTTERY_TICKET WHERE 1=1
AND BET_DATE >= to_date('20200920 00:00:00','yyyyMMdd hh24:mi:ss')
AND BET_DATE <= to_date('20200927 23:59:59','yyyyMMdd hh24:mi:ss')
),
--用户ID，下单天数，下单数
buydays as (
SELECT userid,count(distinct(days))day_num,count(bet_id)bet_num FROM (
SELECT userid,to_char(bet_date, 'yyyyMMdd')days,bet_id FROM ticket
)GROUP BY userid
)

这里为了方便数据查看，指定了2020年9月份其中7天的数据进行统计，结果如下

2、根据用户ID与日期分组，查询用户每天的下单情况(用户ID，下单日期，当天下单数)：

--用户每日下单情况
userday as(
SELECT userid,days,count(1) FROM (
SELECT userid,to_char(bet_date, 'yyyyMMdd')days,bet_id FROM ticket
)GROUP BY userid,days
)

3、使用提到的巧妙方式，查询diff与rn(用户ID，下单日期，当天下单数)：

--查询diff与rn
contis as(
SELECT userid,days,(to_date('20200928', 'yyyyMMdd')-to_date(days, 'yyyyMMdd')) diff,
row_number() over(partition by userid ORDER BY days DESC) rn
FROM userday
)

注意此处20200928这个基础时间的选取，当20200927日下单，则diff于rn的差值为0及后续查出的dr为0，若20200926日下单，则diff于rn的差值为1及后续查出的dr为1，因此当dr为0或1时可查询当前连续天数(将20200927设为当前日)

4、计算每日diff与rn的差值(用户ID，下单日期，diff与rn差)

contis2 as(
SELECT userid,days,(diff-rn)dr FROM contis
)

从结果可以看出1976007这个用户在20200925与20200924这两天差值都是2，所以是连续下单，根据步骤2可以印证这个结果

5、根据dr分组，查询出每个用户的连续天数(用户ID，dr，连续天数)

contis3 as(
SELECT userid,dr,count(1)cons FROM contis2 GROUP BY userid,dr
)

我们就可以看到1976007的连续天数为2天，与步骤4结果吻合

6、查询最大连续天数(用户ID，最大连续天数)

contis4 as(
SELECT userid,max(cons)mcos FROM contis3 group BY userid
)

7、查询当前已连续天数，此例子当前日期设为20200927，根据步骤3的分析如下(用户ID，当前连续天数)

contis5 as(
SELECT userid,count(1)cons FROM contis2 WHERE dr IN(0,1) GROUP BY userid
)

由于当前数据在20200927及20200926都没有记录，所以查出当前连续数据为空，大家可以自行验证。

 

以下为完整SQL

WITH 
ticket as (
SELECT * FROM LOTTERY_TICKET WHERE 1=1
AND BET_DATE >= to_date('20200920 00:00:00','yyyyMMdd hh24:mi:ss')
AND BET_DATE <= to_date('20200927 23:59:59','yyyyMMdd hh24:mi:ss')
),
--下单天数，下单数
buydays as (
SELECT userid,count(distinct(days))day_num,count(bet_id)bet_num FROM (
SELECT userid,to_char(bet_date, 'yyyyMMdd')days,bet_id FROM ticket
)GROUP BY userid
),
--用户每日下单情况
userday as(
SELECT userid,days,count(1) FROM (
SELECT userid,to_char(bet_date, 'yyyyMMdd')days,bet_id FROM ticket
)GROUP BY userid,days
),
contis as(
SELECT userid,days,(to_date('20200928', 'yyyyMMdd')-to_date(days, 'yyyyMMdd')) diff,
row_number() over(partition by userid ORDER BY days DESC) rn
FROM userday
),
contis2 as(
SELECT userid,days,(diff-rn)dr FROM contis
),
contis3 as(
SELECT userid,dr,count(1)cons FROM contis2 GROUP BY userid,dr
),
--用户最大连续天数
contis4 as(
SELECT userid,max(cons)mcos FROM contis3 group BY userid
),
--用户当前连续天数
contis5 as(
SELECT userid,count(1)cons FROM contis2 WHERE dr IN(0,1) GROUP BY userid
)
SELECT a.userid as "用户ID",
a.day_num as "下单天数",
a.bet_num as "下单数",
b.mcos as "最大连续天数",
nvl(c.cons, 0) as "当前连续天数"
FROM buydays a LEFT JOIN contis4 b ON a.userid = b.userid
LEFT JOIN contis5 c ON a.userid = c.userid;

 

至此，对于连续操作的sql统计查询已完成，希望能给大家带来实际使用价值