选择每年销售的前10个产品

tpxzln5u  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(258)

我有两张table:

  1. Sales
  2. columns: (Sales_id, Date , Customer_id, Product_id, Purchase_amount):
  3. Product
  4. columns: ( Product_id, Product_Name, Brand_id,Brand_name)

我必须写一个查询来找出每年销售的前十名产品。我现在的问题是:

  1. WITH PH AS
  2. (SELECT P.Product_Name, LEFT(S.Date,4) "SYEAR", COUNT(S.Product_id) "Product Count"
  3. FROM Sales S LEFT JOIN Product P
  4. ON S.Product_Id=P.Product_Id
  5. GROUP BY P.Product_Name, LEFT(S.Date,4)
  6. SELECT P.Product_Name, "SYEAR", "Product_Count"
  7. FROM (SELECT P.Product_Name, "SYEAR", "Product_Count",
  8. RANK OVER (PARTITION BY "SYEAR" ORDER BY "Product_Count" DESC) "TEMP"
  9. )
  10. WHERE "TEMP"<=10

这似乎不是最优化的查询。你能帮我一下吗?能否有一个替代版本来获得所需的结果?
笔记
代码重复的主要原因是允许按年份分组。给定表中没有年度字段。
日期格式为:yyyymmdd(例如:20200630)
任何帮助都将不胜感激。提前谢谢

ekqde3dh

ekqde3dh1#

可以将窗口函数与聚合结合起来:

  1. SELECT PY.*
  2. FROM (SELECT P.Product_Name, LEFT(S.Date,4) AS YEAR, COUNT(*) AS CNT,
  3. RANK() OVER (PARTITION BY LEFT(S.Date, 4) ORDER BY COUNT(*) DESC) AS SEQNUM
  4. FROM Sales S LEFT JOIN
  5. Product P
  6. ON S.Product_Id = P.Product_Id
  7. GROUP BY P.Product_Name, LEFT(S.Date, 4)
  8. ) PY
  9. WHERE SEQNUM <= 10;

从性能Angular 来看,这可能会生成一个与查询非常相似的执行计划。不过,这更简单。

相关问题