大数据SQL题39 国庆期间的7日动销率和滞销率

吃派小星

已于 2024-06-06 10:45:02 修改

阅读量599

点赞数 5

分类专栏：大数据SQL题文章标签：大数据 sql hive

于 2024-06-05 11:48:44 首次发布

本文链接：https://blog.csdn.net/qq_35237689/article/details/139468383

版权

大数据SQL题专栏收录该内容

50 篇文章

订阅专栏

原题链接：http://practice.atguigu.cn/#/question/39/desc?qType=SQL

题目需求

动销率定义为品类商品中一段时间内有销量的商品占当前已上架总商品数的比例（有销量的商品/已上架总商品数）。

滞销率定义为品类商品中一段时间内没有销量的商品占当前已上架总商品数的比例。（没有销量的商品 / 已上架总商品数）。

只要当天任一店铺有任何商品的销量就输出该天的结果

从订单明细表（order_detail）和商品信息表（sku_info）表中求出国庆7天（指2021-10-01-2021-10-07）每天每个品类的商品的动销率和滞销率

期望结果如下（截取部分）：

category_id（品类id）	first_sale_rate<decimal(16,2)>（动销)	first_unsale_rate<decimal(16,2)>（滞销)	second_sale_rate<decimal(16,2)>（动销)	second_unsale_rate<decimal(16,2)>（滞销)
1	1.00	0.00	0.50	0.50
2	0.75	0.25	0.75	0.25
3	0.25	0.75	0.75	0.25

需要用到的表：

订单明细表：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
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

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

解题思路

SELECT  t1.category_id,
        cast(sales_cnt_1/sku_cnt_1 AS decimal(16,2))     AS first_sale_rate,
        cast(1 - sales_cnt_1/sku_cnt_1 AS decimal(16,2)) AS first_unsale_rate,
        cast(sales_cnt_2/sku_cnt_2 AS decimal(16,2))     AS second_sale_rate,
        cast(1 - sales_cnt_2/sku_cnt_2 AS decimal(16,2)) AS second_unsale_rate,
        cast(sales_cnt_3/sku_cnt_3 AS decimal(16,2))     AS third_sale_rate,
        cast(1 - sales_cnt_3/sku_cnt_3 AS decimal(16,2)) AS third_unsale_rate,
        cast(sales_cnt_4/sku_cnt_4 AS decimal(16,2))     AS fourth_sale_rate,
        cast(1 - sales_cnt_4/sku_cnt_4 AS decimal(16,2)) AS fourth_unsale_rate,
        cast(sales_cnt_5/sku_cnt_5 AS decimal(16,2))     AS fifth_sale_rate,
        cast(1 - sales_cnt_5/sku_cnt_5 AS decimal(16,2)) AS fifth_unsale_rate,
        cast(sales_cnt_6/sku_cnt_6 AS decimal(16,2))     AS sixth_sale_rate,
        cast(1 - sales_cnt_6/sku_cnt_6 AS decimal(16,2)) AS sixth_unsale_rate,
        cast(sales_cnt_7/sku_cnt_7 AS decimal(16,2))     AS seventh_sale_rate,
        cast(1 - sales_cnt_7/sku_cnt_7 AS decimal(16,2)) AS seventh_unsale_rate
FROM
(
	SELECT  si.category_id,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-01',od.sku_id,NULL)) AS sales_cnt_1,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-02',od.sku_id,NULL)) AS sales_cnt_2,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-03',od.sku_id,NULL)) AS sales_cnt_3,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-04',od.sku_id,NULL)) AS sales_cnt_4,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-05',od.sku_id,NULL)) AS sales_cnt_5,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-06',od.sku_id,NULL)) AS sales_cnt_6,
	        COUNT(DISTINCT IF(od.create_date = '2021-10-07',od.sku_id,NULL)) AS sales_cnt_7
	FROM order_detail od
	JOIN sku_info si
	ON od.sku_id = si.sku_id
	WHERE od.create_date BETWEEN '2021-10-01' AND '2021-10-07'
	GROUP BY  si.category_id
) t1
LEFT JOIN
(
	SELECT  category_id,
	        COUNT(DISTINCT IF(from_date <= '2021-10-01',sku_id,NULL)) AS sku_cnt_1,
	        COUNT(DISTINCT IF(from_date <= '2021-10-02',sku_id,NULL)) AS sku_cnt_2,
	        COUNT(DISTINCT IF(from_date <= '2021-10-03',sku_id,NULL)) AS sku_cnt_3,
	        COUNT(DISTINCT IF(from_date <= '2021-10-04',sku_id,NULL)) AS sku_cnt_4,
	        COUNT(DISTINCT IF(from_date <= '2021-10-05',sku_id,NULL)) AS sku_cnt_5,
	        COUNT(DISTINCT IF(from_date <= '2021-10-06',sku_id,NULL)) AS sku_cnt_6,
	        COUNT(DISTINCT IF(from_date <= '2021-10-07',sku_id,NULL)) AS sku_cnt_7
	FROM sku_info
	WHERE from_date <= '2021-10-07'
	GROUP BY  category_id
) t2
ON t1.category_id = t2.category_id