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

题目需求

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

期望结果如下：

需要用到的表：

订单明细表：order_detail

解题思路

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