#sql #google-bigquery
#sql #google-bigquery
Вопрос:
У меня есть два разных источника данных с событиями (скажем, серверные и внешние события). О событии может сообщать только первый, только второй или оба источника. Я пытаюсь найти подход к объединению этих двух источников в один, где все события будут сообщаться один раз. Кроме того, я не хочу терять события.
У меня нет идентификаторов, которые я мог бы использовать для объединения этих источников. Вместо этого у меня есть только тип события, дата-время события и временное окно, которое я мог бы использовать для присоединения к этим событиям. Сложная часть начинается, когда у меня есть несколько событий с обеих сторон, захваченных одним и тем же окном — скажем, три события из источника ‘A’ и два события из источника ‘B’. Я не знаю, как эти события следует комбинировать друг с другом, но это не проблема — я хочу, чтобы они были объединены попарно без повторений, когда одно событие из источника «A» соответствует только одному событию из источника «B», и наоборот. И желательно (но не обязательно) сначала объединить ближайшие события.
И я делаю это в BigQuery, поэтому я не могу использовать рекурсивные запросы.
Вот пример (обратите внимание, что я не знаю фактического значения true_parent):
with raw_data as (
SELECT 1 source_number, 1 dt, '1.1' name, 1 event_type, null true_parent
union all select 1, 60, '1.2', 1, null
union all select 1, 69, '1.3', 1, null
union all select 2, 0, '2.1', 1, '1.1'
union all select 2, 0, '2.2', 1, null
union all select 2, 2, '2.3', 1, '1.2'
union all select 2, 2, '2.4', 1, null
union all select 2, 69, '2.5', 1, '1.3'
union all select 1, 60, '1.1', 2, null
union all select 1, 60, '1.2', 2, null
union all select 1, 69, '1.3', 2, null
union all select 2, 0, '2.1', 2, '1.1'
union all select 2, 0, '2.2', 2, '1.2'
union all select 1, 0, '1.1', 3, null
union all select 1, 1, '1.2', 3, null
union all select 1, 2, '1.3', 3, null
union all select 2, 101, '2.1', 3, '1.3'
union all select 2, 0, '2.2', 3, '1.1'
union all select 2, 3, '2.3', 3, '1.2'
union all select 1, 1, '1.1', 4, null
union all select 1, 100, '1.2', 4, null
union all select 1, 200, '1.3', 4, null
union all select 2, 5, '2.1', 4, '1.1'
union all select 2, 15, '2.2', 4, '1.2'
union all select 2, 102, '2.3', 4, '1.3'
)
, windows as (
select 1 source_number, 20 time_window
union all select 2, 80
)
, dat as (
select
*
from raw_data
left join windows using(source_number)
)
, parent_selection as (
select
c.event_type,
c.name,
c.source_number,
c.dt,
p.name parent,
c.true_parent
from dat c
left join dat p
on c.event_type = p.event_type
and c.source_number > p.source_number
and ABS(c.dt - p.dt) <= c.time_window p.time_window
)
select distinct
*
except (true_parent)
replace(case when true_parent is null then name else parent end as parent)
from parent_selection
where true_parent = parent or true_parent is null
order by event_type, parent, name
Я использовал эту дочерне-родительскую абстракцию, потому что ее удобно группировать по родительским элементам в следующих шагах, но я также буду признателен за любую другую абстракцию, которую можно использовать для создания этих попарных соединений.
Я просто хочу, чтобы алгоритм заменил последнюю часть запроса, поскольку я не знаю фактического true_parent значения.
Вывод:
event_type name source_number dt parent
1 1.1 1 1 1.1
1 2.1 2 0 1.1
1 1.2 1 60 1.2
1 2.3 2 2 1.2
1 1.3 1 69 1.3
1 2.5 2 69 1.3
1 2.2 2 0 2.2
1 2.4 2 2 2.4
2 1.1 1 60 1.1
2 2.1 2 0 1.1
2 1.2 1 60 1.2
2 2.2 2 0 1.2
2 1.3 1 69 1.3
3 1.1 1 0 1.1
3 2.2 2 0 1.1
3 1.2 1 1 1.2
3 2.3 2 3 1.2
3 1.3 1 2 1.3
3 2.1 2 101 1.3
4 1.1 1 1 1.1
4 2.1 2 5 1.1
4 1.2 1 100 1.2
4 2.2 2 15 1.2
4 1.3 1 200 1.3
4 2.3 2 102 1.3
Объяснение:
В event_type 1 1.1 должно быть объединено с 2.1 or 2.2 , и 1.2 — с 2.3 or 2.4 , и 2.5 — с 2.3 в соответствии с ближайшим значением dt. Мне все равно, будет ли 1.1 это сочетаться с 2.1 или 2.2 , но если один из них был добавлен к паре, второй не должен.
В event_type 2 1.1 и 1.2 должен быть объединен с 2.1 or 2.2 , порядок не имеет значения. 2.3 не будет сочетаться ни с каким другим событием.
В event_type 3 2.1 можно комбинировать только с 1.2 или 1.3 , но не с 1.1 , потому 1.1 что не соответствует его временному окну. Итак, 2.1 сочетается с 1.3 тем, что ближе к тому времени 1.2 . Оставшийся 2.2 и 2.3 может быть объединен с 1.1 и 1.2 , но не с 1.3 , потому что он уже был занят 2.1 .
Ответ №1:
Я наконец-то нашел удовлетворительное решение. Идея состоит в том, чтобы заархивировать два источника, упорядоченных по дате и времени, но это все равно должно соответствовать ограничению временного окна. Поэтому я предпринял эти шаги для достижения всех поставленных целей:
- Вычисление номеров сеансов для каждого источника
- Сдвиньте сеанс каждого родителя, если родители не найдены
- Соответствующим образом сдвиньте все последующие сеансы
- Настройте дочерние сеансы на родительские
- Группируйте каждый сеанс на родительском
По-прежнему происходит сбой event_type = 6 , но это можно исправить с помощью настройки time_window.
with raw_data as (
SELECT 1 source_number, 1 dt, '1.1' name, 1 event_type
union all select 2, 0, '2.1', 1
union all select 2, 1, '2.2', 1
union all select 2, 69, '2.5', 1
union all select 3, 60, '3.1', 1
union all select 3, 60, '3.2', 1
union all select 3, 69, '3.3', 1
union all select 1, 1, '1.1', 2
union all select 1, 100, '1.2', 2
union all select 2, 100, '2.1', 2
union all select 2, 200, '2.2', 2
union all select 2, 202, '2.3', 2
union all select 3, 1, '3.1', 2
union all select 3, 10, '3.2', 2
union all select 3, 100, '3.3', 2
union all select 4, 5, '4.1', 2
union all select 4, 15, '4.2', 2
union all select 4, 200, '4.3', 2
union all select 5, 1, '5.1', 2
union all select 5, 5, '5.2', 2
union all select 5, 15, '5.3', 2
union all select 5, 99, '5.4', 2
union all select 5, 100, '5.5', 2
union all select 5, 101, '5.6', 2
union all select 6, 50, '6.1', 2
union all select 6, 140, '6.2', 2
union all select 6, 200, '6.3', 2
union all select 6, 290, '6.4', 2
union all select 7, 50, '7.1', 2
union all select 7, 200, '7.2', 2
union all select 7, 210, '7.3', 2
union all select 7, 1000, '7.4', 2
union all select 1, 1, '1.1', 6
union all select 2, 55, '2.1', 6
union all select 3, 85, '3.1', 6
union all select 4, 255, '4.1', 6
union all select 1, 1, '1.1', 7
union all select 1, 1000, '1.2', 7
union all select 2, 1001, '2.1', 7
union all select 3, 1020, '3.1', 7
union all select 4, 1030, '4.1', 7
)
, windows as (
select 1 source_number, 0 time_window
union all select 2, 60
union all select 3, 60
union all select 4, 120
union all select 5, 120
union all select 6, 150
union all select 7, 500
)
, dat as (
select
*
from raw_data
left join windows using(source_number)
)
, sessions as (
select
*,
row_number() over(partition by event_type, source_number order by dt) session
from dat
)
, calc_parent_shift as (
select
a.*,
case
when count(b.session) = 0
then greatest(countif(count(b.session) = 0) over (w_session) - 1, 0)
else 0
end as parent_shift
from sessions a
left join sessions b
on a.event_type = b.event_type
and a.source_number > b.source_number
and a.session <= b.session
and ABS(a.dt - b.dt) <= a.time_window b.time_window
group by 1, 2, 3, 4, 5, 6
window w_session as (
partition by a.event_type, a.session order by a.dt
)
)
, shift_parent_session as (
select
* except(session),
session max(parent_shift) over (w_shift) as session,
session as old_session
from calc_parent_shift
window w_shift as (
partition by event_type, source_number
order by session
)
)
, shift_child_session as (
select
a.* except(session),
ifnull(array_agg(b.session order by b.source_number, b.session)[offset(0)] - a.old_session, 0) as child_shift,
a.session greatest (
max(ifnull(array_agg(b.session order by b.source_number, b.session)[offset(0)] - a.old_session, 0)) over (w)
- max(a.parent_shift) over (w)
, 0
) as session
from shift_parent_session a
left join shift_parent_session b
on a.event_type = b.event_type
and a.source_number > b.source_number
and a.session <= b.session
and ABS(a.dt - b.dt) <= a.time_window b.time_window
group by 1, 2, 3, 4, 5, 6, 7, a.session
window w as (
partition by a.event_type, a.source_number
order by a.session
)
)
, session_groups as (
select
a.* except (parent_shift, child_shift, old_session),
min(b.source_number) parent_source_number
from shift_child_session a
left join shift_child_session b
on a.event_type = b.event_type
and a.session = b.session
and ABS(a.dt - b.dt) <= a.time_window b.time_window
and a.source_number >= b.source_number
group by 1, 2, 3, 4, 5, 6
)
, result as (
select
event_type,
session,
parent_source_number,
array_agg(source_number) source_number,
array_agg(dt) dt,
array_agg(name) name
from session_groups
group by 1, 2, 3
order by 1, 2, 3
)
select * from result
# where event_type = 6
order by event_type, session