[Oracle] CONNECT BY LEVEL 계층형 쿼리 사용 예제

오라클 데이터베이스에서 CONNECT BY LEVEL을 통해 계층형 쿼리를 작성할 수 있습니다. 이번 게시물은 그에 대한 예제를 몇 가지 준비해 보았습니다.

Oracle Connect By Level 날짜, 연속된 날짜 구하는 방법

Oracle Connect By Level

오라클에서 Connect By Level을 사용하여 연속된 날짜를 구하는 방법입니다. 연속성 관련 통계에서 자주 사용하는 편인데, 메모를 해두지 않아서 이번 기회에 추가해 보기로 합니다. TO_CHAR 함수에서 'd' 매개변수는 요일과 관련한 포맷이며 주말여부를 확인하기 위해 추가하였습니다.

SELECT DAT, TO_CHAR (TO_DATE (DAT), 'd')
       FROM (    SELECT TO_CHAR (TO_DATE (:FRDAT) + (LEVEL - 1), 'YYYYMMDD') AS DAT
                   FROM DUAL
             CONNECT BY LEVEL <=
                             TO_DATE (:TODAT, 'YYYYMMDD')
                           - TO_DATE (:FRDAT, 'YYYYMMDD')
                           + 1);

DAT              TO
---------------- --
20230301         4
20230302         5
20230303         6
20230304         7
20230305         1
20230306         2
20230307         3
20230308         4
20230309         5
20230310         6
20230311         7
20230312         1
20230313         2
20230314         3
20230315         4
20230316         5
20230317         6
20230318         7
20230319         1
20230320         2
20230321         3
20230322         4
20230323         5
20230324         6
20230325         7
20230326         1
20230327         2
20230328         3
20230329         4
20230330         5
20230331         6
20230401         7

32 rows selected.

Connect By Level

Connect By Level

2020년 11월 한 달 간의 방문객 수를 확인하고 싶다고 가정합시다. 예제자료는 다음을 가정합니다.

  • 테이블 이름은 TB_VISITOR
  • 컬럼 이름은 VISTDAT(방문일자), VISTCNT(방문객수) 입니다. 

데이터를 보니 1일에 80명, 2일에 70명, 3일에 0명(표시가 안 되므로), 4일에 92명이 왔습니다. 그러면 3일과 같이 데이터가 없는 부분을 0으로 표시하고 싶을 때 어떻게 해야 할까요? CONNECT BY LEVEL 을 이용한 방법을 소개해드리겠습니다.

