[Oracle]WITH AS(가상 테이블) 쿼리 참고용

통계를 조회할 수 있는 페이지에서 고객측이 요구했던 데이터를 볼 수 있는 페이지가 없어서 가상 테이블 쿼리를 작성했던 내용. TMP_INPUT 테이블에 '년도', '월', '일' 부분에서 월만 변경하여 해당 데이터를 조회할 수 있게 작성하였다. 시스템에서 날짜를 날짜 타입이 아니라 문자 타입(varchar2) 으로 되어 있어서 개발자 입장에서는 편했던 기억이 ㅋㅋ

 

 

-- 통합통계
-- 연도와 월 입력시(날짜 변경 x) 해당 기간동안 시간별 평균 인입호를 구할수 있다.
WITH TMP_INPUT AS (
    SELECT  CONCAT('2021','02','01') AS S_YMD,
     CONCAT('2022','10','31') AS E_YMD
),

TMP_HOUR_TIME AS( 
select  substr(X.S_YMD,1,4) AS S_year,
substr(X.E_YMD,1,4) AS E_year,
  Z.hour as hour, /* 시간 기준  */
SUM(Z.P02) as INHO, /* 기간동안 해당 시간의 총 인입호  */
  CASE  /* 시간당평균인입호 = 총 인입호/24(시간)/30(일수)  */
  when  SUBSTR(X.E_YMD,1,4)-SUBSTR(X.S_YMD,1,4)>=1
  then ROUND((SUM(Z.P02)/24/ ((((SUBSTR(X.E_YMD,1,4)- SUBSTR(X.S_YMD,1,4) )*12)  + SUBSTR(X.E_YMD,5,2) - SUBSTR(X.S_YMD,5,2))*30)) ,2 )
  else ROUND((SUM(Z.P02)/24/ (((SUBSTR(X.E_YMD,5,2)- SUBSTR(X.S_YMD,5,2) )+1)*30)) ,2)
  end as INHOAVG

FROM(


select 'CALL' AS PH1, C.vdn_GRP_CD,C.vdn_GRP_NM AS PH2,  '', D.YMD YMD,
D.HH as HOUR,
                0 P01, sum(d.menucnt) as P02, 0 P03,0 as P04,
               0 as P05,
               0 P06, 0 P07, 
               MAX(C.SEQ) as SEQ1, 0 SEQ2, NULL SEQ3, NULL SEQ4, NULL SEQ5, 0 SEQ6
from 
(SELECT A.VDN_GRP_CD,A.vdn_GRP_NM, A.SEQ, B.vdn_CD,
     GREATEST(B.S_YMD,X.S_YMD) AS S_YMD,
     LEAST(B.E_YMD,X.E_YMD) AS E_YMD,
     a.use_type_cd 
     FROM TB_vdn_GROUP A, TB_vdn_MAPPING B, TMP_INPUT X
     WHERE A.USE_YN = 'Y' and a.use_type_cd in('SI') 
     AND A.vdn_GRP_CD = B.vdn_GRP_CD
     AND B.S_YMD <= X.E_YMD
     AND B.E_YMD >= X.S_YMD)C , TST_IVR_CNT D, TMP_INPUT X
WHERE C.vdn_CD = D.VDN_CD
and D.YMD between X.S_YMD and X.E_YMD
AND D.vdn_cd in('33013','33012','33011','33014')
    AND D.ivr_cd ='MM00000000'
group by C.vdn_GRP_CD,C.vdn_GRP_NM, D.HH

) Z , TMP_INPUT X
group by Z.hour
)

select  CONCAT(Y.S_year,substr(X.S_YMD,5,2)) AS '시작일자(월)'
, CONCAT(Y.E_year,substr(X.E_YMD,5,2)) AS '종료일자(월)'
  , Y.hour as 인입시간
  , Y.INHO as 총인입호
  , Y.INHOAVG as 시간당평균인입호
from TMP_HOUR_TIME Y, TMP_INPUT X;