Postgresql删除重复的反向对

qjp7pelc  于 2022-12-12  发布在  PostgreSQL
关注(0)|答案(3)|浏览(124)

我有这张表:

origin     destination

new york      seattle
new york      chicago
new york      portland
seattle       new york
seattle       chicago
chicago       new york

我必须建立一个图形,因此我需要删除所有重复的反向对,以获得:

origin      destination oneway

new york      seattle     0
new york      chicago     0
new york      portland    1
seattle       chicago     1

我已经读过这篇文章:SQL -- Remove duplicate pairs,但它对我没有用,因为我有字符串文件。
谢谢

kiz8lqtg

kiz8lqtg1#

一种方法使用聚合:

select origin, destination,
       (case when exists (select 1
                          from t t2
                          where t2.origin = t.destination and t2.destination = t.origin
                         )
             then 0 else 1
        end) as one_way
from t
where origin < destination
union all
select origin, destination, 1
from t
where origin > destination;

另一种方法是使用窗口函数:

select origin, destination, (cnt = 1)::int as one_way
from (select t.*,
             count(*) over (partition by least(origin, destination), greatest(origin, destination)) as cnt
      from t
     ) t
where origin < destination or
      (origin > destination and cnt = 1);
yr9zkbsy

yr9zkbsy2#

使用leastgreatestrow_numbercount的一个选项。

select origin,dest,case when cnt_per_pair=1 then 1 else 0 end as one_way
from (select t.*,row_number() over(partition by least(origin,dest),greatest(origin,dest) 
                                   order by dest) as rnum,
      count(*) over(partition by least(origin,dest),greatest(origin,dest)) as cnt_per_pair
      from tbl t
     ) t
where rnum=1
j8ag8udp

j8ag8udp3#

如果你的表是一个笛卡尔积 (我在这个片段中看到的,这意味着你已经颠倒了表中每一行的副本),并且你不关心每一边会有什么,这比上面的一切都要容易得多

select origin, destination
from table t
origin > destination

否则,您可以使用其他答案

相关问题