SQL技术问询：如何按年份找出营收最高的产品并排查错误

数据与需求说明 #

现有YEARLY_PRODUCT_REVENUE表存储产品年度营收数据，样例数据如下：

Year | Product | Revenue 
2000   Table     100  
2000   Chair     200
2000   Bed       150
2010   Table     120  
2010   Chair     190
2010   Bed       390

需求：按年份筛选出对应营收最高的产品，期望输出：

Year | Product | Revenue  
2000   Chair     200
2010   Bed       390

错误SQL及问题分析 #

你编写的SQL语句如下：

SELECT year, product, MIN(revenue)
FROM YEARLY_PRODUCT_REVENUE
GROUP BY article, month;

执行后得到不符合预期的结果，错误原因如下：

1. 聚合函数错误：需求是找营收最高值，你用了MIN(revenue)，实际应该用MAX(revenue)
2. 分组字段错误：表中不存在article和month字段，正确的分组字段应该是year；但即使修正分组字段，直接在GROUP BY year后选取product也会有问题——SQL中分组后只能选取分组字段或聚合函数结果，直接选product会导致非预期的结果（不同数据库引擎处理逻辑不同，无法保证拿到对应最大营收的产品）
3. 逻辑偏差：上述错误叠加，最终输出的是年份中营收较低的产品，且分组错误产生多条数据

正确SQL实现 #

方法1：窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数按年份分区，对营收降序排序后取每个分区的第一条数据：

SELECT year, product, revenue
FROM (
    SELECT 
        year, 
        product, 
        revenue,
        -- 按年份分区，营收降序排序，给每条数据标记序号
        ROW_NUMBER() OVER(PARTITION BY year ORDER BY revenue DESC) AS rn
    FROM YEARLY_PRODUCT_REVENUE
) t
-- 只取每个年份的第一条（营收最高的产品）
WHERE rn = 1;

如果存在多个产品同年营收并列最高，且需要全部输出，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

方法2：子查询关联 #

先通过子查询找出每年的最高营收，再关联原表获取对应产品信息：

SELECT y.year, y.product, y.revenue
FROM YEARLY_PRODUCT_REVENUE y
INNER JOIN (
    -- 先计算每年的最高营收
    SELECT year, MAX(revenue) AS max_revenue
    FROM YEARLY_PRODUCT_REVENUE
    GROUP BY year
) m ON y.year = m.year AND y.revenue = m.max_revenue;

该方法会输出所有同年营收并列最高的产品。

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