PostgreSQL中计算两表数组关联的最小价格总和方法

核心思路 #

1. 拆分表A的group数组为单个元素行；
2. 为每个元素找到表B中包含该元素的所有记录里的最小price；
3. 对所有最小价格求和（也可按表A的id分组求和）。

基础实现SQL #

SELECT SUM(min_price) AS total_sum
FROM (
    SELECT
        elem,
        MIN(b.price) AS min_price
    FROM table_a a,
         unnest(a."group") AS elem  -- 转义关键字字段名"group"
    JOIN table_b b ON elem = ANY(b."group")
    GROUP BY elem
) t;

高效优化版本（预计算表B最小价格） #

当表B数据量较大时，先预计算每个元素在表B中的最小价格，再关联表A拆分后的元素，能显著提升查询效率：

SELECT
    -- 可选：按表A的id分组求和，去掉则计算全局总和
    a.id,
    SUM(b_min.min_price) AS id_total_sum
FROM table_a a,
     unnest(a."group") AS elem
JOIN (
    -- 预计算表B中每个元素对应的最小价格
    SELECT
        unnest(b."group") AS b_elem,
        MIN(b.price) AS min_price
    FROM table_b b
    GROUP BY b_elem
) b_min ON elem = b_min.b_elem
GROUP BY a.id;

关键细节说明 #

• 关键字转义：group是SQL保留关键字，必须用双引号"group"引用字段名，否则会触发语法错误；
• NULL处理：如果表A的某个元素在表B中无匹配记录，MIN(b.price)会返回NULL，求和时会自动忽略。若需将此类情况视为0，可修改为COALESCE(MIN(b.price), 0::money)；
• 类型兼容：PostgreSQL的money类型支持直接参与求和运算，无需额外转换。

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