SQL统计连续问题

连续登录3天及以上的用户、连续提交5天及以上的用户、连续2天及以上购买商品的用户及其对应的天数（连续购物的天数）

数据开发，面试常见问题，统计连续x天的用户，SQL编程题： 牛客 | SQL184 某宝店铺连续2天及以上购物的用户及其对应的天数

一 题目描述

请你统计连续2天及以上在该店铺购物的用户及其对应的天数（若有多个用户，按 user_id 升序）。

销售数据表 sales_tb：

drop table if exists sales_tb;
create table sales_tb
(
    sales_date  date     NOT NULL comment '销售日期',
    user_id     int(10)  NOT NULL comment '用户编号',
    item_id     char(10) NOT NULL comment '货号',
    sales_num   int(10)  NOT NULL comment '销售数量',
    sales_price int(10)  NOT NULL comment '结算金额'
);

假如测试数据为：

truncate sales_tb;
INSERT INTO sales_tb VALUES('2021-11-1', 1, 'A001',  1, 90);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'A002',  2, 220);
INSERT INTO sales_tb VALUES('2021-11-1', 2, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-2', 3, 'C001',  2, 500);
INSERT INTO sales_tb VALUES('2021-11-2', 4, 'B001',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-3', 5, 'C001',  1, 240);
INSERT INTO sales_tb VALUES('2021-11-3', 6, 'C002',  1, 270);
INSERT INTO sales_tb VALUES('2021-11-4', 7, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-4', 8, 'B002',  1, 140);
INSERT INTO sales_tb VALUES('2021-11-4', 9, 'B001',  1, 125);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'B004',  1, 150);
INSERT INTO sales_tb VALUES('2021-11-5', 10, 'A003',  1, 180);
INSERT INTO sales_tb VALUES('2021-11-6', 11, 'B003',  1, 120);
INSERT INTO sales_tb VALUES('2021-11-6', 10, 'B004',  1, 150);

则输出结果：

user_id	days_cnt
10	2

二 解题思路

1. 注意事项

• 连续2天及以上购物（中间不能有间隔）
• 多用户时，按 user_id 升序（SQL默认排序顺序为：升序排列）

2. 预备知识

对单个用户来讲，如果连续购物，那用购物日期减去真实序列（行号），得到的，是相同的值。

举个例子：

-- sales_date row_number() diff
-- 2022-10-14 1            2022-10-13
-- 2022-10-15 2            2022-10-13
-- 2022-10-16 3            2022-10-13
-- 2022-10-18 4            2022-10-14
-- 可以看到，连续的日期，得到的diff值，是相同的
-- 只要计算diff的count，即可计算出连续的天数

3. 具体步骤

-- 1. 先对 sales_date, user_id 去重
--（只需要考虑用户这天买没买，不用考虑买几次）
-- 2. 利用 row_number() 函数，按 user_id 分区，按日期排序，加行号
-- 3. 用购物日期减去行号得到 diff
-- 4. 按 user_id, diff 分组，计算连续购物天数
-- 5. 筛选满足连续天数的用户，去重，排序，得到最终结果

with t1 as (
  select distinct sales_date, user_id from sales_tb
),
t2 as (
  select sales_date, user_id, row_number() over(partition by user_id order by sales_date) as rn from t1
),
t3 as (
  select sales_date, user_id, sales_date - rn as diff from t2
),
t4 as (
  select user_id, count(diff) as days_cnt from t3 group by user_id, diff
)
select distinct user_id, days_cnt from t4 where days_cnt >= 2 order by user_id;

---

参考大佬的博客，不胜感激 😃
Hive SQL 五大经典面试题