PostgreSQL 11含NULL值的多列分组ARRAY_AGG处理需求

针对需求，我们可以通过先标记C值的存在情况，再过滤后分组聚合的方式实现规则要求，以下是适配PostgreSQL 11的SQL语句：

WITH c_marked AS (
    SELECT 
        A,
        B,
        C,
        -- 标记同一A下该C是否存在B非NULL的记录
        EXISTS (
            SELECT 1 
            FROM X x2 
            WHERE x2.A = x.A AND x2.C = x.C AND x2.B IS NOT NULL
        ) AS has_non_null_b
    FROM X
)
SELECT 
    A,
    B,
    ARRAY_AGG(DISTINCT C) AS c_array
FROM c_marked
-- 过滤掉B为NULL但该C已在非NULL B分组中存在的记录
WHERE NOT (B IS NULL AND has_non_null_b)
GROUP BY A, B
ORDER BY A, B NULLS LAST;

逻辑拆解 #

1. 标记阶段：通过EXISTS子查询为每条记录标记，对应C值在同一A维度下是否存在B不为NULL的关联行。
2. 过滤阶段：剔除那些B为NULL、但对应C已经在非NULL B分组中存在的记录，确保规则1的执行——同一C值只保留在非NULL B的分组里。
3. 聚合阶段：按A、B分组后聚合去重的C值为数组，此时NULL分组仅保留只在B为NULL的行中出现的C值，符合规则2。

代入测试数据后，将得到预期结果：

A    B   c_array
____________
PR1  L1  {PL1, PL2}
PR1      {PL3}

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