PostgreSQL关联两表提取年份统计颜色获取每年最受欢迎颜色方法

完整PostgreSQL语句（兼容并列最高销量场景） #

WITH yearly_color_sales AS (
    -- 统计每年各颜色的总销量
    SELECT
        EXTRACT(YEAR FROM FS.order_date) AS sale_year,
        DP.color,
        SUM(FS.quantity) AS total_sales
    FROM fact_sales FS
    JOIN dim_product DP ON FS.product_id = DP.product_id
    GROUP BY sale_year, DP.color
),
sales_ranking AS (
    -- 按年份分组对销量降序排名，销量相同则排名一致
    SELECT
        sale_year,
        color,
        total_sales,
        RANK() OVER (PARTITION BY sale_year ORDER BY total_sales DESC) AS rank_num
    FROM yearly_color_sales
)
-- 筛选每年排名第一的颜色
SELECT sale_year, color, total_sales
FROM sales_ranking
WHERE rank_num = 1
ORDER BY sale_year;

结果说明 #

针对你提供的示例数据，运行后输出结果如下：

如果某一年存在多个颜色销量并列最高的情况，所有并列的颜色都会被返回。

简化写法（PostgreSQL专属，性能更优） #

如果你不需要处理并列最高的场景，只需要取任意一个最高销量的颜色，可以使用DISTINCT ON语法简化语句：

SELECT DISTINCT ON (sale_year)
    EXTRACT(YEAR FROM FS.order_date) AS sale_year,
    DP.color,
    SUM(FS.quantity) AS total_sales
FROM fact_sales FS
JOIN dim_product DP ON FS.product_id = DP.product_id
GROUP BY sale_year, DP.color
ORDER BY sale_year, total_sales DESC;

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