在不同行的多个列之间找到重复的值

I have a dataset with multiple columns that look similar to this:

我有一个具有多个列的数据集，看起来类似于:

ID1     ID2     ID3      ID4
Blue    Grey    Fuchsia  Green
Black   Blue    Orange   Blue
Green   Green   Yellow   Pink
Pink    Yellow  NA       Orange

What I want to do is count how many times each value is duplicated across the four columns. For example, this is what I'd like to get back from the above:

我要做的是计算每个值在四列中重复的次数。例如，这就是我想从上面得到的:

ID      Replicates   
Blue    3       
Black   1    
Green   3       
Pink    2
Grey    1
Yellow  2
Fuchsia 1 
Orange  2

I'd also like to be able to ask which ID value is present in the data set at frequency >2. So the expected result would be: Green and Blue.

我还想知道在>2频率的数据集中有哪个ID值。所以预期的结果是:绿色和蓝色。

Any thoughts on how to do this in Oracle? Thanks!

对如何在Oracle中实现这一点有什么想法吗?谢谢!

4 个解决方案

#1

select c, count(*)
from
(
select ID1 as c from tablename
union all
select ID2 as c from tablename
union all
select ID3 as c from tablename
union all
select ID4 as c from tablename
)
group by c

Add HAVING count(*) > 2 at the end to get only Green and Blue.

添加count(*) > 2，最后只得到绿色和蓝色。

#2

SELECT ID, COUNT(*) FROM(
SELECT ID1 ID FROM TBL UNION ALL
SELECT ID2 ID FROM TBL UNION ALL
SELECT ID3 ID FROM TBL UNION ALL
SELECT ID4 ID FROM TBL ) Q
GROUP BY Q.ID;

#3

select id, sum(id_cnt)
(select ID1 as ID,count(*) as id_cnt from tableA group by ID1
union all
select ID2,count(*)from tableA group by ID2
union all
select ID3,count(*)from tableA group by ID3
union all
select ID4,count(*)from tableA group by ID4)
group by id

Outpout will be similar to

Outpout类似于

ID      Replicates   
Blue    3       
Black   1    
Green   3       
Pink    2
Grey    1
Yellow  2
Fuchsia 1 
Orange  2

#4

Here is another very straight-forward option using Oracle's unpivot keyword:

下面是另一个使用Oracle的unpivot关键字的非常直接的选项:

select id, count(*) as replicates
from tbl
unpivot
(
  id for original_column_name in (id1, id2, id3, id4)  
)
group by id

... and of course, you can add a having and/or order by clause as you please.

…当然，您可以根据您的要求添加一个有和/或order by子句。

#1

select c, count(*)
from
(
select ID1 as c from tablename
union all
select ID2 as c from tablename
union all
select ID3 as c from tablename
union all
select ID4 as c from tablename
)
group by c