You need to enable JavaScript to run this app. 如何用SQL按天统计存在1小时内事件间隔的合格用户数及占比
优惠活动
大模型
产品
解决方案
定价
更多

如何用SQL按天统计存在1小时内事件间隔的合格用户数及占比

基于用户事件表的合格用户统计SQL实现

实现逻辑

核心无需对用户所有事件做两两笛卡尔积比较,仅需通过窗口函数提取同用户同天相邻事件的时间差即可完成判定:

  • 对任意用户当天的事件按时间升序排序,计算相邻两条事件的unix时间戳差值
  • 只要存在任意一组相邻事件的差值≤3600秒(即1小时),该用户即判定为合格
  • 仅单条事件记录的用户,因不存在相邻可比较的事件,自然被排除出合格集合

该写法在大事件量场景下的性能远优于自连接比较的实现方式

公共计算逻辑

两个统计输出可以复用同一套用户合格标记的计算逻辑,基于CTE实现公共部分抽取,避免重复计算:

WITH user_event_adj AS (
    SELECT
        day,
        user_id,
        unixtime,
        -- 按天、用户分组,取组内上一条事件的unix时间戳
        LAG(unixtime) OVER (
            PARTITION BY day, user_id
            ORDER BY unixtime ASC
        ) AS prev_event_ts
    FROM sampletable
),
user_qualified_flag AS (
    SELECT
        day,
        user_id,
        -- 只要存在任意一组相邻事件差≤3600秒,标记为合格
        MAX(
            CASE WHEN unixtime - prev_event_ts <= 3600
            THEN 1
            ELSE 0 END
        ) AS is_qualified
    FROM user_event_adj
    GROUP BY day, user_id
)

场景1:输出按天统计的总用户数、合格用户数

在公共CTE基础上按天聚合,直接得到对应指标:

SELECT
    day,
    COUNT(user_id) AS total_user,
    SUM(is_qualified) AS number_of_qualified_user
FROM user_qualified_flag
GROUP BY day
ORDER BY day;

场景2:输出按天统计的合格用户占比

在公共CTE基础上计算占比,保留两位小数并拼接为百分号格式:

SELECT
    day,
    CONCAT(
        ROUND(
            SUM(is_qualified) * 100.0 / COUNT(user_id),
            2
        ),
        '%'
    ) AS percentage_of_qualified_user
FROM user_qualified_flag
GROUP BY day
ORDER BY day;

逻辑校验说明

对照需求给出的样例数据,逻辑匹配结果如下:

  • 用户1:两事件间隔33274秒(约9.2小时)>3600秒,判定为不合格
  • 用户2:两事件间隔1392秒(约23分钟)≤3600秒,判定为合格
  • 用户3:后两条事件间隔14秒≤3600秒,判定为合格
  • 用户4:仅1条事件记录,判定为不合格

完全符合预先定义的判定规则。

内容的提问来源于stack exchange,提问作者ddd948

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 23:57:01