主页 / 问题 / 1535988
Accepted
Diana Oryol
Asked:2023-08-15 17:57:20 +0000 UTC2023-08-15 17:57:20 +0000 UTC

我们需要找到在车站连续停留的时间

  • 772

大家好。
有一个表：订户 ID、电台 ID、事件日期。
有必要求出在车站连续停留的时间。我在 Oracle 中解决，但随后我会将解决方案转移到 pySpark。
计算事件的持续时间，
如果下一个事件在同一站，则 flag_lead = 0；
如果上一个事件在同一站，则 flag_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

1 个回答

  1. Best Answer

    只需根据当前事件和先前事件位于同一站的事实对条目进行分组即可：

    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 23 年 8 月 1 日 1
    1 1 23 年 8 月 1 日 1
    1 1 23 年 8 月 1 日 1
    1 2 23 年 8 月 1 日 2
    1 1 23 年 8 月 1 日 3
    1 2 23 年 8 月 1 日 4
    1 2 23 年 8 月 1 日 4
    1 2 23 年 8 月 1 日 4
    1 1 23 年 8 月 1 日 5
    1 1 23 年 8 月 1 日 5

    小提琴

    很明显，同一站连续发生的几个事件是编号为 1、4 和 5 的事件组。

    • 1

相关问题