mysql sql 判断连续天数

原创

mob64ca12df9869 2024-09-15 06:14:44 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12df9869的原创作品，请联系作者获取转载授权，否则将追究法律责任

如何在 MySQL 中判断连续天数

在日常的开发过程中，我们经常需要判断某个事件或活动的发生情况，尤其是涉及到时间点的连续性。判断连续天数是一个很常见的需求，比如你可能需要知道用户每天是否都登录了系统。本文将带你一步一步地实现如何在 MySQL 中判断连续天数的功能。

整体流程

在开始代码实现之前，我们首先要明确工作的流程，可以用以下表格来展示：

步骤	说明
1	创建数据表
2	插入示例数据
3	编写 SQL 查询判断连续天数
4	运行查询并分析结果

现在，我们将逐步详细讲解每一个步骤。

步骤详解

1. 创建数据表

首先，我们需要创建一个数据表来记录用户的登录时间。这里我们创建一个名为 user_logins 的表，包含用户的 ID 和登录的时间戳。

CREATE TABLE user_logins (
    user_id INT,
    login_date DATE,
    PRIMARY KEY (user_id, login_date)
);

代码注释：

user_id INT: 用户ID。
login_date DATE: 用户登录的日期，使用 DATE 类型来存储日期信息。
PRIMARY KEY (user_id, login_date): 结合 user_id 和 login_date 作为表的主键，确保每个用户的每次登录时间都是唯一的。

2. 插入示例数据

接下来，我们插入一些示例数据，以便于我们进行测试。以下数据模拟一个用户每天的登录情况。

INSERT INTO user_logins (user_id, login_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(1, '2023-10-03'),
(1, '2023-10-05'),
(1, '2023-10-06'),
(1, '2023-10-07');

代码注释：

将用户 ID 为 1 的登录日期分别设置为 2023 年 10 月 1 日到 10 月 7 日的多个日期。
注意到这段数据中，10 月 4 日没有登录。

3. 编写 SQL 查询判断连续天数

现在我们有了表和数据，接下来就是编写 SQL 查询来判断连续天数。我们可以使用 DATEDIFF 函数与 GROUP BY 结合来找出连续的天数。

SELECT user_id, 
       COUNT(*) AS consecutive_days
FROM (
    SELECT user_id, 
           login_date,
           DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS diff
    FROM user_logins
) AS temp
GROUP BY user_id, diff
HAVING COUNT(*) >= 3;  -- 找到连续三天及以上

代码注释：

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)：为每个用户的登录日期生成一个行号。
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ... DAY)：计算当前登录日期减去行号天数，用于判断连续性。
GROUP BY user_id, diff：根据用户和日期差分组，找出连续的天数。
HAVING COUNT(*) >= 3：筛选出连续登录天数大于或等于3天的情况。