如何用RANK() OVER (PARTITION BY)获取4-6月每月TOP3餐厅销售额

你的现有SQL已经正确计算了每个餐厅在4-6月的月销售额和对应月份的排名，但缺少筛选出每个月排名前3的记录这一步。只需要把原查询作为子查询/CTE，在外层添加过滤条件即可实现需求。

修改方案1：使用子查询 #

SELECT *
FROM (
    SELECT restaurant_id,
           EXTRACT(year FROM submitted_on) AS year,
           EXTRACT(month FROM submitted_on) AS month,
           SUM(total_amount) AS total_sales,
           RANK() OVER (PARTITION BY EXTRACT(month FROM submitted_on) 
                            ORDER BY SUM(total_amount) DESC) AS rank
      FROM orders
     WHERE submitted_on::date BETWEEN '2021-04-01' AND '2021-06-30'
     GROUP BY restaurant_id, year, month
) ranked_orders
WHERE rank <= 3
ORDER BY month, rank;

修改方案2：使用CTE（可读性更强） #

WITH ranked_orders AS (
    SELECT restaurant_id,
           EXTRACT(year FROM submitted_on) AS year,
           EXTRACT(month FROM submitted_on) AS month,
           SUM(total_amount) AS total_sales,
           RANK() OVER (PARTITION BY EXTRACT(month FROM submitted_on) 
                            ORDER BY SUM(total_amount) DESC) AS rank
      FROM orders
     WHERE submitted_on::date BETWEEN '2021-04-01' AND '2021-06-30'
     GROUP BY restaurant_id, year, month
)
SELECT *
FROM ranked_orders
WHERE rank <= 3
ORDER BY month, rank;

补充说明 #

• 给SUM(total_amount)加了别名total_sales，让结果更易读，你可以根据需要调整
• 原SQL用的RANK()函数会保留并列排名：比如如果有两家餐厅在某月销售额并列第3，结果会返回4条记录。如果需要严格只取前3条（即使有并列也截断），可以把RANK()换成ROW_NUMBER()，但要注意ROW_NUMBER()在并列时会随机分配排名，若需要稳定排序，可以在ORDER BY里再加一个字段（比如restaurant_id）。

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