【Pregsql】多列间横向去重

一、问题

选出5列中非空唯一值大于等于2的行，数据如下，目标行为第1行：

二、解决

1.此问题可看做多列间的横向去重，主要思路：

（1）通过array()函数将列变成数组；再通过unnest()把array类型展开成多行；

（2）通过子查询语法封装上述数据，进行非空筛选和唯一值统计；

代码如下：

select *
from (
    select '1' as col1, '2' as col2, ' ' as col3, '1' as col4, '3' as col5
    union all
    select '2' as col1, ' ' as col2, ' ' as col3, '2' as col4, ' ' as col5
) as a
where (select count(distinct col) from unnest(array[col1, col2, col3, col4, col5]) as tb(col) where col != ' ') >=2

2.结果：

 

【参考】

1.https://stackoverflow.com/questions/3994556/eliminate-duplicate-array-values-in-postgres