[Connect by]_ 계층형 쿼리 & 재귀 쿼리 응용 ( 이달의 휴일 구하기 )

개요 

휴일 정보 테이블과 연계하여

이달( 선택한 달 ) 의 총 일수, 총 휴일 수 , 총 근무일 수 를 구하는 쿼리 작성

 

OUTPUT

• Total_cnt : 해당 월의 총 일수
• Working_cnt : 해당 월의 근무일 수 ( 평일 )
• Holiday_cnt 해당 월의 휴일 

사용 Oracle 함수

• connect by
• to_char
• to_date
• with as ()

---

휴일 테이블

HOLIDAY

해당 테이블에는

• 휴일 ('YYYY-MM-DD')
• 생성일
• 작성자

3가지의 데이터가 들어가도록 구성

---

선택 월

2022-11-01

11월에는

평일이 22일

휴일이 8일

총일이 30일이 있습니다.

---

Connect by 를 응용한 계층형 쿼리

SELECT
    COUNT(*) AS TOTAL_CNT
     ,SUM(WORK_TOTAL_CNT) AS WORKING_CNT
     ,SUM(HOLI_TOTAL_CNT) AS HOLIDAY_CNT
FROM(
        SELECT
            CASE WHEN d NOT IN (1,7) AND b.HOLIDAY IS NULL THEN 1 ELSE 0 END AS WORK_TOTAL_CNT
             ,CASE WHEN d IN (1,7) OR b.HOLIDAY IS NOT NULL THEN 1 ELSE 0 END AS HOLI_TOTAL_CNT
        FROM (
                 SELECT
                    DT
                 ,  D
                 FROM (
                          SELECT
                                  TO_DATE('20221101000000','yyyymmddhh24miss') + (LEVEL -1) AS DT
                                  ,TO_CHAR(TO_DATE('20221101000000','yyyymmddhh24miss') + (LEVEL -1),'D') AS D
                          FROM DUAL
                          CONNECT BY LEVEL <= (TO_DATE('20221130235959','yyyymmddhh24miss') - TO_DATE('20221101000000','yyyymmddhh24miss')) +1
                          )

             ) A
                 left join HOLIDAY B
                           ON A.DT = B.HOLIDAY

    )
;

'20221101000000' 의 자리에 파라미터를 위치시키면 원하는 달의 일수를 구할 수 있습니다.

 

먼저 계층형 쿼리를 사용하여 해당 시작일로부터 끝일 까지의

날자와 해당 날자 코드를 받습니다.

 

1: 일요일 ~

7: 토요일

 

이를 LEFT JOIN 을 이용하여 해당 날자와 휴일 테이블의 날자가 일치하는 곳에 데이터가 붙고

없는곳은 NULL 이 들어갈 것입니다.

 

이를 CASE 문을 통해

 

날자코드가 1,7 (주말) 이 아니고 (AND) HOLIDAY 컬럼의 값이 null인 경우가 평일이 되고

날자코드가 1,7 (주말) 에 속하거나 (OR) HOLIDAY 컬럼에 값이 있을 경우가 주말이 됩니다.

 

이를 SUM 함수를 통해 총량을 구하게 되면 각각의 평일총수, 휴일 총수가 됩니다.

 

 

주말 삽입

 

평일인 1일 2일 3일을 추가하고 쿼리 실행시 다음과 같습니다.

 

 

평일이 3일 줄고

휴일이 3일 증가한 것을 확인할 수 있습니다.

---

+@

CONNECT BY 는 Oracle 에서 지원하는 계층형 쿼리입니다.

 

만약 데이터베이스가 Oracle 이 아닐 경우를 대비하여 쿼리를 작성해야 합니다.

이는 WITH AS 를 이용한 재귀함수를 통해 구현할 수 있습니다.

 

쿼리

WITH CONTINUOUS(SDT,EDT) AS (
    SELECT
        TO_DATE('20221101000000','yyyymmddhh24miss') SDT
        ,TO_CHAR(TO_DATE('20221101000000','yyyymmddhh24miss'),'D') AS EDT
    FROM DUAL
    UNION ALL
    SELECT
        SDT +1 SDT
        ,TO_CHAR(SDT +1 , 'D') AS EDT
    FROM CONTINUOUS
    WHERE
        SDT +1 < TO_DATE('20221130235959','yyyymmddhh24miss')
)
SELECT
    COUNT(*) AS TOTAL_CNT
    ,SUM(WORK_TOTAL_CNT) AS WORKING_CNT
    ,SUM(HOLI_TOTAL_CNT) AS HOLIDAY_CNT
FROM(
        SELECT
            CASE WHEN d NOT IN (1,7) AND b.HOLIDAY IS NULL THEN 1 ELSE 0 END AS WORK_TOTAL_CNT
            ,CASE WHEN d IN (1,7) OR b.HOLIDAY IS NOT NULL THEN 1 ELSE 0 END AS HOLI_TOTAL_CNT
        FROM (
                SELECT
                    to_char(SDT, 'yyyymmddhh24miss') DT,
                    to_char(SDT,'D') D
                FROM CONTINUOUS

                 ) A
              left join HOLIDAY B
              ON A.DT = to_char(B.HOLIDAY,'yyyymmddhh24miss')
        )
;

 

 

 

감사합니다.