PostgreSQL按自定义月度周期计算各ID的数值差值

步骤1：处理时间字段并筛选目标记录 #

先将日期和时间字段合并为完整时间戳，同时截断秒数；再筛选出每日06:00的记录（忽略其他小时产生的数据）。

步骤2：按Id分组计算月度差值 #

使用LAG()函数，按Id分组、按处理后的时间戳排序，获取上一个周期（对应上月6点）的数值，最终计算当前数值与上一周期数值的差值。

完整SQL示例（以PostgreSQL为例） #

WITH processed_data AS (
    SELECT
        Id,
        -- 合并日期时间并截断秒数
        DATE_TRUNC('minute', TO_TIMESTAMP(日期 || ' ' || 时间, 'YYYY-MM-DD HH24:MI:SS')) AS record_time,
        数值,
        -- 生成周期标识：将06:00的记录归属到对应月度周期（如2024-02-01 06:00归为2024-01周期）
        DATE_TRUNC('month', record_time - INTERVAL '6 hours') AS period_month
    FROM Table1
    -- 仅保留每日06:00的记录（忽略秒数后时间为06:00）
    WHERE DATE_PART('hour', TO_TIMESTAMP(日期 || ' ' || 时间, 'YYYY-MM-DD HH24:MI:SS')) = 6
      AND DATE_PART('minute', TO_TIMESTAMP(日期 || ' ' || 时间, 'YYYY-MM-DD HH24:MI:SS')) = 0
)
SELECT
    Id,
    period_month,
    数值 AS current_period_value,
    LAG(数值) OVER (PARTITION BY Id ORDER BY record_time) AS last_period_value,
    数值 - LAG(数值) OVER (PARTITION BY Id ORDER BY record_time) AS monthly_diff
FROM processed_data
ORDER BY Id, period_month;

关键说明 #

1. 周期匹配：通过record_time - INTERVAL '6 hours'调整时间，让06:00的记录归属到对应月度周期（比如2024-02-01 06:00的记录会被归为2024-01周期），完全符合你定义的“1月周期从1月1日6点到2月1日6点”的规则。
2. LAG()函数逻辑：PARTITION BY Id确保每个Id单独计算周期差值，ORDER BY record_time保证按时间顺序获取上一周期的数值。
3. 数据过滤：仅保留小时为6、分钟为0的记录，自动忽略其他小时产生的冗余数据。

结果示例（以Key_001_C为例） #

第一个周期因无历史数据显示NULL，后续周期的monthly_diff即为对应月度的数值差值。

内容的提问来源于stack exchange，提问作者Denis.A