目录
1. 查询销售件数高于品类平均数的商品
1.1 题目需求
从订单明细表(order_detail)中查询累积销售件数高于其所属品类平均数的商品。
期望结果如下:
sku_id | name | sum_num | cate_avg_num |
---|---|---|---|
2 | 手机壳 | 302 | 110.5 |
5 | 破壁机 | 242 | 194.75 |
7 | 热水壶 | 252 | 194.75 |
8 | 微波炉 | 253 | 194.75 |
10 | 帐篷 | 299 | 290.5 |
11 | 烧烤架 | 320 | 290.5 |
12 | 遮阳伞 | 349 | 290.5 |
1.2 代码实现
hive>
select sku_id,
name,
sum_num,
cate_avg_num
from (
select od.sku_id,
category_id,
name,
sum_num,
avg(sum_num) over (partition by category_id) cate_avg_num
from (
select sku_id,
sum(sku_num) sum_num
from order_detail
group by sku_id
) od
left join
(
select sku_id,
name,
标签:sku,中级,sum,Hive,num,SQL,avg,id,select
From: https://blog.csdn.net/qq_45115959/article/details/142253707