根据销售数量为销售产品创建销售等级表

Question

我有如下几栏的销售数据

Customer Id | ItemID | Order_Qty | Order_Date

我想要创建一个销售排名表的基础上，总销量为一个项目。

最高的排名，最高数量的项目出售。

例如：

在我的结果表中，我想要下面的列。

ItemId | Total Quantity | Rank 

以下是我到目前为止在传统SQL格式中的尝试-

select ItemId ,
       RANK() over (partition by ItemId order by Qty desc) as salesrank
from (SELECT ItemId, sum(order_qty) as Qty
      FROM sales
      group by 1
     )

由于某种原因，所有的销售级别都是1。

Answer 1

下面是用于BigQuery标准SQL的

#standardSQL
SELECT 
  ItemID, Total_Quantity, 
  RANK() OVER(ORDER BY Total_Quantity DESC) AS rnk
FROM (
  SELECT ItemID, SUM(Order_Qty) AS Total_Quantity
  FROM sales
  GROUP BY ItemID
)
-- ORDER BY rnk

Answer 2

我推测你想要ean的军衔。如果是的话：

SELECT ean, SUM(order_qty) as Qty,
       RANK() OVER (ORDER BY SUM(order_qty) DESC) as rnk
FROM sales
GROUP BY ean;

在遗留SQL中，这可能是：

SELECT ean, SUM(order_qty) as Qty,
       RANK() OVER (ORDER BY qty DESC) as rnk
FROM sales
GROUP BY ean