[Oracle] 🚂 쿼리 열차 출발합니다~ 시간대 별로 데이터 개수 세기

최근에 쿼리 작성할 일이 많았는데

 
(다시 시작된 일 찾아서 하는 대견한(?) 주디🐰
하나씩 내 이름으로 올리는 거 제법 재밌단 말이지~?!)

 

 
근데 뭔가 검색하면서 쿼리를 작성하니 나중에는 또 까먹고 다시 검색할 것이 눈에 보여
하나하나 간단한 예제로 정리해보려고 한다!
 
(근데 이 정도면 금융권은 알고리즘보다 SQL 문제를 더 내야 한다!!!! 재밌는데 어려워!! 튜닝은 더 모르겠어)
 
그럼 쿼리 열차 출발~~🚂🚂
비키세요 비키세요 폭주 기관차 주디 지나갑니다 🐰🚂

 

이 글은 Oracle 기준으로 작성된 글입니다.

 

🌱  시간대 별로 데이터 개수 세기

▶️  요구사항

• 1시부터 24시까지 각 시간대 별로 데이터의 개수를 출력
• 데이터가 없는 시간대에도 0이라고 표시해서 데이터 출력

흔히 통계 데이터 조회에서 1시부터 24시까지 각 시간대별 방문 데이터 개수를 원하는 경우가 있다.
간단하게 생각하면 시간 데이터에서 시간만 추출해서 GROUP BY를 해주면 된다.
하지만 그렇게 되면
 
아래와 같이 데이터가 없는 시간대는 출력이 안된다.
 
 [ AS-IS ]

HOUR	COUNT
2	5
4	8

 
 
[ TO-BE ]
데이터가 없는 시간대에도 count 값을 0으로 출력하고 싶다.

HOUR	COUNT
1	0
2	5
3	0
4	8
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0
21	0
22	0
23	0
24	0

 
 

✔️  프로그래머스 : 입양 시각 구하기(2)

문제 링크

 
내 요구사항과 유사한 프로그래머스 문제의 예시를 가지고 하나씩 풀이해 보자.
(문제 풀이와 다르게 정말 간소화해서 작성했다.)

NAME	TYPE
ANIMAL_ID	VARCHAR(N)
DATETIME	DATETIME

 

⛳️  계획

하나씩 계획을 세워보자.

1. 우선 T1은 1부터 24시까지의 데이터를 가지고 있는 테이블이다.
2. T2는 각 동물을 입양한 시간을 가지고 있는 테이블이다.
3. 두 가지를 OUTER JOIN을 통해서 연결해 준다.
4. 데이터가 NULL 경우는 0으로 값을 치환해 준다.

 

⚙️ 구현

[ CONNECT BY ]

 
Oracle에서는 계층 데이터를 위해 CONNECT BY 절을 지원한다.
그리고 LEVEL이라는 칼럼이 존재하고 이것은 계층을 의미한다.
 

SELECT LEVEL AS HOUR 
FROM DUAL 
CONNECT BY LEVEL <= 24;

 
이것을 출력하면 아래와 같은 결과를 얻을 수 있다. LEVEL은 1부터 시작하고 CONNECT BY 절의 조건에 따라 순차적으로 증가한다.

LEVEL

1

2

3

...

24

 
T1 테이블은 준비가 됐다. T2 테이블 준비하러 가자!
 
 

[ 날짜 데이터 : TO_CHAR ]

 
현재 입양한 시간의 칼럼 데이터 타입은 datetime이다.
현재 원하는 1,2,3,4와 같은 데이터 형식으로 날짜 포맷의 변환을 해보자.
 

SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(*) AS COUNT 
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME, 'HH24');

 
날짜 데이터 변환은 정말 많이 사용한다. 이건 따로 포스팅해 보자.
그러면 T2 테이블도 완성했다.
 
 

[ OUTER JOIN ]

 
outer join은 내가 지금 구현하려고 하는 상황에서 아주 유용하다.
 

🐰 : 어우 조인도 포스팅해야지! 뭐야 블로그 소재 축제가 계속되네

🤖 : 바쁘게 움직여라 박주디!!

 
 
INNER JOIN인 경우, 양쪽 테이블에 공통인 데이터를 조인하게 된다.
즉, 동등한 조건이 있는 경우에 사용한다.
 
하지만 OUTER JOIN은 한 테이블에는 데이터가 있지만 한 테이블에는 데이터가 없는 경우에도 두 테이블 모두 출력하고 싶을 때 사용할 수 있다.
즉, INNER JOIN에 원하는 부분을 추가로 가져올 수 있다.
 
그래서 매핑되는 데이터가 없는 경우에는 NULL을 표시한다.
 
LEFT JOIN인지 RIGHT JOIN인지와
(+) 붙이는 방향은 다음과 같다.
 

• ANSI JOIN

T1
LEFT OUTER JOIN
T2
ON T1.HOUR = T2.HOUR

 
ANSI JOIN 방식에서는 조인시킬 데이터가 있는 테이블 방향을 가리킨다.
LEFT면 T1 기준. RIGHT면 T2 기준.
 

• Oracle JOIN

FROM T1, T2
WHERE T1.HOUR = T2.HOUR(+)

 
Oracle JOIN은 조인시킬 데이터가 없는 테이블 옆에 (+)를 붙인다.
즉, LEFT JOIN은 오른쪽에 (+) 부호를 붙이고, RIGHT JOIN은 왼쪽에 (+) 부호를 붙인다.
 
그래서 코드를 합쳐보면
 

• ANSI JOIN

FROM (SELECT LEVEL AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
    ) T1
LEFT OUTER JOIN
    (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, 
            COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
    ) T2
ON T1.HOUR = T2.HOUR

 

• Oracle JOIN

FROM (SELECT LEVEL AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
    ) T1
    , (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR,
              COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
    ) T2
WHERE T1.HOUR = T2.HOUR(+)

 
 
이렇게 조인하게 되면 아래와 같이 조인된다.

HOUR	COUNT
1	NULL
2	5
3	NULL
...	...
24	NULL

 
다 왔다!! 마무리해 보자!
 
 

[ NVL ]

 
COUNT를 NULL이 아닌 0으로 출력하자!
NVL 함수를 사용하면 NULL인 경우 원하는 값을 넣을 수 있다.

SELECT T1.HOUR, NVL(T2.COUNT, 0) AS COUNT

 
 

✨ 최종 완성 쿼리

• ANSI JOIN

SELECT T1.HOUR, NVL(T2.COUNT, 0) AS COUNT
FROM (SELECT LEVEL AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
    ) T1
LEFT OUTER JOIN
    (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, 
            COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
    ) T2
ON T1.HOUR = T2.HOUR

 

• Oracle JOIN

SELECT T1.HOUR, NVL(T2.COUNT, 0) AS COUNT
FROM (SELECT LEVEL AS HOUR 
    FROM DUAL 
    CONNECT BY LEVEL <= 24
    ) T1
    , (SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, 
              COUNT(*) AS COUNT 
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
    ) T2
WHERE T1.HOUR = T2.HOUR(+)

 
간단한 쿼리지만 단계별로 알아봤다!!
 
 

---

📌 Reference

• [프로그래머스] SQL 입양 시각 구하기(2) - Oracle