Плоские и многоуровневые вложенные данные
В BQ я использовал ARRAY_AGG(STRUCT(... для реструктуризации некоторых плоских данных, но хотел пойти еще дальше: создать еще один массив записей внутри массива записей.
Хотя STRUCT не существует в PostgreSQL, мне интересно, как с этим справиться и там.
Учитывая плоские данные:
WITH a AS (
SELECT 'ABC' company, 'adress1' address, 'name1' name, 'email1' email, 'work' ph_type, '+123' ph_nr
UNION ALL
SELECT 'ABC' company, 'adress1' address, 'name1' name, 'email1' email, 'cell' ph_type, '+987'
UNION ALL
SELECT 'DEF' company, 'adress2' address, 'name2' name, 'email2' email, 'work' ph_type, '+127'
UNION ALL
SELECT 'DEF' company, 'adress2' address, 'name2' name, 'email2' email, 'cell' ph_type, '+988'
UNION ALL
SELECT 'XYZ' company, 'adress3' address, 'name3' name, 'email3' email, 'work' ph_type, '+456'
)
Я могу вот так воткнуть contact
SELECT company, address, ARRAY_AGG(STRUCT(name, email, ph_type, ph_nr)) contact
FROM a
GROUP BY company, address
ORDER BY 1
но как я могу вложить, в том же операторе выбора, phones (массив записей в contact)?
Представление JSON будет выглядеть так - для первого контакта:
[
{
"company": "ABC",
"address": "adress1",
"contact": [
{
"name": "name1",
"email": "email1",
"phone": [
{
"ph_type": "work",
"ph_nr": "+123"
},
{
"ph_type": "cell",
"ph_nr": "+987"
}
},
...
Вероятно, это можно сделать с помощью предложения WITH или подзапроса для последовательной обработки агрегатов, но не уверены, что это будет работать хорошо (данные читаются дважды?).
У меня 600 миллионов записей, которые нужно анализировать ежедневно, поэтому я думаю о наиболее эффективном способе.
РЕДАКТИРОВАТЬ: исправленное определение имени
Ответы 1
Ответ на ваш вопрос - два уровня агрегирования.
Однако меня смущает сам вопрос, потому что запрос использует name, но это не определено в данных.
Вот пример того, что нужно делать:
SELECT company, address, ARRAY_AGG(STRUCT(email, phones)) as contact
FROM (SELECT company, name, address, email, ARRAY_AGG(STRUCT(ph_type, ph_nr)) as phones
FROM a
GROUP BY company, name, address, email
) a
GROUP BY company, address
ORDER BY 1
метрики производительности: я использовал частичный набор данных, который у меня был - 49 миллионов записей (5 ГБ) - и потребовалось 294 секунды для обработки при наличии ORDER BY как на ARRAY_AGG, так и на 104 без них. Заказ на все больше для удобства, так что пропущу. Тем не менее, все еще надеемся сократить время запроса.
@YannickEinsweiler. . . Два уровня агрегирования на большом столе будут дорогими.
Моя плохая - вы правы, я поправил запрос. Мне было интересно, является ли подзапрос единственным выходом. Беспокойство вызывает производительность на больших наборах данных.