GROUP BY 无法直接计算时间间隔，必须用窗口函数LAG()配合ORDER BY构造相邻访问对，再在外层聚合；不同数据库需适配时间差计算函数（如PostgreSQL用EXTRACT，MySQL用TIMESTAMPDIFF）。
GROUP BY 本身不能直接算时间间隔，必须配合窗口函数
SQL 的 GROUP BY 只能聚合（如 AVG()、COUNT()），无法获取“上一次访问时间”来算差值。真正需要的是按用户分组后，在时间序列中逐行计算相邻记录的间隔，这必须用窗口函数 LAG() 或 LEAD() 配合 ORDER BY 实现。
常见错误是写成这样：SELECT user_id, AVG(visit_time - LAG(visit_time)) FROM logs GROUP BY user_id —— 这在绝大多数数据库里会报错，因为 LAG() 是窗口函数，不能直接出现在 GROUP BY 聚合上下文中。
- 先用
ROW_NUMBER()或LAG()在子查询/CTE 中算出每条记录的前一次访问时间 - 再在外层对每个用户取非 NULL 的间隔值求平均
- 注意：时间相减结果单位依赖数据库（PostgreSQL 返回 interval，MySQL 返回秒数，SQL Server 可能需
DATEDIFF()）
PostgreSQL 示例：用 LAG() + EXTRACT 计算天数间隔
假设表 user_visits 有 user_id 和 visit_time（类型为 TIMESTAMP）：
WITH ranked AS (
SELECT
user_id,
visit_time,
LAG(visit_time) OVER (PARTITION BY user_id ORDER BY visit_time) AS prev_visit
FROM user_visits
)
SELECT
user_id,
AVG(EXTRACT(EPOCH FROM (visit_time - prev_visit)) / 86400) AS avg_days_between
FROM ranked
WHERE prev_visit IS NOT NULL
GROUP BY user_id;
关键点：EXTRACT(EPOCH FROM ...) 把 interval 转为秒数，除以 86400 得天数；WHERE prev_visit IS NOT NULL 排除每个用户的首条记录（它没有“上一次”）。
MySQL 8.0+ 兼容写法：用 DATEDIFF 或 TIMESTAMPDIFF
MySQL 不支持直接减 timestamp 得数值，必须用函数：
WITH ranked AS (
SELECT
user_id,
visit_time,
LAG(visit_time) OVER (PARTITION BY user_id ORDER BY visit_time) AS prev_visit
FROM user_visits
)
SELECT
user_id,
AVG(TIMESTAMPDIFF(DAY, prev_visit, visit_time)) AS avg_days_between
FROM ranked
WHERE prev_visit IS NOT NULL
GROUP BY user_id;
-
TIMESTAMPDIFF(DAY, ...)更安全，避免跨月时DATEDIFF()的隐式截断问题 - 如果原始时间精度到秒，且需要小时级平均，可改用
TIMESTAMPDIFF(HOUR, ...) - MySQL 5.7 不支持 CTE 和窗口函数，必须用自连接模拟，性能差且易出错
容易被忽略的边界情况
真实数据里，用户可能有重复时间戳、时区混用、或单次访问（无法计算间隔）——这些都会让平均值失真：
- 去重：若同一用户同秒多次访问，
LAG()会把它们当连续记录，间隔为 0，建议先按(user_id, DATE(visit_time), HOUR(visit_time))聚合去噪 - 时区：确保所有
visit_time已转为统一时区（如 UTC），否则跨时区用户的时间差会被放大 - 单次访问用户：他们的
prev_visit全为 NULL，WHERE条件会直接过滤掉，最终结果里不出现这些人 —— 如果业务要求包含，得额外用LEFT JOIN补 0 或 NULL 处理
算平均访问间隔不是简单套个 GROUP BY 就行，核心在于先构造出“相邻访问对”，再聚合。窗口函数是绕不开的一环，而不同数据库对时间运算的支持差异，恰恰是最常卡住的地方。
