MySQL中用5%/95%百分位替代MIN/MAX去除极值的技术咨询

需求背景 #

你需要将原有查询中attentionTime的MIN替换为5%百分位、MAX替换为95%百分位，以此规避极值对统计结果的影响，同时保留平均值的计算。原查询语句如下：

select AVG(attentionTime) AS time_attention, MAX(attentionTime) AS time_max, MIN(attentionTime) AS time_min FROM records_stats WHERE parent_cat = {N}

百分位的通俗解释 #

先帮你理清百分位的核心含义——它不是移除低于5%或高于95%的数值，而是一个「分界参考值」：

• 5%百分位（P5）：把所有attentionTime从小到大排序后，有5%的数据小于等于这个数值，剩下95%的数据大于等于它。
• 95%百分位（P95）：同理，排序后有95%的数据小于等于这个数值，仅5%的数据大于等于它。

用这个方式替代MIN和MAX，就能避开那些极端小或极端大的异常值，让统计结果更贴合大多数数据的实际分布情况。

修改后的SQL语句 #

不同数据库的百分位函数语法略有差异，这里给你常见数据库的适配写法：

MySQL 8.0+ / PostgreSQL #

SELECT
  AVG(attentionTime) AS time_attention,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY attentionTime) AS time_p95,
  PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY attentionTime) AS time_p5
FROM records_stats
WHERE parent_cat = {N};

注：PERCENTILE_CONT会返回插值后的连续数值，如果需要取数据中实际存在的离散数值，可以用PERCENTILE_DISC替代。

SQL Server #

SELECT
  AVG(attentionTime) AS time_attention,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY attentionTime) OVER () AS time_p95,
  PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY attentionTime) OVER () AS time_p5
FROM records_stats
WHERE parent_cat = {N}
GROUP BY (); -- 确保只返回一行统计结果

Oracle #

SELECT
  AVG(attentionTime) AS time_attention,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY attentionTime) AS time_p95,
  PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY attentionTime) AS time_p5
FROM records_stats
WHERE parent_cat = {N};

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