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

最新推荐文章于 2025-07-10 10:58:16 发布

原创最新推荐文章于 2025-07-10 10:58:16 发布 · 3.8k 阅读

16 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #sql

本文介绍如何利用MySQL的窗口函数解决查询连续3天登录的用户问题。通过为用户登录时间编号，计算参照时间并按用户和参照日期分组统计，找到登录次数大于等于3的用户。

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

题目
一、解题思路
二、具体步骤
三、题目使用的表和数据

题目

table_a （用户userid和登录时间time）求连续登录3天的用户数。

一、解题思路

①可以使用窗口函数，根据用户分组然后再根据登录时间升序，再进行编号，这里的编号函数可以使用row_number()、dense_rank()、rank()都可以，因为同一用户不可能在同一时间登录，所以同一用户不可能出现两个完全相同的时间。
②如果两个时间连续，必定登录的时间减去编号天数是相同的
③相同个数大于3的用户即为所求

二、具体步骤

1.窗口函数得到编号(date_)

代码如下（示例）：

SELECT * ,dense_rank() OVER(PARTITION BY userid ORDER BY time)date_ FROM user

运行结果如图：
在这里插入图片描述

2.使用登录时间减去编号对应的天数得到参照时间（flag_date),再将参照日期用date（）函数转换为日期

代码如下（示例）：

SELECT *,date(date_sub(time,INTERVAL  date_ DAY )) flag_date FROM (
SELECT * ,dense_rank() OVER(PARTITION BY userid ORDER BY time)date_ FROM user)a

运行结果如图：
在这里插入图片描述

3.最后根据userid和flag_date分组，并统计次数，次数大于等于3即为所求

代码如下（示例）：

SELECT userid FROM (SELECT userid,
                           DATE(DATE_SUB(time, INTERVAL date_ DAY)) flag_date
                    FROM
                        (SELECT *, DENSE_RANK() OVER (PARTITION BY userid ORDER BY time) date_ FROM user) a
                    GROUP BY userid, flag_date
HAVING
    COUNT(flag_date) >= 3) b

三、题目使用的表和数据

CREATE TABLE `user` (
  `userid` int DEFAULT NULL,
  `time` datetime DEFAULT NULL
);
INSERT INTO user VALUES (1,'2019-01-01 12:34:22'),(2,'2019-01-01 12:22:22'),
                        (3,'2019-01-03 12:34:22'),(1,'2019-01-02 12:34:22'),
                        (2,'2019-01-03 12:34:20'),(3,'2019-01-03 12:34:10'),
                        (2,'2019-01-04 12:34:20'),(1,'2019-01-03 12:34:20'),
                        (3,'2019-01-02 12:34:20'),(4,'2022-08-01 19:43:00'),
                        (4,'2022-08-02 19:43:09'),(3,'2019-01-04 12:34:20')