HiveSQL 面试题 - 查询累积销售件数高于其所属品类平均数的商品

1 需求

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品。

• 商品信息表 sku_info

sku_id(商品id)	name(商品名称)	category_id(分类id)	from_date(上架日期)	price(商品价格)
1	xiaomi 10	1	2020-01-01	2000
6	洗碗机	2	2020-02-01	2000
9	自行车	3	2020-01-01	1000

• 订单明细表 order_detail1

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

• 期望结果

sku_id	name	sum_num	cate_avg_num
2	手机壳	6044	1546
5	破壁机	242	194
7	热水壶	252	194
8	微波炉	253	194
12	遮阳伞	20682	5373

2 解答

• 思路

使用开窗函数统计基本指标再做统计即可，参考如下实现方案

• 实现

1 使用开窗函数，统计基本指标

select sku_info.sku_id,
       name,
       category_id,
       sum(sku_num) over (partition by sku_info.sku_id)                sum_sku,
       sum(sku_num) over (partition by category_id)                    sum_cate,
       count(DISTINCT sku_info.sku_id) over (partition by category_id) num_cate
from sku_info
     inner join order_detail on sku_info.sku_id = order_detail.sku_id

sku_id	name	category_id	sum_sku	sum_cate	num_cate
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
1	xiaomi 10	1	51	6184	4
2	手机壳	1	6044	6184	4
2	手机壳	1	6044	6184	4

2 计算每个品类下的平均件数，并对数据进行去重

select sku_id,
       name,
       sum_sku                          sum_num,
       cast(sum_cate / num_cate as int) cate_avg_num
from (
         select sku_info.sku_id,
                name,
                category_id,
                sum(sku_num) over (partition by sku_info.sku_id)                sum_sku,
                sum(sku_num) over (partition by category_id)                    sum_cate,
                count(DISTINCT sku_info.sku_id) over (partition by category_id) num_cate
         from sku_info
              inner join order_detail on sku_info.sku_id = order_detail.sku_id
         ) t1
GROUP by sku_id, name, sum_sku, sum_cate, num_cate

sku_id	name	sum_num	cate_avg_num
1	xiaomi 10	51	1546
10	帐篷	299	5373
11	烧烤架	320	5373
12	遮阳伞	20682	5373
2	手机壳	6044	1546
3	apple 12	36	1546
4	xiaomi 13	53	1546
5	破壁机	242	194
6	洗碗机	32	194
7	热水壶	252	194
8	微波炉	253	194
9	自行车	194	5373

3 从第二步的结果中取出所需的数据

select *
from (
    select sku_id,
    name,
    sum_sku                          sum_num,
    cast(sum_cate / num_cate as int) cate_avg_num
    from (
        select sku_info.sku_id,
        name,
        category_id,
        sum(sku_num) over (partition by sku_info.sku_id)                sum_sku,
        sum(sku_num) over (partition by category_id)                    sum_cate,
        count(DISTINCT sku_info.sku_id) over (partition by category_id) num_cate
        from sku_info
        inner join order_detail on sku_info.sku_id = order_detail.sku_id
    ) t1
    GROUP by sku_id, name, sum_sku, sum_cate, num_cate
) t2
where sum_num > cate_avg_num;

sku_id	name	sum_num	cate_avg_num
12	遮阳伞	20682	5373
2	手机壳	6044	1546
5	破壁机	242	194
7	热水壶	252	194
8	微波炉	253	194