如何实现PostgreSQL中支持任意列的单行多列行向中位数函数？

需求背景 #

我需要计算单行中多列的中位数（用于识别异常值），场景类似计算单行多列均值，但目标为中位数。现有示例表数据如下：

X   Y    Z
-------------
6   3    3
5   6    NULL
4   5    6
11  7    8

期望输出结果：

MEDIAN
-------------
3
5 或 5.5
5
8

注：当非空值数量为偶数时，中位数可取任一中间值，也可取中间值的平均值，两种方式均接受。

我希望将解决方案封装为PostgreSQL函数，能像GREATEST/LEAST函数那样直接调用，且不硬编码表或列名，支持传入任意数量的列，例如median(x,y,z)或median(a,b,c,d,e)这类调用方式。

解决方案：创建可变参数中位数函数 #

利用PostgreSQL的可变参数特性和数组处理能力，可实现通用的单行多列中位数计算函数，代码如下：

CREATE OR REPLACE FUNCTION median(VARIADIC args NUMERIC[])
RETURNS NUMERIC AS $$
DECLARE
    cleaned_args NUMERIC[];
    arg_count INT;
    mid_idx INT;
BEGIN
    -- 过滤数组中的NULL值，仅保留有效数值
    cleaned_args := ARRAY(SELECT unnest(args) WHERE unnest(args) IS NOT NULL);
    arg_count := array_length(cleaned_args, 1);
    
    -- 处理无有效数据的情况，返回NULL
    IF arg_count = 0 THEN
        RETURN NULL;
    END IF;
    
    -- 对有效数值数组进行排序
    cleaned_args := ARRAY(SELECT unnest(cleaned_args) ORDER BY unnest(cleaned_args));
    
    -- 根据元素数量奇偶性计算中位数
    IF arg_count % 2 = 1 THEN
        mid_idx := (arg_count + 1) / 2;
        RETURN cleaned_args[mid_idx];
    ELSE
        -- 此处返回两个中间值的平均值，若需单个中间值，改为 cleaned_args[arg_count/2] 或 cleaned_args[arg_count/2 +1] 即可
        RETURN (cleaned_args[arg_count/2] + cleaned_args[arg_count/2 + 1]) / 2;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用示例 #

针对示例表，调用函数的SQL语句如下（替换your_table_name为实际表名）：

SELECT median(X, Y, Z) AS MEDIAN FROM your_table_name;

执行后将得到如下结果：

median
--------
      3
    5.5
      5
      8

若需要在偶数个值时返回单个中间值，修改函数中偶数分支的返回语句即可，比如改为RETURN cleaned_args[arg_count/2];，此时第二行结果会变为5。

函数特性说明 #

• VARIADIC args NUMERIC[]：支持传入任意数量的NUMERIC类型列/值，PostgreSQL会自动将参数打包为数组
• 自动过滤NULL值，仅基于有效数值计算中位数
• IMMUTABLE标记：表明函数输入不变时输出恒定，可提升查询性能

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