Итак, я пытаюсь создать запрос, в котором я группирую подзапрос в массиве. Я не эксперт в Postgres, особенно в массивах запросов, поэтому я пытаюсь изучить более сложные вещи.
Я хочу получить следующий результат:
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
Кто-нибудь знает, как решить эту проблему с запросом и получить результат, который я хочу получить?
После адаптации запроса, предложенного @a_horse_with_no_name:
SELECT j."Start time"::date AS "Date",
["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}}
Итак, теперь мне нужно найти способ показать дату только один раз и создать второе измерение в массиве...
Это действительно частично решает проблему, теперь у меня есть: 21.11.2022 | {TestJob1, 1500} - 21.11.2022 | {TestJob2, 1100} .... Так что я все еще хочу их, поэтому я вижу только одну дату
Вы можете агрегировать это в два этапа: сначала вы создаете временную таблицу, которая подсчитывает каждый Job name
по Start time
, а затем вы агрегируете свои массивы с помощью ARRAY_AGG().
-- Creation of a temporary table
WITH agg_by_date_and_job_name AS (
SELECT
"Start time" AS "Date",
ARRAY ["Job name", count(*)::text] AS "Job"
FROM
amdw
GROUP BY
"Date",
"Job name"
)
SELECT
"Date",
ARRAY_AGG("Job") AS "Jobs"
FROM
agg_by_date_and_job_name
GROUP BY
"Date"
ORDER BY
"Date" DESC;
Почему подзапрос? Мне кажется
array["Job name", count(*)::varchar]
будет делать то, что вы хотите.