Presto/Trino中如何高效排除最差5%数据，优化MTTD计算查询？

你的现有方案存在冗余过滤条件、刚性截断（ROW_NUMBER导致相同值被不公平排除）等问题，以下是几种更优的实现方式：

方案一：使用PERCENT_RANK()函数 #

利用百分位排名函数，灵活保留前95%的数据，避免相同值被生硬截断：

SELECT AVG(time_to_detection) / 60 AS "MTTD"
FROM (
    SELECT time_to_detection,
           PERCENT_RANK() OVER (ORDER BY time_to_detection) AS pr
    FROM itx_ops_metrics.incidents_inc_jira
    WHERE array_join(involved_teams, ', ') LIKE '%Business Technology%'
      AND time_to_detection IS NOT NULL
      AND YEAR(created_at) = 2024
) t
WHERE pr <= 0.95;

优势： #

• 相同time_to_detection值的行拥有相同的百分位排名，不会出现部分被排除的情况
• 自动适配数据量变化，无需手动计算行数比例
• 去掉了冗余的过滤条件，查询更简洁

方案二：先计算95分位数阈值再过滤 #

先明确找到95%分位点的阈值，再过滤掉超过阈值的数据，逻辑更直观：

WITH threshold AS (
    -- 离散分位数，返回数据中实际存在的值
    SELECT PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY time_to_detection) AS p95
    FROM itx_ops_metrics.incidents_inc_jira
    WHERE array_join(involved_teams, ', ') LIKE '%Business Technology%'
      AND time_to_detection IS NOT NULL
      AND YEAR(created_at) = 2024
)
SELECT AVG(time_to_detection) / 60 AS "MTTD"
FROM itx_ops_metrics.incidents_inc_jira, threshold
WHERE array_join(involved_teams, ', ') LIKE '%Business Technology%'
  AND time_to_detection IS NOT NULL
  AND YEAR(created_at) = 2024
  AND time_to_detection <= p95;

如果需要连续插值的分位数（非数据中实际存在的值），可将PERCENTILE_DISC替换为PERCENTILE_CONT。

大数据场景优化：使用近似分位数函数 #

如果数据集非常大，追求查询性能可使用近似分位数函数，牺牲少量精度换取速度：

WITH threshold AS (
    SELECT APPROX_PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY time_to_detection) AS p95
    FROM itx_ops_metrics.incidents_inc_jira
    WHERE array_join(involved_teams, ', ') LIKE '%Business Technology%'
      AND time_to_detection IS NOT NULL
      AND YEAR(created_at) = 2024
)
SELECT AVG(time_to_detection) / 60 AS "MTTD"
FROM itx_ops_metrics.incidents_inc_jira, threshold
WHERE array_join(involved_teams, ', ') LIKE '%Business Technology%'
  AND time_to_detection IS NOT NULL
  AND YEAR(created_at) = 2024
  AND time_to_detection <= p95;

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