Как составить SQL запрос по звонкам?

Question

[Алексей]

Добрый день!
Есть одна таблица в Postgresql - список звонков из АТС-ки

Вот кусок таблицы с полями:


Нужно эти строки сгруппировать по полю pbx_call_id (так как этот id определяет один звонок) и вывести все поля из исходной таблицы, но так, чтобы значениях в них были взяты из записи группы с минимальной датой (callstart). Другими словами нужно вывести только записи с красной меткой.

Если подскажете как это сделать, то уже моя задача будет решена. Но было бы еще лучше, если бы намекнули, каким образом можно было бы реализовать дополнительное поле (например sips), в котором бы аккумулировались в виде JSON-массива все записи группы с полями disposition и sip, то есть для первой записи добавилось бы поле sips с содержимым:

[{"disposition": "call failed", "sip": "100"}, 
{"disposition": "no answer", "sip": "101"}, 
{"disposition": "call failed", "sip": "102"}]

Буду благодарен любым советам и подсказкам.

Answer 1

[galaxy]

Ну что-то типа:

select distinct pbx_call_id,
       first_value(clid) over (partition by pbx_call_id order by callstart) clid,
       ... -- остальные поля
  from calls;

По второму вопросу, так можно собрать то, что нужно в колонку

select pbx_call_id,
       jsonb_agg(json_build_object('disposition', disposition, 'sip', sip)) as sips
  from calls
 group by pbx_call_id

Comments

[Алексей]

Работает, это то, что надо! )

Answer 2

[noremorse_ru]

То ли я не понял вопроса, то ли тут нужен элементарный WHERE callstart < ***, а на какой черт отдельное json поле делать вообще непонятно, если оно дублирует данные из таблицы. Конвертировать в json нужно не на стороне БД, по крайней мере, в вашем случае когда данные дублируются, а не агрегируются.

Comments

[Алексей]

Пытался сформулировать вопрос как мог, видимо, не очень получилось. По этому на скрине я красными метками отметил поля, которые должны вернуться в результате запроса. И данные в JSON не дублируются, а наоборот дополняются из отсеченных записей. Насколько я понимаю это group by по pbx_call_id но с применением подфункции, в которой что-то типа WHERE zz.pbx_call_id = z.pbx_call_id order by zz.callstart ASC но не могу сообразить как это правильно скрутить вместе

Answer 3

[d-stream]

Напрашивается оконная функция:
группируем по pbx_call_id
сортируем по callstart
и берем только первые (row_number()=1)

Не совсем уверен, что прокатит row_number() в where, но канва вот примерно такая:

SELECT 
id OVER(PARTITION BY pbx_call_id ORDER BY callstart ASC) 
FROM table 
Where ROW_NUMBER()=1

то бишь получаем id (я же правильно понимаю что это pk?) первых записей для каждого звонка ну и дальше их или к ним join'им саму таблицу

Comments

[Алексей]

Спасибо за пример! Осталось только понять, почему OVER у меня вызывает синтаксическую ошибку )

[d-stream]

гы... очепятка)

SELECT 
id, ROW_NUMBER ()  OVER(PARTITION BY pbx_call_id ORDER BY callstart ASC) 
FROM table 
Where ROW_NUMBER()=1