查询至少连续三天下单的用户

1 需求描述

  查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下

2 order_info表结构

  订单信息表部分数据如下

3 建表语句

DROP TABLE IF EXISTS order_info;
create table order_info(
    `order_id`     string COMMENT '订单id',
    `user_id`      string COMMENT '用户id',
    `create_date`  string COMMENT '下单日期',
    `total_amount` decimal(16, 2) COMMENT '订单总金额'
) COMMENT '订单表'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

4 数据装载

insert overwrite table order_info
values ('1', '101', '2021-09-27', 29000.00),
       ('2', '101', '2021-09-28', 70500.00),
       ('3', '101', '2021-09-29', 43300.00),
       ('4', '101', '2021-09-30', 860.00),
       ('5', '102', '2021-10-01', 46180.00),
       ('6', '102', '2021-10-01', 50000.00),
       ('7', '102', '2021-10-01', 75500.00),
       ('8', '102', '2021-10-02', 6170.00),
       ('9', '103', '2021-10-02', 18580.00),
       ('10', '103', '2021-10-02', 28000.00),
       ('11', '103', '2021-10-02', 23400.00),
       ('12', '103', '2021-10-03', 5910.00),
       ('13', '104', '2021-10-03', 13000.00),
       ('14', '104', '2021-10-03', 69500.00),
       ('15', '104', '2021-10-03', 2000.00),
       ('16', '104', '2021-10-03', 5380.00),
       ('17', '105', '2021-10-04', 6210.00),
       ('18', '105', '2021-10-04', 68000.00),
       ('19', '105', '2021-10-04', 43100.00),
       ('20', '105', '2021-10-04', 2790.00),
       ('21', '106', '2021-10-04', 9390.00),
       ('22', '106', '2021-10-05', 58000.00),
       ('23', '106', '2021-10-05', 46600.00),
       ('24', '106', '2021-10-05', 5160.00),
       ('25', '107', '2021-10-05', 55350.00),
       ('26', '107', '2021-10-05', 14500.00),
       ('27', '107', '2021-10-06', 47400.00),
       ('28', '107', '2021-10-06', 6900.00),
       ('29', '108', '2021-10-06', 56570.00),
       ('30', '108', '2021-10-06', 44500.00),
       ('31', '108', '2021-10-07', 50800.00),
       ('32', '108', '2021-10-07', 3900.00),
       ('33', '109', '2021-10-07', 41480.00),
       ('34', '109', '2021-10-07', 88000.00),
       ('35', '109', '2020-10-08', 15000.00),
       ('36', '109', '2020-10-08', 9020.00),
       ('37', '1010', '2020-10-08', 9260.00),
       ('38', '1010', '2020-10-08', 12000.00),
       ('39', '1010', '2020-10-08', 23900.00),
       ('40', '1010', '2020-10-08', 6790.00);

5 SQL实现

1）实现方式1

  如下图所以，可以先按照user_id分组对create_date进行排序，如果create_date是连续的，create_date和排序序号rn的差值（diff）就会相等，可以看出user_id的相等diff的个数大于3，即为连续3天下单的用户

SQL实现如下

select distinct user_id -- 对用户多个连续下单进行去重
from (
         select user_id
         from (
                  select user_id
                       , create_date
                       , date_sub(create_date, row_number() over (partition by user_id order by create_date)) diff
                  from (
                           select user_id
                                , create_date
                           from order_info
                           group by user_id, create_date
                       ) t1 -- 同一天可能多个用户下单，进行去重
              ) t2 -- 判断一串日期是否连续：若连续，用这个日期减去它的排名，会得到一个相同的结果
         group by user_id, diffdate
         having count(diffdate) >= 3 -- 连续下单大于等于三天
     ) t3;

2）实现方式2

  如下图所示，使用偏移量lead函数，按照user_id分组，create_date排序，将create_date向下移动2位，如果create_date向下移动2位后与create_date差值为2，即为连续3天下单的用户

SQL实现如下：

select distinct user_id -- 对用户多个连续下单进行去重
from (
    select user_id
        ,create_date
        ,datediff((lead(create_date,2,'2099-12-31') over (partition by user_id order by create_date)) , create_date) date_diff
    from (
        select user_id
            ,create_date
        from order_info
        group by user_id,create_date -- 同一天可能多个用户下单，进行去重
        ) t1
    ) t2
where date_diff = 2

3）实现方式3

  如下图所示，开窗求count()，窗口范围设置为当前时间的前一天和当前时间的后一天，如果count()=3，即为连续3天下单的用户

SQL实现

select distinct user_id -- 对用户多个连续下单进行去重
from (
    select user_id
        ,count(*) over(partition by user_id order by ts range between 86400 preceding and 86400 following) cn
    from (
        select user_id
            ,unix_timestamp(create_date,'yyyy-MM-dd') ts
        from (
            select user_id
                ,create_date
            from order_info
            group by user_id,create_date -- 同一天可能多个用户下单，进行去重
            ) t1
        ) t2
    ) t3 
where cn = 3