Надо найти длительность непрерывного нахождения на станцииях

Рейтинг: 1Ответов: 1Опубликовано: 15.08.2023

Всем привет.
Есть таблица: ИД абонента, ИД станции, Дата события.
Надо найти длительность непрерывного нахождения на станциях. Решаю в Oracle, но потом перенесу решение в pySpark.
Рассчитала длительность события,
флаг_lead = 0, если следующее событие на той же станции,
флаг_lag = 0, если предыдущее событие на той же станции. Если значение этих друх флагов перемножить, то, вроде, получаю нужный критерий для группировки строк.

select subs_id,
       base_stat_id,
       subs_act_date,
       coalesce(lead(subs_act_date, 1)over(partition by subs_id order by subs_act_date), sysdate) as end_subs_act_date,
       coalesce(lead(subs_act_date, 1)over(partition by subs_id order by subs_act_date), sysdate)  - subs_act_date as duration,
       case when lead(base_stat_id, 1)over(partition by subs_id order by subs_act_date) = base_stat_id then 0 else 1 end *
       case when lag(base_stat_id, 1)over(partition by subs_id order by subs_act_date) = base_stat_id then 0 else 1 end as bs_flag
from (
select 1 as subs_id, 1 as base_stat_id, to_date('8:40', 'hh24:mi') as subs_act_date from dual
union all select 1,1,to_date('8:55', 'hh24:mi') from dual
union all select 1,1,to_date('9:20', 'hh24:mi') from dual
union all select 1,2,to_date('10:00', 'hh24:mi') from dual
union all select 1,1,to_date('11:15', 'hh24:mi') from dual
union all select 1,2,to_date('12:00', 'hh24:mi') from dual
union all select 1,2,to_date('13:50', 'hh24:mi') from dual
union all select 1,2,to_date('18:50', 'hh24:mi') from dual
union all select 1,1,to_date('18:55', 'hh24:mi') from dual
union all select 1,1,to_date('19:20', 'hh24:mi') from dual
)src_tab

введите сюда описание изображения

Не могу придумать, как отделить данные из блоков 1 и 2. Так как между этими блоками абонент был на других станциях и объединять длительность нахождения в эти периоды нельзя.

sql oracle pyspark
Источник: Stack Overflow на русском

Ответы

▲ 1Принят

Просто группируйте записи по факту, что текущее и предыдущее события были на одной и той же станции:

WITH 

-- исходные данные
src_tab AS (
  select 1 as subs_id, 1 as base_stat_id, to_date('8:40', 'hh24:mi') as subs_act_date from dual
  union all select 1,1,to_date('8:55', 'hh24:mi') from dual
  union all select 1,1,to_date('9:20', 'hh24:mi') from dual 
  union all select 1,2,to_date('10:00', 'hh24:mi') from dual
  union all select 1,1,to_date('11:15', 'hh24:mi') from dual
  union all select 1,2,to_date('12:00', 'hh24:mi') from dual
  union all select 1,2,to_date('13:50', 'hh24:mi') from dual
  union all select 1,2,to_date('18:50', 'hh24:mi') from dual
  union all select 1,1,to_date('18:55', 'hh24:mi') from dual
  union all select 1,1,to_date('19:20', 'hh24:mi') from dual
),

-- сравнение станций текущего и предыдущего событий
cte AS (
SELECT subs_id, base_stat_id, subs_act_date,
       CASE WHEN base_stat_id = LAG(base_stat_id) OVER (PARTITION BY subs_id 
                                                       ORDER BY subs_act_date)
            THEN 0
            ELSE 1 
            END AS station_changed
from src_tab
)

-- подсчёт номера группы
SELECT subs_id, base_stat_id, subs_act_date,
       SUM(station_changed) OVER (PARTITION BY subs_id 
                                  ORDER BY subs_act_date) group_number
FROM cte
SUBS_ID BASE_STAT_ID SUBS_ACT_DATE GROUP_NUMBER
1 1 01-AUG-23 1
1 1 01-AUG-23 1
1 1 01-AUG-23 1
1 2 01-AUG-23 2
1 1 01-AUG-23 3
1 2 01-AUG-23 4
1 2 01-AUG-23 4
1 2 01-AUG-23 4
1 1 01-AUG-23 5
1 1 01-AUG-23 5

fiddle

Сразу видно, что несколько событий подряд на одной и той же станции - это группы событий с номерами 1, 4 и 5.