【SQL】大数据高频面试题之连续活跃天数

  数据岗经常分析的一个指标是连续登录天数，今天来学习求解连续登录天数问题的一般流程。

  对于连续天数问题的求解，一般会用到窗口函数 row_number()，求解的思路如下：

假定用户的登录日期是：

uid	log_dt
1	2022-02-03
1	2022-02-06
1	2022-02-07
1	2022-02-08
1	2022-02-11
1	2022-02-12

使用 row_number() 对 log_dt 进行编号：

uid	log_dt	rn
1	2022-02-03	1
1	2022-02-06	2
1	2022-02-07	3
1	2022-02-08	4
1	2022-02-11	5
1	2022-02-12	6

使用 log_dt 减去 rn 得到 group_dt：

uid	group_dt
1	2022-02-02
1	2022-02-04
1	2022-02-04
1	2022-02-04
1	2022-02-06
1	2022-02-06

次数我们发现，对于连续登录的日期，减去 row_number 之后得到的日期是同一天！此时答案也呼之欲出了，对 group_dt 进行 group by 操作，再使用 cnt，这个 cnt 值就是连续登录的天数：

uid	group_dt	cnt
1	2022-02-02	1
1	2022-02-04	3
1	2022-02-06	2

所以最大连续登录天数就是 3 天。下面通过一道例题来实战训练。

牛客SQL29.某宝店铺连续2天及以上购物的用户及其对应的天数^[1]

描述

11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。

11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

sales_date	user_id	item_id	sales_num	sales_price
2021-11-01	1	A001	1	90
2021-11-01	2	A002	2	220
2021-11-01	2	B001	1	120
2021-11-02	3	C001	2	500
2021-11-02	4	B001	1	120
2021-11-03	5	C001	1	240
2021-11-03	6	C002	1	270
2021-11-04	7	A003	1	180
2021-11-04	8	B002	1	140
2021-11-04	9	B001	1	125
2021-11-05	10	B003	1	120
2021-11-05	10	B004	1	150
2021-11-05	10	A003	1	180
2021-11-06	11	B003	1	120
2021-11-06	10	B004	1	150

请你统计连续2天及以上在该店铺购物的用户及其对应的次数（若有多个用户，按user_id升序排序），以上例子的输出结果如下：

user_id	days_count
10	2

用上面讲解的方法进行求解。为了清晰展现求解过程，这里使用了 with 表达式：

with t1 as(
    select user_id,sales_date,
        row_number() over(partition by user_id order by sales_date) as rn
    from sales_tb
),
t2 as(
    select user_id,sales_date,
        date_sub(sales_date,interval rn day) as group_dt
    from t1
)
select user_id,count(1) as days_cnt
from t2
group by user_id,group_dt
having days_cnt>=2;

t1 表如下：

user_id	sales_date	rn
1	2021-11-01	1
2	2021-11-01	1
2	2021-11-01	2
3	2021-11-02	1
4	2021-11-02	1
5	2021-11-03	1
6	2021-11-03	1
7	2021-11-04	1
8	2021-11-04	1
9	2021-11-04	1
10	2021-11-05	1
10	2021-11-05	2
10	2021-11-05	3
10	2021-11-06	4
11	2021-11-06	1

t2 表如下：

user_id	sales_date	group_dt
1	2021-11-01	2021-10-31
2	2021-11-01	2021-10-31
2	2021-11-01	2021-10-30
3	2021-11-02	2021-11-01
4	2021-11-02	2021-11-01
5	2021-11-03	2021-11-02
6	2021-11-03	2021-11-02
7	2021-11-04	2021-11-03
8	2021-11-04	2021-11-03
9	2021-11-04	2021-11-03
10	2021-11-05	2021-11-04
10	2021-11-05	2021-11-03
10	2021-11-05	2021-11-02
10	2021-11-06	2021-11-02
11	2021-11-06	2021-11-05

欢迎关注公众号。

Reference

[1]牛客SQL29.某宝店铺连续2天及以上购物的用户及其对应的天数：https://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf?tpId=268&tqId=2285906&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268