如何在T-SQL中按日期升序实现智能分组（含数据集示例）

示例数据集 #

+-------+----------+----------------+
| ID    | SUM      |  DATE          |
+-------+----------+----------------+
|     8 |        0 |     2023-01-01 |
|     8 |        0 |     2023-01-02 |
|     8 |       10 |     2023-01-03 |
|     8 |        0 |     2023-01-04 |
|     8 |      200 |     2023-01-05 |
|     8 |      200 |     2023-01-06 |
|     8 |      200 |     2023-01-07 |
|     8 |      200 |     2023-01-08 |
|     8 |      200 |     2023-01-09 |
|   778 |      200 |     2023-10-25 |
|   778 |      200 |     2023-10-26 |
+-------+----------+----------------+

期望分组结果 #

按ID分组，对日期升序排列的连续相同SUM值分组，取每组最小日期，结果如下：

+-------+----------+----------------+
| ID    | SUM      |  DATE          |
+-------+----------+----------------+
|     8 |        0 |     2023-01-01 |
|     8 |       10 |     2023-01-03 |
|     8 |        0 |     2023-01-04 |
|     8 |      200 |     2023-01-05 |
|   778 |      200 |     2023-10-25 |
+-------+----------+----------------+

尝试的错误查询及问题 #

原查询直接按ID和SUM分组，会将非连续的相同SUM合并，导致ID=8、SUM=0、日期2023-01-04的分组被合并到之前的0值组中，缺失该分组：

SELECT
      [ID],
      [SUM],
      MIN([DATE]) AS [Date]
 FROM [dbo].[test] 
 GROUP BY [ID], [SUM]

错误结果：

+-------+----------+----------------+
| ID    | SUM      |  DATE          |
+-------+----------+----------------+
|     8 |        0 |     2023-01-01 |
|     8 |       10 |     2023-01-03 |
|     8 |      200 |     2023-01-05 |
|   778 |      200 |     2023-10-25 |
+-------+----------+----------------+

解决方案 #

使用窗口函数LAG()识别连续相同SUM的分组，再按分组聚合：

WITH grouped_data AS (
    SELECT 
        ID,
        SUM,
        DATE,
        -- 同一ID下，当前行SUM与上一行不同时生成新分组标识
        SUM(CASE 
            WHEN LAG(SUM) OVER (PARTITION BY ID ORDER BY DATE) != SUM 
                 OR LAG(SUM) OVER (PARTITION BY ID ORDER BY DATE) IS NULL 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY ID ORDER BY DATE) AS group_id
    FROM [dbo].[test]
)
SELECT 
    ID,
    SUM,
    MIN(DATE) AS DATE
FROM grouped_data
GROUP BY ID, SUM, group_id
ORDER BY ID, DATE;

逻辑说明 #

1. LAG(SUM) OVER (PARTITION BY ID ORDER BY DATE)：获取同一ID下，按日期排序的前一行SUM值
2. 对比当前行SUM与前一行，若不同或为组内第一行（LAG返回NULL），则标记为新分组，通过累加生成唯一的group_id
3. 最终按ID、SUM、group_id分组，取每组最小日期，即可得到连续相同SUM的分组结果

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