大数据SQL题27 找出销售额连续3天超过100的商品

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

题目需求

从订单详情表（order_detail）中找出销售额连续3天超过100的商品

期望结果如下：

sku_id（商品id）
1
10
11
12
2
3
4
5
6
7
8
9

需要用到的表：

订单明细表：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

解题思路

1. 过滤出所有销售额大于100的日期，使用ROW_NUMBER()找出连续区间

SELECT  sku_id
FROM
(
	SELECT  sku_id
	FROM
	(
		SELECT  sku_id,
		        create_date,
		        ROW_NUMBER() OVER (PARTITION BY sku_id ORDER BY  create_date) AS rn
		FROM order_detail
		GROUP BY  sku_id,
		          create_date
		HAVING SUM(price * sku_num) > 100
	) t1
	GROUP BY  sku_id,
	          DATE_SUB(create_date,rn)
	HAVING COUNT(1) >= 3
) t2
GROUP BY  sku_id 

2. 双排名列找出连续区间

SELECT  sku_id
FROM
(
	SELECT  sku_id
	FROM
	(
		SELECT  sku_id,
		        is_achieve,
		        ROW_NUMBER() OVER (PARTITION BY (sku_id,is_achieve) ORDER BY  create_date) AS rn,
		        DATEDIFF(create_date,'1970-01-01')                                         AS date_rn
		FROM
		(
			SELECT  sku_id,
			        create_date,
			        IF(SUM(price * sku_num) > 100,1,0) AS is_achieve
			FROM order_detail
			GROUP BY  sku_id,
			          create_date
		) t1
	) t2
	GROUP BY  sku_id,
	          date_rn - rn
	HAVING SUM(is_achieve) >= 3
) t3
GROUP BY  sku_id

3. 使用lag()窗口函数取前两条记录判断是否近3天

SELECT  sku_id
FROM
(
	SELECT  sku_id,
	        create_date,
	        is_achieve,
	        LAG(create_date,1,NULL) OVER (PARTITION BY sku_id ORDER BY  create_date) AS lag_1_date,
	        LAG(create_date,2,NULL) OVER (PARTITION BY sku_id ORDER BY create_date)  AS lag_2_date,
	        LAG(is_achieve,1,0) OVER (PARTITION BY sku_id ORDER BY create_date)      AS lag_1_achieve,
	        LAG(is_achieve,2,0) OVER (PARTITION BY sku_id ORDER BY create_date)      AS lag_2_achieve
	FROM
	(
		SELECT  sku_id,
		        create_date,
		        IF(SUM(price * sku_num) > 100,1,0) AS is_achieve
		FROM order_detail
		GROUP BY  sku_id,
		          create_date
	) t1
) t2
WHERE DATEDIFF(create_date, lag_1_date) = 1
AND DATEDIFF(create_date, lag_2_date) = 2
AND lag_1_achieve = 1
AND lag_2_achieve = 1
GROUP BY  sku_id

4. 使用UDTF函数explode()生成近3天日期行

SELECT  t2.sku_id
FROM
(
	SELECT  sku_id,
	        DATE_sub(create_date,ROW_NUMBER() OVER (PARTITION BY (sku_id,create_date)) - 1) AS range_date
	FROM
	(
		SELECT  sku_id,
		        create_date
		FROM order_detail
		GROUP BY  sku_id,
		          create_date
	) t1 LATERAL VIEW EXPLODE(split(space(2), '')) tmp AS element
) t2
JOIN
(
	SELECT  sku_id,
	        create_date,
	        IF(SUM(price * sku_num) > 100,1,0) AS is_achieve
	FROM order_detail
	GROUP BY  sku_id,
	          create_date
) t3
ON t2.sku_id = t3.sku_id AND t2.range_date = t3.create_date
GROUP BY  t2.sku_id
HAVING SUM(t3.is_achieve) >= 3

5. 使用UDTF函数posexplode()生成近3天日期行

SELECT  t2.sku_id
FROM
(
	SELECT  sku_id,
	        DATE_sub(create_date,pos) AS range_date
	FROM
	(
		SELECT  sku_id,
		        create_date
		FROM order_detail
		GROUP BY  sku_id,
		          create_date
	) t1 LATERAL VIEW POSEXPLODE(split(space(2), '')) tmp AS pos, element
) t2
JOIN
(
	SELECT  sku_id,
	        create_date,
	        IF(SUM(price * sku_num) > 100,1,0) AS is_achieve
	FROM order_detail
	GROUP BY  sku_id,
	          create_date
) t3
ON t2.sku_id = t3.sku_id AND t2.range_date = t3.create_date
GROUP BY  t2.sku_id
HAVING SUM(t3.is_achieve) >= 3