MySQL计算连续3天登陆的用户

矫情吗；* 2023-02-25 10:01 13阅读 0赞

# 解题思路 #

> 根据user\_id对用户登陆日期进行开窗分组排序，再用登陆日期减去序号rn,如果连续的话，登陆日期减去序号的值应该是相同的。

# 开始解题 #

## 创建数据集 ##

USE demo;
    DROP TABLE IF EXISTS login3days;
    CREATE TABLE login3days(user_id varchar(20),login_date date);
    INSERT INTO login3days VALUES ('a','2020-07-01'),('a','2020-07-02'),('a','2020-07-03'),('b','2020-07-01');
    SELECT * FROM login3days;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]

## 分析过程 ##

SELECT user_id,login_date,rn,date_add(login_date,interval -rn day) as date_flag
    FROM (
    	SELECT *
    		,row_number() over (partition by user_id order by login_date asc) as rn
    	FROM login3days) t

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]

## 最终答案 ##

SELECT USER_id,count(*) as days_count
    FROM (
    SELECT *
    	,row_number() over (partition by user_id order by login_date asc) as rn
    FROM login3days) t
    group by user_id,DATE_ADD(login_date,interval -rn day)
    HAVING days_count = 3

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70]: /images/20230209/d29db1cd2fd443409e4d941fb4c85790.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 1]: /images/20230209/3e27a7a7e16143bb9110ab9d84be1b10.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMjQ2NzAy_size_16_color_FFFFFF_t_70 2]: /images/20230209/27670817b8ad48bda0a90545786ff401.png