MonHun
/
Obsidian
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Obsidian/Recognition/Work Related/1.업무메모/2024-04-22.md

11 KiB
Raw Permalink Blame History

-- 통계 백업


/*
 * 시간별 집계 조회조건 = 일자 from ~ to
 */

-- 집계쿼리
select substr(flt_dt, 1, 8) as flt_de
     , stand_no
     , stand_on_off
--     , case when actl_apn_in_tm is not null then 'A'
--            when actl_apn_out_tm is not null then 'D'
--            else 'T' END as flt_se
--	 , ac_grad
--	 , ac_ty_iata
	, sum(decode(substr(flt_dt,9,2), '01', 1, 0)) as tm01
	, sum(decode(substr(flt_dt,9,2), '02', 1, 0)) as tm02
	, sum(decode(substr(flt_dt,9,2), '03', 1, 0)) as tm03
	, sum(decode(substr(flt_dt,9,2), '04', 1, 0)) as tm04
	, sum(decode(substr(flt_dt,9,2), '05', 1, 0)) as tm05
	, sum(decode(substr(flt_dt,9,2), '06', 1, 0)) as tm06
	, sum(decode(substr(flt_dt,9,2), '07', 1, 0)) as tm07
	, sum(decode(substr(flt_dt,9,2), '08', 1, 0)) as tm08
	, sum(decode(substr(flt_dt,9,2), '09', 1, 0)) as tm09
	, sum(decode(substr(flt_dt,9,2), '10', 1, 0)) as tm10
	, sum(decode(substr(flt_dt,9,2), '11', 1, 0)) as tm11
	, sum(decode(substr(flt_dt,9,2), '12', 1, 0)) as tm12
	, sum(decode(substr(flt_dt,9,2), '13', 1, 0)) as tm13
	, sum(decode(substr(flt_dt,9,2), '14', 1, 0)) as tm14
	, sum(decode(substr(flt_dt,9,2), '15', 1, 0)) as tm15
	, sum(decode(substr(flt_dt,9,2), '16', 1, 0)) as tm16
	, sum(decode(substr(flt_dt,9,2), '17', 1, 0)) as tm17
	, sum(decode(substr(flt_dt,9,2), '18', 1, 0)) as tm18
	, sum(decode(substr(flt_dt,9,2), '19', 1, 0)) as tm19
	, sum(decode(substr(flt_dt,9,2), '20', 1, 0)) as tm20
	, sum(decode(substr(flt_dt,9,2), '21', 1, 0)) as tm21
	, sum(decode(substr(flt_dt,9,2), '22', 1, 0)) as tm22
	, sum(decode(substr(flt_dt,9,2), '23', 1, 0)) as tm23
	, sum(decode(substr(flt_dt,9,2), '24', 1, 0)) as tm24
