Oracle地铁OD全网15分钟净客流统计SQL

 

原始数据：

/*15分钟客流统计*/
with tb as(
--使用正则表达式拼接格式化时间17:17:52为2018/1/1 17:52:00 date类型
select t.*,
       to_date(regexp_substr(t.进站时间, '[^:]+', 1, 2) || ':' ||regexp_substr(t.进站时间, '[^:]+', 1, 3) || ':00', 'hh24:mi:ss') as 进站时间戳,
       to_date(regexp_substr(t.出站时间, '[^:]+', 1, 2) || ':' ||regexp_substr(t.出站时间, '[^:]+', 1, 3) || ':00', 'hh24:mi:ss') as 出站时间戳
from   cq_dt_in_ext_table1 t
),
tb1 as(
--对刷卡进站时间每15分钟分组
select gp, count(*) as in_cnt
from   (select t.进站时间戳,
               trunc(t.进站时间戳, 'mi') - mod(to_char(进站时间戳, 'mi'), 15) / 24 / 60 as gp,
               t.卡号,
               t.进站编码
        from   tb t)
group  by gp
),
tb2 as
--对刷卡出站时间每15分钟分组
(select gp, count(*) as out_cnt
from   (select t.出站时间戳,trunc(t.出站时间戳, 'mi') -mod(to_char(出站时间戳, 'mi'), 15) / 24 / 60 as gp,
               t.卡号,
               t.出站编码
        from   tb t)
group  by gp
),
tb3 as(
--构造5-24点每15分钟时间码表
select to_date(to_char(to_date('05:00:00', 'hh24:mi:ss') +level * 15 / 24 / 60,'hh24:mi:ss'),'hh24:mi:ss') as consult_time
  from dual
connect by level <= 76
),
tb4 as(
select t3.consult_time,
       coalesce(t1.in_cnt, 0) in_cnt,
       coalesce(t2.out_cnt, 0) out_cnt,
       '全网' as in_line
  from tb3 t3
  left join tb1 t1 on t3.consult_time = t1.gp
  left join tb2 t2 on t3.consult_time = t2.gp
)
select t.consult_time 当前时间,
       sum(t.in_cnt) over(order by consult_time) - sum(t.out_cnt) over(order by consult_time) 净客流,
       t.in_line 线路
  from tb4 t;

查询结果：