在数组列上查找重复的值 [英] Find duplicated values on array column

问题描述

我有一个带有数组列的表，像这样：

I have a table with a array column like this:

my_table
id   array
--   -----------
1    {1, 3, 4, 5}
2    {19,2, 4, 9}
3    {23,46, 87, 6}
4    {199,24, 93, 6}

我想要结果是，重复的值在哪里，像这样：

And i want as result what and where is the repeated values, like this:

value_repeated    is_repeated_on
--------------    -----------
4                 {1,2}
6                 {3,4}

有可能吗？我不知道该怎么做。我不怎么开始！我迷路了！

Is it possible? I don't know how to do this. I don't how to start it! I'm lost!

推荐答案

使用 unnest 将数组转换为行，然后 array_agg 从 id s

Use unnest to convert the array to rows, and then array_agg to build an array from the ids

它应该看起来像这样：

SELECT v AS value_repeated,array_agg(id) AS is_repeated_on FROM 
(select id,unnest(array) as v from my_table) 
GROUP by v HAVING Count(Distinct id) > 1

请注意 HAVING Count（Distinct id）> 1 正在过滤甚至不会出现的值

Note that HAVING Count(Distinct id) > 1 is filtering values that don't appear even once

这篇关于在数组列上查找重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！