如何对齐不同分辨率的两个时间序列？PostgreSQL实现问询

针对你的需求——为rates表的每一行匹配prices表中时间更早的最新记录，这里提供两种PostgreSQL原生的实现方案：

方法一：LATERAL 子查询（推荐，性能最优） #

利用PostgreSQL的LATERAL关键字，可以让子查询直接引用外部表的字段，对rates的每一行单独执行匹配逻辑，是最直观高效的方式：

SELECT 
    r.date AS rate_date,
    r.rate,
    p.date AS latest_price_date,
    p.price
FROM rates r
LEFT JOIN LATERAL (
    -- 对当前rate的日期，找到prices中<=该日期的最新行
    SELECT date, price
    FROM prices
    WHERE date <= r.date
    ORDER BY date DESC
    LIMIT 1
) p ON true;

说明： #

• 使用LEFT JOIN确保即使prices中没有早于当前rate日期的记录，rates的行依然会被保留，此时p的字段会返回NULL
• 如果只需要匹配有对应price的rate记录，把LEFT JOIN改成JOIN即可

方法二：窗口函数分组匹配 #

适合需要处理更复杂时间分组逻辑的场景，通过合并两个表并分组的方式实现匹配：

WITH combined_data AS (
    -- 合并两个表，标记数据来源
    SELECT date, price, NULL::double AS rate, 'price' AS source FROM prices
    UNION ALL
    SELECT date, NULL::double AS price, rate, 'rate' AS source FROM rates
),
grouped_data AS (
    -- 按日期排序，用累加的方式为每个rate行划分到最近的price组
    SELECT 
        *,
        SUM(CASE WHEN source = 'price' THEN 1 ELSE 0 END) OVER (ORDER BY date) AS price_group
    FROM combined_data
)
-- 筛选出rate行，取对应组内的最新price记录
SELECT 
    date AS rate_date,
    rate,
    FIRST_VALUE(price) OVER (PARTITION BY price_group ORDER BY date) AS price,
    FIRST_VALUE(date) OVER (PARTITION BY price_group ORDER BY date) AS latest_price_date
FROM grouped_data
WHERE source = 'rate'
ORDER BY rate_date;

性能优化提示 #

如果数据量较大，务必给prices表的date字段创建索引，让查询能快速定位到符合条件的记录：

CREATE INDEX idx_prices_date ON prices(date);

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