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_timefrom 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_linefrom tb3 t3left join tb1 t1 on t3.consult_time = t1.gpleft 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;

查询结果：