如何查找1秒间隔内请求数≥5次的用户IP并统计对应请求记录

需求说明 #

我们有存储用户IP和请求时间戳的日志数据，需要筛选出任意1秒时间间隔内发起请求数≥5次的用户。
样例日志数据如下：

user_ip     time
---------------------------
user_ip1    16:11:10.56
user_ip1    16:11:10.67
user_ip1    16:11:10.87
user_ip2    16:11:10.92
user_ip2    16:11:10.97
user_ip1    16:11:11.15
user_ip1    16:11:11.20
user_ip1    16:11:11.30
user_ip2    16:11:12.13
user_ip2    16:11:13.50
user_ip2    16:11:13.80

上述样例中，user_ip1在16:11:10.56到16:11:11.30的0.74秒区间内共发起6次请求，符合筛选条件；user_ip2的请求分散在多个时间区间，不满足条件，最终输出仅包含user_ip1。

实现思路 #

• 按用户IP对日志数据分组
• 对每个用户的请求时间按升序排序
• 对每条请求，统计该请求时间往后1秒区间内的总请求数
• 只要存在任意一个区间的请求数≥5，就将该用户加入结果集

代码实现 #

SQL 实现（支持窗口函数的数据库如MySQL 8.0+/PostgreSQL） #

首先需要把时间字段转换为可计算的时间类型，再用范围窗口函数统计：

SELECT DISTINCT user_ip
FROM (
    SELECT 
        user_ip,
        COUNT(*) OVER (
            PARTITION BY user_ip 
            ORDER BY UNIX_TIMESTAMP(time) * 1000
            RANGE BETWEEN CURRENT ROW AND 999 FOLLOWING
        ) as request_cnt_in_1s
    FROM request_log
) t
WHERE request_cnt_in_1s >=5;

Python Pandas 实现 #

import pandas as pd

# 读取日志数据，将time列转换为datetime类型
df = pd.read_csv('request_log.csv', parse_dates=['time'])

def check_high_freq(group):
    # 按时间排序
    group = group.sort_values('time').reset_index(drop=True)
    # 对每条请求统计1秒内的请求数
    for i in range(len(group)):
        end_time = group.loc[i, 'time'] + pd.Timedelta(seconds=1)
        cnt = group[group['time'] <= end_time].shape[0] - i
        if cnt >=5:
            return True
    return False

# 分组过滤得到符合条件的用户
result = df.groupby('user_ip').filter(check_high_freq)['user_ip'].unique()
print(result)

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