PostgreSQL使用lag窗口函数分组查询返回0行及忽略首尾行问题

我来帮你搞定这个需求！你的核心目标是获取最近1小时内按5分钟分组的medium[1]总和，同时排除分组结果的第一行和最后一行，之前的两种方法都踩了坑，咱们一步步来修正：

问题分析 #

先说说你之前的尝试为什么没生效：

1. Lag窗口函数的查询：你在子查询已经得到分组结果的情况下，用lag去取上一行数据，然后再加OFFSET 2，如果分组后的结果行数≤2，直接就返回0行了——这就是你明明有原始数据但查不到结果的原因，完全没必要用lag来绕一圈。
2. 排除最后一条id的方法：分组聚合是基于时间分组的，每个分组对应多条原始数据，你只排除单个最新的id，根本不会影响整个分组的存在，所以最后一个分组还是会出现在结果里。

正确解决方案 #

我们可以先得到分组聚合的结果，然后给这些结果标记行号，再筛选掉第一行和最后一行：

WITH grouped_data AS (
    -- 第一步：按5分钟分组计算medium[1]的总和，时间范围取最近85分钟（确保覆盖最近1小时的分组）
    SELECT 
        to_timestamp(floor((extract('epoch' FROM timestamp) / 300)) * 300) AS timestamp_min,
        type,
        floor(sum(medium[1])) AS sum_first_medium
    FROM default_dataset
    WHERE 
        type = 'ap_clients.wlan0' 
        AND timestamp > current_timestamp - INTERVAL '85 minutes' 
        AND organization_id = '9fc02db4-c3df-4890-93ac-8dd575ca5638'
    GROUP BY timestamp_min, type
    ORDER BY timestamp_min ASC
),
ranked_data AS (
    -- 第二步：给分组结果标记行号，同时计算总行数
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY timestamp_min) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM grouped_data
)
-- 第三步：筛选掉第一行（row_num=1）和最后一行（row_num=total_rows）
SELECT timestamp_min, type, sum_first_medium
FROM ranked_data
WHERE row_num != 1 AND row_num != total_rows;

代码解释： #

• grouped_data CTE：完成你最初的需求——按5分钟分组，计算medium[1]的总和，时间范围取85分钟是为了确保包含最近1小时内的所有分组（避免因为时间边界丢失数据）。
• ranked_data CTE：用ROW_NUMBER()给每个分组结果按时间排序分配行号，同时用COUNT(*) OVER()得到分组后的总行数，这样我们就能精准定位首尾行。
• 最终查询：直接排除行号为1和总行数的记录，剩下的就是你需要的中间结果。

额外验证 #

如果想确认分组后的行数是否符合预期，可以先单独查询grouped_data看看结果数量：

SELECT COUNT(*) FROM grouped_data;

这样就能知道是不是因为分组后行数太少（比如≤2）导致筛选后没有结果——如果是这种情况，你可能需要调整时间范围或者确认数据的时间分布哦。

内容的提问来源于stack exchange，提问作者Cátia Matos