계층형 쿼리
--CONNECT BY LEVEL 을 통해 LEVEL 을 조회해봤습니다.
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10
오라클 날짜 계층형
--이를 이용하여 2020년 11월 1일부터 11월 30일까지 LEVEL을 이용하여 표시해 보았습니다.
--해당 부분은 정답이 아닙니다. 판단에 따라 수정하시면 됩니다.
SELECT TO_CHAR (TO_DATE ('20201101', 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') DAT
                   FROM DUAL
             CONNECT BY LEVEL <= TO_DATE ('20201130', 'YYYYMMDD') - TO_DATE ('20201101', 'YYYYMMDD') + 1
oracle date level

달력을 중심으로 하여 얼마나 방문했는지 확인하는 예제 입니다.

WITH TB_VISITOR
     AS (SELECT '20201101' VISTDAT, 80 VISTCNT FROM DUAL UNION ALL
         SELECT '20201102', 70 FROM DUAL UNION ALL
         SELECT '20201104', 92 FROM DUAL UNION ALL
         SELECT '20201106', 82 FROM DUAL UNION ALL
         SELECT '20201107', 77 FROM DUAL UNION ALL
         SELECT '20201118', 93 FROM DUAL UNION ALL
         SELECT '20201110', 71 FROM DUAL)
--SELECT * FROM TB_VISITOR         
SELECT DAT, VISTDAT, VISTCNT
    FROM    (    
            SELECT TO_CHAR (TO_DATE ('20201101', 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') DAT
                   FROM DUAL
             CONNECT BY LEVEL <= TO_DATE ('20201130', 'YYYYMMDD') - TO_DATE ('20201101', 'YYYYMMDD') + 1) TA
         LEFT JOIN
            TB_VISITOR
         ON VISTDAT = DAT
ORDER BY DAT

CONNECT BY LEVEL 월별 마지막 날짜 구하기

오라클 계층 쿼리에서 CONNECT BY 절과 LEVEL 수도컬럼을 사용하여 월별, 마지막 날짜를 구하는 방법에 대한 예제자료입니다. 계층이라는 표현 자체에서도 알 수 있듯이, 데이터 간의 상하관계를 이용하여 조회할 때 유용하게 사용할 수 있습니다.

최근 4개년도의 각 달마다 마지막 날짜의 수익을 알고 싶은 상황을 가정합시다. 다음의 조건을 기준으로 진행합니다.

  • 마지막 날짜에 일괄적인 수익정산이 이루어지기 때문에 나머지 다른 날짜는 필요가 없다고 함.
  • 각 달의 마지막날 수익만 있으면 되고, 수익이 없더라도 표시를 원하는 상황.
  • 수익이 없는 경우 공란으로 표시하도록 요청함.

오라클 DUAL 테이블과 CONNECT BY를 사용하겠습니다. 이런 식의 계층형 쿼리의 장점은 간결한 표현으로 연속된 데이터를 조회할 수 있다는 것입니다. 그리고 레프트 조인을 이용하여 수익이 없더라도 표시를 쉽게 할 수 있습니다.

다음은 현재시간을 기준으로 하여 4년 치 마지막 날짜를 구하는 방법입니다. 해당 계층 쿼리는 다음과 같은 결과를 가져옵니다.

  • 2023년 2월 기준으로 조회한 결과이며 간단히 흐름을 안내해 드리기 위해 컬럼을 나누어 보겠습니다.
  • LEVEL 컬럼은 레벨 수도컬럼(pseudocolumn)입니다.
  • TO_CHAR (SYSDATE, 'YYYYMMDD') 컬럼은 현재 시간을 조회합니다.
  • TO_CHAR (SYSDATE + (1 - LEVEL), 'YYYYMMDD') 컬럼으로 날짜를 레벨별로 하나씩 빼보겠습니다.
  • TO_CHAR (ADD_MONTHS (SYSDATE, 1 - LEVEL), 'YYYYMMDD') 컬럼은 현재 시간에 레벨별로 월 단위로 빼보겠습니다.
  • TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, 1 - LEVEL)), 'YYYYMMDD') 컬럼으로 현재 시간에서 레벨 개월을 빼고 마지막 날짜를 구해봅니다.
  • 추후 날짜 지정이 필요하다면 SYSDATE 부분을 수정하면 될 것입니다.
SELECT LEVEL,
                TO_CHAR (SYSDATE, 'YYYYMMDD'),
                TO_CHAR (SYSDATE + (1 - LEVEL), 'YYYYMMDD'),
                TO_CHAR (ADD_MONTHS (SYSDATE, 1 - LEVEL), 'YYYYMMDD'),
                TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, 1 - LEVEL)), 'YYYYMMDD')
           FROM DUAL
     CONNECT BY LEVEL <= 12 * 4;

     LEVEL TO_CHAR(SYSDATE, TO_CHAR(SYSDATE+ TO_CHAR(ADD_MONT TO_CHAR(LAST_DAY
---------- ---------------- ---------------- ---------------- ----------------
         1 20230223         20230223         20230223         20230228
         2 20230223         20230222         20230123         20230131
         3 20230223         20230221         20221223         20221231
         4 20230223         20230220         20221123         20221130
         5 20230223         20230219         20221023         20221031
         6 20230223         20230218         20220923         20220930
         7 20230223         20230217         20220823         20220831
         8 20230223         20230216         20220723         20220731
         9 20230223         20230215         20220623         20220630
        10 20230223         20230214         20220523         20220531
        11 20230223         20230213         20220423         20220430
        12 20230223         20230212         20220323         20220331
        13 20230223         20230211         20220223         20220228
        14 20230223         20230210         20220123         20220131
        15 20230223         20230209         20211223         20211231
        16 20230223         20230208         20211123         20211130
        17 20230223         20230207         20211023         20211031
        18 20230223         20230206         20210923         20210930
        19 20230223         20230205         20210823         20210831
        20 20230223         20230204         20210723         20210731
        21 20230223         20230203         20210623         20210630
        22 20230223         20230202         20210523         20210531
        23 20230223         20230201         20210423         20210430
        24 20230223         20230131         20210323         20210331
        25 20230223         20230130         20210223         20210228
        26 20230223         20230129         20210123         20210131
        27 20230223         20230128         20201223         20201231
        28 20230223         20230127         20201123         20201130
        29 20230223         20230126         20201023         20201031
        30 20230223         20230125         20200923         20200930
        31 20230223         20230124         20200823         20200831
        32 20230223         20230123         20200723         20200731
        33 20230223         20230122         20200623         20200630
        34 20230223         20230121         20200523         20200531
        35 20230223         20230120         20200423         20200430
        36 20230223         20230119         20200323         20200331
        37 20230223         20230118         20200223         20200229
        38 20230223         20230117         20200123         20200131
        39 20230223         20230116         20191223         20191231
        40 20230223         20230115         20191123         20191130
        41 20230223         20230114         20191023         20191031
        42 20230223         20230113         20190923         20190930
        43 20230223         20230112         20190823         20190831
        44 20230223         20230111         20190723         20190731
        45 20230223         20230110         20190623         20190630
        46 20230223         20230109         20190523         20190531
        47 20230223         20230108         20190423         20190430
        48 20230223         20230107         20190323         20190331

48 rows selected.

CONNECT BY LEVEL LEFT OUTER JOIN

커넥트 바이 절 및 레벨로 계층 데이터를 조회하고 레프트 아우터 조인으로 수익을 조회해 보겠습니다. 예시는 1년으로 하겠습니다.

SELECT LAST_DAY_OF_LAST_4_YEARS, SALES_DATE, SALES
       FROM    (    SELECT TO_CHAR (LAST_DAY (ADD_MONTHS (SYSDATE, 1 - LEVEL)),
                                    'YYYYMMDD')
                              LAST_DAY_OF_LAST_4_YEARS
                      FROM DUAL
                CONNECT BY LEVEL <= 12 * 1)
            LEFT JOIN
               SALES
            ON SALES_DATE = LAST_DAY_OF_LAST_4_YEARS;

LAST_DAY_OF_LAST SALES_DA      SALES
---------------- -------- ----------
20230228         23/02/28       2000
20230131         23/01/31       1000
20221231         22/12/31      12000
20221130
20220331
20220630
20221031
20220831
20220731
20220430
20220930
20220531

12 rows selected.

실제 세일즈 테이블에는 11월 1일에 수익이 11,000원이 있으나 마지막 날짜가 아니기에 조회가 되지 않습니다. 이렇게 해달라고 요청했는데, 필요 시 풀 아우터 조인을 사용하거나, 다른 방안을 검토해 볼 수 있습니다.

  • 23년 2월 28일 2,000 조회
  • 23년 1월 31일 1,000 조회
  • 22년 12월 31일 12,000 조회
  • 22년 11월 31일 수익 없으니 공란 표시
  • 22년 11월 1일 수익 11,000 있으나 조회를 하지 않도록 원하여 표시하지 않습니다.
SELECT SALES_DATE, SALES FROM SALES;

SALES_DA      SALES
-------- ----------
23/02/28       2000
23/01/31       1000
22/12/31      12000
22/11/01      11000

CONNECT BY LEVEL 특정 구간 시작일자, 종료일자 설정하기

특정 시작날짜부터 종료날짜까지 조회하는 예제 쿼리입니다. 예제는 2022년 12월 31일부터 2023년 1월 15일까지 계층으로 조회해 보았습니다. 시작일자부터 레벨을 증가하며 조회해도 되고, 종료일자부터 레벨을 감소하며 조회해도 결과는 똑같습니다. 별도의 정렬이 없다면 순서에서 차이가 있을 수 있습니다.

WITH SET_DATE AS (SELECT :FROMDAT, :TODAT FROM DUAL)
         SELECT :FROMDAT || '~' || :TODAT,
                LEVEL,
                TO_DATE (:FROMDAT) + LEVEL - 1,
                TO_DATE (:TODAT) - LEVEL + 1
           FROM SET_DATE
     CONNECT BY LEVEL <= TO_DATE (:TODAT) - TO_DATE (:FROMDAT) + 1;

:FROMDAT||'~'||:TODAT LEVEL TO_DATE( TO_DATE(
--------------------- ---------- -------- --------
20221231~20230115      1 22/12/31 23/01/15
20221231~20230115      2 23/01/01 23/01/14
20221231~20230115      3 23/01/02 23/01/13
20221231~20230115      4 23/01/03 23/01/12
20221231~20230115      5 23/01/04 23/01/11
20221231~20230115      6 23/01/05 23/01/10
20221231~20230115      7 23/01/06 23/01/09
20221231~20230115      8 23/01/07 23/01/08
20221231~20230115      9 23/01/08 23/01/07
20221231~20230115      10 23/01/09 23/01/06
20221231~20230115      11 23/01/10 23/01/05
20221231~20230115      12 23/01/11 23/01/04
20221231~20230115      13 23/01/12 23/01/03
20221231~20230115      14 23/01/13 23/01/02
20221231~20230115      15 23/01/14 23/01/01
20221231~20230115      16 23/01/15 22/12/31

16 rows selected.
댓글 쓰기
가져가실 때, 출처 표시 부탁드려요! 감사합니다. 💗