使用MySQL GROUP BY INTERVAL 1 HOUR排查1小时窗口内高频登录用户

Alright, let's break down how to solve this problem—finding the user with the most login attempts in any 1-hour window from your user_activity table, and also flagging those suspicious high-volume attempts (like the Python/OpenCV2 captcha cracks you mentioned).

Core Approach #

The key here is to use a sliding time window to count logins for each user across every possible 1-hour interval in your data. We'll first filter for only LOGIN events, then calculate login counts over rolling 1-hour windows per user, and finally pick out the user with the highest peak count.

SQL Implementations by Database #

MySQL 8.0+ #

MySQL supports sliding windows with RANGE when using Unix timestamps to define the 3600-second (1 hour) window:

WITH login_events AS (
    -- Filter down to only login events
    SELECT user_id, timestamp
    FROM user_activity
    WHERE event_class = 'LOGIN'
),
user_hourly_counts AS (
    -- Calculate rolling 1-hour login count for each user at each timestamp
    SELECT
        user_id,
        timestamp,
        COUNT(*) OVER (
            PARTITION BY user_id
            ORDER BY UNIX_TIMESTAMP(timestamp)
            RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW
        ) AS hourly_login_count
    FROM login_events
)
-- Get the maximum hourly count per user, then sort to find the top user
SELECT
    user_id,
    MAX(hourly_login_count) AS max_hourly_logins,
    MIN(timestamp) AS window_start,
    MAX(timestamp) AS window_end
FROM user_hourly_counts
GROUP BY user_id
ORDER BY max_hourly_logins DESC
LIMIT 1;

PostgreSQL #

PostgreSQL makes interval-based windows more straightforward with native INTERVAL support:

WITH login_events AS (
    SELECT user_id, timestamp
    FROM user_activity
    WHERE event_class = 'LOGIN'
),
user_hourly_counts AS (
    SELECT
        user_id,
        timestamp,
        COUNT(*) OVER (
            PARTITION BY user_id
            ORDER BY timestamp
            RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
        ) AS hourly_login_count
    FROM login_events
)
SELECT
    user_id,
    MAX(hourly_login_count) AS max_hourly_logins,
    -- Get the exact window where the peak occurred
    MIN(timestamp) FILTER (WHERE hourly_login_count = MAX(hourly_login_count) OVER (PARTITION BY user_id)) AS window_start,
    MAX(timestamp) FILTER (WHERE hourly_login_count = MAX(hourly_login_count) OVER (PARTITION BY user_id)) AS window_end
FROM user_hourly_counts
GROUP BY user_id
ORDER BY max_hourly_logins DESC
LIMIT 1;

Optimization Tips #

• Add an Index: For large datasets, create a composite index on (event_class, user_id, timestamp) to speed up filtering, partitioning, and sorting. This will drastically reduce query time.

  -- MySQL/PostgreSQL compatible index
  CREATE INDEX idx_login_events ON user_activity (event_class, user_id, timestamp);
  

Handle Ties (Multiple Top Users) #

If multiple users have the same maximum hourly login count, replace the final LIMIT 1 with a ranking window to get all top users:

WITH ... -- Keep the same login_events and user_hourly_counts CTEs
user_max_counts AS (
    SELECT
        user_id,
        MAX(hourly_login_count) AS max_hourly_logins,
        MIN(timestamp) AS window_start,
        MAX(timestamp) AS window_end
    FROM user_hourly_counts
    GROUP BY user_id
),
ranked_users AS (
    SELECT
        *,
        RANK() OVER (ORDER BY max_hourly_logins DESC) AS rank
    FROM user_max_counts
)
SELECT * FROM ranked_users WHERE rank = 1;

Flag Suspicious Captcha-Cracking Attempts #

Since you mentioned students using bots to crack captchas (showing hundreds of logins in an hour), add a HAVING clause to filter for only users with extreme login counts:

-- Insert this into the final GROUP BY query
GROUP BY user_id
HAVING MAX(hourly_login_count) > 100 -- Adjust threshold based on your normal login patterns
ORDER BY max_hourly_logins DESC;

内容的提问来源于stack exchange，提问作者ABC Taylor