Блог

Главная - Вопросы по программированию - bigquery left join по ближайшему предыдущему значению

bigquery left join по ближайшему предыдущему значению

#sql #join #google-bigquery #left-join

# #sql #Присоединиться #google-bigquery #левое соединение

Вопрос:

У меня есть две таблицы, как показано ниже:

ТАБЛИЦА_1:

 timestamp                id
2020-11-24 01:05:00 UTC  AA
2020-11-24 01:07:00 UTC  AA
2020-11-24 01:07:00 UTC  BB

ТАБЛИЦА_2:

 timestamp                id   covered
2020-11-24 01:04:00 UTC  AA   true
2020-11-24 01:06:00 UTC  AA   false

Я хочу добавить столбец в table_1, чтобы показать закрытое значение из table_2. Хотя временные метки не совпадают, я хочу получить ближайшую предыдущую временную метку, записанную в таблице_2.
Например, AA был закрыт в 1: 04 и раскрыт в 1: 06, поэтому таблица результатов выглядит следующим образом:

 timestamp                id   covered
2020-11-24 01:05:00 UTC  AA   true
2020-11-24 01:07:00 UTC  AA   false
2020-11-24 01:07:00 UTC  BB   null

Не могли бы вы помочь мне завершить этот запрос:

 SELECT TABLE_1.timestamp, TABLE_1.id, TABLE_2.covered 
FROM TABLE_1 
LEFT JOIN 
TABLE_2
ON TABLE_1.id = TABLE_2.id
AND ?

Ответ №1:

BigQuery не в восторге от неравновесных соединений. Таким образом, альтернативой является использование union all и некоторая хитрость:

 select t12.*
from (select t12.*,
             last_value(covered ignore nulls) over (partition by id order by timestamp) as imputed_covered
      from ((select timestamp, id, null as covered
             from table_1
            ) union all
            (select timestamp, id, covered
             from table_2
            )
           ) t12
     ) t12
where covered is null;

Это объединение двух таблиц. Затем last_value() извлекается самое последнее covered значение, из которого table_1 будут получены строки table_2 .

Финал where просто фильтрует результирующий набор, чтобы иметь только table_1 строки.

Метки: