PostgreSQL子查询用外部未分组列报错及数组聚合需求解决

需求 #

需要编写PostgreSQL查询，将每日的任务名称及其执行次数分组为二维数组，期望输出如下：

Date    |             Jobs             
-----------------------------------------------------------------------------
 21/11/2022 | {{TestJob1,1500},{TestJob2,1100},{TestJob3,500}}
 20/11/2022 | {{TestJob1,1300},{TestJob2,100},{TestJob3,500}}
 19/11/2022 | {{TestJob1,1400},{TestJob2,1900}}
 18/11/2022 | {{TestJob1,1200},{TestJob2,1700},{TestJob3,800},{TestJob4,500}}

尝试过程及问题 #

第一次查询及错误 #

最初尝试的查询：

SELECT j."Start time"::date AS "Date",
  (select array["Job name", count(*)::varchar] from amdw."Job runs" where "Start time"::date = j."Start time"::date group by "Job name") as "Jobs"
  FROM amdw."Job runs" j
  GROUP BY "Date"
  ORDER BY "Date" DESC;

执行时出现错误：

SQL Error [42803]: ERROR: subquery uses ungrouped column "j.Start time" from outer query
  Position: 135

错误原因：子查询引用了外部查询未分组的j."Start time"列，且子查询返回多行结果，无法直接作为单个列的值。

修改后的查询及问题 #

按照建议修改后的查询：

SELECT j."Start time"::date AS "Date",
  array["Job name", count(*)::varchar] as "Jobs"
  FROM amdw."Job runs" j
  GROUP BY "Date", "Job name"
  ORDER BY "Date" DESC;

输出结果中日期重复，每个任务单独占一行，未形成二维数组：

Date    |             Jobs             
-----------------------------------------------------------------------------
 21/11/2022 | {TestJob1,1500}
 21/11/2022 | {TestJob2,1100}
 21/11/2022 | {TestJob3,500}
 20/11/2022 | {TestJob1,1300}
 20/11/2022 | {TestJob2,100}
 20/11/2022 | {TestJob3,500}

解决方案 #

使用PostgreSQL的array_agg聚合函数，先为每个任务生成一维数组，再将同一日期下的所有一维数组聚合为二维数组，同时确保数组内元素类型一致（统一转为text类型）：

SELECT
  "Start time"::date AS "Date",
  array_agg(array["Job name"::text, count(*)::text]) AS "Jobs"
FROM amdw."Job runs"
GROUP BY "Start time"::date
ORDER BY "Date" DESC;

说明 #

1. array["Job name"::text, count(*)::text]：将任务名称和执行次数转换为text类型后组成一维数组，避免类型不兼容问题。
2. array_agg(...)：将同一日期下的所有一维数组聚合为一个二维数组。
3. GROUP BY "Start time"::date：按日期分组，确保每个日期只出现一次。

执行该查询后，即可得到符合期望的输出格式：每个日期对应一行，Jobs列是包含当日所有任务统计的二维数组。

内容的提问来源于stack exchange，提问作者kevingoos