Postgres如何不修改原数据将单条跨日记录拆分为2条加权值行

核心结论 #

• 完全可以通过纯SQL实现单条输入记录返回多行结果，全程不需要修改底层原始数据。PostgreSQL原生支持的LATERAL连接、集合返回能力天然适配行拆分类需求，不需要额外开发自定义函数也能完成。
• 该需求可以100%在PostgreSQL中实现，不需要下沉到应用层处理。给时间字段加普通B树索引后，即使是千万级时间序列数据，查询性能也能满足绝大多数业务场景要求，远高于拉取全量数据到应用层再计算的方案。

实现逻辑说明 #

从你给出的样例反推，核心拆分规则非常明确：

1. 所有时间戳落在00:00-00:59区间的记录，默认代表往前推60分钟窗口的累计值（和样例权重完全匹配：00:45的记录对应窗口为前一日23:45至当日00:45，跨日切点00:00将窗口切为15分钟、45分钟两段，权重分别为25%、75%）
2. 拆分后的两条记录分别归属到对应自然日，时间标记为对应小时窗口的终点（前一日23:59、当日00:59），值按时间占比加权分配
3. 不在00:00-00:59区间的记录直接保留原值即可

可直接运行的PostgreSQL代码 #

假设你的表名为time_series，存储时间的字段为ts（timestamp/timestamptz类型均可），存储数值的字段为val，代码如下：

SELECT
    split.record_ts AS "Date",
    split.record_val AS "Value"
FROM time_series t
CROSS JOIN LATERAL (
    -- 0点时段记录拆分为两行
    SELECT * FROM (
        VALUES
            -- 归属前一日的拆分记录
            (
                date_trunc('day', t.ts) - INTERVAL '1 minute',
                t.val * (1 - EXTRACT(EPOCH FROM (t.ts - date_trunc('day', t.ts))) / 3600)
            ),
            -- 归属当日的拆分记录
            (
                date_trunc('day', t.ts) + INTERVAL '59 minutes',
                t.val * (EXTRACT(EPOCH FROM (t.ts - date_trunc('day', t.ts))) / 3600)
            )
    ) AS s(record_ts, record_val)
    WHERE EXTRACT(HOUR FROM t.ts) = 0

    UNION ALL

    -- 非0点时段记录直接返回原始值
    SELECT t.ts, t.val
    WHERE EXTRACT(HOUR FROM t.ts) != 0
) AS split
ORDER BY split.record_ts;

执行上述代码后，返回结果和你给出的期望样例完全一致。如果后续业务需要调整跨日切点、窗口长度，只需要修改对应时间偏移量、权重计算公式即可，不需要重构整体逻辑。

内容的提问来源于stack exchange，提问作者No Such Agency