from (
	select ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as flt_dt, stand_no, 'ON' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where  ACTL_STAND_ON_TM is not null
	union all 
	select ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as flt_dt, stand_no, 'OFF' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where ACTL_STAND_OFF_TM is not null
	order by stand_no, 4, 5
) m
where flt_dt like '2024042%'
--and stand_no = '367'
and stand_no in (
	 select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as std_stand_no
--	      , decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
	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', 'AF02009' ) )
group by substr(flt_dt, 1, 8), stand_no, stand_on_off
order by flt_de, stand_no, decode(stand_on_off, 'ON', 1, 2)



-- 확인용
select stand_no
     , stand_on_off
--     , case when actl_apn_in_tm is not null then 'A'
--            when actl_apn_out_tm is not null then 'D'
--            else 'T' END as flt_se
--	 , ac_grad
--	 , ac_ty_iata
	, sum(decode(substr(flt_dt,9,2), '01', 1, 0)) as tm01
	, sum(decode(substr(flt_dt,9,2), '02', 1, 0)) as tm02
	, sum(decode(substr(flt_dt,9,2), '03', 1, 0)) as tm03
	, sum(decode(substr(flt_dt,9,2), '04', 1, 0)) as tm04
	, sum(decode(substr(flt_dt,9,2), '05', 1, 0)) as tm05
	, sum(decode(substr(flt_dt,9,2), '06', 1, 0)) as tm06
	, sum(decode(substr(flt_dt,9,2), '07', 1, 0)) as tm07
	, sum(decode(substr(flt_dt,9,2), '08', 1, 0)) as tm08
	, sum(decode(substr(flt_dt,9,2), '09', 1, 0)) as tm09
	, sum(decode(substr(flt_dt,9,2), '10', 1, 0)) as tm10
	, sum(decode(substr(flt_dt,9,2), '11', 1, 0)) as tm11
	, sum(decode(substr(flt_dt,9,2), '12', 1, 0)) as tm12
	, sum(decode(substr(flt_dt,9,2), '13', 1, 0)) as tm13
	, sum(decode(substr(flt_dt,9,2), '14', 1, 0)) as tm14
	, sum(decode(substr(flt_dt,9,2), '15', 1, 0)) as tm15
	, sum(decode(substr(flt_dt,9,2), '16', 1, 0)) as tm16
	, sum(decode(substr(flt_dt,9,2), '17', 1, 0)) as tm17
	, sum(decode(substr(flt_dt,9,2), '18', 1, 0)) as tm18
	, sum(decode(substr(flt_dt,9,2), '19', 1, 0)) as tm19
	, sum(decode(substr(flt_dt,9,2), '20', 1, 0)) as tm20
	, sum(decode(substr(flt_dt,9,2), '21', 1, 0)) as tm21
	, sum(decode(substr(flt_dt,9,2), '22', 1, 0)) as tm22
	, sum(decode(substr(flt_dt,9,2), '23', 1, 0)) as tm23
	, sum(decode(substr(flt_dt,9,2), '24', 1, 0)) as tm24
from (
	select ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as flt_dt, stand_no, 'ON' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where  ACTL_STAND_ON_TM is not null
	union all 
	select ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as flt_dt, stand_no, 'OFF' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where ACTL_STAND_OFF_TM is not null
	order by stand_no, 4, 5
) m
where flt_dt between '20240401' and '20240431'
--and stand_no = '367'
and stand_no in (
	 select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as std_stand_no
--	      , decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
	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', 'AF02009' ) )
group by stand_no, stand_on_off
order by stand_no, decode(stand_on_off, 'ON', 1, 2)








select 'ON' as stand_on_off
     , ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as flt_dt
     , a.stand_no
     , ac_grad
     , ac_ty_iata
     , actl_apn_in_tm
     , actl_apn_out_tm
     , b.DSTRCT_TY
     , ucm.fun_get_cmmn_nm('CT003', 'AF02003') as stand_DSTRCT_TY
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', 'AF02009' ) )b
where a.STAND_NO = b.STAND_NO
and a.ACTL_STAND_ON_TM is not null
and a.actl_stand_on_de = '20240422'
and a.stand_no = '253'




/*
 * 일자별 집계 조회조건 = 년월(yyyymm) from ~ to
 */

-- 집계쿼리
select stand_no
     , stand_on_off
--     , case when actl_apn_in_tm is not null then 'A'
--            when actl_apn_out_tm is not null then 'D'
--            else 'T' END as flt_se
--	 , ac_grad
--	 , ac_ty_iata
	, sum(decode(substr(flt_dt,7,2), '01', 1, 0)) as day01
	, sum(decode(substr(flt_dt,7,2), '02', 1, 0)) as day02
	, sum(decode(substr(flt_dt,7,2), '03', 1, 0)) as day03
	, sum(decode(substr(flt_dt,7,2), '04', 1, 0)) as day04
	, sum(decode(substr(flt_dt,7,2), '05', 1, 0)) as day05
	, sum(decode(substr(flt_dt,7,2), '06', 1, 0)) as day06
	, sum(decode(substr(flt_dt,7,2), '07', 1, 0)) as day07
	, sum(decode(substr(flt_dt,7,2), '08', 1, 0)) as day08
	, sum(decode(substr(flt_dt,7,2), '09', 1, 0)) as day09
	, sum(decode(substr(flt_dt,7,2), '10', 1, 0)) as day10
	, sum(decode(substr(flt_dt,7,2), '11', 1, 0)) as day11
	, sum(decode(substr(flt_dt,7,2), '12', 1, 0)) as day12
	, sum(decode(substr(flt_dt,7,2), '13', 1, 0)) as day13
	, sum(decode(substr(flt_dt,7,2), '14', 1, 0)) as day14
	, sum(decode(substr(flt_dt,7,2), '15', 1, 0)) as day15
	, sum(decode(substr(flt_dt,7,2), '16', 1, 0)) as day16
	, sum(decode(substr(flt_dt,7,2), '17', 1, 0)) as day17
	, sum(decode(substr(flt_dt,7,2), '18', 1, 0)) as day18
	, sum(decode(substr(flt_dt,7,2), '19', 1, 0)) as day19
	, sum(decode(substr(flt_dt,7,2), '20', 1, 0)) as day20
	, sum(decode(substr(flt_dt,7,2), '21', 1, 0)) as day21
	, sum(decode(substr(flt_dt,7,2), '22', 1, 0)) as day22
	, sum(decode(substr(flt_dt,7,2), '23', 1, 0)) as day23
	, sum(decode(substr(flt_dt,7,2), '24', 1, 0)) as day24
	, sum(decode(substr(flt_dt,7,2), '25', 1, 0)) as day25
	, sum(decode(substr(flt_dt,7,2), '26', 1, 0)) as day26
	, sum(decode(substr(flt_dt,7,2), '27', 1, 0)) as day27
	, sum(decode(substr(flt_dt,7,2), '28', 1, 0)) as day28
	, sum(decode(substr(flt_dt,7,2), '29', 1, 0)) as day29
	, sum(decode(substr(flt_dt,7,2), '30', 1, 0)) as day30
	, sum(decode(substr(flt_dt,7,2), '31', 1, 0)) as day31
from (
	select ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as flt_dt, stand_no, 'ON' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where  ACTL_STAND_ON_TM is not null
	union all 
	select ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as flt_dt, stand_no, 'OFF' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where ACTL_STAND_OFF_TM is not null
	order by stand_no, 4, 5
) m
where substr(flt_dt, 1, 6) between '202403' and '202403'
--and stand_no = '367'
and stand_no in (
	 select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as std_stand_no
--	      , decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
	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', 'AF02009' ) )
group by stand_no, stand_on_off
order by stand_no, decode(stand_on_off, 'ON', 1, 2)




/*
 * 년도별 집계 조회조건 = 년도(yyyy) to
 */

-- 집계쿼리
select stand_no
     , stand_on_off
--     , case when actl_apn_in_tm is not null then 'A'
--            when actl_apn_out_tm is not null then 'D'
--            else 'T' END as flt_se
--	 , ac_grad
--	 , ac_ty_iata
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -5 , 1, 0)) as year_5
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -4 , 1, 0)) as year_4
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -3 , 1, 0)) as year_3
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -2 , 1, 0)) as year_2
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -1 , 1, 0)) as year_1
	, sum(decode(substr(flt_dt,1,4), to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -0 , 1, 0)) as year_0
from (
	select ucm.fun_kst_tm(ACTL_STAND_ON_de||actl_stand_on_tm) as flt_dt, stand_no, 'ON' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where  ACTL_STAND_ON_TM is not null
	union all 
	select ucm.fun_kst_tm(ACTL_STAND_OFF_de||actl_stand_OFF_tm) as flt_dt, stand_no, 'OFF' as stand_on_off
	     , ac_grad, ac_ty_iata, actl_apn_in_tm, actl_apn_out_tm
	from uic.SACP_GAM_LAST 
	where ACTL_STAND_OFF_TM is not null
	order by stand_no, 4, 5
) m
where substr(flt_dt, 1, 4) between to_char(to_date(:ar_year||'0101', 'yyyymmdd'), 'yyyy') -5  and :ar_year
--and stand_no = '367'
and stand_no in (
	 select ucm.FUN_PRE_ZERO_REMOVE(replace(a.stand_no, '00', '')) as std_stand_no
--	      , decode(b.STAND_NO, null, 'N', 'Y') as cctv_yn
	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', 'AF02009' ) )
group by stand_no, stand_on_off
order by stand_no, decode(stand_on_off, 'ON', 1, 2)