ANSI SQL实现指定时长非重叠时间窗口分组方法

需求说明 #

现有存储多主体索引日期的数据表，需要对行数据做分组，满足以下规则：

• 同组内所有行的index日期跨度不超过1年，即组内首行与末行的日期间隔小于等于1年
• 当某行的index日期与当前组的最小index日期间隔超过1年时，该行自动作为新组的起始行
• 需要在结果中新增index_grp字段标识分组，取值为对应组的起始日期

测试数据 #

CREATE TABLE myTable (id CHAR(1), rownr INTEGER, index DATE);
INSERT INTO myTable VALUES ('A', 1, '2018-01-01');
INSERT INTO myTable VALUES ('A', 2, '2018-08-01');
INSERT INTO myTable VALUES ('A', 3, '2019-03-04');
INSERT INTO myTable VALUES ('A', 4, '2019-09-09');
INSERT INTO myTable VALUES ('A', 5, '2020-02-15');
INSERT INTO myTable VALUES ('B', 1, '2020-12-31');
INSERT INTO myTable VALUES ('B', 2, '2021-03-04');
INSERT INTO myTable VALUES ('B', 3, '2022-01-01');
INSERT INTO myTable VALUES ('B', 4, '2022-02-20');

错误写法问题 #

此前尝试使用滑动窗口实现的代码如下：

SELECT id, rownr, index
,     MIN(index) OVER(PARTITION BY id ORDER BY index RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND CURRENT ROW) AS index_grp
FROM myTable;

该写法存在窗口重叠问题：例如id=A、rownr=3的行（index='2019-03-04'）返回的index_grp值为2018-08-01，不符合“新组从超期行开始”的逻辑。Teradata SQL的RESET WHEN扩展可直接实现条件触发新窗口的效果，但不属于ANSI SQL标准范畴。

标准SQL实现方案 #

ANSI SQL未提供原生的窗口重置语法，可通过递归CTE实现该逻辑，核心步骤：

• 先按id分区、index升序为每行生成连续行号，解决日期重复时的排序稳定性问题
• 以每个ID的第一行作为第一个分组的起始点，逐行递归关联下一条记录
• 关联时判断当前行日期与上一条记录所属组的起始日期间隔，超过1年则将当前行设为新组起始，否则沿用上一组的起始日期
  完整代码如下：

WITH RECURSIVE sorted_rows AS (
    SELECT
        id,
        rownr,
        index,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY index, rownr) AS rn
    FROM myTable
),
group_calc AS (
    -- 递归锚点：每个ID的首行作为第一个分组的起始
    SELECT
        id,
        rownr,
        index,
        rn,
        index AS index_grp
    FROM sorted_rows
    WHERE rn = 1

    UNION ALL

    -- 递归遍历后续行，判断是否需要开启新组
    SELECT
        cur.id,
        cur.rownr,
        cur.index,
        cur.rn,
        CASE
            WHEN cur.index - prev.index_grp > INTERVAL '1' YEAR
            THEN cur.index
            ELSE prev.index_grp
        END AS index_grp
    FROM sorted_rows cur
    INNER JOIN group_calc prev
        ON cur.id = prev.id
        AND cur.rn = prev.rn + 1
)
SELECT id, rownr, index, index_grp
FROM group_calc
ORDER BY id, rownr;

执行结果验证 #

代码执行后返回的分组完全符合规则：

• id=A：
  • 组1（index_grp='2018-01-01'）：包含rownr=1、2，日期跨度7个月
  • 组2（index_grp='2019-03-04'）：包含rownr=3、4、5，最大日期2020-02-15与组起始日间隔差18天满1年，未超阈值
• id=B：
  • 组1（index_grp='2020-12-31'）：包含rownr=1、2，日期跨度2个月
  • 组2（index_grp='2022-01-01'）：包含rownr=3、4，rownr=3日期与上一组起始日间隔1年1天，超阈值后开新组

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