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

-- 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秒）内完成

稿定AI社区

在线AI创意灵感社区

61

查看详情

大家都在看：