MySQL实现起止时间间分钟序列生成及每分钟登录用户数统计

嘿，作为SQL初学者碰到这个需求完全没问题，我来给你拆解一下实现步骤，保证你能看懂～

核心思路 #

我们需要先生成登录时间段覆盖范围内的所有分钟时间点，再把这些时间点和原表关联，统计每个时间点有多少用户的登录时段包含它。

方法一：用递归CTE（MySQL 8.0及以上版本推荐） #

MySQL 8.0开始支持递归CTE（公共表表达式），这是生成时间序列最简洁的方式：

-- 第一步：生成所有需要的分钟时间点
WITH RECURSIVE minute_sequence AS (
    -- 起始值：取表中最早的登录开始时间，截断到整分钟
    SELECT MIN(DATE_FORMAT(starttime, '%Y-%m-%d %H:%i:00')) AS minute_point
    FROM your_table_name
    UNION ALL
    -- 递归生成下一分钟，直到超过最晚的登录结束时间
    SELECT DATE_ADD(minute_point, INTERVAL 1 MINUTE) AS minute_point
    FROM minute_sequence
    WHERE minute_point < (SELECT MAX(DATE_FORMAT(endtime, '%Y-%m-%d %H:%i:00')) FROM your_table_name)
)
-- 第二步：关联原表统计每分钟登录用户数
SELECT 
    ms.minute_point,
    COUNT(DISTINCT t.Id) AS login_user_count
FROM minute_sequence ms
LEFT JOIN your_table_name t 
    -- 判断当前分钟点是否在用户的登录时间段内
    ON ms.minute_point >= DATE_FORMAT(t.starttime, '%Y-%m-%d %H:%i:00')
    AND ms.minute_point <= DATE_FORMAT(t.endtime, '%Y-%m-%d %H:%i:00')
GROUP BY ms.minute_point
ORDER BY ms.minute_point;

代码解释：

• minute_sequence：递归生成从最早登录开始到最晚登录结束的所有整分钟点（比如1999-05-07 15:00:00、1999-05-07 15:01:00）。
• LEFT JOIN：确保即使某分钟没有用户登录，也会显示该分钟点（此时login_user_count为0）。
• COUNT(DISTINCT t.Id)：避免同一用户在同一分钟被重复统计（如果你的表中每个用户只有一条登录记录，也可以去掉DISTINCT，但加上更稳妥）。

方法二：用数字表（兼容MySQL 5.x版本） #

如果你的MySQL版本低于8.0，不支持CTE，可以先创建一个数字表来生成时间序列：

1. 先创建一个存储数字的临时表（数字范围覆盖一天的分钟数即可，一天最多1440分钟）：

CREATE TEMPORARY TABLE numbers (n INT);
INSERT INTO numbers VALUES 
(0),(1),(2),(3),(4),(5),...,
(1438),(1439),(1440); -- 可以用循环批量插入，手动写的话选够范围就行

2. 生成分钟序列并统计：

SELECT 
    minute_point,
    COUNT(DISTINCT t.Id) AS login_user_count
FROM (
    -- 生成所有需要的分钟点
    SELECT 
        DATE_ADD(
            (SELECT MIN(DATE_FORMAT(starttime, '%Y-%m-%d %H:%i:00')) FROM your_table_name),
            INTERVAL n MINUTE
        ) AS minute_point
    FROM numbers
    WHERE DATE_ADD(
            (SELECT MIN(DATE_FORMAT(starttime, '%Y-%m-%d %H:%i:00')) FROM your_table_name),
            INTERVAL n MINUTE
        ) <= (SELECT MAX(DATE_FORMAT(endtime, '%Y-%m-%d %H:%i:00')) FROM your_table_name)
) AS ms
LEFT JOIN your_table_name t 
    ON ms.minute_point >= DATE_FORMAT(t.starttime, '%Y-%m-%d %H:%i:00')
    AND ms.minute_point <= DATE_FORMAT(t.endtime, '%Y-%m-%d %H:%i:00')
GROUP BY ms.minute_point
ORDER BY ms.minute_point;

测试你的示例数据 #

用你给出的示例数据（Id=1，starttime='1999-05-07 15:00'，endtime='1999-05-07 16:45'），执行上述SQL后，会生成从1999-05-07 15:00:00到1999-05-07 16:45:00的所有分钟点，每个点的login_user_count都是1，符合预期。

注意事项 #

• 记得把代码中的your_table_name替换成你实际的表名。
• 如果你的表数据量很大，建议给starttime和endtime字段加索引，提升查询效率。

内容的提问来源于stack exchange，提问作者okechukwu anya