如何用SQL CASE语句替换超限时长并保留用户总数，计算平均时长

原查询通过WHERE Duration < 3600过滤了超时时长的用户，导致统计的用户总数缺失，平均时长计算偏差。要保留所有用户并将超3600秒的时长替换为上限值，可通过以下两种方式实现：

方案1：使用CASE条件判断 #

通过CASE语句对超时时长进行替换，再计算总和与平均值：

SELECT
  date,
  COUNT(*) AS total_users,
  SUM(CASE WHEN duration > 3600 THEN 3600 ELSE duration END) / COUNT(*) AS avgdur
FROM your_table_name
GROUP BY date;

方案2：使用LEAST函数（更简洁） #

多数数据库支持LEAST()函数，直接取duration和3600中的较小值：

SELECT
  date,
  COUNT(*) AS total_users,
  SUM(LEAST(duration, 3600)) / COUNT(*) AS avgdur
FROM your_table_name
GROUP BY date;

验证示例数据 #

针对你提供的6条示例数据：

• 替换后总时长为：3600+200+600+1000+3600+200 = 9200
• 平均时长：9200 ÷ 6 ≈ 1533，与预期结果一致

可选：替换为平均值 #

如果需要将超时时长替换为该日期的平均时长（而非固定上限3600），可使用子查询实现：

SELECT
  t.date,
  COUNT(*) AS total_users,
  SUM(CASE WHEN t.duration > 3600 THEN avg_dur ELSE t.duration END) / COUNT(*) AS avgdur
FROM your_table_name t
JOIN (
  SELECT date, AVG(duration) AS avg_dur
  FROM your_table_name
  GROUP BY date
) dt ON t.date = dt.date
GROUP BY t.date;

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