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

下图为一张订单表（order1），现希望查找出至少连续3天下单的用户。

第一步：将订单表按UserId分组根据日期Date排序

第二步：用日期Date减去对应的排序号Num，若日期是连续的，则相减的结果Datedif相等。

第三步：按UserId，Datedif分组计数，得到各用户的连续下单天数。

第四步：筛选连续下单天数≥n的用户

# 订单表  order1
# UserId  Date  Orders
# step1: 将订单表按UserId分组根据日期Date排序
 SELECT UserId,`Date`,orders,
 row_number() over(PARTITION BY UserId ORDER BY DATE)Num
 FROM order1
 # step2: 用日期Date减去对应的排序号Num，若日期是连续的，则相减的结果Datedif相等。
 SELECT UserId,`Date`,`Date`-Num  AS DateDif
 FROM 
 (
 SELECT UserId,`Date`,orders,
 row_number() over(PARTITION BY UserId ORDER BY DATE)Num
 FROM order1
 )a
 # step3:按UserId，Datedif分组计数，得到各用户的连续下单天数。
 SELECT UserId,COUNT(1) AS Consecutive
 FROM (
  SELECT UserId,`Date`,`Date`-Num  AS DateDif
 FROM 
 (
 SELECT UserId,`Date`,orders,
 row_number() over(PARTITION BY UserId ORDER BY DATE)Num
 FROM order1
 )a
 )b
 GROUP BY UserId,DateDif
 # 筛选连续下单天数≥3的用户
 SELECT Userid
 FROM
 (
 SELECT UserId,COUNT(1) AS Consecutive
 FROM (
 SELECT UserId,`Date`,`Date`-Num  AS DateDif
 FROM 
 (
 SELECT UserId,`Date`,orders,
 row_number() over(PARTITION BY UserId ORDER BY DATE)Num
 FROM order1
 )a
 )b
 GROUP BY UserId,DateDif
 )c
 WHERE Consecutive>=3