sql - 从数组生成大小为 2 的 postgres 中的唯一组合

Question

我有一个跟进 this question我在其中生成了此类表的数组:

 val | fkey | num
 ------------------
 1   |  1   | 10
 1   |  2   | 9  
 1   |  3   | 8  
 2   |  3   | 1 

其中生成的返回行会像这样(fkeys 基本上聚合到一个列表中):

1 | [1,2,3]

我想做的是根据“num”列中的值修改查询。也就是说，我想要这样的东西:

1 | [1,2] | [10, 9]
1 | [1,3] | [10, 8]
1 | [2,3] | [9, 8]

返回查询中第三列的排序不影响我。现在我有这样的东西:

SELECT val, array_agg(fkey), array_agg(num)
FROM mytable
GROUP BY val
Having Count(fkey) > 1

但这会返回更像的东西:

1 | [1,2,3] | [10, 9, 8]

这没问题，除了我无法轻易分辨出第三个数组中的哪个数字来自哪个 fkey(如果这有意义的话)。像这样的东西可以用来跟踪它:

1 | [1,2,3] | [10 - 1, 9 - 2, 8 - 3]

我不确定执行此操作的最佳方法是什么，但我愿意接受建议。

编辑:我使用的是 Postgres 9.3.6。表定义是:

awesome-db=# \d mytable
Table "public.mytable"
Column    |  Type   | Modifiers 
----------+---------+-----------
 val      | bytea   | not null
 fkey     | uuid    | not null
 num      | integer | not null
 Indexes:
"comp_key" UNIQUE CONSTRAINT, btree (fkey, num, val)
"fingerprint_index" btree (val)

Answer 1

您需要一个使用 row_number 的自连接:

select t1.val,t1.fkey||','||t2.fkey,t1.num||','|| t2.num
from (select row_number() over(order by val) rn,
       val,
       fkey,
       num
from mytable) t1 
join (select row_number() over(order by val) rn,
       val,
       fkey,
       num
from mytable) t2
on t1.val=t2.val and t1.rn<t2.rn

SQLFIDDLE DEMO