PostgreSQL固定非重叠7天窗口统计实现求助

问题描述 #

我需要编写PostgreSQL查询，生成固定非重叠窗口并统计每个窗口内的记录数。下一个窗口需从上个窗口结束日期后的第一条记录日期开始，数据存在日期间隔，属于典型的gaps and islands问题。

约束条件 #

• 数据按id、ts分区
• 窗口固定为7天
• 窗口之间不重叠
• 下一个窗口的起始时间为上个窗口结束日期后的第一条记录的ts

当前难点 #

如何确定上个窗口结束后存在时间间隔时，下一个窗口的起始时间。

示例数据 #

id|ts                     |
--+-----------------------+
 1|2024-12-20 21:48:24.877|
 1|2025-01-04 03:17:32.757|
 1|2025-01-17 20:14:57.942|
 2|2025-01-02 22:57:29.979|
 2|2025-01-15 16:16:17.941|
 2|2025-01-16 16:25:20.665|
 2|2025-01-29 16:17:04.410|
 2|2025-01-30 16:26:21.598|
 3|2024-12-19 20:33:39.793|
 3|2024-12-28 06:44:24.236|
 3|2024-12-31 05:13:19.438|
 3|2025-01-03 10:14:29.228|
 3|2025-01-09 18:11:22.303|
 3|2025-01-10 18:32:00.508|
 3|2025-01-12 20:21:10.596|
 3|2025-01-16 17:40:39.347|

期望输出 #

id|window_start           |window_end             |count|
--+-----------------------+-----------------------+-----|
 1|2024-12-20 21:48:24.877|2024-12-27 21:48:24.877|    1|
 1|2025-01-04 03:17:32.757|2025-01-11 03:17:32.757|    1|
 1|2025-01-17 20:14:57.942|2025-01-24 20:14:57.942|    1|
 2|2025-01-02 22:57:29.979|2025-01-09 22:57:29.979|    1|
 2|2025-01-15 22:57:29.979|2025-01-22 22:57:29.979|    2|
 2|2025-01-29 22:57:29.979|2025-02-05 22:57:29.979|    2|
 3|2024-12-19 20:33:39.793|2024-12-26 20:33:39.793|    1|
 3|2024-12-28 06:44:24.236|2025-01-04 06:44:24.236|    3|
 3|2025-01-09 18:11:22.303|2025-01-16 18:11:22.303|    4|

解决方案 #

查询语句 #

WITH ranked_data AS (
    SELECT
        id,
        ts,
        -- 标记当前记录是否为新窗口的起始点：第一条记录 或 晚于上一个窗口结束时间
        CASE
            WHEN LAG(ts) OVER (PARTITION BY id ORDER BY ts) IS NULL THEN 1
            WHEN ts > LAG(ts) OVER (PARTITION BY id ORDER BY ts) + INTERVAL '7 days' THEN 1
            ELSE 0
        END AS is_new_window_start
    FROM your_table_name
),
window_groups AS (
    SELECT
        id,
        ts,
        -- 累计求和生成窗口组ID，同一窗口的记录共享同一个组ID
        SUM(is_new_window_start) OVER (PARTITION BY id ORDER BY ts) AS window_group
    FROM ranked_data
),
window_stats AS (
    SELECT
        id,
        window_group,
        MIN(ts) AS window_start,
        MIN(ts) + INTERVAL '7 days' AS window_end,
        COUNT(*) AS count
    FROM window_groups
    GROUP BY id, window_group
)
SELECT
    id,
    window_start,
    window_end,
    count
FROM window_stats
ORDER BY id, window_start;

逻辑解释 #

1. ranked_data CTE：按id分组排序，判断每条记录是否为新窗口的起始点。规则是：
   • 分组内的第一条记录必然是新窗口起始；
   • 如果当前记录的ts晚于上一条记录所在窗口的结束时间（上一条ts+7天），则作为新窗口起始。
2. window_groups CTE：通过累计求和is_new_window_start，将属于同一个窗口的记录归为同一组。每次遇到新窗口起始点，组ID递增，确保同一窗口的记录拥有相同的组ID。
3. window_stats CTE：对每个窗口组，取组内最早的ts作为窗口起始时间，加上7天得到结束时间，同时统计组内的记录数。
4. 最后按id和窗口起始时间排序输出结果，完全符合需求。

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