如何用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
相关产品推荐
相关产品推荐

