Есть ли способ выполнить ЛЕВОЕ СОЕДИНЕНИЕ ПО Боковой стороне с BigQuery?

Учитывая несколько строк с повторяющимися name и разными timestamp, я хотел бы выбрать строку с самым новым timestamp, если дубликат name возникает, скажем, в течение 45 минут после первого timestamp.

Вот что работало в PostgreSQL:

SELECT i.ts AS base_timestamp, j.ts AS newer_timestamp, i.name
FROM tbl i
LEFT JOIN LATERAL
(SELECT j.ts
 FROM tbl j
 WHERE i.name = j.name
 AND j.ts > i.ts
 AND j.ts < (i.ts + INTERVAL '45 minutes')
) j ON TRUE
WHERE j.ts is NULL

Отличное объяснение LATERAL здесь: https://heap.io/blog/engineering/postgresqls-powerful-new-join-type-lateral

LATERAL join похож на цикл SQL foreach, в котором PostgreSQL будет перебирать каждую строку в наборе результатов и оценивать подзапрос, используя эту строку в качестве параметра.

Это похоже на коррелированный подзапрос, но в соединении.

Затем я просто беру только те строки, для которых нет более новой отметки времени (WHERE j.ts is NULL).

Как это сделать в BigQuery?

EDIT: я создал пример группировки PostgreSQL в SQLFiddle как указано в комментариях.

Вход:

  ('Duplication Example','2019-06-22 19:10:25'),
  ('Duplication Example','2019-06-22 23:58:31'), 
  ('Duplication Example','2019-06-23 00:08:00')

Вывод (в средней строке удалена отметка 23:58:31):

base_timestamp          newer_timestamp name
2019-06-22T19:10:25Z    (null)          Duplication Example
2019-06-23T00:08:00Z    (null)          Duplication Example

google-bigquery lateral-join
person Martin Burch    schedule 05.12.2019    source источник
comment
Здесь есть что-то умное в использовании секунд в качестве числового предложения RANGE medium.com/@ubethke/   -  person Martin Burch    schedule 05.12.2019
comment
пожалуйста, поясните - вас больше интересует решение / рассмотрение того, что находится в заголовке вашего сообщения (которое является довольно общим и концептуальным) или что находится в первом предложении в теле вопроса (более практично и, скорее всего, ваш реальный вариант использования быть адресованным)? это две очень разные вещи, поэтому, пожалуйста, поясните!   -  person Mikhail Berlyant    schedule 05.12.2019
comment
Привет, @MikhailBerlyant, спасибо, что посмотрели на мой вопрос. Меня больше интересует решение моей конкретной проблемы, как указано в первом предложении тела вопроса. Если ответ начинается с «Нет», BigQuery не имеет LATERAL, вам нужно решить вашу проблему, используя ... это было бы наиболее полезно.   -  person Martin Burch    schedule 06.12.2019
comment
это то, что я ожидал - вернусь к вам с ответом, как только у вас будет время: o)   -  person Mikhail Berlyant    schedule 06.12.2019
comment
пожалуйста, добавьте упрощенный пример входных данных и ожидаемого результата. Я попытался прочитать / проанализировать ваш случай и увидеть, что все еще не ясно, какую именно логику вы хотите применить для получения результата. хороший пример ввода / вывода обязательно поможет. всего одного имени будет достаточно, но некоторое репрезентативное количество ts ​​(временных меток), пожалуйста   -  person Mikhail Berlyant    schedule 06.12.2019
comment
Спасибо за руководство, @MikhailBerlyant ... Теперь я обновил вопрос небольшим примером. Как вы думаете, нужно больше строк?   -  person Martin Burch    schedule 09.12.2019
comment
на мой взгляд - вы либо объясняете желаемую логику во всех деталях , либо представляете действительно хороший пример с гораздо большим, чем просто двумя-тремя строками, чтобы мы могли собрать логику. пример, который у вас сейчас есть, совсем не помогает: o (   -  person Mikhail Berlyant    schedule 13.12.2019

Ответы (2)


arrow_upward
1
arrow_downward

Ваш случай выглядит как задача для оконных функций. Но поскольку вам кажется, что боковые объединения интересуют больше, чем решение проблемы, которую вы представили: в BigQuery есть afaik только неявная версия боковых объединений: при объединении с невложенными массивами.

Это демонстрирует идею:

WITH t AS (
  SELECT 'a' as id, [2,3] as arr 
  UNION ALL SELECT 'b', [56, 7]
)

SELECT * EXCEPT(arr) 
FROM t LEFT JOIN UNNEST(arr)
person Martin Weitzmann    schedule 05.12.2019
comment
Привет, парень, Мартин! Я согласен, это действительно похоже на задачу, которую можно решить с помощью оконной функции. Примерно в то же время, когда вы отправили свой ответ, я добавил комментарий со ссылкой на эту статью: medium.com/@ubethke/ ... но я до сих пор не понимаю, как именно это сделать. Ваш ответ больше похож на замену CROSS JOIN: WITH t AS ( SELECT 'a' as id, [2,3] AS arr UNION ALL SELECT 'b', [56, 7] AS arr ) SELECT id, arr FROM t CROSS JOIN t.arr - person Martin Burch; 05.12.2019
comment
Не уверен, что вы имеете в виду, но перекрестное соединение на невложенном массиве также является неявным боковым соединением. - person Martin Weitzmann; 05.12.2019
comment
Для большей ясности: если вы считаете, что для этого требуется оконная функция в BigQuery, покажите запрос с оконной функцией. Я заинтересован в решении проблемы, указанной в вопросе, и я не понимаю, как это сделать с помощью предоставленного вами примера запроса. - person Martin Burch; 05.12.2019
comment
Как упоминалось в сообщении, я не решил вашу проблему, а скорее объяснил боковые соединения в BigQuery. Все хорошо. - person Martin Weitzmann; 05.12.2019

arrow_upward
1
arrow_downward

Это можно заархивировать с помощью функции WINDOW.

SELECT
  name,
  MAX(timestamp) AS timestamp_new
FROM
(
  SELECT 
    i.name,
    COUNT(*) OVER (PARTITION BY i.name ORDER BY i.ts RANGE BETWEEN 45 * 60 * 1000 PRECEDING AND CURRENT ROW) as 45_window_count,
    i.ts AS timestamp
  FROM 
    tbl i
)
WHERE 45_window_count > 1
GROUP BY user
person DinushaNT    schedule 19.12.2019
comment
Похоже, потребуется корректировка числового диапазона, поскольку 45 000 000 миллисекунд больше 12 часов. Правильный расчет скорее 45 * 60 * 1000, разве нет? 45 минут * 60 секунд в минуту * 1000 миллисекунд в секунду. - person Martin Burch; 20.12.2019
comment
Правильный. Обновил ответ. - person DinushaNT; 20.12.2019