oracle 的 median 函数不识别也不过滤异常值，仅对非 null 值排序后取中位数；需预先用 where、子查询或 filter 配合 percentile_cont 等方式剔除异常值。
MEDIAN 在 Oracle 中确实存在，但它不支持直接过滤异常值——它只对当前分组内所有非 NULL 值排序后取中位数，不会跳过离群点。想用它“处理”异常值，本质是得先让异常值不出现在输入里。
Oracle 的 MEDIAN 函数本身不识别异常值
Oracle 的 MEDIAN() 是一个标准聚合函数，行为很明确：忽略 NULL，对剩余值升序排列，奇数取中间、偶数取中间两数平均。它没有内置的阈值、Z-score 或 IQR 计算能力。
常见误解是以为 MEDIAN(salary) 会自动“鲁棒”地避开异常值——其实不会。如果某部门有 1 条 salary = 1000000 的错误记录，而其他都是 5000–8000，MEDIAN 仍会把它纳入排序，可能把中位数拉高几十倍（尤其当样本少时）。
- 它不接受
FILTER (WHERE ...)子句（不像 PostgreSQL 或 Databricks SQL） - 不能和窗口函数嵌套用于动态计算分位数来定义异常边界
- 不支持
DISTINCT修饰（Oracle 文档明确说明MEDIAN忽略重复值逻辑已内置，但不可显式声明）
必须预过滤：在 GROUP BY 前用 WHERE 或子查询剔除异常
最可靠的做法，是在调用 MEDIAN 之前，用业务规则或统计逻辑把异常值筛掉。Oracle 支持 WITH 子句和分析函数，可以组合完成。
例如，按部门计算薪资中位数，同时排除明显异常的记录：
WITH stats AS (
SELECT dept_id,
AVG(salary) AS avg_sal,
STDDEV(salary) AS std_sal
FROM employees
GROUP BY dept_id
),
filtered AS (
SELECT e.*
FROM employees e
JOIN stats s ON e.dept_id = s.dept_id
WHERE e.salary BETWEEN (s.avg_sal - 2 * s.std_sal) AND (s.avg_sal + 2 * s.std_sal)
AND e.salary IS NOT NULL
)
SELECT dept_id, MEDIAN(salary) AS median_salary
FROM filtered
GROUP BY dept_id;
- 这里用了 Z-score 思路（±2σ），但你也可以换成固定阈值：
e.salary 0 - 注意：子查询必须确保
salary非空，否则MEDIAN虽然忽略NULL，但若整组只剩NULL，结果就是NULL - 不要试图在
MEDIAN()内部用CASE WHEN—— 它不接受条件表达式作为参数，会报ORA-00932: inconsistent datatypes
替代方案：用 PERCENTILE_CONT 实现更灵活的异常感知中位数
Oracle 同时提供 PERCENTILE_CONT(0.5)，语法上比 MEDIAN 更可控，且支持 FILTER（Oracle 12cR2+）和窗口用法，适合需要动态定义“正常范围”的场景。
例如，在同一语句中先算 IQR 边界，再过滤并求中位数：
SELECT dept_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FILTER (
WHERE salary BETWEEN q1 AND q3
) AS median_iqr_filtered
FROM (
SELECT dept_id, salary,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept_id) AS q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY dept_id) AS q3
FROM employees
)
GROUP BY dept_id;
-
PERCENTILE_CONT允许配合FILTER，这是MEDIAN不具备的关键能力 - 但注意：
FILTER是逐行判断，不是先过滤再聚合；上面写法实际依赖外层GROUP BY和内层窗口函数协同，需测试执行计划是否高效 - 若数据量大，两次
PERCENTILE_CONT窗口计算可能比单次MEDIAN慢 2–3 倍
容易被忽略的细节：空组、数据类型与执行顺序
Oracle 对 MEDIAN 的容错性看似强，但几个边界情况极易出错：
- 当某
dept_id经预过滤后无任何非NULL值，MEDIAN返回NULL，但不会报错——业务代码必须检查该NULL是否合理 -
MEDIAN输入列如果是VARCHAR2且含数字字符串（如'123'），不会自动转数值，而是按字符排序（'9' > '100'），结果完全错误 - 聚合顺序不可控：Oracle 先执行
WHERE→ 再GROUP BY→ 最后算MEDIAN。你无法让MEDIAN先运行再决定哪些行该保留 - 分区表上，若异常值集中在某个分区，而你没在
WHERE中指定分区键，可能导致全分区扫描，性能骤降
Oracle 的 MEDIAN 是个“老实人”函数：给它什么，它就中位什么。真正处理异常值的逻辑，永远落在它前面那层 WHERE、WITH 或 FILTER 上。别指望函数自己做判断——那是你该干的事。
