本文介绍在 bigquery（尤其是 legacy sql 环境）中实现健壮中位数计算的两种专业方案：一是复用官方持久化 udf（限 us 区域），二是自建支持标量与数组统一输入的临时 sql udf，彻底解决类型不一致导致的执行错误。
本文介绍在 bigquery（尤其是 legacy sql 环境）中实现健壮中位数计算的两种专业方案：一是复用官方持久化 udf（限 us 区域），二是自建支持标量与数组统一输入的临时 sql udf，彻底解决类型不一致导致的执行错误。
在 BigQuery 中实现中位数函数时，开发者常遇到一个典型陷阱：输入数据形态不统一——有时是单个数值（如 FLOAT64 标量），有时是数组（如 ARRAY
推荐解法：纯 SQL 实现的泛型中位数 UDF（兼容标量与数组）
BigQuery 的强大之处在于——中位数完全可通过标准 SQL 高效实现，无需 JS，也无需牺牲类型灵活性。以下是一个生产就绪的临时函数定义，它能正确处理三种常见输入形式：
- 单个数值：median(42)
- 数值数组：median([1, 5, 3, 8])
- 查询结果聚合：median((SELECT ARRAY_AGG(score) FROM exams WHERE subject = 'Math'))
CREATE TEMP FUNCTION median(arr ANY TYPE) AS (
(
SELECT
IF(
MOD(ARRAY_LENGTH(sorted_arr), 2) = 0,
-- 偶数长度：取中间两数平均值
(sorted_arr[OFFSET(DIV(ARRAY_LENGTH(sorted_arr), 2) - 1)] +
sorted_arr[OFFSET(DIV(ARRAY_LENGTH(sorted_arr), 2))]) / 2.0,
-- 奇数长度：取正中元素
sorted_arr[OFFSET(DIV(ARRAY_LENGTH(sorted_arr), 2))]
)
FROM (
SELECT ARRAY_AGG(x ORDER BY x) AS sorted_arr
FROM UNNEST(
-- 关键：统一转为数组 —— 标量包装为单元素数组，原生数组保持不变
CASE
WHEN ARRAY_LENGTH(CAST(arr AS ARRAY<int64>)) IS NOT NULL THEN CAST(arr AS ARRAY<int64>)
ELSE [CAST(arr AS INT64)]
END
) AS x
)
)
);</int64></int64>
⚠️ 重要说明与注意事项：
- 上述函数使用 ANY TYPE 参数，但内部通过 CAST 和 CASE 显式判别输入类型，规避了 JS UDF 的模板限制；
- 为保障数值精度，建议统一转换为 NUMERIC 或 FLOAT64（示例中为简化使用 INT64，实际可替换为 CAST(arr AS ARRAY
)）； - UNNEST 要求输入为数组，因此对非数组输入（标量）必须手动包裹为 [value]；
- Legacy SQL 对 ARRAY_AGG(... ORDER BY ...) 支持良好，无需额外启用特性；
- 若需跨区域使用，切勿依赖 bigquery-public-data.persistent_udfs.median（仅 US 区域可用且已弃用），应优先采用上述自建 SQL UDF。
✅ 验证示例：
-- 测试标量输入 SELECT median(7); -- 返回 7.0 -- 测试奇数长度数组 SELECT median([5, 7, 9, 1]); -- 返回 6.0（排序后 [1,5,7,9] → (5+7)/2） -- 测试偶数长度数组 SELECT median([2, 4, 6]); -- 返回 4.0（排序后 [2,4,6] → 索引 1 元素） -- 结合表查询使用（创建新表时） CREATE TABLE my_dataset.summary AS SELECT category, median(ARRAY_AGG(value)) AS median_value FROM my_dataset.raw_data GROUP BY category;
总结：在 BigQuery Legacy SQL 中，放弃 JS UDF 的类型脆弱性，转向声明式 SQL 实现，不仅能提升执行性能（避免 JS 引擎开销），更能获得强类型安全与跨区域兼容性。该 median 函数可直接集成至 ETL 流程或物化视图构建中，是稳健数据分析的基础组件。
