4.7 KiB
4.7 KiB
with tbl as (
select *
from (SELECT TO_CHAR(TO_DATE(:V_ToMonth||'01','YYYYMMDD') -1 + LEVEL, 'YYYYMMDD') AS ymd FROM DUAL CONNECT BY LEVEL <= 31 ) d
, (select * from ucm.sacp_report_times where dt_se = 'M') hm
)
select m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh, count(*) as use_min, count(distinct m.gam_id||m.gam_se) as use_co
from (
select a.stand_no
, ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as on_dt
, ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as off_dt
, ac_grad
, ac_ty_iata as ac_ty
, b.DSTRCT_TY
, a.gam_id, a.gam_se
from uic.SACP_GAM_LAST a,
(select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as stand_no
, decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
, a.DSTRCT_TY
from ucm.SACP_STAND a, ucm.SACP_CCTVID_MAPPING b
where a.STAND_NO = b.STAND_NO (+)
and a.DSTRCT_TY in ( 'AF02003', 'AF02004', 'AF02007') -- Aprong3, Apron4, Maintain
)b
where a.STAND_NO = b.STAND_NO
) m, tbl t
where t.ymd||t.hh||t.mi between m.on_dt and m.off_dt
and t.ymd = V_TODAY
and t.hh between V_PreHour and V_ToHour
group by m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh
-- 특정 주기장 특정 시간대 사용량/시간(분) 확인
with tbl as (
select *
from (SELECT TO_CHAR(TO_DATE(:V_ToMonth||'01','YYYYMMDD') -1 + LEVEL, 'YYYYMMDD') AS ymd FROM DUAL CONNECT BY LEVEL <= 31 ) d
, (select * from ucm.sacp_report_times where dt_se = 'M') hm
)
select m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh, count(*) as use_min, count(distinct m.gam_id||m.gam_se) as use_co
from (
select a.stand_no
, ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as on_dt
, ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as off_dt
, ac_grad
, ac_ty_iata as ac_ty
, b.DSTRCT_TY
, a.gam_id, a.gam_se
from uic.SACP_GAM_LAST a,
(select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as stand_no
, decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
, a.DSTRCT_TY
from ucm.SACP_STAND a, ucm.SACP_CCTVID_MAPPING b
where a.STAND_NO = b.STAND_NO (+)
and a.DSTRCT_TY in ( 'AF02003', 'AF02004', 'AF02007') -- Aprong3, Apron4, Maintain
)b
where a.STAND_NO = b.STAND_NO
) m, tbl t
where t.ymd||t.hh||t.mi between m.on_dt and m.off_dt
and t.ymd = '20240820'
and t.hh between '09' and '10'
and stand_no = '233'
group by m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh
-- 특정 주기장 raw데이터 확인
select *
from uic.SACP_GAM_LAST
where stand_no = '233'
and ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) >= '20240820'||'0600'
order by ACTL_STAND_ON_de, actl_stand_on_tm
-- 특정 주기장 집계 결과 확인
select *
from ucm.SACP_STATS_STAND_POSSESN_TIME
where stats_date = '20240820'
and stats_hour = '11'
and stand_no = '233'
-- 주기장 사용량 재 집계
merge into UCM.SACP_STATS_STAND_POSSESN_TIME tgt
using (
with tbl as (
select *
from (SELECT TO_CHAR(TO_DATE(:V_ToMonth||'01','YYYYMMDD') -1 + LEVEL, 'YYYYMMDD') AS ymd FROM DUAL CONNECT BY LEVEL <= 31 ) d
, (select * from ucm.sacp_report_times where dt_se = 'M') hm
)
select m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh, count(*) as use_min, count(distinct m.gam_id||m.gam_se) as use_co
from (
select a.stand_no
, ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as on_dt
, ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as off_dt
, ac_grad
, ac_ty_iata as ac_ty
, b.DSTRCT_TY
, a.gam_id, a.gam_se
from uic.SACP_GAM_LAST a,
(select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as stand_no
, decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
, a.DSTRCT_TY
from ucm.SACP_STAND a, ucm.SACP_CCTVID_MAPPING b
where a.STAND_NO = b.STAND_NO (+)
and a.DSTRCT_TY in ( 'AF02003', 'AF02004', 'AF02007') -- Aprong3, Apron4, Maintain
)b
where a.STAND_NO = b.STAND_NO
) m, tbl t
where t.ymd||t.hh||t.mi between m.on_dt and m.off_dt
and t.ymd = '20240820'
and t.hh between '08' and '10'
group by m.DSTRCT_TY, m.stand_no, ac_grad, ac_ty, t.ymd, t.hh
) src
on ( tgt.stats_date = src.ymd
and tgt.stats_hour = src.hh
and tgt.stand_no = src.stand_no
and tgt.ac_ty = src.ac_ty )
when Matched then
update set tgt.use_time = tgt.use_time + (src.use_min - tgt.use_time)
, tgt.use_co = tgt.use_co + (src.use_co - tgt.use_co)
, updusr_id = 'System'
, updt_dt = systimestamp
when not matched then
insert (stats_date, stats_hour, stand_no, ac_ty, DSTRCT_TY, ac_grad, use_time, use_CO, REGISTER_ID, REGIST_DT)
values( src.ymd, src.hh, src.stand_no, nvl(src.ac_ty, '.'), src.DSTRCT_TY, src.ac_grad, src.use_min, src.use_co, 'System', systimestamp ) ;