如何用SQL统计两次及以上点击间隔不少于1小时的用户数量

核心思路 #

借助窗口函数LAG()获取每个用户上一次点击的时间，计算相邻点击的时间差，过滤出存在至少一次间隔≥1小时的用户，最终统计符合要求的用户总数即可。

实现代码（兼容支持窗口函数的主流SQL引擎，如PostgreSQL/Spark SQL/Hive等） #

假设你的数据表名为user_clicks，代码如下：

WITH user_click_lag AS (
    SELECT
        UserID,
        Date,
        -- 按用户分组，按点击时间升序排列，取当前用户上一次的点击时间
        LAG(Date) OVER (PARTITION BY UserID ORDER BY Date) AS last_click_time
    FROM user_clicks
),
valid_users AS (
    SELECT
        UserID
    FROM user_click_lag
    -- 过滤掉用户第一次点击（无上次点击时间的记录）
    WHERE last_click_time IS NOT NULL
    -- 计算相邻点击间隔≥1小时，若Date字段已是timestamp类型可跳过转时间的步骤
    AND EXTRACT(EPOCH FROM (TO_TIMESTAMP(Date, 'YYYY-MM-DD HH24:MI:SS UTC') - TO_TIMESTAMP(last_click_time, 'YYYY-MM-DD HH24:MI:SS UTC'))) / 3600 >= 1
    GROUP BY UserID
)
-- 统计符合要求的用户总数
SELECT COUNT(DISTINCT UserID) AS qualified_user_count
FROM valid_users;

不同引擎适配说明 #

• 如果你使用MySQL 8.0及以上版本，将时间差判断逻辑替换为以下代码即可：

AND TIMESTAMPDIFF(HOUR, STR_TO_DATE(last_click_time, '%Y-%m-%d %H:%i:%s UTC'), STR_TO_DATE(Date, '%Y-%m-%d %H:%i:%s UTC')) >= 1

• 如果你的需求是该用户的所有相邻点击间隔都不小于1小时，而非存在至少一次符合要求的间隔，只需在valid_users的GROUP BY UserID后增加HAVING MIN(时间差计算逻辑) >= 1即可。
• 若你的表中Date字段已经是timestamp/datetime类型，无需调用TO_TIMESTAMP/STR_TO_DATE转格式，直接计算两个字段的差值即可。

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