如何从SQL聚合数组中排除每行最大值并合并结果？

第一步：生成排除每组最大值的数组 #

先通过窗口函数标记每组内的id排序，过滤掉最大值后再聚合：

SELECT array_agg(id) AS ids
FROM (
    SELECT 
        id,
        a_c_id,
        a_c_dttm,
        a_c_code,
        -- 按分组字段分区，id降序排序，最大值排第1位
        ROW_NUMBER() OVER (PARTITION BY a_c_id, a_c_dttm, a_c_code ORDER BY id DESC) AS rn
    FROM fff
) t
-- 排除每组的最大值（rn=1的记录）
WHERE rn > 1
GROUP BY a_c_id, a_c_dttm, a_c_code;

执行后就能得到你要的中间结果：

{5}
{8,12}
{6}
{7}

第二步：合并所有数组为最终结果 #

如果不需要保留分组，直接合并所有符合条件的id，更简洁的写法是直接筛选后聚合：

SELECT array_agg(DISTINCT id) AS final_ids
FROM (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY a_c_id, a_c_dttm, a_c_code ORDER BY id DESC) AS rn
    FROM fff
) t
WHERE rn > 1;

执行后得到最终结果：{5,8,12,6,7}

注意事项 #

如果同一分组内存在多个相同的最大值（比如同一组有两个id=13），想要排除所有最大值的话，把ROW_NUMBER()换成RANK()即可，这样所有最大值的rn都会是1，过滤后会全部排除。

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