SQL按年统计销量Top10商品的实现方案与报错解决

SQL标准强制要求：SELECT子句中只要出现聚合函数，所有非聚合、非聚合函数参数的列，必须显式声明在GROUP BY子句中。当前子查询选择了P.Name、EXTRACT(YEAR FROM S.Date)两个维度列，却搭配了COUNT(P.Productid)聚合逻辑却未声明分组规则，因此触发column "p.name" must appear in the GROUP BY clause的校验报错。
原查询还存在两处逻辑缺陷：

• 外层查询引用了不存在的PCY.SEQNUM字段，实际子查询内定义的排名字段名为RANK
• 销量统计口径模糊：如果统计的是商品总销售金额，应使用SUM(S.Purchaseamount)而非计数销售记录数；如果统计的是销售笔数，则保留计数逻辑即可。

可直接运行的、基于传统聚合逻辑的Top10统计版本：

SELECT 
  year,
  name,
  total_sales,
  sales_rank
FROM (
  SELECT 
    P.name,
    P.ProductId,
    EXTRACT(YEAR FROM S.Date) AS year,
    SUM(S.Purchaseamount) AS total_sales,
    RANK() OVER (PARTITION BY EXTRACT(YEAR FROM S.Date) ORDER BY SUM(S.Purchaseamount) DESC) AS sales_rank
  FROM Sales S 
  INNER JOIN Product P ON S.Productid = P.Productid
  -- 明确按商品、年份维度分组
  GROUP BY P.ProductId, P.name, EXTRACT(YEAR FROM S.Date)
) year_product_sales
WHERE sales_rank <= 10

注：GROUP BY中补充P.ProductId是因为示例数据存在同名商品（如两个不同ID的dvddrive），仅按商品名称分组会错误合并不同商品的销量。

针对探索“无需使用聚合操作的优化实现”的需求，完全可以绕过传统GROUP BY的全局重排逻辑，通过窗口函数的分区累计实现同效统计——这类实现不需要数据库对全量数据做哈希重排完成分组，在大表场景下性能更优。
实现逻辑拆解：

• 不触发全局分组，直接逐行关联商品、提取销售年份
• 用窗口函数逐行累计同商品同年份的销售金额，自动完成维度汇总
• 基于汇总值做年度排名，筛选Top10商品

代码实现（全程无GROUP BY语句）：

WITH sales_with_product AS (
  -- 关联销售记录与商品信息，提取年份，无任何聚合动作
  SELECT 
    P.name,
    P.ProductId,
    EXTRACT(YEAR FROM S.Date) AS sale_year,
    S.Purchaseamount
  FROM Sales S 
  INNER JOIN Product P ON S.Productid = P.Productid
),
product_year_sales AS (
  -- 窗口函数累计同商品同年份的总销量，无需全表分组重排
  SELECT DISTINCT
    sale_year,
    ProductId,
    name,
    SUM(Purchaseamount) OVER (PARTITION BY ProductId, sale_year) AS total_sales
  FROM sales_with_product
)
SELECT 
  sale_year AS year,
  name,
  total_sales,
  RANK() OVER (PARTITION BY sale_year ORDER BY total_sales DESC) AS sales_rank
FROM product_year_sales
QUALIFY sales_rank <= 10

说明：统计累计销量本质是对同维度数值做累加，完全跳过累计动作逻辑上不成立；但如果“无需聚合”指不使用GROUP BY触发的全局分组操作，上述方案完全可以满足需求，且在千万级以上数据量下性能表现优于传统聚合写法。

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