Obsidian/Recognition/Work Related/프로젝트/스마트계류장/1.데이터베이스/작업SQL 백업/통계-과거데이터 기종 업데이트.md

• 유도로

create table ucm.SACP_STATS_TWY_POSSESN_TIME_tmp as
select *
from ucm.SACP_STATS_TWY_POSSESN_TIME


update ucm.SACP_STATS_TWY_POSSESN_TIME_tmp a
set ac_ty = nvl((select cd_ref1
		from ucm.SACP_CMMN_CD 
		where cd_ty_id ='CT063'
 		and cd_id = a.ac_ty  ), a.ac_ty||'QQQ')


delete
from ucm.SACP_STATS_TWY_POSSESN_TIME
where stats_date < '20240807'


insert into ucm.SACP_STATS_TWY_POSSESN_TIME(stats_date, stats_hour, area_id, ac_ty, use_time, use_co, REGISTER_ID)
select stats_date, stats_hour, area_id, ac_ty
     , sum(use_time)
     , sum(use_co)
     , 'System'
from ucm.SACP_STATS_TWY_POSSESN_TIME_tmp
group by stats_date, stats_hour, area_id, ac_ty

• 주기장

create table ucm.SACP_STATS_STAND_POSSESN_TIME_tmp as 
select *
from ucm.SACP_STATS_STAND_POSSESN_TIME
where stats_date||stats_hour < '2024080813'


update ucm.SACP_STATS_STAND_POSSESN_TIME_tmp a
set ac_ty = nvl((select cd_ref1
		from ucm.SACP_CMMN_CD 
		where cd_ty_id ='CT063'
 		and cd_id = a.ac_ty  ), a.ac_ty||'QQQ')

delete
from ucm.SACP_STATS_STAND_POSSESN_TIME
where stats_date||stats_hour < '2024080814'


insert into ucm.SACP_STATS_STAND_POSSESN_TIME(stats_date, stats_hour, stand_no, ac_ty, ac_grad, dstrct_ty, use_time, use_co, REGISTER_ID)
select stats_date, stats_hour, stand_no, ac_ty, max(ac_grad), min(dstrct_ty)
     , sum(use_time)
     , sum(use_co)
     , 'System'
from ucm.SACP_STATS_STAND_POSSESN_TIME_tmp
group by stats_date, stats_hour, stand_no, ac_ty
order by stats_date, stats_hour, stand_no, ac_ty


select *
from ucm.SACP_STATS_STAND_POSSESN_TIME
where stats_date||stats_hour = '2024080813'



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
			     , ucm.fun_get_acty_ICAO(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
	) 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 ) ;