PostgreSQL编写自定义函数：计算数组中每个元素对应的其余元素乘积

没问题，我来帮你实现这个PostgreSQL函数，正好可以用你提到的exp(sum(ln(value)))思路来做。先从基础版本说起，再扩展到能处理负数和0的健壮版本。

基础版本（仅适用于正数值数组） #

这个版本简单直接，完美匹配你给的示例场景（所有元素都是正数）：

CREATE OR REPLACE FUNCTION array_product_exclude_self(arr numeric[])
RETURNS numeric[] AS $$
DECLARE
    result_arr numeric[];
BEGIN
    -- 处理空数组或NULL输入
    IF arr IS NULL OR array_length(arr, 1) = 0 THEN
        RETURN '{}'::numeric[];
    END IF;

    WITH indexed_elements AS (
        -- 把数组拆成带位置索引的行
        SELECT 
            ordinality AS idx, 
            value
        FROM unnest(arr) WITH ORDINALITY
    )
    -- 对每个位置，计算排除自身的所有元素乘积，再聚合回数组
    SELECT array_agg(product ORDER BY idx) INTO result_arr
    FROM (
        SELECT 
            ie.idx,
            exp(sum(ln(ie_other.value))) AS product
        FROM indexed_elements ie
        -- 自连接，只关联非自身的元素
        JOIN indexed_elements ie_other ON ie.idx != ie_other.idx
        GROUP BY ie.idx
    ) sub;

    RETURN result_arr;
END;
$$ LANGUAGE plpgsql;

测试一下 #

运行这条语句：

SELECT array_product_exclude_self(ARRAY[2, 3, 4, 5]);

会返回你想要的结果：{60,40,30,24}

代码解释 #

1. unnest(arr) WITH ORDINALITY：这是PostgreSQL把数组拆分为行的常用方式，ordinality会给每个元素分配原始位置的索引，确保我们能按原顺序重组结果。
2. 自连接indexed_elements表，通过ie.idx != ie_other.idx过滤掉当前元素，只保留其他所有元素。
3. exp(sum(ln(ie_other.value)))：利用对数的性质，乘积的对数等于对数的和，再用指数还原成乘积，完美避开循环计算的麻烦。
4. array_agg(product ORDER BY idx)：按原始位置索引排序聚合，得到和输入数组顺序一致的结果数组。

健壮版本（支持负数和0） #

刚才的基础版本有个局限：如果数组里有0或者负数，ln(0)或者ln(负数)会报错。下面这个版本处理了这些边界情况：

CREATE OR REPLACE FUNCTION array_product_exclude_self_safe(arr numeric[])
RETURNS numeric[] AS $$
DECLARE
    result_arr numeric[];
    total_negatives integer; -- 数组中负数的总数
    total_zeros integer;     -- 数组中0的总数
BEGIN
    IF arr IS NULL OR array_length(arr, 1) = 0 THEN
        RETURN '{}'::numeric[];
    END IF;

    -- 先统计数组里的负数和0的数量，用于后续逻辑判断
    SELECT 
        COUNT(*) FILTER (WHERE value < 0),
        COUNT(*) FILTER (WHERE value = 0)
    INTO total_negatives, total_zeros
    FROM unnest(arr);

    WITH indexed_elements AS (
        SELECT 
            ordinality AS idx, 
            value,
            CASE WHEN value < 0 THEN 1 ELSE 0 END AS is_negative, -- 当前元素是否为负数
            CASE WHEN value = 0 THEN 1 ELSE 0 END AS is_zero     -- 当前元素是否为0
        FROM unnest(arr) WITH ORDINALITY
    )
    SELECT array_agg(
        CASE
            -- 情况1：数组中有至少2个0 → 所有位置的乘积都包含0，结果全为0
            WHEN total_zeros >= 2 THEN 0
            -- 情况2：数组中只有1个0
            WHEN total_zeros = 1 THEN
                CASE WHEN ie.is_zero = 1 THEN
                    -- 当前元素是0，乘积是其他非0元素的乘积，同时处理符号
                    SIGN(POWER(-1, total_negatives - ie.is_negative)) * exp(sum(ln(ABS(ie_other.value))) FILTER (WHERE ie_other.value != 0))
                ELSE
                    -- 当前元素不是0，乘积包含那个唯一的0，结果为0
                    0
                END
            -- 情况3：数组中没有0，处理负数符号后计算乘积
            ELSE
                SIGN(POWER(-1, total_negatives - ie.is_negative)) * exp(sum(ln(ABS(ie_other.value))))
        END
        ORDER BY idx
    ) INTO result_arr
    FROM indexed_elements ie
    LEFT JOIN indexed_elements ie_other ON ie.idx != ie_other.idx
    GROUP BY ie.idx, ie.is_zero, ie.is_negative, total_negatives, total_zeros;

    RETURN result_arr;
END;
$$ LANGUAGE plpgsql;

测试用例 #

• 含负数的情况：

SELECT array_product_exclude_self_safe(ARRAY[-2, 3, -4, 5]);
-- 返回 {-60,40,-30,24}

• 含单个0的情况：

SELECT array_product_exclude_self_safe(ARRAY[2, 0, 4, 5]);
-- 返回 {0,40,0,0}

• 含多个0的情况：

SELECT array_product_exclude_self_safe(ARRAY[2, 0, 4, 0]);
-- 返回 {0,0,0,0}

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