Использование формирующихся массивов во время выполнения sql запроса

Question

В продолжение предыдущего вопроса (https://ru.stackoverflow.com/questions/1205926/Агрегация-в-clickhouse/1206119#1206119), пытаюсь найти способ использовать информацию из сформированного массива. Из таблицы:

id    text     pid      date
1   "Текст1"    1   "2020-01-01"
2   "Текст2"    3   "2010-01-01"
3   "Текст1"    2   "2020-10-01"
4   "Текст1"    3   "2015-07-03"
5   "Текст2"    4   "2014-09-07"

С помощью запроса select text, min(date), max(date), array_agg(pid) from table group by text получается следующий результат:

 text        min_date        max_date     array_agg
"Текст1"    "2015-07-03"    "2020-10-01"    {1,2,3}
"Текст2"    "2010-01-01"    "2014-09-07"    {3,4}

Как нужно модифицировать запрос, чтобы из array_agg найти pid, имеющий самую "свежую" дату, получая такую таблицу:

 text        min_date        max_date     array_agg   last_pid
"Текст1"    "2015-07-03"    "2020-10-01"    {1,2,3}       2
"Текст2"    "2010-01-01"    "2014-09-07"    {3,4}         4

last_pid равен 4 во второй строке из-за того, что все еще происходит группировка по text. Собственно вопрос в том, можно ли как-то делать некий внутренний запрос, используя формирующийся массив, учитывая внешнюю группировку по text?

Answer 1

SELECT DISTINCT
       "text", 
       MIN("date") OVER (PARTITION BY "text"), 
       MAX("date") OVER (PARTITION BY "text"), 
       ARRAY_AGG("pid") OVER (PARTITION BY "text"),
       FIRST_VALUE("pid") OVER (PARTITION BY "text" ORDER BY "date" DESC)
FROM "table"

Answer 2

Ваш запрос с одной добавкой и без оконных функции:

select 
    "text", min("date"), max("date"), array_agg(pid),
    (array_agg(pid order by "date" desc))[1] as last_pid,
 from "table"
 group by "text";

БЛОГ НА HUSL

Использование формирующихся массивов во время выполнения sql запроса

Ответы (2 шт):