Obsidian/Recognition/Work Related/프로젝트/스마트계류장/1.데이터베이스/통계관련/데이터 검증/Gate 사용량 통계.md

```sql



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 ) ;



```