ClickHouse：如何高效计算剔除首尾5%值的平均值？

要实现剔除特定字段前5%和后5%数据后计算平均值，同时避免多次扫描表的性能损耗，你可以用窗口函数+WITH子句的方式，仅做一次全表扫描就能完成需求，比你提供的多子查询方案更高效：

WITH 
    quantile(0.05)(x) OVER () AS lower_bound,
    quantile(0.95)(x) OVER () AS upper_bound
SELECT avg(x)
FROM your_table_name
WHERE x BETWEEN lower_bound AND upper_bound

方案优势对比 #

你原本的查询会触发三次全表扫描：两次子查询分别计算分位数，主查询再扫描一次过滤数据：

SELECT avg(x) 
FROM table  
WHERE x <= (SELECT quantile(0.95)(x) FROM table)  
  AND x >= (SELECT quantile(0.05)(x) FROM table)

而窗口函数方案在单次扫描中就能完成分位数计算与数据过滤，大幅降低IO开销，针对大表的性能提升会非常显著。

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