比较数组的相等性，忽略元素的顺序 [英] Compare arrays for equality, ignoring order of elements

问题描述

我有一个包含 4 个数组列的表......结果如下:

I have a table with 4 array columns.. the results are like:

ids       signed_ids   new_ids   new_ids_signed
{1,2,3} | {2,1,3}    | {4,5,6} | {6,5,4}

无论如何通过忽略元素的顺序来比较 ids 和 signed_ids 以使它们相等?

Anyway to compare ids and signed_ids so that they come out equal, by ignoring the order of the elements?

推荐答案

最简单的方法是对它们进行排序并比较它们的排序.请参阅在 PostgreSQL 中对数组进行排序.

The simplest thing to do is sort them and compare them sorted. See sorting arrays in PostgreSQL.

给定样本数据:

CREATE TABLE aa(ids integer[], signed_ids integer[]);
INSERT INTO aa(ids, signed_ids) VALUES (ARRAY[1,2,3], ARRAY[2,1,3]);

如果数组条目总是整数，最好的做法是使用 intarray 扩展名，正如 Erwin 在他的回答中解释的那样.它比任何纯 SQL 公式都快很多.

the best thing to do is to if the array entries are always integers is to use the intarray extension, as Erwin explains in his answer. It's a lot faster than any pure-SQL formulation.

否则，对于适用于任何数据类型的通用版本，定义一个array_sort(anyarray):

Otherwise, for a general version that works for any data type, define an array_sort(anyarray):

CREATE OR REPLACE FUNCTION array_sort(anyarray) RETURNS anyarray AS $$
SELECT array_agg(x order by x) FROM unnest($1) x;
$$ LANGUAGE 'SQL';

并使用它对排序后的数组进行排序和比较:

and use it sort and compare the sorted arrays:

SELECT array_sort(ids) = array_sort(signed_ids) FROM aa;

有一个重要的警告:

SELECT array_sort( ARRAY[1,2,2,4,4] ) = array_sort( ARRAY[1,2,4] );

会是假的.这可能是也可能不是您想要的，这取决于您的意图.

will be false. This may or may not be what you want, depending on your intentions.

或者，定义一个函数array_compare_as_set:

CREATE OR REPLACE FUNCTION array_compare_as_set(anyarray,anyarray) RETURNS boolean AS $$
SELECT CASE
  WHEN array_dims($1) <> array_dims($2) THEN
    'f'
  WHEN array_length($1,1) <> array_length($2,1) THEN
    'f'
  ELSE
    NOT EXISTS (
        SELECT 1
        FROM unnest($1) a 
        FULL JOIN unnest($2) b ON (a=b) 
        WHERE a IS NULL or b IS NULL
    )
  END
$$ LANGUAGE 'SQL' IMMUTABLE;

然后:

SELECT array_compare_as_set(ids, signed_ids) FROM aa;

这与比较两个 array_sort ed 值略有不同.array_compare_as_set 将消除重复，使 array_compare_as_set(ARRAY[1,2,3,3],ARRAY[1,2,3]) 为真，而 array_sort(ARRAY[1,2,3,3]) = array_sort(ARRAY[1,2,3]) 将是假的.

This is subtly different from comparing two array_sorted values. array_compare_as_set will eliminate duplicates, making array_compare_as_set(ARRAY[1,2,3,3],ARRAY[1,2,3]) true, whereas array_sort(ARRAY[1,2,3,3]) = array_sort(ARRAY[1,2,3]) will be false.

这两种方法的性能都会很差.请考虑确保始终将数组存储在首位.

Both of these approaches will have pretty bad performance. Consider ensuring that you always store your arrays sorted in the first place.

这篇关于比较数组的相等性，忽略元素的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！