MySQL经典题目1--求用户最长的连续登录天数”

-- 请用SQL实现，求用户最长的连续登录天数”？？

答：

1.建库建表填写数据

CREATE DATABASE test2;
USE test2;
DROP TABLE IF EXISTS tb_dau;
CREATE TABLE `tb_dau` (
    `fdate` DATE,
    `user_id` INT
);
INSERT INTO tb_dau(fdate, user_id) VALUES 
('2023-01-01', 10000),
('2023-01-02', 10000),
('2023-01-03', 10000),
('2023-01-03', 10000),
('2023-01-04', 10000),
('2023-01-02', 20000),
('2023-01-02', 20000),
('2023-01-03', 20000),
('2023-01-02', 30000),
('2024-01-04', 40000),
('2024-01-02', 20000),
('2024-01-03', 20000),
('2024-01-02', 30000);

2.分步骤解析：

方式一

①先对id和日期进行去重

# 先对id和日期进行去重
SELECT user_id,fdate 
FROM tb_dau GROUP BY user_id,fdate

 

②通过窗口函数进行排序

# 通过窗口函数进行排序
SELECT user_id,fdate,row_number() over(PARTITION BY user_id ORDER BY fdate) r
FROM tb_dau

 

③计算减去名次

# 计算减去名次
-- DATE_SUB(date, INTERVAL expr unit) 其中,date是要进行减法运算的日期,expr是要减去的时间量,unit是时间量的单位
SELECT user_id,fdate,expr,DATE_SUB(fdate,INTERVAL expr DAY) AS DAY
FROM(
	SELECT user_id,fdate,row_number() over(PARTITION BY user_id ORDER BY fdate) expr
	FROM tb_dau) AS t

 

④计算连续天数

# 计算连续天数
SELECT user_id,DAY,COUNT(DAY) AS active_day
FROM (
	SELECT user_id,fdate,r,DATE_SUB(fdate,INTERVAL r DAY) AS DAY
	FROM (
		SELECT user_id,fdate,row_number() over(PARTITION BY user_id ORDER BY fdate) r
		FROM tb_dau) AS t
) AS t1
GROUP BY user_id,DAY

 

⑤计算最大登录天数

# 计算最大登录天数
SELECT user_id,MAX(active_day) AS max_consec_days
FROM (
    SELECT user_id,DAY,COUNT(DAY) AS active_day
    FROM (
        SELECT user_id,fdate,expr,DATE_SUB(fdate,INTERVAL expr DAY) AS DAY
        FROM (
            SELECT user_id,fdate,row_number() over(PARTITION BY user_id ORDER BY fdate) expr
            FROM tb_dau) AS t
    ) AS t1
GROUP BY user_id,DAY
) AS t2
GROUP BY user_id

方式二：若求2023年1月1日——2023年1月31日用户最大登录的天数？

SELECT b.user_id user_id ,MAX(cn) max_consec_days 
FROM (
	SELECT a.user_id ,COUNT(1) cn 
	FROM (
		SELECT user_id ,fdate-(row_number() over(PARTITION BY user_id ORDER BY fdate)) sub 
		FROM tb_dau
        WHERE fdate>='2023-01-01'AND fdate<='2023-01-31')a 
	GROUP BY a.user_id ,a.sub)b 
GROUP BY b.user_id;

方式三：

SELECT S2.user_id, MAX(S2.num) max_consec_days FROM
    (SELECT S1.user_id, DATE_SUB(S1.fdate, INTERVAL rank1 DAY), COUNT(*) num FROM
        (SELECT user_id, fdate, row_number() over(PARTITION BY user_id ORDER BY fdate) rank1
         FROM tb_dau
         WHERE fdate>='2023-01-01'AND fdate<='2023-01-31') S1
    GROUP BY S1.user_id, DATE_SUB(S1.fdate, INTERVAL rank1 DAY)) S2
GROUP BY S2.user_id