一尘不染

DISTINCT有两个array_agg（或一个内有元组的array_agg）？

sql

我有以下查询：

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()

输出看起来像这样：

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(row(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)}

PS为了更好地了解，我忽略了示例输出中的随机化部分。

实际结果集：

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)"}"

阅读 142

2021-05-23

共1个答案

一尘不染

类似于我在上一个问题中回答的内容，ARRAY of rows就像您建议的和简短的位置符号一样：

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();

但是我更喜欢带有二维数组的其他版本。

2021-05-23