WITH 절 사용하기 (feat. PARTITION BY)

HackerRank 문제 Basic 부분은 얼추 다 풀어서 medieum 단계를 풀고 있는데 확실히 난이도가 급상승한 것 같다ㅠㅠㅠ

 

오늘은 한 문제밖에 풀지 못했지만 대충 알곤 있었지만 활용해보지 못한 문법들이 있어서 고민하고 또 정리하면서 시간을 보냈던 것 같다.

 

오늘 고민했던 문제! 

Occupations라는 주제의 문제이다.

 

 

지시 사항

---

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

 

샘플 입력 예시 & 출력 예시는 다음과 같다.

Sample Input

Sample Output

 

 

정답 코드

---

WITH PIVOT AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) AS ranking
               FROM Occupations )

SELECT 
    MAX(CASE WHEN Occupation = 'Doctor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Professor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Singer' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Actor' THEN NAME ELSE NULL END)
FROM PIVOT
GROUP BY ranking;

 

 

 

출력 결과

 

---

 

 

 

 

문제 풀이

---

우선 피봇 테이블을 만드는 문제 같아서 PIVOT 테이블을 만들어서 사용하고 싶었는데 이 문제 같은 경우 어떻게 어떻게 해보려 했지만 잘 되지 않았다. 혹시 PIVOT 테이블 사용해서 푸는 법 아시는 분은 댓글로 알려주세요! ㅠㅠ

 

먼저 정답 코드를 풀이하고, 다른 방법으로 푸는 방법도 추가로 적어보겠다.

정답 코드에서 WITH 절이 나오는데 사실 전에 재귀 쿼리에서 사용한 것도 그렇고 제대로 정리한 적이 없는 것 같아서 이번 기회에 정리해보았다.

 

 

 

 

WITH 절

---

WITH문의 사용하는 목적은 “Query의 전체적인 가독성을 높이고, 재활용할 수 있기”때문이다.

같은 서브 쿼리를 반복해서 사용하거나, 쿼리가 너무 복잡해져 가독성이 좋지 않을 때 사용하면 좋다.

이름을 가진 subquery로 정의한 후 사용하고, 대부분의 DBMS에서 지원하는 기능이다.

 

WITH 절 기본 구조

WITH 임시테이블명 AS (
	SUB QUERY)

 

기본 예제

SELECT A.DEPTNO, B.DNAME, A.SAL
FROM (SELECT DEPTNO, SUM(SAL) AS SAL FROM EMP GROUP BY DEPTNO) A, DEPT B
WHERE A.DEPTNO = B.DEPTNO

위와 같은 예제가 있다고 하자. 이때 FROM 절에 들어가는 SUB QUERY를 WITH 절로 만들고 싶다.

 

WITH EMP_W1 AS (
	SELECT DEPTNO, SUM(SAL) AS SAL
	FROM EMP
	GROUP BY DEPTNO
)

SELECT A.DEPTNO, B.DNAME, A.SAL
FROM EMP_W1 A, DEPT B
WHERE A.DEPTNO = B.DEPTNO

EMP_W1라는 가상의 테이블을 WITH 절로 만들고,

SUB QUERY를 AS 뒤에 넣는다.

 

이런 식으로 WITH 절을 따로 빼서 서브 쿼리를 정의하고 재활용해서 사용한다. 

그러면 다시 문제의 WITH 절을 살펴보자.

 

WITH PIVOT AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) AS ranking
               FROM Occupations )

“PIVOT”이라는 이름으로 WITH 절을 만들었다.

하지만 서브 쿼리를 더 면밀히 살펴봐야겠다.

 

 

 

 

 

서브 쿼리 살펴보기

---

SELECT *, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name)
FROM Occupations;

 

ROW_NUMBER() OVER (ORDER BY 정렬할 필드)

이 함수는 순위를 구하는 용도로 사용이 가능한데 같은 값에 대해여 무시한다는 특징이 있다.

순위를 반환해주는 다른 함수들(RANK, DENSE_RANK OVER)도 있긴 한데 오늘 이걸 다 풀어서 설명하면 양이 너무 많아질 것 같아서 따로 이 주제만 가지고 정리하기로 하고, 오늘은 ROW_NUMBER() OVER()에 대해서만 알아보자.

 

한마디로 인덱스처럼 정렬을 해주는 것인데 숫자를 정렬하면 오름차순(디폴트)으로 정렬하여 앞에 순서를 붙일 것이고, 문자면 알파벳순으로 정렬하여 숫자를 붙일 것이다.

 

OVER 뒤에는 (ORDER BY 기준 칼럼)을 넣는다.

 

 

PARTITION BY

이번 문제의 OVER 뒤를 보면 ORDER BY는 NAME인데, PARTITION BY가 추가되어있다. 이것의 의미는 GROUP BY처럼 OCCUPATION으로 그룹을 만들고 그 그룹 내에서 NAME을 기준으로 순서를 매겨라 라는 의미가 있다.

 

그래서 실행 결과를 보면 아래와 같다.

 

Actor, Doctor, Professor, (Singer) 순으로 파티션이 나눠져 이름 순으로 뒤에 순서가 붙게 된다.

 

실행 결과

Singer는 캡쳐과정에서 잘림

 

 

 

 

나머지 코드 확인

---

SELECT 
    MAX(CASE WHEN Occupation = 'Doctor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Professor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Singer' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Actor' THEN NAME ELSE NULL END)
FROM PIVOT
GROUP BY ranking;

MAX로 감싸준 이유는?

• 바로 '집계 함수'가 존재해야 GROUP BY를 사용할 수 있기 때문이다.
• 값이 존재하지 않는다면 NULL 값으로 처리해주기 위해 쓸모없는 MAX를 넣어준 것이다.
• STRING이라서 MAX가 들어간다고 딱히 출력 값이 변하지 않으며, MIN을 넣어도 반환 값은 변함없다.

 

GROUP BY ranking을 해준 이유는?

같은 rank를 가진 이름끼리 한 행에 넣어주기 위함이다.

결과 코드를 보면 1번 행에 해당하는 이름들은 각 파티션에서 rank 1을 갖고 있다.

2번 행도 마찬가지로 각 파티션에서 rank 2를 갖고 있으며, 없는 값에 대해서는 Null 값이 들어가게 “ELSE NULL” 조건을 추가해준다.

 

 

 

 

 

만약 다른 방법으로 WITH 절을 사용하지 않고 출력하고 싶다면?

---

SELECT 
    MAX(CASE WHEN Occupation = 'Doctor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Professor' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Singer' THEN NAME ELSE NULL END),
    MAX(CASE WHEN Occupation = 'Actor' THEN NAME ELSE NULL END)
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY name) AS ranking FROM Occupations) AS TEMP
GROUP BY ranking;

FROM 절에 WITH 절로 작성했던 내용을 그대로 넣어주면 되는데, 여기서 주의할 점은 이 서브 쿼리에 대해서 이름을 지어줘야 한다. 위의 예시에서는 AS TEMP라고 지어주었다.

 

 

 

 

 

 

 

 

REF

---

[Oracle] WITH절

[MySQL] HackerRank - Occupations