阵列比较平等，无视元素的顺序 [英] 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扩展，如欧文解释了他的答案。这是一个的很多的比任何纯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;

还有一个重要的警告：

There's an important caveat:

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.

这两种方法都将有pretty糟糕的表现。考虑确保您始终保存在首先进行排序的数组。

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

这篇关于阵列比较平等，无视元素的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！