欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-15 17:33:36
...

原始数据:

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;

查询结果:

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