基于登录登出时间表，查询指定日期范围每日特定时刻并发用户数

Alright, let's figure out how to solve this problem. The goal is to count how many users were active exactly at 12:00 each day between a given date range. For a user to be considered active at that moment, their login time has to be on or before the 12:00 check time, and their logout time has to be on or after that same check time.

First, we need to generate all the 12:00 time points for the dates we care about. Then, for each of those points, we count the users whose sessions overlap with that time. Here's how to do it across common databases:

PostgreSQL Example #

PostgreSQL has a handy generate_series function that makes creating the target time points a breeze:

WITH target_times AS (
    SELECT generate_series(
        TIMESTAMP '2018-04-01 12:00:00',
        TIMESTAMP '2018-04-06 12:00:00',
        INTERVAL '1 day'
    ) AS check_time
)
SELECT
    check_time::DATE AS target_date,
    COUNT(DISTINCT u.id) AS concurrent_users
FROM target_times tt
LEFT JOIN your_table u
    ON u.login <= tt.check_time
    AND u.logout >= tt.check_time
GROUP BY target_date
ORDER BY target_date;

MySQL 8.0+ Example (With Recursive CTE) #

MySQL 8.0 and above support recursive CTEs, which we can use to generate the daily 12:00 time points:

WITH RECURSIVE target_times AS (
    SELECT STR_TO_DATE('2018-04-01 12:00:00', '%Y-%m-%d %H:%i:%s') AS check_time
    UNION ALL
    SELECT check_time + INTERVAL 1 DAY
    FROM target_times
    WHERE check_time < STR_TO_DATE('2018-04-06 12:00:00', '%Y-%m-%d %H:%i:%s')
)
SELECT
    DATE(check_time) AS target_date,
    COUNT(DISTINCT u.id) AS concurrent_users
FROM target_times tt
LEFT JOIN your_table u
    ON u.login <= tt.check_time
    AND u.logout >= tt.check_time
GROUP BY target_date
ORDER BY target_date;

MySQL Pre-8.0 Example (Without Recursive CTE) #

If you're stuck on an older MySQL version, you can manually create a list of dates using a union of numbers:

SELECT
    DATE(tt.check_time) AS target_date,
    COUNT(DISTINCT u.id) AS concurrent_users
FROM (
    SELECT
        STR_TO_DATE(CONCAT('2018-04-', LPAD(n, 2, '0'), ' 12:00:00'), '%Y-%m-%d %H:%i:%s') AS check_time
    FROM (
        SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
    ) nums
) tt
LEFT JOIN your_table u
    ON u.login <= tt.check_time
    AND u.logout >= tt.check_time
GROUP BY target_date
ORDER BY target_date;

Key Notes: #

• COUNT(DISTINCT u.id): Use this to avoid counting the same user multiple times if they have overlapping sessions (if each user only has one active session at a time, you can simplify to COUNT(u.id)).
• Left Join: Ensures we get a row for every date in the range, even if there are 0 concurrent users at 12:00 that day.
• Time Type Matching: Make sure your login and logout columns are properly typed as datetime (or equivalent in your database) to avoid conversion errors.

内容的提问来源于stack exchange，提问作者Sergei Shumilin