hive练习题（五）

商品的销售数量Top10,排名需要考虑并列排名的情况

数据表ods_sales_orders的前10行

第一步: 对统计每个产品的订单量

SELECT product_key,COUNT(sales_order_key) AS sale_amount 
FROM ods_sales_orders GROUP BY product_key

第二步：对订单量进行排序

知识点：dense_rank()是连续排序，比如有两个第一名时后面仍然跟着第二名

SELECT a.product_key AS product_key,a.sale_amount AS sale_amount,
dense_rank() over(ORDER BY a.sale_amount DESC) AS rn
FROM
(SELECT product_key,COUNT(sales_order_key) AS sale_amount 
FROM ods_sales_orders GROUP BY product_key)a

第三步：选出前Top10产品

SELECT c.product_key AS product_key,c.sale_amount AS sale_amount,c.rn AS rn FROM (
SELECT a.product_key AS product_key,a.sale_amount AS sale_amount,
dense_rank() over(ORDER BY a.sale_amount DESC) AS rn
FROM
(SELECT product_key,COUNT(sales_order_key) AS sale_amount 
FROM ods_sales_orders GROUP BY product_key)a
)c
WHERE c.rn<=10;