PostgreSQL按相同id、ind_id、tar_id分组取ind或tar数组最长行的方法

实现思路 #

使用PostgreSQL内置的窗口函数对数据按指定字段分组，按ind和tar数组的最大长度倒序排序，每个分组仅取排序第一的行即可。

核心函数说明 #

• array_length(数组, 维度)：获取数组的长度，你的样例中是一维数组，维度固定填1即可
• GREATEST(值1, 值2)：返回两个值中的较大值，用于取每行ind和tar数组的最大长度
• ROW_NUMBER() OVER(partition by 分组字段 order by 排序规则)：窗口函数，对每个分组内的行按排序规则生成序号

查询语句（仅查看结果，不修改原表） #

将下方代码中的你的表名替换为实际表名即可执行：

WITH ranked_data AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY id, ind_id, tar_id
            ORDER BY GREATEST(array_length(ind, 1), array_length(tar, 1)) DESC
        ) AS row_num
    FROM 你的表名
)
SELECT "text", id, ind_id, tar_id, ind, tar
FROM ranked_data
WHERE row_num = 1;

去重语句（直接删除原表重复行，保留最长数组行） #

注意：执行前务必先备份原表数据，避免误操作丢失数据：

DELETE FROM 你的表名 t1
USING 你的表名 t2
WHERE 
    t1.id = t2.id
    AND t1.ind_id = t2.ind_id
    AND t1.tar_id = t2.tar_id
    AND GREATEST(array_length(t1.ind, 1), array_length(t1.tar, 1)) < GREATEST(array_length(t2.ind, 1), array_length(t2.tar, 1));

边界说明 #

如果同一分组内存在多行的最大数组长度完全相同，上述语句会随机返回其中一行。如果有明确的优先级要求，可以在窗口函数的ORDER BY后补充额外排序规则，比如要优先保留更早插入的行可以加, t1.ctid ASC即可。

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