如何按动态日期组聚合/划分窗口数据（非静态）？

要实现你这种从最新日期开始，按固定天数（比如5天）动态划分区间的分组需求，递归CTE是最直接的解决方案——因为这种分组逻辑依赖前一个分组的边界，窗口函数很难直接处理这种“依赖前序结果”的动态区间。

先给你一个可以直接运行的完整SQL，基于你的测试数据：

WITH input AS (
    SELECT * FROM (
        VALUES 
            (date '2018-05-11', 'lorem'), 
            (date '2018-05-10', 'ipsum'), 
            (date '2018-05-07', 'dolor'), 
            (date '2018-05-05', 'hello'), 
            (date '2018-05-04', 'world'), 
            (date '2018-04-30', 'foo'), 
            (date '2018-04-15', 'bar') 
    ) AS v(date, name)
),
recursive_groups AS (
    -- 第一步：初始化第一个分组，从最新日期开始覆盖5天区间
    SELECT 
        MAX(date) OVER () AS group_max_date,
        MAX(date) OVER () - INTERVAL '4 days' AS group_min_date,
        date,
        name
    FROM input
    WHERE date = (SELECT MAX(date) FROM input)
    
    UNION ALL
    
    -- 递归生成后续分组：以上一组的最小日期减1作为新组的最大日期，再覆盖5天
    SELECT 
        rg.group_min_date - INTERVAL '1 day' AS group_max_date,
        (rg.group_min_date - INTERVAL '1 day') - INTERVAL '4 days' AS group_min_date,
        i.date,
        i.name
    FROM recursive_groups rg
    JOIN input i 
        ON i.date <= rg.group_min_date - INTERVAL '1 day'
        AND i.date > (rg.group_min_date - INTERVAL '1 day') - INTERVAL '4 days'
    -- 避免重复加入已经分组的行
    WHERE NOT EXISTS (
        SELECT 1 FROM recursive_groups rg2 
        WHERE rg2.date = i.date
    )
)
-- 最后按分组聚合，得到期望结果
SELECT 
    MIN(date) AS min_date,
    MAX(date) AS max_date,
    STRING_AGG(name, ', ' ORDER BY date DESC) AS names
FROM recursive_groups
GROUP BY group_max_date, group_min_date
ORDER BY max_date DESC;

思路解释 #

1. 初始化分组：先找到数据里的最新日期，把它作为第一个分组的group_max_date，然后往前推4天得到group_min_date（这样区间刚好是5天：group_max_date到group_min_date），把属于这个区间的行加入初始组。
2. 递归生成后续分组：每个新分组的group_max_date是上一个分组group_min_date减1天，再往前推4天得到新的group_min_date，然后把所有还没被分组的、落在这个新区间的行拉进来，直到没有未分组的数据为止。
3. 聚合结果：按每个分组的边界聚合，用STRING_AGG把同组的名字按日期倒序拼接，就得到了你想要的输出。

关于窗口函数的疑问 #

你提到想用窗口函数实现，其实这种动态分组逻辑窗口函数很难搞定。因为窗口函数的计算是基于固定的窗口范围（比如排序后的行、固定的日期范围），它没法“记住”上一个分组的边界，也不能动态调整后续的分组区间——窗口函数的窗口定义是静态的，而你的需求是每个分组的区间完全依赖前一个分组的结果，这种“链式依赖”的逻辑递归CTE更擅长处理。

再说说你之前的两种思路：

• 第一种思路里，你在lag里引用了还没定义的groupByDate，SQL的执行顺序是先处理FROM/WINDOW，再处理SELECT里的列，所以groupByDate在lag执行时还不存在，自然会报错。
• 第二种思路里，max(date) filter(...) over w的问题在于，每个行的input.date不同，导致过滤条件不一样，同一组内的行计算出来的max_date会有差异，没法得到统一的分组标识，所以达不到效果。

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