PostgreSQL - 返回多个任意子查询的结果

Like the title of the question suggests, I'm attempting take a number of arbitrary sub-queries and combine them into a single, large query.

就像问题的标题所暗示的那样,我正在尝试采用一些任意的子查询并将它们组合成一个大的查询。

Ideally, I'd like to the data to be returned as a single record, with each column being the result of one of the sub-queries. E.G.

理想情况下,我希望将数据作为单个记录返回,每列都是其中一个子查询的结果。例如。

|   sub-query 1   |   sub-query 2   |      ...
|-----------------|-----------------|-----------------
| (array of rows) | (array of rows) |      ...

The sub-queries themselves are built using Knex.js in a Node app and are completely arbitrary. I've come fairly close to a proper solution, but I've hit a snag.

子查询本身是在Node应用程序中使用Knex.js构建的,并且完全是任意的。我已经接近一个合适的解决方案了,但我遇到了麻烦。

My current implementation has the final query like so:

我当前的实现有这样的最终查询:

SELECT
  array_agg(sub0.*) as s0,
  array_agg(sub1.*) as s1,
  ...
FROM
  (...) as sub0,
  (...) as sub1,
  ...
;

Which mostly works, but causes huge numbers of duplicates in the output. During my testing, I found that it returns records such each record is duplicated a number of times equal to how many records would have been returned without the duplicates. For example, a sub-query that should return 10 records would, instead, return 100 (each record being duplicated 10 times).

这主要起作用,但在输出中导致大量重复。在我的测试过程中,我发现它返回记录,这样每条记录的重复次数等于没有重复记录的返回记录数。例如,应返回10条记录的子查询将返回100(每条记录重复10次)。

I've yet to figure out why this occurs or how to fix the query to not get the issue.

我还没弄清楚为什么会发生这种情况或如何修复查询以避免问题。

So far, I've only been able to determine that:

到目前为止,我只能确定:

The number of records returned by the sub-queries is correct when queried separately

单独查询时,子查询返回的记录数是正确的

The duplicates are not caused by intersections between the sub-queries
- i.e. sub-queries contain rows that exist in other sub-queries

重复项不是由子查询之间的交叉引起的,即子查询包含存在于其他子查询中的行

Thanks in advance.

提前致谢。

2 个解决方案

#1

Just place the arbitrary queries in the select list:

只需将任意查询放在选择列表中:

with sq1 as (
    values (1, 'x'),(2, 'y')
), sq2 as (
    values ('a', 3), ('b', 4), ('c', 5)
)
select
    (select array_agg(s.*) from (select * from sq1) s) as s0,
    (select array_agg(s.*) from (select * from sq2) s) as s1
;
        s0         |            s1             
-------------------+---------------------------
 {"(1,x)","(2,y)"} | {"(a,3)","(b,4)","(c,5)"}

#2

Also you can add row_number to sub-queries and use that column to outer join tables (instead of cross join):

您还可以将row_number添加到子查询中,并将该列用于外连接表(而不是交叉连接):

SELECT
  array_agg(sub0.*) as s0,
  array_agg(sub1.*) as s1
FROM
  (SELECT row_number() OVER (), * FROM (VALUES (1, 'x'),(2, 'y')) t) as sub0
  FULL OUTER JOIN
  (SELECT row_number() OVER (), * FROM (VALUES ('a', 3), ('b', 4), ('c', 5)) t1) as sub1
  ON sub0.row_number=sub1.row_number
;

             s0             |               s1                
----------------------------+---------------------------------
 {"(1,1,x)","(2,2,y)",NULL} | {"(1,a,3)","(2,b,4)","(3,c,5)"}
(1 row)

#1