Нужна помощь в объединении 2 таблиц и уникальном объединении их с помощью BigQuery SQL
У меня есть 2 таблицы с разной степенью детализации в наборе данных BigQuery. Мне нужно объединить эти два и свернуть их с помощью BigQuery SQL таким образом, чтобы значение в одном из столбцов 2-й таблицы стало столбцами в итоговой таблице.
Таблица 1 - tb1 выглядит так
user_id event_date
A 2019-02-01
B 2019-02-10
C 2019-01-15
Таблица 2 - tb2 выглядит так
user_id activity_id activity_date
A 1 2019-01-01
A 1 2019-02-05
A 2 2019-01-15
B 2 2019-02-02
B 3 2019-02-01
C 1 2019-01-02
Я пытаюсь написать SQL-запрос для создания окончательной таблицы, которая сообщает нам количество записей для каждого действия для user_id, где activity_date находится в пределах (event_date - 90 дней) для этого user_id, т.е. activity_date находится в 90 днях, предшествующих до события_даты. Итак, в этом случае вывод будет выглядеть так
user_id event_date act_1 act_2 act_3
A 2019-02-01 1 1 0
B 2019-02-10 0 2 1
C 2019-01-15 1 0 0
Столбец act_1 соответствует activity_id=1 и так далее.
Есть некоторые дополнительные сложности, такие как: -
- Количество отдельных идентификаторов действий в таблице 2 может меняться со временем. Итак, я не знаю заранее, сколько столбцов будет создано в выходной таблице.
- Я не могу сделать это в питоне, но это нужно сделать в BQ. Это связано с тем, что фактическая таблица 2 очень велика (42 ТБ с 31 миллиардом строк), и ее перенос из BQ в другой продукт GCS для запуска python может быть громоздким.
Любая помощь приветствуется.
Я создал таблицу со списком всех идентификаторов активности, а затем написал приведенный ниже код, чтобы использовать их для создания списка имен столбцов. ``` SELECT concat('activity_',regexp_extract(json,'{activity_id:([^\ 3;]*)}')) FROM ( SELECT REPLACE(TO_JSON_STRING(t), '"','') AS json FROM (выберите activity_id из activity_list) as t) ``` Но после этого я застрял. Действительно не уверен, что попробовать
Ответы 1
Ниже приведен стандартный SQL BigQuery и просто для демонстрации вашего подхода к сводным данным.
Если бы вы знали заранее, сколько у вас различных идентификаторов activity_id, и если их число невелико, например, три, как в вашем примере, вы бы сделали так же просто, как показано ниже.
#standardSQL
SELECT
user_id,
event_date,
COUNTIF(activity_id = 1) act_1,
COUNTIF(activity_id = 2) act_2,
COUNTIF(activity_id = 3) act_3
FROM `project.dataset.table1` t1
JOIN `project.dataset.table2` t2
USING(user_id)
GROUP BY user_id, event_date
ORDER BY user_id, event_date
если применить к выборочным данным как в вашем вопросе - результат будет
Row user_id event_date act_1 act_2 act_3
1 A 2019-02-01 2 1 0
2 B 2019-02-10 0 1 1
3 C 2019-01-15 1 0 0
Но как вы упомянули
The number of distinct activity_ids in Table 2 can change over time. So, I don't know before hand how many columns will be created in the output table
Итак, вам нужно сгенерировать вышеуказанный запрос динамически - ниже приведен пример такого
#standardSQL
WITH activities AS (
SELECT DISTINCT activity_id
FROM `project.dataset.table2`
), generate_query AS (
SELECT CONCAT(
'SELECT user_id, event_date',
STRING_AGG(CONCAT(',COUNTIF(activity_id = ', CAST(activity_id AS STRING), ') act_', CAST(activity_id AS STRING)), ''),
' FROM `project.dataset.table1` t1 JOIN `project.dataset.table2` t2 USING(user_id) GROUP BY user_id, event_date ORDER BY user_id, event_date'
) AS query
FROM activities
)
SELECT query FROM generate_query
Опять же, если применить к вашему образцу данных - результат будет
SELECT user_id, event_date,COUNTIF(activity_id = 1) act_1,COUNTIF(activity_id = 2) act_2,COUNTIF(activity_id = 3) act_3 FROM `project.dataset.table1` t1 JOIN `project.dataset.table2` t2 USING(user_id) GROUP BY user_id, event_date ORDER BY user_id, event_date
если посмотреть поближе к приведенному выше результату - вы можете увидеть - это именно тот запрос, который мы изначально написали вручную - но теперь он был сгенерирован для нас - и независимо от того, сколько у вас различных Activity_id (очевидно, ограничения на количество столбцов все еще применяются) - это будет продуктом необходимый запрос
Итак, теперь вам просто нужно скопировать текст запроса из приведенного выше результата и просто запустить его, что даст желаемый результат.
Row user_id event_date act_1 act_2 act_3
1 A 2019-02-01 2 1 0
2 B 2019-02-10 0 1 1
3 C 2019-01-15 1 0 0
Как видите, это двухэтапный процесс, но вы можете написать его с помощью клиента по вашему выбору.
Примечание: я сосредоточился на сути вопроса и вообще не обращался к материалам, связанным с 90 днями - я чувствую, что это была второстепенная деталь в вашем вопросе.
Удивительно! это работает. Вы правы, что 90-дневная детализация на самом деле не является частью задачи, с которой я сталкиваюсь, и я могу включить ее, используя предложения where. Я выясню лучший способ выполнения по расписанию, но это решает мою основную проблему. Спасибо!
что ты уже испробовал?