PostgreSQL中按时间戳日期匹配将交易表价格映射到价格历史表

前提表结构说明 #

本次操作涉及两张数据表：

• 交易表 transaction：核心字段为 transact_at（timestamp 类型，存储交易精确时间戳）、price（numeric 类型，存储对应交易的价格），其余业务字段不影响本次操作
• 价格历史表 price_history：核心字段为 record_date（date 类型，对应单日日期，每日仅存在唯一一条记录）、price（numeric 类型，待填充的单日价格字段）

核心更新SQL（默认取当日最后一笔交易价格） #

UPDATE price_history ph
SET price = t.daily_price
FROM (
    SELECT
        DATE(transact_at) AS transact_date,
        -- 按交易时间倒序取当日最后一笔交易价格，可按需调整聚合规则
        (array_agg(price ORDER BY transact_at DESC))[1] AS daily_price
    FROM transaction
    GROUP BY DATE(transact_at)
) t
WHERE ph.record_date = t.transact_date;

不同业务场景的规则调整 #

如果需要其他统计口径的单日价格，可替换上述子查询中的daily_price计算逻辑：

• 取当日所有交易的平均价格：替换为 AVG(price) AS daily_price
• 取当日最高交易价格：替换为 MAX(price) AS daily_price
• 取当日最低交易价格：替换为 MIN(price) AS daily_price
• 取当日第一笔交易价格：将array_agg的排序规则改为ORDER BY transact_at ASC

注意事项 #

PostgreSQL 中transaction为内置关键字，如果你的交易表实际命名为transaction，查询时需用双引号包裹表名，即 "transaction"。
如果价格历史表中存在部分日期无对应交易数据的情况，执行上述更新后这些日期的price字段会保留原有值，若需要统一设置为特定值（如0或NULL），可额外执行更新语句处理无匹配的记录。

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