BIGQUERY: замените «нулевой» результат из одной таблицы идентификатором пользователя из другой таблицы.

надежные эксперты BQ.

Предыстория: у меня есть пользователи, читающие статьи на веб-сайте (таблица пользователей A), и пользователи, переходящие к статьям из электронной почты (таблица пользователей B), и у меня есть таблицы просмотра BQ для каждого набора пользователей. В таблице пользователей A отсутствует некоторый user_id пользователей, которые перешли по электронной почте. См. таблицу пользователей A ниже.

*User Table A* - Website

id  | user_id    | article id  | viewed_at
------------------------------------------------------------------
1   | 1          | 1000        | 2019-01-25 01:04:00 UTC
2   | 2          | 1001        | 2019-01-25 01:03:00 UTC
3   | 3          | 1002        | 2019-01-25 01:03:00 UTC
4   | null       | 1001        | 2019-01-25 01:04:00 UTC
5   | null       | 1000        | 2019-01-24 20:49:00 UTC
6   | null       | 1003        | 2019-01-24 20:47:00 UTC


*User Table B* - Email

id  | user_id    | article id  | clicked_at
------------------------------------------------------------------
1   | 1          | 1000        | 2019-01-25 01:04:00 UTC
2   | 1          | 1000        | 2019-01-24 20:49:00 UTC
3   | 6          | 1003        | 2019-01-24 20:47:00 UTC

  • Я хочу создать одно представление/таблицу, которая заменяет нулевой результат user_id в пользовательской таблице A на user_id из пользовательской таблицы B, ЕСЛИ, значения views_at и clicked_at совпадают И > article_id из пользовательской таблицы A и пользовательской таблицы B также совпадают.

  • Я также хочу сохранить нулевой результат user_id, если соответствующие view_at/clicked_at и article_id не найдены в пользовательской таблице B.

*Desired Result Table*

id  | user_id    | article id  | viewed_at
------------------------------------------------------------------
1   | 1          | 1000        | 2019-01-25 01:04:00 UTC
2   | 2          | 1001        | 2019-01-25 01:03:00 UTC
3   | 3          | 1002        | 2019-01-25 01:03:00 UTC
4   | null       | 1001        | 2019-01-25 01:04:00 UTC
5   | 1          | 1000        | 2019-01-24 20:49:00 UTC
6   | 6          | 1003        | 2019-01-24 20:47:00 UTC

Я надеюсь в этом есть смысл.

Пожалуйста помоги. Это делало мою голову в течение месяцев.


sql google-bigquery timestamp bigquery-standard-sql
person Livewire    schedule 09.06.2019    source источник
comment
@mikhail, ты был так полезен в прошлом, у тебя есть какие-нибудь мысли по этому поводу?   -  person Livewire    schedule 10.06.2019
comment
Спасибо, @gordonlinoff, вы так полезны. Хотя у меня есть один вопрос. Я прогнал ваш запрос к одной статье, общее количество прочтений (на веб-сайте) которой составляет 6251. Я бы предположил, что это общее количество прочтений не должно меняться, если я заменяю только «нулевые» значения соответствующим user_id (если он существует). Когда я запускаю оба ваших запроса (которые дают тот же результат, что и запрос Микаила), я получаю общий результат подсчета количества прочитанных статей, равный 21 109. Вы знаете, почему это может происходить?   -  person Livewire    schedule 11.06.2019

Ответы (2)


arrow_upward
2
arrow_downward

Ниже приведен стандартный SQL BigQuery.

#standardSQL
SELECT 
  a.id,
  IFNULL(a.user_id, b.user_id) user_id,
  a.article_id,
  viewed_at
FROM `project.dataset.website` a
LEFT JOIN `project.dataset.email` b
ON a.user_id IS NULL
AND a.article_id = b.article_id
AND viewed_at = clicked_at
person Mikhail Berlyant    schedule 10.06.2019
comment
Спасибо, @mikhail, ты такой полезный. Хотя у меня есть один вопрос. Я прогнал ваш запрос к одной статье, общее количество прочтений (на веб-сайте) которой составляет 6251. Я бы предположил, что это общее количество прочтений не должно меняться, если я заменяю только «нулевые» значения соответствующим user_id (если он существует). Когда я запускаю оба ваших запроса (которые дают тот же результат, что и запрос Гордона), я получаю общий результат подсчета количества прочитанных статей, равный 21 109. Вы знаете, почему это может происходить? - person Livewire; 11.06.2019
comment
Я ожидал бы этого, потому что нет никакой логики, которая не позволяет одной и той же строке в таблице A с NULL соответствовать нескольким строкам в таблице B с той же статьей и данными, что увеличивает количество строк в результате. вам нужно придумать бизнес-логику, которая бы учитывала это, и задавать вопросы по почте. ваш текущий вопрос как есть - дан ответ - person Mikhail Berlyant; 11.06.2019
comment
Спасибо @mikhail. Я могу запросить разные user_id, которые действительно помогают с другой моей проблемой, так что вам очень нужна ваша помощь. - person Livewire; 11.06.2019

arrow_upward
0
arrow_downward

Я думаю, вы можете использовать left join:

select w.id,
       coalesce(w.user_id, e.user_id) as user_id,
       w.article_id, w.viewed_at
from website w left join
     email e
     on w.article_id = e.article_id and
        w.viewed_at = e.viewed_at and
        w.user_id is null;

Обратите внимание, что эта логика предполагает, что у вас нет дубликатов в таблице email по отношению к article_id/viewed_at.

person Gordon Linoff    schedule 09.06.2019
comment
@Livewire . . . Вы приняли по существу тот же ответ, опубликованный через 6 часов после этого. - person Gordon Linoff; 11.06.2019
comment
Привет, @gordonlinoff, извини, я новичок в том, как работает выбор ответа. Не могли бы вы вкратце объяснить этикет? Извините за любое оскорбление... не намеренно. - person Livewire; 11.06.2019