按首次订单10分钟间隔分组同客户订单的SQL实现问题

需求说明 #

将同一客户的订单按如下规则分组：若订单发生在某组首次订单的10分钟内则归为该组，之后寻找下一个新的首次订单并重复分组逻辑。

示例数据 #

分组结果示例 #

Customer group  orders
   6        1      3
            2      4,5
            3      8
   7        1      9,10
            2      11,12
            3      13

原始订单数据 #

id  customer time
    3   6   2021-05-12 12:14:22.000000
    4   6   2021-05-12 12:24:24.000000
    5   6   2021-05-12 12:29:16.000000
    8   6   2021-05-12 13:01:40.000000
    9   7   2021-05-14 12:13:11.000000
    10  7   2021-05-14 12:20:01.000000
    11  7   2021-05-14 12:45:00.000000
    12  7   2021-05-14 12:48:41.000000
    13  7   2021-05-14 12:58:16.000000
    18  9   2021-05-18 12:22:13.000000
    25  15  2021-05-18 13:44:02.000000
    26  16  2021-05-17 09:39:02.000000
    27  16  2021-05-18 19:38:43.000000
    28  17  2021-05-18 15:40:02.000000
    29  18  2021-05-19 15:32:53.000000
    30  18  2021-05-19 15:45:56.000000
    31  18  2021-05-19 16:29:09.000000
    34  15  2021-05-24 15:45:14.000000
    35  15  2021-05-24 15:45:14.000000
    36  19  2021-05-24 17:14:53.000000

原代码问题 #

原CTE代码未在递归关联时按客户分组，导致case when d.StartTime > dateadd(minute, 10, c.first_time)逻辑出现跨客户比较的错误，无法正确分组。原代码如下：

with 
data as (select Customer,StartTime,Id, row_number() over(partition by Customer order by StartTime) rn from orders t),
cte as (
     select d.*, StartTime as first_time 
     from data d
     where rn = 1
     union all 
     select d.*, 
         case when d.StartTime > dateadd(minute, 10, c.first_time)
             then d.StartTime
             else c.first_time
         end
     from cte c 
    inner join data d on d.rn = c.rn + 1
 )
select c.*, dense_rank() over(partition by Customer order by first_time) grp 
from cte c;

解决方案 #

SQL Server版本 #

递归CTE需要在关联时加上Customer匹配，确保只在同一客户内递归计算分组基准时间：

WITH data AS (
    SELECT 
        Customer, 
        StartTime, 
        Id, 
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY StartTime) rn 
    FROM orders t
),
cte AS (
    SELECT 
        d.Customer,
        d.StartTime,
        d.Id,
        d.rn,
        d.StartTime AS first_time
    FROM data d
    WHERE rn = 1
    UNION ALL
    SELECT 
        d.Customer,
        d.StartTime,
        d.Id,
        d.rn,
        CASE 
            WHEN d.StartTime > DATEADD(minute, 10, c.first_time) 
            THEN d.StartTime 
            ELSE c.first_time 
        END AS first_time
    FROM cte c
    INNER JOIN data d 
        ON d.Customer = c.Customer  -- *关键：按客户关联，避免跨客户递归*
        AND d.rn = c.rn + 1
)
SELECT 
    Customer,
    Id,
    StartTime,
    DENSE_RANK() OVER(PARTITION BY Customer ORDER BY first_time) AS grp
FROM cte
ORDER BY Customer, rn;

MySQL版本 #

MySQL 8.0+支持递归CTE，逻辑与SQL Server一致，仅时间函数略有差异：

WITH RECURSIVE data AS (
    SELECT 
        Customer, 
        StartTime, 
        Id, 
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY StartTime) rn 
    FROM orders t
),
cte AS (
    SELECT 
        d.Customer,
        d.StartTime,
        d.Id,
        d.rn,
        d.StartTime AS first_time
    FROM data d
    WHERE rn = 1
    UNION ALL
    SELECT 
        d.Customer,
        d.StartTime,
        d.Id,
        d.rn,
        CASE 
            WHEN d.StartTime > DATE_ADD(c.first_time, INTERVAL 10 MINUTE) 
            THEN d.StartTime 
            ELSE c.first_time 
        END AS first_time
    FROM cte c
    INNER JOIN data d 
        ON d.Customer = c.Customer  -- *关键：按客户关联，避免跨客户递归*
        AND d.rn = c.rn + 1
)
SELECT 
    Customer,
    Id,
    StartTime,
    DENSE_RANK() OVER(PARTITION BY Customer ORDER BY first_time) AS grp
FROM cte
ORDER BY Customer, rn;

核心说明 #

• 递归CTE中通过d.Customer = c.Customer确保只在同一客户内进行基准时间的递归计算，彻底解决跨客户比较的问题。
• 最终通过DENSE_RANK()对同一客户的first_time分组，得到每个订单所属的组号。

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