如何在BigQuery表中计算每行三个列值的中位数

通用实现方案（兼容绝大多数SQL引擎） #

对于固定3个数值列的场景，可直接通过GREATEST、LEAST函数组合实现，逻辑是三个数的中位数等价于总和减去最大值、减去最小值后剩余的数值，示例SQL如下：

SELECT 
    Item,
    `Column 1`,
    `Column 2`,
    `Column 3`,
    (`Column 1` + `Column 2` + `Column 3`) - GREATEST(`Column 1`, `Column 2`, `Column 3`) - LEAST(`Column 1`, `Column 2`, `Column 3`) AS Median
FROM 你的表名;

上述写法代入你给出的示例数据，输出结果完全符合预期：

• Item A：10+12+4 - 12 -4 = 10
• Item B：5+14+20 -20 -5 =14
• Item C：15+5+4 -15 -4=5
  注意：如果列存在NULL值，需要先根据业务逻辑处理NULL（比如替换为0或者过滤对应行）再做计算

支持数组排序的SQL引擎简化方案 #

如果你使用的是PostgreSQL、Spark SQL、BigQuery等支持数组排序函数的引擎，可以直接将多列转为数组排序后取中间值，写法更简洁，也方便后续扩展列数，示例（PostgreSQL为例）：

SELECT 
    Item,
    "Column 1",
    "Column 2",
    "Column 3",
    (ARRAY(SELECT unnest(ARRAY["Column 1", "Column 2", "Column 3"]) ORDER BY 1))[2] AS Median
FROM 你的表名;

三个元素排序后数组的第二个值（PostgreSQL数组下标从1开始）即为中位数，逻辑和上面的通用方案一致。

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