将ID添加到两列中的重复项

Question

我有一张代表城市间贸易往来的表格，我想添加一个id来表示同一产地/目的地和目的地/原产地相同的组。

例如：

| origin | destination
|--------|------------
| 8      | 2
| 2      | 8
| 8      | 2
| 8      | 5
| 8      | 5
| 9      | 1
| 1      | 9

将成为：

| id | origin | destination
|----|--------|------------
| 0  | 8      | 2
| 0  | 2      | 8
| 0  | 8      | 2
| 1  | 8      | 5
| 1  | 8      | 5
| 2  | 9      | 1
| 2  | 1      | 9

我可以有相同的来源/目的地，但我也可以有原产地/目的地=目的地/原产地，我希望所有这些群体都能被识别出来。

Answer 1

一种方法:使用窗口函数dense_rank()和LEAST

SELECT dense_rank() OVER (ORDER BY GREATEST(origin, destination)
                                 , LEAST   (origin, destination)) - 1 AS id
     , origin, destination
FROM   trade;

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_11&fiddle=84e9a5d9ccd980b2c489882a2d62f791

- 1以0开头，就像您的示例一样。