分组+窗口函数判断连续天数

分组+窗口函数判断连续天数

题目

请你统计最大连续回答问题的天数大于等于3天的用户及其等级?

用到的表

SELECT * 
FROM author_tb;

SELECT *
FROM answer_tb;

表 author_tb

表 answer_tb

创建表并初始化表中数据

drop table if exists author_tb;
CREATE TABLE author_tb(
author_id int(10) NOT NULL, 
author_level int(10) NOT NULL,
sex char(10) NOT NULL);
INSERT INTO author_tb VALUES(101 , 6, 'm');
INSERT INTO author_tb VALUES(102 , 1, 'f');
INSERT INTO author_tb VALUES(103 , 1, 'm');
INSERT INTO author_tb VALUES(104 , 3, 'm');
INSERT INTO author_tb VALUES(105 , 4, 'f');
INSERT INTO author_tb VALUES(106 , 2, 'f');
INSERT INTO author_tb VALUES(107 , 2, 'm');
INSERT INTO author_tb VALUES(108 , 5, 'f');
INSERT INTO author_tb VALUES(109 , 6, 'f');
INSERT INTO author_tb VALUES(110 , 5, 'm');

drop table if exists answer_tb;
CREATE TABLE answer_tb(
answer_date date NOT NULL, 
author_id int(10) NOT NULL,
issue_id char(10) NOT NULL,
char_len int(10) NOT NULL);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E001' ,150);
INSERT INTO answer_tb VALUES('2021-11-1', 101, 'E002', 200);
INSERT INTO answer_tb VALUES('2021-11-1',102, 'C003' ,50);
INSERT INTO answer_tb VALUES('2021-11-1' ,103, 'P001', 35);
INSERT INTO answer_tb VALUES('2021-11-1', 104, 'C003', 120);
INSERT INTO answer_tb VALUES('2021-11-1' ,105, 'P001', 125);
INSERT INTO answer_tb VALUES('2021-11-1' , 102, 'P002', 105);
INSERT INTO answer_tb VALUES('2021-11-2',  101, 'P001' ,201);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C002', 200);
INSERT INTO answer_tb VALUES('2021-11-2',  110, 'C001', 225);
INSERT INTO answer_tb VALUES('2021-11-2' , 110, 'C002', 220);
INSERT INTO answer_tb VALUES('2021-11-3', 101, 'C002', 180);
INSERT INTO answer_tb VALUES('2021-11-4' ,109, 'E003', 130);
INSERT INTO answer_tb VALUES('2021-11-4', 109, 'E001',123);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C001',160);
INSERT INTO answer_tb VALUES('2021-11-5', 108, 'C002', 120);
INSERT INTO answer_tb VALUES('2021-11-5', 110, 'P001', 180);
INSERT INTO answer_tb VALUES('2021-11-5' , 106, 'P002' , 45);
INSERT INTO answer_tb VALUES('2021-11-5' , 107, 'E003', 56);

思路

难点：“最大连续回答问题的天数”该如何实现？

初次看到这个问题，确实非常不好实现，这里，我们先对 answer_tb表 根据日期对作者author_id进行分组，因为每天每个作者可能会回答多个问题。代码和结果如下：

SELECT answer_date,
       author_id
FROM answer_tb
GROUP BY answer_date, author_id;

看到分组的结果后，我们突发奇想，可以把分组的结果作为临时表再次进行操作，如何操作哪？

• 根据 author_id 进行 partition ，然后使用 窗口函数dense_rank()

代码如下：

思路TH
t
AS
(
	SELECT answer_date,
		   author_id
	FROM answer_tb
	GROUP BY answer_date, author_id
)
SELECT author_id,
			 answer_date,
			 DENSE_RANK() OVER(PARTITION BY author_id ORDER BY answer_date) AS 'dense_rank'
FROM t;

通过查询得到的这个结果，我们基本上就实现了“连续日期”的这个问题。以上查询了2次，我们可以把分组和窗口函数合并成成只需要查询一次，代码如下：

SELECT answer_date,
       author_id,
       dense_rank() OVER w AS 'cnt'
FROM answer_tb
GROUP BY answer_date, author_id
WINDOW w AS(PARTITION BY author_id ORDER BY answer_date); # 这里我们把窗口体写在了尾部

2次执行的结果是一模一样的，由此我们也能够看出来，先执行的是分组函数，窗口函数是最后执行的。

得到这个临时表后，日期虽然进行了排名，但是对于不连续的日期也进行了排序，所以这里我们要使用如下的逻辑进行判断是否为连续日期：

• 如果是连续日期，日期-排名 是相等的；

通过 筛选 日期-排名 的值是否相等，我们既可以筛选出连续日期，需要用到一个函数date_sub()，实现的代码如下：

SELECT author_id,
			 COUNT(1) day_cnt
			FROM
			(
				SELECT answer_date,
							 author_id,
							 dense_rank() OVER w AS 'cnt'
				FROM answer_tb
				GROUP BY answer_date, author_id
				WINDOW w AS(PARTITION BY author_id ORDER BY answer_date)
			) t1
GROUP BY author_id, DATE_SUB(answer_date, INTERVAL cnt DAY)

注意排序的条件：DATE_SUB(answer_date,INTERVAL cnt DAY)， 如果是连续日期情况，通过 "日期-对应排序"得到的结果进行count,即可得到对应的连续日期的天数。

• 最后，我们对目前到的的临时表进行筛选 day_cnt 的日期 >= 3 即可得到题目所要求的结果，代码如下：

WITH
t
AS
(
SELECT author_id,
			 COUNT(1) day_cnt
			FROM
			(
				SELECT answer_date,
							 author_id,
							 dense_rank() OVER w AS 'cnt'
				FROM answer_tb
				GROUP BY answer_date, author_id
				WINDOW w AS(PARTITION BY author_id ORDER BY answer_date)
			) t1
GROUP BY author_id, DATE_SUB(answer_date, INTERVAL cnt DAY)
HAVING day_cnt >= 3
)SELECT t.author_id, tt.author_level, t.day_cnt AS 'days_cnt'
FROM t
INNER JOIN author_tb tt
ON t.author_id = tt.author_id;

总结

这道题目到这里也就结束了，本题属于困难题，主要的难度在于如何求作者连续回答的天数，我们使用所掌握的SQL查询知识点进行求解，把问题分成一步一步的进行求解：

• 由于每天一个作者可以回答多个问题，我们根据日期和作者id进行分组，分组的结果对作者id进行PARTITION BY，得到每个作者不同日期的排名；
• 然后，由于日期不连续也会排名，我们要知道如果是连续日期 日期-对应排名 是不变的；
• 各级author_id和 日期-对应排名 进行分组聚合；
• 筛选连续天数 >= 3 的作者；

到此，本题也就结束了，整体来说本题使用了分组查询和窗口函数已经一些常用的日期处理函数和窗口函数，附录中我会补充到，谢谢大家。

附录

dense_rank() 和 rank()

下面的查询显示了RANK（）和DENSE_RANK（）之间的差异，前者生成带间隙的列组，后者生成不带间隙的列组。

日期加减数字天数

• DATE_SUB()
• DATE_ADD()

SELECT NOW() FROM DUAL;
SELECT DATE_SUB(NOW(),INTERVAL 3 DAY) FROM DUAL;

DATE_ADD() 同理可得。

【每日一记，防止忘记】