从数组生成在大小两个postgres的唯一组合
问题描述:
我有一个后续到this question其中我产生这种类型的表的阵列:从数组生成在大小两个postgres的唯一组合
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)
答
你需要一个self join
使用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
答
你有什么你问什么:元素的顺序在两个数组对应。
你的第一个想法:
随着对组合的所有列(val, fkey, num)
和NOT NULL
的限制,你会得到两个元素(小(num, fkey)
第一)的阵列,像这样的UNIQUE
约束:
SELECT t1.val
, ARRAY[t1.num, t2.num] AS nums
, ARRAY[t1.fkey, t2.fkey] AS fkeys
FROM mytable t1
JOIN mytable t2 USING (val)
WHERE (t1.num, t1.fkey) < (t2.num, t2.fkey);
或者您第二个想法:
SELECT val, array_agg(num) AS nums, array_agg(num::text || ' - ' || fkey) AS fkeys
FROM (
SELECT *
FROM mytable
ORDER BY num, fkey
) sub
GROUP BY val
HAVING count(*) > 1;
+0
我添加了一些信息 - 唯一的约束是在所有三列之间。在我的定义中可以重复(val,fkey)。 – 2015-04-01 16:40:24
在同一个'SELECT'列表中的两个'array_agg()'调用以相同的顺序执行元素,除非通过将'ORDER BY'添加到函数参数中指示。所以你*可以*告诉两个数组中的哪些元素合并在一起,并且你的当前函数应该完成这项工作。我错过了什么? – 2015-04-01 08:08:39
通常,像这样的问题需要您提供表定义(psql中的'\ d mytable')和您的Postgres版本。 – 2015-04-01 08:23:20
在psql中复制/粘贴从'\ d mytable'得到的内容* * * * * * * * * *否则我们无法确定完整的图片。例如,我仍然不知道你的列是否被定义为“NOT NULL”。 – 2015-04-02 02:46:29