DISTINCT具有两个ARRAY_AGG（或一个ARRAY_AGG与元组内）？ [英] DISTINCT with two array_agg (or one array_agg with tuple inside)?
本文介绍了DISTINCT具有两个ARRAY_AGG（或一个ARRAY_AGG与元组内）？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下查询：
SELECT DISTINCT ON（ps.p）
m.groundtruth，ps.p，ARRAY_AGG（m.anchor_id），ARRAY_AGG（m.id）
从
测量米
加入
（SELECT UNNEST（point_array）为P）AS PS
ON ST_DWithin（ps.p，m.groundtruth，距离）
GROUP BY ps.p，m.groundtruth
ORDER BY ps.p，随机的（）
输出看起来像：
groundtruth | p | anchor_array | id_array
-------------------------------------------------- ----
G1 | P1 | {1,3,3,3,4} | {1,2,3,4,5}
G2 | P1 | {1,5,7} | {6,7,8}
G1 | P2 | {1,3,3,3,4} | {1,2,3,4,5}
替代查询：
SELECT DISTINCT ON（ps.p）
m.groundtruth，ps.p，ARRAY_AGG（行（m.anchor_id，m.id））
...
输出：
groundtruth | p | combined_array
-------------------------------------------------- ---------
G1 | P1 | {（1,1），（3,2），（3,3），（3,4），（4,5）}
G2 | P1 | {（1,6），（5,7），（7,8）}
G1 | P2 | {（1,1），（3,2），（3,3），（3,4），（4,5）}
我想实现什么：
- 摆脱重复的条目里面anchor_array
- ，并为每个已删除项目：从具有相同指数id_array删除项
或为可选的查询和输出：
- 请每个元组不同的有关元组的第一项的
结果应该是什么样子的：
groundtruth | p | anchor_array | id_array
-------------------------------------------------- ----
G1 | P1 | {1,3,4} | {1,2,5}
G2 | P1 | {1,5,7} | {6,7,8}
G1 | P2 | {1,3,4} | {1,2,5}
或为可选的查询和输出：
groundtruth | p | combined_array
-------------------------------------------------- ---------
G1 | P1 | {（1,1），（3,2），（4,5）}
G2 | P1 | {（1,6），（5,7），（7,8）}
G1 | P2 | {（1,1），（3,2），（4,5）}
P.S。我忽略了示例输出随机化一部分更好的概览。
实时结果集：
磷; groundtruth; IDS
-------------------------------------------------- -------------------------------------------
\"0101000000EE7C3F355EF24F4019390B7BDA011940\";\"010100000094F6065F98E44F40A930B610E4A01B40\";\"{\"(29,250)\",\"(30,251)\",\"(31,241)\",\"(32,263)\",\"(33,243)\",\"(34,264)\",\"(35,277)\"}\"
\"0101000000EE7C3F355EF24F40809F71E140681940\";\"010100000094F6065F98E44F40A930B610E4A01B40\";\"{\"(29,250)\",\"(30,251)\",\"(31,257)\",\"(32,276)\",\"(33,272)\",\"(34,264)\",\"(35,249)\"}\"
\"0101000000EE7C3F355EF24F40E605D847A7CE1940\";\"0101000000EE7C3F355EF24F4019390B7BDA011940\";\"{\"(30,194)\",\"(31,181)\",\"(32,168)\",\"(33,124)\",\"(34,141)\",\"(35,4)\"}\"
\"0101000000EE7C3F355EF24F404C6C3EAE0D351A40\";\"010100000014D044D8F0DC4F4073BA2C2636DF1C40\";\"{\"(30,281)\",\"(31,278)\",\"(32,297)\",\"(33,284)\",\"(34,294)\",\"(35,303)\"}\"
\"0101000000EE7C3F355EF24F40B3D2A414749B1A40\";\"0101000000DE9387855AEB4F4062670A9DD7581A40\";\"{\"(30,235)\",\"(31,214)\",\"(32,220)\",\"(33,221)\",\"(34,217)\",\"(35,232)\"}\"
\"0101000000EE7C3F355EF24F4019390B7BDA011B40\";\"0101000000AF94658863D54F40A7E8482EFF211E40\";\"{\"(27,316)\",\"(31,329)\",\"(32,334)\",\"(33,340)\",\"(34,327)\",\"(35,324)\"}\"
\"0101000000EE7C3F355EF24F40809F71E140681B40\";\"0101000000DE9387855AEB4F4062670A9DD7581A40\";\"{\"(30,224)\",\"(31,210)\",\"(32,220)\",\"(33,230)\",\"(34,226)\",\"(35,213)\"}\"
\"0101000000EE7C3F355EF24F40E605D847A7CE1B40\";\"010100000014D044D8F0DC4F4073BA2C2636DF1C40\";\"{\"(30,281)\",\"(31,304)\",\"(32,288)\",\"(33,293)\",\"(34,306)\",\"(35,295)\"}\"
\"0101000000EE7C3F355EF24F404C6C3EAE0D351C40\";\"010100000094F6065F98E44F40A930B610E4A01B40\";\"{\"(29,250)\",\"(30,256)\",\"(31,257)\",\"(32,271)\",\"(33,254)\",\"(34,260)\",\"(35,277)\"}\"
\"0101000000EE7C3F355EF24F4019390B7BDA011D40\";\"010100000007F0164850C44F405F46B1DCD24A2040\";\"{\"(31,383)\",\"(32,409)\",\"(33,390)\",\"(34,411)\",\"(35,407)\"}\"
解决方案
要什么，我在自己的 preceding问题回答类似，只需用行ARRAY 像你的建议和较短的位置记数法：
SELECT DISTINCT ON（1）
P，groundtruth，ARRAY_AGG（ROW（anchor_id，ID））作为ID。
FROM（
SELECT DISTINCT ON（1，2，3）
ps.p，m.groundtruth，m.anchor_id，m.id
FROM（SELECT UNNEST（point_array）为P）AS PS
JOIN测量M于ST_DWithin（ps.p，m.groundtruth，距离）
ORDER BY 1，2，3，随机（）
） X
GROUP BY 1,2
ORDER BY 1，随机（）;
但我喜欢其他的版本有一个2维数组更好。
I've got the following query:
SELECT DISTINCT ON (ps.p)
m.groundtruth, ps.p, ARRAY_AGG(m.anchor_id), ARRAY_AGG(m.id)
FROM
measurement m
JOIN
(SELECT unnest(point_array) AS p) AS ps
ON ST_DWithin(ps.p, m.groundtruth, distance)
GROUP BY ps.p, m.groundtruth
ORDER BY ps.p, RANDOM()
The output looks like that:
groundtruth | p | anchor_array | id_array
------------------------------------------------------
G1 | P1 | {1,3,3,3,4} | {1,2,3,4,5}
G2 | P1 | {1,5,7} | {6,7,8}
G1 | P2 | {1,3,3,3,4} | {1,2,3,4,5}
Alternative query:
SELECT DISTINCT ON (ps.p)
m.groundtruth, ps.p, ARRAY_AGG(row(m.anchor_id, m.id))
...
Output:
groundtruth | p | combined_array
-----------------------------------------------------------
G1 | P1 | {(1,1),(3,2),(3,3),(3,4),(4,5)}
G2 | P1 | {(1,6),(5,7),(7,8)}
G1 | P2 | {(1,1),(3,2),(3,3),(3,4),(4,5)}
What I want to achieve:
- Getting rid of duplicate entries inside anchor_array
- And for each deleted item: Delete the item from the id_array with the same index
Or for the alternative query and output:
- Make each tuple distinct concerning the first entry of the tuple
What the result should look like:
groundtruth | p | anchor_array | id_array
------------------------------------------------------
G1 | P1 | {1,3,4} | {1,2,5}
G2 | P1 | {1,5,7} | {6,7,8}
G1 | P2 | {1,3,4} | {1,2,5}
Or for the alternative query and output:
groundtruth | p | combined_array
-----------------------------------------------------------
G1 | P1 | {(1,1),(3,2),(4,5)}
G2 | P1 | {(1,6),(5,7),(7,8)}
G1 | P2 | {(1,1),(3,2),(4,5)}
P.S. I have ignored the randomization part in the example output for better overview.
Real result set:
p ; groundtruth ; ids
---------------------------------------------------------------------------------------------
"0101000000EE7C3F355EF24F4019390B7BDA011940";"010100000094F6065F98E44F40A930B610E4A01B40";"{"(29,250)","(30,251)","(31,241)","(32,263)","(33,243)","(34,264)","(35,277)"}"
"0101000000EE7C3F355EF24F40809F71E140681940";"010100000094F6065F98E44F40A930B610E4A01B40";"{"(29,250)","(30,251)","(31,257)","(32,276)","(33,272)","(34,264)","(35,249)"}"
"0101000000EE7C3F355EF24F40E605D847A7CE1940";"0101000000EE7C3F355EF24F4019390B7BDA011940";"{"(30,194)","(31,181)","(32,168)","(33,124)","(34,141)","(35,4)"}"
"0101000000EE7C3F355EF24F404C6C3EAE0D351A40";"010100000014D044D8F0DC4F4073BA2C2636DF1C40";"{"(30,281)","(31,278)","(32,297)","(33,284)","(34,294)","(35,303)"}"
"0101000000EE7C3F355EF24F40B3D2A414749B1A40";"0101000000DE9387855AEB4F4062670A9DD7581A40";"{"(30,235)","(31,214)","(32,220)","(33,221)","(34,217)","(35,232)"}"
"0101000000EE7C3F355EF24F4019390B7BDA011B40";"0101000000AF94658863D54F40A7E8482EFF211E40";"{"(27,316)","(31,329)","(32,334)","(33,340)","(34,327)","(35,324)"}"
"0101000000EE7C3F355EF24F40809F71E140681B40";"0101000000DE9387855AEB4F4062670A9DD7581A40";"{"(30,224)","(31,210)","(32,220)","(33,230)","(34,226)","(35,213)"}"
"0101000000EE7C3F355EF24F40E605D847A7CE1B40";"010100000014D044D8F0DC4F4073BA2C2636DF1C40";"{"(30,281)","(31,304)","(32,288)","(33,293)","(34,306)","(35,295)"}"
"0101000000EE7C3F355EF24F404C6C3EAE0D351C40";"010100000094F6065F98E44F40A930B610E4A01B40";"{"(29,250)","(30,256)","(31,257)","(32,271)","(33,254)","(34,260)","(35,277)"}"
"0101000000EE7C3F355EF24F4019390B7BDA011D40";"010100000007F0164850C44F405F46B1DCD24A2040";"{"(31,383)","(32,409)","(33,390)","(34,411)","(35,407)"}"
解决方案
Similar to what I answered at your preceding question, just with ARRAY of rows like you suggested and shorter positional notation:
SELECT DISTINCT ON (1)
p, groundtruth, array_agg(ROW(anchor_id, id)) AS ids
FROM (
SELECT DISTINCT ON (1, 2, 3)
ps.p, m.groundtruth, m.anchor_id, m.id
FROM (SELECT unnest(point_array) AS p) AS ps
JOIN measurement m ON ST_DWithin(ps.p, m.groundtruth, distance)
ORDER BY 1, 2, 3, random()
) x
GROUP BY 1, 2
ORDER BY 1, random();
But I like the other version with a 2-dimensional array better.
这篇关于DISTINCT具有两个ARRAY_AGG（或一个ARRAY_AGG与元组内）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
