如何用SQL实现连续登录预警_SQL实现连续登录预警逻辑

login_events

user_id

event_time

-- SQL实现连续登录预警的核心逻辑
WITH UserLoginTimestamps AS (
    -- 步骤1: 筛选出需要分析的登录事件。
    -- 通常，我们会限定一个时间窗口，比如只分析最近24小时内的登录。
    -- 这样做既能减少数据量，又能确保预警的时效性。
    SELECT
        user_id,
        event_time
    FROM
        login_events
    WHERE
        event_time >= NOW() - INTERVAL '1' DAY -- 以PostgreSQL为例，获取过去24小时数据
),
LaggedLoginTimes AS (
    -- 步骤2: 为每个用户的每次登录，获取其上一次登录的时间。
    -- 这是一个关键步骤，通过窗口函数LAG，我们可以轻松地在同一用户的时间序列中进行比较。
    SELECT
        user_id,
        event_time,
        LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS previous_event_time
    FROM
        UserLoginTimestamps
),
ConsecutiveFlaggedLogins AS (
    -- 步骤3: 计算当前登录与上一次登录之间的时间差，并标记是否为“快速连续”。
    -- 这里我们定义一个阈值，比如2分钟（120秒）。如果两次登录间隔小于这个阈值，
    -- 我们就认为它们是“连续”的，否则，就视为一个新的连续登录组的开始。
    SELECT
        user_id,
        event_time,
        previous_event_time,
        EXTRACT(EPOCH FROM (event_time - previous_event_time)) AS time_diff_seconds, -- 计算秒级时间差
        CASE
            WHEN EXTRACT(EPOCH FROM (event_time - previous_event_time)) <= 120 -- 假设2分钟内算作快速连续
            THEN 0 -- 0表示与上一个事件连续
            ELSE 1 -- 1表示不连续，或者说是新的一组连续事件的开始
        END AS is_new_group_start
    FROM
        LaggedLoginTimes
    WHERE
        previous_event_time IS NOT NULL -- 第一次登录没有前一个事件，所以排除
),
GroupedConsecutiveLogins AS (
    -- 步骤4: 通过累加 `is_new_group_start` 来创建连续登录的组ID。
    -- 这是一个“Gaps and Islands”问题的经典解法。每当遇到一个不连续的登录（is_new_group_start=1），
    -- 累加和就会增加，从而为后续的连续登录创建一个新的组ID。
    SELECT
        user_id,
        event_time,
        time_diff_seconds,
        SUM(is_new_group_start) OVER (PARTITION BY user_id ORDER BY event_time) AS consecutive_group_id
    FROM
        ConsecutiveFlaggedLogins
),
WarningCandidates AS (
    -- 步骤5: 统计每个连续组内的登录次数、开始/结束时间及总时长。
    -- 这一步将每个连续组聚合起来，为后续的预警判断做准备。
    SELECT
        user_id,
        consecutive_group_id,
        MIN(event_time) AS group_start_time,
        MAX(event_time) AS group_end_time,
        COUNT(event_time) AS login_count_in_group,
        EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time))) AS group_duration_seconds
    FROM
        GroupedConsecutiveLogins
    GROUP BY
        user_id,
        consecutive_group_id
)
-- 步骤6: 最终筛选出符合预警条件的连续登录行为。
-- 这里的预警条件是：在特定时间段内（例如5分钟内）登录次数达到或超过3次。
-- 这个阈值和时间窗口可以根据业务需求和对“异常”的定义进行灵活调整。
SELECT
    user_id,
    group_start_time,
    group_end_time,
    login_count_in_group,
    group_duration_seconds,
    '连续登录预警：用户在短时间内多次登录，可能存在异常！' AS warning_message
FROM
    WarningCandidates
WHERE
    login_count_in_group >= 3 -- 预警阈值：连续登录次数达到或超过3次
    AND group_duration_seconds <= 300; -- 预警时间窗口：整个连续登录过程在5分钟（300秒）内完成