sql - DISTINCT 有两个 array_agg(或一个 array_agg 里面有元组)？

Question

我有以下查询:

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

附言为了更好地概述，我忽略了示例输出中的随机化部分。

---

真实结果集:

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

Answer 1

类似于我在您的 preceding question 上的回答，就像你建议的那样使用 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();

但我更喜欢另一个带有二维数组的版本。