10. 查询销售件数高于品类平均数的商品

题目需求

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

期望结果如下：

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

需要用到的表：

商品信息表：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_detail

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
…	…	…	…	…	…

实现一

-- 1） 把 sku_info 中的 name、category_id 字段添加到 order_detail 表中。
-- order_detail join sku_info on order_detail.sku_id = sku_info.sku_id
with tmp as (
    select order_detail.sku_id  as sku_id,
           order_detail.sku_num as sku_num,
           sku_info.name        as name,
           sku_info.category_id as category_id
    from order_detail
             join sku_info on order_detail.sku_id = sku_info.sku_id
)

-- 4） 取出 `累积销售件数` > `其所属品类平均数` 的商品
select distinct sku_id,
                name,
                sum_num,
                cate_avg_num
from (
         select sku_id,
                name,
                -- 2） 统计每件商品的累积销售件数
                sum(sku_num) over (partition by sku_id)                             as sum_num,
                -- 3）查询每个品类所有商品销售件数的平均数 = 每个品类所有商品销售总件数 / 每个品类所有商品种数
                cast(sum(sku_num) over (partition by category_id) /
                     count(distinct sku_id) over (partition by category_id) as int) as cate_avg_num
         from tmp
     ) t3
where sum_num > cate_avg_num;

总结

1. where条件中进行判断的 sum_num 和 cate_avg_num ，需要数据类型一致。

题目来源

http://practice.atguigu.cn/#/question/10/desc?qType=SQL