【MySQL】求SQL连续问题，SQL连续问题的本质，统计用户连续登录

置顶 Fire Fish

已于 2023-08-13 12:06:10 修改

阅读量3.5k

点赞数 10

分类专栏： mysql 文章标签：数据库 mysql

于 2019-07-24 20:05:15 首次发布

本文链接：https://blog.csdn.net/yuchangyuan5237/article/details/97158616

版权

mysql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

文章目录

1. sql连续问题的概念
2. sql连续问题的本质
3. 分析过程（可直接看解决方案）
4. 解决方案
- 4.1. Mysql8.0版本（正确的写法）
- 4.2. Mysql5.7版本（正确的写法）
5. 附录番外篇
- 5.1. 背景和问题
- 5.2. 分析过程

本文详细介绍如何求解SQL连续问题，连续问题的本质是什么，最后给出了在MySQL5.7和MySQL8.0的求解方案。

前言：本文系作者 2019 年原创，谢绝转载

1. sql连续问题的概念

sql编程中常听到关于 连续 的问题，如：游戏连续签到7天可以获得奖品、计算用户活跃度连续登陆4天即认为活跃等。那么如何写sql算出那些用户满足呢？

2. sql连续问题的本质

表情包，分析一下的图像结果

这里先给出结论后面进行分析：

数据库中连续问题的本质就是：单调递增的等差数列，嘿，神奇吧跟数学联动起来啦
求解方法是：增加额外的等差递增的列，然后进行做差分组。rowId（编号） 是一个不错的选择

3. 分析过程（可直接看解决方案）

要解决的核心问题有：

连续的概念
怎么使不同的连续区间进入不同的分组

3.1. 如何计算用户连续登录7天

假设现在有张表记录了用户的登录日志，如下图，有2列分别是用户id列 user_id、登录时间列 login_time，一个用户可能有多条记录。问题：找出连续7天都登录的用户，具体是哪7天？

3.2. 连续登录在数据上的表现形式

怎么才叫连续，昨天登陆了，今天登陆了，就连续两天登陆了；如果明天也会登陆，那么就连续三天登陆了。这种含义在数据上是怎么表现的呢？如下：

今天的日期 - 昨天的日期 = 1，说明我连续两天登陆
明天的日期 - 今天的日期 = 1，说明我连续三天登陆了。那么差等于1的行都是连续的

但是，下一个问题就是，不同的连续登陆区间要怎么区分？

可能有的用户连续登录2天后，间隔了几天之后，又连续登录了3天

3.3. 如何用sql表现数据的这种关系

表情包，砸键盘的图像结果

基于以上分析可以知道，是不是连续的关键就看做差是否一致，但遗憾的是数据库是非常 不擅长行与行 之间的操作，如：下一行减去上一行，数据库 擅长列与列 的操作或连接操作，那能不能把下一行减去上一行的操作转化为列与列的操作呢，考虑增加一列如何？

另外一个问题是我们怎么样保证不同连续区间进入不同分组？如果只是等于1，那么不同的登录区间进入一个分组。既然要分组，那么分组的条件是什么？

为了简单，我们简化一下模型。连续问题往往只有两列数据，那用户登录问题来说，一列是：用户名，一列是登录时间（年月日）

3.4. 解决问题

我们拿用户名分组，登录时间分组，或者用户名和登录时间分组都是不合适的，因为他们都不满足我们的要求：把连续登陆数据分到一组来的目的。可是我们的连续数据来源就只有用户名和登录时间，登陆时间是增加的，可能连续的，可能不连续的，怎么在可能连续可能不连续的记录中找到共同点，成了问题的关键

连续登录时间 + [???] = 固定的数据

从以上公式看，连续增加的数据加上什么数据等于不变的数据，必然是加上连续递增等差的数据，在数据库中常见的连续递增的数据是什么，不就是 序号 ？那么故事的主角 row_number 出现了

3.5. 结论

表情包，perfect 的图像结果

👍🏻👍🏻，看到这里其实你大概就已经知道了，尽管我几乎没有画任何分析图，以后不管任何复杂的连续问题都是等差数列的缩影。到这里其实以上提出的问题都解决了，剩下的大家可以自己思考或者直接看解决方案

4. 解决方案

下面描述了处理问题的步骤：

新增一列rownum
```
-- mysql8.0的分窗函数
ROW_NUMBER() OVER ( ORDER BY login_time ) AS rownum
```
只要是等差单调递增的列就可以，在数据库中没有任何理由不用 row_number
用时间与新增列做差
```
-- mysql8.0的分窗函数
COUNT(1) OVER (PARTITION BY login_date - rownum) AS group_count
```
如果差一致，说明与时间列相对静止，又新增列是连续的，所以时间列也是连续的，又新增列的单调的，所以不同连续区间的差是不一样的
剩下的你根据需求改巴改巴就OK了
解题完成。

4.1. Mysql8.0版本（正确的写法）

-- 加上新增列
WITH temp01 AS (SELECT user_id, DATE(login_time) AS login_date, ROW_NUMBER() OVER ( ORDER BY login_time ) AS rownum
                FROM user_log),
-- 做差，分组统计
     temp02 AS (SELECT user_id,
                       login_date,
                       COUNT(1) OVER (PARTITION BY login_date - rownum) AS group_count
                FROM temp01)
-- 得到结果
SELECT *
FROM temp02
WHERE group_count >= 3;

4.2. Mysql5.7版本（正确的写法）

表情包，爆赞的图像结果

-- mysql5.7对于这种或其他复杂查询只能使用临时表或者存储过程
CREATE TEMPORARY TABLE temp01 (
    -- 排名
    SELECT @rownum := @rownum + 1 AS rownum, user_id, DATE(login_time) AS login_date
    FROM user_log,
         (SELECT @rownum := 0) t
    ORDER BY login_time);
CREATE TEMPORARY TABLE temp02 (
-- 分组
    SELECT login_date - rownum AS group_id
    FROM temp01
    GROUP BY login_date - rownum
    HAVING COUNT(1) >= 3);
-- 具体业务查询
SELECT *
FROM temp01 a
         INNER JOIN temp02 b
                    ON (login_date - rownum) = b.group_id