如何编写SQL排除10分钟内的重复点击数据？

原始数据 #

date                      id         process      name
2022-01-01 12:23:33       12         security     John
2022-01-01 12:25:33       12         security     John
2022-01-01 12:27:33       12         security     John
2022-01-01 12:29:33       12         security     John
2022-01-01 14:04:45       12         security     John
2022-01-05 03:53:11       12         Infra        Sasha
2022-01-05 03:57:30       12         Infra        Sasha
2022-01-06 12:23:33       12         Infra        Sasha

需求说明 #

当相同id、process、name的记录时间间隔在10分钟内时，视为重复点击，仅保留每组内的第一条有效记录（后续间隔10分钟内的重复记录剔除，间隔超过10分钟的保留为新的有效记录）。预期结果如下：

2022-01-01 12:23:33       12         security     John
2022-01-01 14:04:45       12         security     John
2022-01-05 03:53:11       12         Infra        Sasha
2022-01-06 12:23:33       12         Infra        Sasha

解决方案SQL #

使用窗口函数LAG()获取同组内上一条记录的时间，结合时间差计算筛选出有效记录：

WITH ranked_records AS (
    SELECT 
        date,
        id,
        process,
        name,
        -- 获取同组内上一条记录的时间
        LAG(date) OVER (PARTITION BY id, process, name ORDER BY date) AS prev_date
    FROM your_table_name
)
SELECT date, id, process, name
FROM ranked_records
WHERE 
    -- 保留每组的第一条记录
    prev_date IS NULL 
    OR 
    -- 保留与上一条记录间隔超过10分钟的记录
    TIMESTAMPDIFF(MINUTE, prev_date, date) > 10
ORDER BY id, process, name, date;

逻辑说明 #

1. PARTITION BY id, process, name：将数据按id、process、name分组，确保仅在同一用户同一流程组内比较时间间隔。
2. LAG(date) OVER (...)：在每组内按时间排序后，获取当前记录的上一条记录时间。
3. TIMESTAMPDIFF(MINUTE, prev_date, date)：计算当前记录与上一条记录的时间差（单位为分钟）。
4. 筛选条件：保留每组第一条记录（prev_date为NULL），或与上一条间隔超过10分钟的记录，剔除10分钟内的重复点击。

注：若使用的SQL方言不支持TIMESTAMPDIFF()，可替换为对应语法，比如PostgreSQL可用EXTRACT(EPOCH FROM (date - prev_date))/60 > 10计算分钟差。

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