如何用SQL按客户、Company、Line分组识别连续日期并计算时长与总消费

需求说明 #

• 按同一Customer、同一Company、同一Line，将**连续日期（相邻记录间隔仅1天）**的记录划分为一组
• 计算每组的核心指标：
  • Duration = (EndDate - StartDate) + 1（EndDate为组内最后一个连续日期）
  • TotalSpending = 组内Spending总和
• 需移除重复数据：同一Customer在同一日期、同一Company、同一Line下的多条记录视为重复

原始数据表定义与测试数据 #

create table tbl
(
  Company   char,
  Line      char(2),
  Customer  varchar(5),
  StartDate date,
  Spending  decimal(10,2)
);

insert into tbl values 
('A',       's1',      'Tom',         '20210202',    10.00),
('A',       's1',      'Tom',         '20210201',    10.00),
('A',       's1',      'Tom',         '20210203',    10.00),
('A',       's2',      'Tom',         '20210204',    10.00),
('A',       's2',      'Tom',         '20210206',    10.00),
('B',       's1',      'Tom',         '20210201',    15.00),
('A',       's3',      'Tom',         '20210207',    10.00),
('A',       's3',      'Ken',         '20210207',    10.00),
('C',       's1',      'Tom',         '20210201',    20.00);

当前使用的SQL代码 #

with
cte as
(
    select *, 
           g = case when Line = lag(Line) over (partition by Company, Customer 
                                                     order by StartDate)
                    then 0
                    else 1
                    end
    from   tbl
),
cte2 as
(
    select *, grp = sum(g) over (partition by Company, Customer 
                                     order by StartDate)
    from   cte
)
select Company, Line, Customer,
      StartDate = min(StartDate),
       EndDate = max(StartDate),
       Duration = datediff(day, min(StartDate), max(StartDate)) + 1,
       TotalSpending = sum(spending)
from   cte2
group by Company, Customer, Line, grp
order by Company, Customer, grp

期望输出结果 #

Company Line    Customer    StartDate   EndDate    Duration TotalSpending
A       s1      Tom         2021-02-01  2021-02-03  3       30.00
A       s2      Tom         2021-02-04  2021-02-04  1       10.00
A       s2      Tom         2021-02-06  2021-02-06  1       10.00
A       s3      Tom         2021-02-07  2021-02-07  1       10.00
B       s1      Tom         2021-02-01  2021-02-01  1       15.00
C       s1      Tom         2021-02-01  2021-02-01  1       20.00
A       s3      Ken         2021-02-07  2021-02-07  1       10.00

修正后的SQL解决方案 #

原代码存在两个核心问题：

1. 仅判断了Line是否与上一条相同，未校验日期是否连续（间隔1天）
2. 未处理重复数据

以下是修正后的代码：

WITH deduplicated AS (
    -- 第一步：去重，合并同一Customer/Company/Line/StartDate的消费
    SELECT 
        Company,
        Line,
        Customer,
        StartDate,
        SUM(Spending) AS Spending
    FROM tbl
    GROUP BY Company, Line, Customer, StartDate
),
group_marker AS (
    -- 第二步：标记连续日期的分组起点
    SELECT 
        *,
        grp_start = CASE 
            WHEN DATEDIFF(day, LAG(StartDate) OVER (PARTITION BY Company, Line, Customer ORDER BY StartDate), StartDate) > 1
                 OR LAG(StartDate) OVER (PARTITION BY Company, Line, Customer ORDER BY StartDate) IS NULL
            THEN 1
            ELSE 0
        END
    FROM deduplicated
),
group_id AS (
    -- 第三步：生成唯一分组ID
    SELECT 
        *,
        grp = SUM(grp_start) OVER (PARTITION BY Company, Line, Customer ORDER BY StartDate)
    FROM group_marker
)
-- 第四步：按分组聚合计算结果
SELECT 
    Company,
    Line,
    Customer,
    MIN(StartDate) AS StartDate,
    MAX(StartDate) AS EndDate,
    DATEDIFF(day, MIN(StartDate), MAX(StartDate)) + 1 AS Duration,
    SUM(Spending) AS TotalSpending
FROM group_id
GROUP BY Company, Line, Customer, grp
ORDER BY Company, Customer, StartDate;

代码逻辑说明 #

1. deduplicated CTE：先对重复数据去重，将同一维度下的当日消费合并，确保每个(Customer, Company, Line, StartDate)组合唯一
2. group_marker CTE：按Customer/Company/Line分区，通过LAG()函数对比当前记录与上一条的日期差，标记新分组的起点
3. group_id CTE：通过累加分组起点标记，生成每个连续组的唯一ID
4. 最终聚合：按分组ID和维度字段分组，计算每组的起始/结束日期、时长和总消费

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