44 lines
1.8 KiB
SQL
44 lines
1.8 KiB
SQL
with
|
|
'create_role' as start_event,
|
|
('create_role', 'pvp_end') as evnet_all,
|
|
'2021-06-30 00:00:00' as start_data,
|
|
'2021-07-06 23:59:59' as end_data
|
|
select event_chain,
|
|
count() as values
|
|
from (with
|
|
toUInt32(minIf(`#event_time`, `#event_name` = start_event)) AS start_event_ts,
|
|
arraySort(
|
|
x ->
|
|
x.1,
|
|
arrayFilter(
|
|
x -> x.1 >= start_event_ts,
|
|
groupArray((toUInt32(`#event_time`), `#event_name`))
|
|
)
|
|
) AS sorted_events,
|
|
arrayEnumerate(sorted_events) AS event_idxs,
|
|
arrayFilter(
|
|
(x, y, z) -> z.1 >= start_event_ts and (z.2 = start_event OR y > 1800),
|
|
event_idxs,
|
|
arrayDifference(sorted_events.1),
|
|
sorted_events
|
|
) AS gap_idxs,
|
|
arrayMap(x -> x, gap_idxs) AS gap_idxs_,
|
|
arrayMap(x -> if(has(gap_idxs_, x), 1, 0), event_idxs) AS gap_masks,
|
|
arraySplit((x, y) -> y, sorted_events, gap_masks) AS split_events
|
|
select `#account_id`,
|
|
arrayJoin(split_events) AS event_chain_,
|
|
arrayMap(x ->
|
|
x.2, event_chain_) AS event_chain,
|
|
has(event_chain, start_event) AS has_midway_hit
|
|
from (select `#event_time`, `#event_name`, `#account_id`
|
|
from shjy.event
|
|
where addHours(`#event_time`, 8) >= start_data
|
|
and addHours(`#event_time`, 8) <= end_data
|
|
and `#event_name` in evnet_all)
|
|
group by `#account_id`
|
|
HAVING has_midway_hit = 1
|
|
)
|
|
where arrayElement(event_chain, 1) = start_event
|
|
GROUP BY event_chain
|
|
ORDER BY values desc
|