오라클 데이터베이스에서 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
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
달력을 중심으로 하여 얼마나 방문했는지 확인하는 예제 입니다.
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 DATCONNECT 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 11000CONNECT 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.