如何统计含事件的时间分段数？实现用户/日维度平台停留时长指标

看起来你需要统计的是用户每天在平台上占用的独立1分钟时段数——毕竟同一分钟内的多条事件只算1个time unit对吧？那核心思路就是先把事件时间归一到分钟级别，去重后再按用户和日期计数。

假设你的事件表叫user_events，包含user_id（用户ID）、event_timestamp（事件发生时间戳）这两个核心字段，这里提供通用SQL方案，以及针对不同数据库的适配版本：

通用标准SQL方案 #

SELECT
  user_id,
  DATE(event_timestamp) AS event_date,
  COUNT(DISTINCT DATE_TRUNC('minute', event_timestamp)) AS time_spent_units
FROM user_events
GROUP BY user_id, DATE(event_timestamp)
ORDER BY user_id, event_date;

逻辑拆解 #

• DATE_TRUNC('minute', event_timestamp)：把每个事件的时间截断到分钟维度，比如2024-05-20 14:35:22会被转换为2024-05-20 14:35:00，让同一分钟内的所有事件归为同一个时间标识。
• COUNT(DISTINCT ...)：对每个用户+日期组合，统计去重后的分钟数，这就是你定义的"time unit"总数——同一分钟的多条事件只会被计数一次。
• DATE(event_timestamp)：将时间戳转换为纯日期格式，实现按日期分组的需求。

不同数据库适配版本 #

MySQL版本

MySQL没有DATE_TRUNC函数，用DATE_FORMAT实现分钟截断：

SELECT
  user_id,
  DATE(event_timestamp) AS event_date,
  COUNT(DISTINCT DATE_FORMAT(event_timestamp, '%Y-%m-%d %H:%i:00')) AS time_spent_units
FROM user_events
GROUP BY user_id, DATE(event_timestamp)
ORDER BY user_id, event_date;

SQL Server版本

用DATEADD+DATEDIFF组合实现分钟截断：

SELECT
  user_id,
  CAST(event_timestamp AS DATE) AS event_date,
  COUNT(DISTINCT DATEADD(minute, DATEDIFF(minute, 0, event_timestamp), 0)) AS time_spent_units
FROM user_events
GROUP BY user_id, CAST(event_timestamp AS DATE)
ORDER BY user_id, CAST(event_timestamp AS DATE);

如果需要把结果转换成小时/天等单位，直接做数值转换即可，比如COUNT(...) / 60 AS time_spent_hours。

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