Как лучше всего создавать массивы объединенных таблиц в Big Query?

Я пытаюсь выполнить преобразование данных внутри Big Query с помощью SQL.

Скажем, у меня есть три таблицы:

Клиент - данные о клиенте, такие как возраст и т. Д. Подписки - данные о том, какие подписки есть у пользователя. Взаимодействие - данные о том, как клиенты взаимодействуют с цифровыми продуктами.

Я бы хотел собрать это внутри одной таблицы, используя вложенные поля.

Я могу присоединиться ко всем этим таблицам, но я хотел бы объединить их в массивы.

Итак, вместо трех таблиц я получаю следующее:

id:123,
name:David,
age:30,
subscritions: [{
  name:sub1
  price:10
},
{
  name:sub2
  price:20
}],
engagment: [{
  event:visited_product_x
  time:2020-06-10
},
{
  event:visited_product_y
  time:2020-06-10
}]

Конечно, я использовал array_agg в SELECT. И это прекрасно работает при добавлении только одной таблицы. Однако при добавлении еще одной я получаю повторяющиеся строки, которые мне не нужны. Итак, я думаю, мне следует использовать array_agg не в SELECT, а где-то еще.

Но как лучше всего решить эту проблему?


arrays sql google-bigquery array-agg
person David Beck    schedule 10.06.2020    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете использовать подзапросы для создания полей. Что-то вроде этого:

select c.*,
       (select array_agg(s)
        from substriptions s
        where s.user_id = c.user_id
       ) as subscriptions,
       (select array_agg(e)
        from engagements e
        where e.user_id = c.user_id
       ) as engagements
from customers c
person Gordon Linoff    schedule 10.06.2020
comment
Спасибо! Такой простой и элегантный. - person David Beck; 10.06.2020