Oracle 如何判定各列的数值 去重取其中一条

newkid

sql_tigerliu 发表于 2021-4-6 10:43
用listagg+order by合并前3列生成一个新列, 然后再根据新列去重, 应该可以实现

是的，在listagg之前要UNPIVOT把列转换成行，大家的思路都是排序后去重。

sql_tigerliu

newkid 发表于 2021-4-6 21:05
是的，在listagg之前要UNPIVOT把列转换成行，大家的思路都是排序后去重。

我看错了, 看来还是要动手搞一下才更清楚, 题主没有把去重后的保留条件说清楚, 那就随便保留一条吧, 等下简单写一下

sql_tigerliu

我把上面有人提到的用case when的写法写出来,如newkid所说, 这种写法在字段数少的时候可以用一下, 如果涉及的字段数多,组合就多了,建议先简单合并, 再用一个自定义函数把字符串重新排序:
with tmp (c1,c2,c3,c4)
as
(select 'a','b','b','c' from dual union all
select 'b','b','a','d' from dual union all
select 'a','b','e','c' from dual union all
select 'e','b','a','d' from dual
),tmp1 as
(
select --sort high to low
case when c1>=c2 and c2>=c3  then c1||','||c2||','||c3
      when c1>=c3 and c3>=c2  then c1||','||c3||','||c2
      when c2>=c3 and c3>=c1  then c2||','||c3||','||c1
      when c2>=c1 and c1>=c3  then c2||','||c1||','||c3
      when c3>=c2 and c2>=c1  then c3||','||c2||','||c1
      when c3>=c1 and c1>=c2  then c3||','||c1||','||c2  
      end as c1c2c3,
c1,c2,c3,c4
from tmp
)
select c1,c2,c3,c4 from
(
select a.* , row_number() over (partition by c1c2c3 order by null) as rn
from tmp1 a
)
where rn=1;

〇〇

clickhouse 有 arraygroup

〇〇

记错了，arraySort才是解决这个问题的

〇〇

文档  https://clickhouse.tech/docs/zh/sql-reference/functions/array-functions/

〇〇

〇〇 发表于 2021-4-8 06:35
记错了，arraySort才是解决这个问题的

Set allow_experimental_window_functions = 1;

with tmp --(c1,c2,c3,c4)
as
(select 'a'c1,'b'c2,'b'c3,'c'c4  union all
select 'b','b','a','d'  union all
select 'a','b','e','c'  union all
select 'e','b','a','d'
),tmp1 as
(
select --sort high to low
arraySort([c1,c2,c3]) c1c2c3,
c1,c2,c3,c4
from tmp
)
select c1,c2,c3,c4 from
(
select a.* , row_number() over (partition by c1c2c3 order by null) as rn
from tmp1 a
)
where rn=1;