某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

SQL194. 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

author_id	author_level	sex
101	6	m
102	1	f
103	1	m
104	3	m
105	4	f
106	2	f
107	2	m
108	5	f
109	6	f
110	5	m

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：

answer_date	author_id	issue_id	char_len
2021-11-01	101	E001	150
2021-11-01	101	E002	200
2021-11-01	102	C003	50
2021-11-01	103	P001	35
2021-11-01	104	C003	120
2021-11-01	105	P001	125
2021-11-01	102	P002	105
2021-11-02	101	P001	201
2021-11-02	110	C002	200
2021-11-02	110	C001	225
2021-11-02	110	C002	220
2021-11-03	101	C002	180
2021-11-04	109	E003	130
2021-11-04	109	E001	123
2021-11-05	108	C001	160
2021-11-05	108	C002	120
2021-11-05	110	P001	180
2021-11-05	106	P002	45
2021-11-05	107	E003	56

请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：

author_id	author_level	days_cnt
101	6	3

示例1

输入:

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);

输出:

101|6|3

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6388KB, 提交时间: 2022-01-22

select author_id,author_level,days_cnt
FROM
(
select author_id,count(diff) as days_cnt
FROM(
    select *,answer_date-t_rank as diff
    FROM(
        -- select distinct *
        -- from(
            select distinct distinct answer_date,author_id,
            dense_rank()over(partition by author_id order by answer_date) as t_rank
            from answer_tb 
            -- t1
        )t2
    )t3
group by author_id,diff
having(days_cnt)>=3
)t4
join author_tb
using(author_id)

Mysql 解法, 执行用时: 37ms, 内存消耗: 6412KB, 提交时间: 2022-02-10

SELECT *
FROM
(
	SELECT DISTINCT author_id, author_level, COUNT(*) OVER(PARTITION BY author_id, const_col) AS days_cnt
	FROM
	(
		SELECT 
			*, DAY(answer_date) - ROW_NUMBER() OVER(PARTITION BY author_id ORDER BY answer_date) AS const_col
		FROM
		(
			SELECT DISTINCT
				author_id, answer_date, author_level
			FROM author_tb au
			JOIN answer_tb an USING (author_id)
		) AS t1
	) AS t2
) AS t3
WHERE days_cnt >= 3

Mysql 解法, 执行用时: 37ms, 内存消耗: 6448KB, 提交时间: 2022-01-25

select author_id,author_level,max(cnt)
from
(select author_id,author_level,s_date,count(1)cnt
from
(select author_id,author_level,date_sub(answer_date,interval rnk day)s_date
from
(select a.author_id author_id,author_level,answer_date,rank()over(partition by author_id order by answer_date )rnk
from author_tb a join answer_tb an on a.author_id=an.author_id
group by a.author_id,answer_date,author_level)a)b
group by author_id,author_level,s_date
having cnt>=3)c
group by author_id,author_level

Mysql 解法, 执行用时: 37ms, 内存消耗: 6456KB, 提交时间: 2022-01-22

select t2.author_id,author_level,t2.day_cnt
from 
    (select author_id,count(*) day_cnt
    from 
        (select answer_date,author_id,
                dense_rank()over(partition by author_id order by answer_date) as cnt
        from answer_tb
        group by answer_date,author_id
        ) t1
    group by author_id,date_sub(answer_date,interval cnt day)
    having count(*)>=3
    ) t2
join author_tb
on t2.author_id=author_tb.author_id
order by t2.author_id;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6508KB, 提交时间: 2022-01-24

select
a.author_id,
author_level,
count(*) as days_cnt
from 


    (SELECT
    answer_date,author_id,
    rank()over(partition by author_id order by answer_date) as r
    from answer_tb 
    group by answer_date,author_id) as a
join author_tb as at
on a.author_id = at.author_id
group by a.author_id,author_level
having days_cnt >=3
order by a.author_id

上一题

下一题

详情