PostgreSQL实现按资产自动遍历年份查询历史平均价格

问题背景 #

我有如下PostgreSQL查询语句，用于返回一年前某资产的平均价格：

select
    AVG(last),
    NOW() - interval '1 YEAR' as DATE
from
    PRICE_HIST
where
    CREATED_AT between NOW() - interval '1 YEAR' and NOW() - interval '1 YEAR - 12 HOURS'

现在需要扩展该查询，自动遍历数据库中各资产对应的所有存在的年份（将语句中的1 YEAR替换为2、3、4等），且无需预先知晓各资产的最早记录年份。示例数据如下：

id      created_at                      last    symbol
74      2021-10-19 19:41:53.382 -0300   280000  BTCBRL
46      2019-10-19 19:41:47.481 -0300   35000   BTCBRL
49      2018-10-19 19:41:47.519 -0300   15000   BTCBRL
1432    2022-10-19 09:47:58.038 -0300   101868  BTCBRL
1481    2022-10-19 09:48:09.559 -0300   102021  BTCBRL
1513    2022-10-19 09:48:19.686 -0300   101395  BTCBRL
1517    2022-10-19 09:48:20.706 -0300   102500  BTCBRL
1531    2022-10-19 09:48:24.559 -0300   102926  BTCBRL

解决方案 #

方案一：生成年份间隔序列，匹配原查询的12小时窗口 #

该方案严格对应原查询的逻辑，自动生成每个资产需要遍历的年份间隔，计算对应窗口的平均价格：

WITH asset_year_intervals AS (
    -- 按资产分组，生成需要遍历的年份间隔（1代表1年前，2代表2年前，依此类推）
    SELECT 
        symbol,
        generate_series(1, EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM MIN(created_at))::INTEGER) AS year_offset
    FROM PRICE_HIST
    GROUP BY symbol
),
date_ranges AS (
    -- 转换每个年份间隔为对应的时间窗口
    SELECT 
        symbol,
        year_offset,
        NOW() - (year_offset || ' YEAR')::INTERVAL AS window_start,
        NOW() - (year_offset || ' YEAR - 12 HOURS')::INTERVAL AS window_end
    FROM asset_year_intervals
)
SELECT 
    dr.symbol,
    dr.year_offset AS years_ago,
    dr.window_start AS reference_date,
    AVG(ph.last) AS average_price
FROM date_ranges dr
LEFT JOIN PRICE_HIST ph 
    ON ph.symbol = dr.symbol
    AND ph.created_at BETWEEN dr.window_start AND dr.window_end
GROUP BY dr.symbol, dr.year_offset, dr.window_start
ORDER BY dr.symbol, dr.year_offset DESC;

逻辑说明：

1. asset_year_intervals CTE：统计每个资产的最早记录年份，计算到当前年份的间隔，用generate_series生成从1到该间隔的整数序列，代表需要查询的“几年前”。
2. date_ranges CTE：把每个年份间隔转换成原查询中的时间窗口，即当前日期往前推X年的起始和结束时间（包含12小时的范围）。
3. 关联查询：将生成的时间窗口与原表关联，按资产和年份间隔分组计算平均价格，使用LEFT JOIN确保即使某年份窗口没有数据也会返回结果（此时average_price为NULL）。

方案二：按自然年份统计全年平均价格（简化版） #

如果不需要严格匹配“当前日期往前推X年的12小时窗口”，而是要统计每个资产每一年的全年平均价格，可以用更简洁的分组查询：

SELECT 
    symbol,
    EXTRACT(YEAR FROM created_at) AS record_year,
    AVG(last) AS average_price
FROM PRICE_HIST
GROUP BY symbol, EXTRACT(YEAR FROM created_at)
ORDER BY symbol, record_year DESC;

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