如何用PostgreSQL计算股票年初至今(YTD)表现？求优化方案

问题背景 #

需要计算股票的年初至今（YTD）表现，现有基于candles表的查询语句可得到结果，但希望获得更高效的PostgreSQL查询方案，同时需要适合新手的教程/文档推荐。

当前使用的查询语句 #

SELECT t1.contract_id, t1.timestamp_tick, t1.close, t2.timestamp_tick, t2.close, t2.close / NULLIF(t1.close,0) -1 as perf FROM 
(
SELECT * FROM candles where id in (
SELECT max(id)  FROM public.candles WHERE extract(year from timestamp_tick)= '2022' and freq = 1380
group by contract_id 
    ) order by contract_id
) as t1

    INNER JOIN

(
SELECT * FROM candles where id in (
SELECT max(id)  FROM public.candles WHERE extract(year from timestamp_tick)= '2023' and freq = 1380
group by contract_id 
)
) as t2 
ON t1.contract_id = t2.contract_id  order by t1.contract_id

优化后的查询方案 #

使用窗口函数和CTE（公共表表达式）重构查询，提升性能与可读性：

WITH yearly_latest AS (
    SELECT 
        contract_id,
        timestamp_tick,
        close,
        EXTRACT(YEAR FROM timestamp_tick) AS tick_year,
        -- 按合约+年份分组，取每组中id最大的最新记录
        ROW_NUMBER() OVER (PARTITION BY contract_id, EXTRACT(YEAR FROM timestamp_tick) ORDER BY id DESC) AS rn
    FROM candles
    -- 提前过滤频率和年份，减少数据处理量
    WHERE freq = 1380
        AND EXTRACT(YEAR FROM timestamp_tick) IN (2022, 2023)
)
SELECT 
    t2022.contract_id,
    t2022.timestamp_tick AS end_2022_ts,
    t2022.close AS end_2022_close,
    t2023.timestamp_tick AS current_2023_ts,
    t2023.close AS current_2023_close,
    -- 计算YTD收益率，避免除以0
    t2023.close / NULLIF(t2022.close, 0) - 1 AS ytd_perf
FROM yearly_latest t2022
-- 关联同合约的2022年末和2023年最新数据
JOIN yearly_latest t2023 
    ON t2022.contract_id = t2023.contract_id
WHERE t2022.tick_year = 2022 AND t2022.rn = 1
    AND t2023.tick_year = 2023 AND t2023.rn = 1
ORDER BY t2022.contract_id;

优化说明 #

• 减少表扫描次数：原查询多次扫描candles表，优化后仅扫描一次，性能提升明显
• 可读性更强：CTE将“获取各合约年末最新数据”的逻辑封装，结构清晰
• 逻辑更严谨：窗口函数直接按分组取最新记录，避免子查询嵌套可能出现的逻辑漏洞

新手教程/文档推荐 #

• PostgreSQL官方文档「Getting Started」章节：内容权威全面，覆盖基础语法、查询优化、常用函数等核心知识点，是入门的首选资料
• 《PostgreSQL实战》：国内技术作者编写，结合实际业务场景，用大量实例讲解操作技巧，适合新手快速上手
• 开源社区PostgreSQL入门专栏：很多开发者分享的实战经验和基础教程，可结合实际练习巩固知识点

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