PostgreSQL提取同ID&Dir且30分钟内dist最大值记录

问题描述 #

从table_1表提取数据：筛选id与dir列值相同、datetime处于30分钟时间范围内的记录组，获取每个组内dist列值最大的完整行数据。

建表语句（参考） #

CREATE TABLE table_1 (
    id INT,
    dir VARCHAR(50),
    datetime TIMESTAMP,
    dist NUMERIC,
    other_cols TEXT -- 其他业务列
);

解决方案 #

根据时间范围的定义（滑动窗口/固定窗口），提供两种常用实现方式：

方式1：滑动时间窗口（同ID/Dir下，任意记录datetime差≤30分钟为一组）

使用窗口函数的RANGE子句定义滑动时间范围，直接标记每组内的最大dist行：

WITH ranked_records AS (
    SELECT
        *,
        -- 按ID、Dir分组，在当前记录前后30分钟窗口内按dist降序排名
        ROW_NUMBER() OVER (
            PARTITION BY id, dir
            ORDER BY dist DESC
            RANGE BETWEEN INTERVAL '30 minutes' PRECEDING AND INTERVAL '30 minutes' FOLLOWING
        ) AS rank_num
    FROM table_1
)
-- 取每个窗口内排名第一的行（即dist最大的行）
SELECT id, dir, datetime, dist, other_cols
FROM ranked_records
WHERE rank_num = 1;

方式2：固定30分钟窗口（按整点/半点划分固定时间窗口）

如果需求是将时间按每30分钟的固定区间分组（如10:00-10:30、10:30-11:00），可通过截断时间生成窗口标识：

WITH window_groups AS (
    SELECT
        *,
        -- 生成30分钟窗口的起始时间（如10:15→10:00，10:40→10:30）
        DATE_TRUNC('minute', datetime) - INTERVAL '1 minute' * (EXTRACT(minute FROM datetime)::INT % 30) AS window_start
    FROM table_1
),
ranked_in_group AS (
    SELECT
        *,
        -- 按ID、Dir、窗口起始时间分组，取dist最大的行
        ROW_NUMBER() OVER (PARTITION BY id, dir, window_start ORDER BY dist DESC) AS rank_num
    FROM window_groups
)
SELECT id, dir, datetime, dist, other_cols
FROM ranked_in_group
WHERE rank_num = 1;

关键说明 #

• 若原CTE关联查询失效，通常是因为未正确处理时间范围的分组逻辑，窗口函数能更直观地实现“分组-排序-取最值”的需求。
• 使用ROW_NUMBER()会在dist相同时随机取一行，若需保留所有dist最大值的行，可替换为RANK()。

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