基于90天日期差分组，按ID返回对应最大DateField的SQL实现

我来帮你解决这个按日期间隔分组的SQL问题，这是一个很常见的时序数据分组场景，用窗口函数就能优雅实现：

首先先确认原始表结构和测试数据：

CREATE TABLE Dummy_Data ( 
    ID INT, 
    TextField VARCHAR(20), 
    DateField DATE 
);

INSERT INTO Dummy_Data (ID, TextField, DateField) 
VALUES 
    (1, 'Random Text', '2018-01-04'), 
    (1, 'Random Text', '2018-02-04'), 
    (1, 'Random Text', '2018-05-01'), 
    (2, 'Random Text', '2017-01-14'), 
    (2, 'Random Text', '2017-05-01'), 
    (2, 'Random Text', '2018-01-01'), 
    (2, 'Random Text', '2018-02-01'), 
    (3, 'Random Text', '2018-01-04');

需求回顾 #

• 针对同一ID的记录：
  1. 如果所有相邻行的DateField天数差都小于90天，返回该ID的最大DateField
  2. 如果存在相邻行天数差大于90天，以这个间隔为节点拆分分组，返回每个分组的最大DateField

预期示例结果 #

• ID=1的所有相邻日期差均小于90天，预期返回：

ID  TextField     DateField
---------------------------
1   Random Text   2018-05-01

• ID=2存在相邻日期差大于90天的情况，预期返回：

ID  TextField     DateField
---------------------------
2   Random Text   2017-01-14
2   Random Text   2017-05-01
2   Random Text   2018-02-01

解决方案SQL #

WITH RankedDates AS (
    SELECT 
        ID,
        TextField,
        DateField,
        -- 获取同一ID下按日期排序的上一行日期
        LAG(DateField) OVER (PARTITION BY ID ORDER BY DateField) AS PrevDate,
        -- 判断当前行与上一行的日期差是否超过90天，标记拆分点
        CASE 
            WHEN DATEDIFF(day, LAG(DateField) OVER (PARTITION BY ID ORDER BY DateField), DateField) > 90 
            THEN 1 
            ELSE 0 
        END AS IsBreak
    FROM Dummy_Data
),
GroupedData AS (
    SELECT 
        ID,
        TextField,
        DateField,
        -- 累加拆分标记，生成每个分组的唯一标识
        SUM(IsBreak) OVER (PARTITION BY ID ORDER BY DateField) AS GroupID
    FROM RankedDates
)
SELECT 
    ID,
    TextField,
    MAX(DateField) AS DateField
FROM GroupedData
GROUP BY ID, TextField, GroupID
ORDER BY ID, DateField;

逻辑拆解 #

1. RankedDates 公共表表达式（CTE）：

   • 使用LAG()窗口函数，按ID分组、日期排序，获取每条记录的上一行日期
   • 通过DATEDIFF(day, ...)计算日期差，判断是否超过90天，生成IsBreak标记（1表示需要拆分分组，0表示不需要）

2. GroupedData CTE：

   • 对IsBreak进行累加（同样按ID分组、日期排序），每次遇到IsBreak=1时，累加值会递增，这样就为每个连续的日期组生成了唯一的GroupID，实现了按间隔拆分分组的效果

3. 最终查询：

   • 按ID、TextField、GroupID分组，取每个分组的最大DateField，正好符合需求中“返回每个分组最大日期”的要求

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