如何用MySQL查询存在连续下单日的客户及分段连续天数

需求说明 #

• 获取存在连续下单日期的客户ID及各分段连续天数
• 连续下单定义：日期连续，单日多单不影响
• 同一客户的不同连续段需分开统计（例如客户4有2023-06-24至2023-06-27、2023-06-29至2023-06-30两个连续段，需分别输出）

表结构 #

create table orders(
  orderid INT,
  orderdate date,
  customerid int
);

测试数据 #

insert into orders (orderid, orderdate, customerid)
values(1,'2023-06-20',1),
    (2, '2023-06-21', 2),
    (3, '2023-06-22', 3),
    (4, '2023-06-22', 1),
    (5, '2023-06-23', 3),
    (6, '2023-06-22', 1),
    (7, '2023-06-26', 4),
    (8, '2023-06-27', 4),
    (9, '2023-06-29', 4),
    (10, '2023-06-29', 5),
    (11, '2023-06-30', 5),
    (12, '2023-06-28', 5),
    (13, '2023-06-25', 4),
    (14, '2023-06-24', 4),
    (15, '2023-06-30', 4);

当前SQL及问题 #

现有SQL：

with t1 as(
select customerid, orderdate,
case when lead(orderdate) over (partition by customerid order by orderdate) is null then 1
else abs(orderdate - lead(orderdate) over (partition by customerid order by orderdate)) end as gap
from orders)
select customerid, sum(gap) as consecutive
from t1
where gap>0
group by customerid
having count(*)=sum(gap) and count(*)>1;

现有输出：

+------------+------------------+
| customerid | consecutive_days |
+------------+------------------+
|          3 |                2 |
|          5 |                3 |
+------------+------------------+

问题：无法识别同一客户的多个连续段，遗漏了客户4的两个连续分段统计。

期望输出 #

+------------+------------------+
| customerid | consecutive_days |
+------------+------------------+
|          3 |                2 |
|          4 |                4 |
|          4 |                2 |
|          5 |                3 |
+------------+------------------+

解决方案SQL #

核心思路：先对客户下单日期去重，再通过日期间隔标记连续段，最后按客户+连续段分组统计天数。

WITH customer_dates AS (
    -- 去重每个客户的下单日期，排除单日多单干扰
    SELECT DISTINCT customerid, orderdate
    FROM orders
),
date_groups AS (
    SELECT 
        customerid,
        orderdate,
        -- 生成连续段分组ID：当前日期与前一天间隔大于1时，开启新分组
        SUM(CASE 
                WHEN DATEDIFF(orderdate, LAG(orderdate) OVER (PARTITION BY customerid ORDER BY orderdate)) > 1 
                THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY customerid ORDER BY orderdate) AS group_id
    FROM customer_dates
)
SELECT 
    customerid,
    COUNT(*) AS consecutive_days
FROM date_groups
GROUP BY customerid, group_id
HAVING COUNT(*) > 1; -- 仅保留连续天数大于1的分段

结果验证 #

执行上述SQL后，将得到与期望一致的输出，准确统计每个客户的所有连续下单分段天数。

内容的提问来源于stack exchange，提问作者user23860092