无法正确关联CTE中上年销售占比数据，求SQL优化方案

原问题核心 #

需要查询美洲、欧洲、亚洲三个区域1999-2000年的以下数据：

• 各年份、各销售渠道的销售额
• 区域年度总销售额
• 渠道销售额占区域总额的百分比
• 对应渠道上一年的占比及差值

原SQL存在两个关键问题：

• 关联CTE时未匹配当前年份=CTE年份+1的逻辑，导致单条数据关联了多年份的上年占比
• 使用窗口函数加DISTINCT的方式聚合，容易产生重复数据

修正后的SQL #

WITH annual_channel_sales AS (
    -- 先聚合每年、每个区域、每个渠道的核心数据
    SELECT 
        c3.region,
        t.year,
        c2.channel,
        SUM(s.amount) AS channel_sales,
        SUM(SUM(s.amount)) OVER(PARTITION BY c3.region, t.year) AS region_total_sales,
        -- 计算当前年份渠道占比
        TO_CHAR(
            SUM(s.amount) / SUM(SUM(s.amount)) OVER(PARTITION BY c3.region, t.year) * 100,
            'fm99D00%'
        ) AS current_percent
    FROM sh.sales s 
    JOIN sh.customers c ON s.cust_id = c.cust_id 
    JOIN sh.times t ON s.time_id = t.time_id 
    JOIN sh.channels c2 ON s.channel_id = c2.channel_id 
    JOIN sh.countries c3 ON c.country_id = c3.country_id 
    WHERE 
        c3.region IN ('Americas', 'Europe','Asia') 
        AND t.year IN (1998, 1999, 2000) -- 包含1998年用于取1999年的上年数据
    GROUP BY c3.region, t.year, c2.channel
)
SELECT 
    region,
    year,
    channel,
    channel_sales,
    region_total_sales,
    current_percent,
    -- 用LAG函数获取上一年同区域同渠道的占比
    LAG(current_percent) OVER(PARTITION BY region, channel ORDER BY year) AS previous_year_percent,
    -- 计算占比差值（当前-上年）
    TO_CHAR(
        (REPLACE(current_percent, '%', '')::NUMERIC) - (REPLACE(LAG(current_percent) OVER(PARTITION BY region, channel ORDER BY year), '%', '')::NUMERIC),
        'fm99D00%'
    ) AS percent_diff
FROM annual_channel_sales
WHERE year IN (1999, 2000) -- 只返回需求的1999-2000年数据
ORDER BY year, region, channel;

关键修正点说明 #

1. 预聚合数据：先通过GROUP BY聚合每年/区域/渠道的销售额，避免窗口函数加DISTINCT导致的重复问题，同时让数据结构更清晰
2. LAG函数取上年数据：用LAG(...) OVER(PARTITION BY region, channel ORDER BY year)直接获取同区域同渠道上一年的占比，替代原错误的CTE关联逻辑，彻底解决年份匹配错误问题
3. 修正笔误：原SQL中sum(s.amount_sold)应为sum(s.amount)，c3.country_region应为c3.region，同时修正了WHERE条件中的错误年份（原包含2001）
4. 差值计算：通过替换百分号并转成数值类型，实现占比的差值计算并格式化输出

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