SQL连续问题的本质

@TOC

连续问题的概念

我们常听到关于"连续"概念的问题，如1、游戏连续签到7天可以获得奖品；2、计算用户活跃度，连续登陆4天即认为活跃等。 那么如何写sql算出那些用户满足呢，继续看

连续问题的本质

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

1. 数据库中“连续问题”的本质就是：等差数列，连续函数，单调递增，系2019年全网独创禁止转载
2. 求解方法是：增加额外的等差递增的列，然后进行做差分组。rowId是一个不错的选择

分析过程（可直接看最后题解）

要解决的核心问题有：

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

1、如何计算用户连续7天登录

有张表记录了用户的登录日志，有两列，用户名userId,login_time。一个用户可能有多条记录。问题：怎么找出连续7天登录的用户，是哪7天。

2、连续登录在数据上的表现形式

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

• 今天的日期 - 昨天的日期 = 1，说明我连续两天登陆
• 明天的日期 - 昨天的日期 = 1，说明我连续三天登陆了。那么所有差等于1的行都是连续的（注意第一行要包括）。

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

3、如何用sql表现数据的这种关系

基于以上分析可以知道：是不是连续的关键就看做差是否一致。但遗憾的是数据库是非常不擅长做这种下一行减去上一行的操作的，数据库擅长的是"连接"操作， 数据库擅长左边和右边的操作。那是不是考虑增加一列呢？

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

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

4、解决问题

我们拿"用户名"分组，"登录时间"分组，或者"用户名和登录时间"分组都是不合适的，因为他们都不满足我们目的"把连续登陆数据分到一组来的目的"。

关键就是我们的连续数据来源就是登录时间，登陆时间是增加的，可能连续的，可能不连续的，怎么在可能连续可能不连续的记录中找到共同点，，成了问题的关键

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

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

5、结论

恭喜，看到这里其实你大概就已经懂了，尽管我几乎没有画任何分析图，以后不管任何复杂的连续问题都是等差数列的缩影。

到这里其实以上提出的问题都解决了，剩下的大家可以自己思考或者按文章开头给出的结论进行解决。下面是番外（有兴趣的同学可以先尝试吧文章提出的问题解决之后再看看番外一起交流）

题解

1. 新增列，只要是等差单调递增的列就可以，在数据库中没有任何理由不用row_number。0，1，2，3，4

-- mysql8.0的分窗函数
ROW_NUMBER() OVER ( ORDER BY login_time ) AS rownum

2. 用时间与新增列做差，如果差一致，说明与时间列相对静止，又新增列是连续的，所以时间列也是连续的，又新增列的单调的，所以不同连续区间的差是不一样的

-- mysql8.0的分窗函数
COUNT(1) OVER (PARTITION BY login_date - rownum) AS group_count

3. 剩下的你根据需求改巴改巴就OK了
4. 解题完成。

《正确的写法》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;

《正确的写法》Mysql5版本

-- mysql5.0 对于这种或其他复杂查询只能使用临时表或者存储过程
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

番外篇（可以不听我扯废话了）

---

背景和问题

只有两列数据：user_id列，login_time列。现在有一下问题：求连续登陆超过3天的记录

分析过程

1、怎么才叫连续登陆？

答：我昨天登陆了，我今天也登陆了，我明天也登陆，就是连续登陆（现象）

2、在数据上是什么表现？

答：只能从时间上找"数据原因"。如果【今天日期-昨天日期=1且明天日期-今天日期=1】则说明连续两天登录，也就是错位相减。所以有下面的推论：

下一行日期-上一行日期=1(数据上) ===> 说明连续(现象上)

但是数据库是不擅长行与行之间的操作，数据库擅长列与列直接的操作，怎么可以转换？

3、下一个问题是有不同的连续登陆区间(现象上)在数据上就是不同的分组，那么分组的条件是什么

找到这个分组的group_id问题就解决了，肯定不能是user_id，也不能是login_time，但是只有这两列，我们可能需要引入第三方列

对这个列有什么要求：

1. 这个列要怎么保证不同连续区间的group_id不同
2. 这个列要怎么保证相同连续区间的group_id相同

下面我只能说有想象成分:

1. 说根据数学知识，我们知道单调函数每个值，是不同的
2. 根据物理知识，我们知道相对静止的物体，距离是一样的。
3. 相对静止在这里表现为跟时间变化的差一样 ===> 等差数列
4. 单调在这里表现为 ===> 单调递增
5. 等差数列 + 单调递增 ===> 在数据库中用什么表示呢 于是row_number就出现了

4、结论

row_number可以满足这个要求。 结束!!!

我不允许你看过后以后不会解连续问题！，不然惩罚你狠狠的关注我 欢迎微信搜索公众号“火鱼Java杂谈”，保姆式Spring5源码教程与你仅差一个关注。