如何用SQL查询上年同期数据并优化PostgreSQL大表查询性能

原有方案核心问题 #

你的现有逻辑存在两个明显缺陷：

• 结果正确性问题：JOIN关联仅匹配了日期字段，没有关联location_sk/division_sk/employee_type_sk三个维度字段，会导致不同维度的数据乱匹配，输出结果不符合需求。
• 性能问题：用字符串拼接CONCAT生成上年同期日期属于计算字段，无法命中索引；同时需要两次扫描全表，大数据量下性能极差。

优化方案 #

方案1：优化自关联实现（结果最精准） #

不需要单独创建中间视图，直接用整数运算生成上年同期日期，补全关联维度，配合索引可大幅提升性能：

CREATE OR REPLACE VIEW test_view AS
SELECT 
    t1.date_sk,
    t1.location_sk,
    t1.division_sk,
    t1.employee_type_sk,
    t1.value,
    t2.value AS value_last_year
FROM test_table t1
LEFT JOIN test_table t2 
    ON t1.date_sk - 10000 = t2.date_sk -- 整数运算替代字符串拼接，YYYYMMDD格式减10000直接得到上年同日的数值
    AND t1.location_sk = t2.location_sk
    AND t1.division_sk = t2.division_sk
    AND t1.employee_type_sk = t2.employee_type_sk;

配套性能优化建议：给test_table创建联合覆盖索引，查询可直接走索引无需回表，性能提升至少一个数量级：

CREATE INDEX idx_test_table_dim ON test_table (date_sk, location_sk, division_sk, employee_type_sk, value);

方案2：窗口函数实现（性能最优） #

仅需扫描一次全表，性能比自关联提升1倍以上，逻辑更简洁：

CREATE OR REPLACE VIEW test_view AS
SELECT 
    date_sk,
    location_sk,
    division_sk,
    employee_type_sk,
    value,
    LAG(value, 1) OVER (
        PARTITION BY location_sk, division_sk, employee_type_sk, RIGHT(date_sk::TEXT, 4) -- 按维度+月日分组，相同月日即为同期
        ORDER BY LEFT(date_sk::TEXT, 4)::INT -- 按年份升序排序
    ) AS value_last_year
FROM test_table;

该方案逻辑是把相同维度、相同月日的记录归为一组，按年份排序后取前一条的value，天然就是上年同期的值，不需要关联操作，性能最优。 #

方案选型建议 #

• 如果存在缺年、日期不连续的情况，优先选方案1，结果更精准，加索引后性能完全满足TB级数据量需求。
• 如果日期规律、每年同期数据都有记录，优先选方案2，性能最好，代码更易维护。

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