마농의 SQL 백문백답: 단순하고 쉽게 작성하는 SQL 노하우 [10회]

분석함수(Analytic Function)(6)

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다.

앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 시간에는 전체 대비 비율을 구하는 분석함수 RATIO_TO_REPORT 함수를 배웠다. 그리고 분석함수의 WINDOW 절에서 RANGE를 사용하여 특정 기간만큼의 합계를 구하는 방법을 배웠다.

이번 시간에는 분석함수를 응용하여 복잡한 문제를 해결하는 방법을 소개하겠다.

[질문 42] 연속된 날짜를 하나의 기간으로 묶어서 표현하고 싶어요.

이 문제는 상당히 어렵다. 지금까지 배웠던 분석함수에 응용력과 창의력을 더해야만 문제를 풀 수 있을 것이다. 아래는 테이블 원본과 결과표다.

원본은 번호별로 날짜가 저장된 테이블이며, 결과는 번호별로 연속된 날짜를 하나로 묶어 표현한 것이다.

원본에 있는 1월 1일, 1월 2일, 1월 3일 연속된 3개 일자 3행(Row)을 1월 1일부터 1월 3일까지 1행(Row)으로 표현하는 문제다. 지금까지 배웠던 분석함수를 이용해 풀 수 있는 문제이지만, 어떤 함수를 어떻게 사용해야 문제를 풀 수 있는지 막막할 것이다.

우선 연속된 날짜인지 아닌지를 판별해야 할 것이다. 이를 위해서는 이전 날짜와 현재 날짜를 비교해야 할 것이다.

SELECT no, dt
, DECODE(dt - 1, LAG(dt) OVER(PARTITION BY no ORDER BY dt), 0, 1) flag
FROM t
;

분석함수 LAG를 이용해 이전 행의 날짜를 가져왔다 이를 현재행의 날짜와 비교하는데 현재 날짜보다 하루 전날 인지를 판단한다. DECODE 문을 이용하여 이전 일자가 하루 전날이면(즉 연속일자이면) 0을, 아니면 1을 조회한다. 앞 날짜와 연속되는 날짜는 0으로 표현되고 불연속되는 지점은 1로 표현된다.

SELECT no, dt
, flag
, SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp
FROM (SELECT no, dt
, DECODE(dt - 1, LAG(dt) OVER(PARTITION BY no ORDER BY dt), 0, 1) flag
FROM t
)
;

이번에는 FLAG 값을 순차적으로 누적하여 합산하여 GRP 항목을 구했다. 이 항목의 값을 보면 연속된 날짜끼리 동일한 값을 가지는 것을 알 수 있다. 이 항목을 기준으로 GROUP BY 하여 집계한다면 원하는 결과를 얻을 수 있다.

SELECT no
, MIN(dt) sdt
, MAX(dt) edt
, COUNT(*) cnt
FROM (SELECT no, dt
, SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp
FROM (SELECT no, dt
, DECODE(dt - 1, LAG(dt) OVER(PARTITION BY no ORDER BY dt)
, 0, 1) flag
FROM t
)
)
GROUP BY no, grp
ORDER BY no, sdt
;

LAG를 이용해 이전 날짜를 가져오고, 이를 이용해 연속여부를 판별하는 FLAG 항목을 만들어 냈다. 이 FLAG 항목을 누적 합산하여 그룹기준 항목인 GRP를 만들어 냈다. 이후 이 GRP를 기준으로 집계하면 결과가 도출된다. LAG OVER()와 SUM() OVER()가 사용됐다.

여러 단계를 거쳐야만 해결되는 방법이다. 좀 더 간단한 방법은 없을까 이번에는 다른 방식으로 문제에 접근해 보자.

SELECT no, dt
, ROW_NUMBER() OVER(PARTITION BY no ORDER BY dt) rn
, dt - ROW_NUMBER() OVER(PARTITION BY no ORDER BY dt) grp
FROM t
;

번호별로 ROW_NUMBER 를 이용하여 날짜 순서대로 순번을 부여했다(RN). 이 순번을 날짜에서 차감했다. 그 결과 GRP라는 날짜항목이 생성됐다. 그런데 이 GRP 항목을 잘 살펴보면 연속된 날짜끼리 같은 값을 갖고 있는 것이 보인다.

앞선 방식과는 전혀 다른 방법, 다른 타입으로 GRP 항목이 도출됐다. 도출 과정, 도출 결과는 다르지만 공통점은 연속된 날짜끼리 동일한 값을 가진다는 것이다. 이 값을 이용하여 그룹바이 집계한다면, 또한 원하는 결과가 도출될 것이다.

SELECT no
, MIN(dt) sdt
, MAX(dt) edt
, COUNT(*) cnt
FROM (SELECT no, dt
, dt - ROW_NUMBER() OVER(PARTITION BY no ORDER BY dt) grp
FROM t
)
GROUP BY no, grp
ORDER BY no, sdt
;

이번 방법은 순차적으로 증가하는 날짜에서 순차적으로 증가하는 숫자를 빼면 동일한 날짜가 나오는 특성을 이용해 문제를 푼 것이다. 순차적으로 증가하던 날짜, 즉 연속적인 날짜가 갑자기 연속이 끊어지게 되면 다른 날짜가 나오게 된다. 이러한 특성을 이용하여 앞선 방법보다 도출 과정을 좀 더 간결하게 줄였다.

순번을 구하기 위해 우리는 ROW_NUMBER라는 분석함수를 이용했다. 그런데 위에서 이용한 특성을 구하기 위해 반드시 분석함수를 이용해야 할까 우리는 ROW_NUMBER와 유사한 ROWNUM을 주목해 볼 필요가 있다.

이번에는 ROWNUM을 이용해 문제를 풀어보자.

SELECT no, dt
, ROWNUM rn
, dt - ROWNUM grp
FROM (SELECT no, dt FROM t ORDER BY no, dt)
;

ROW_NUMBER를 이용해 GRP 항목을 구한 것과 유사한 결과다. ROW_NUMBER을 이용할 때는 PARTITION BY 구문을 사용할 수 있어 NO 별로 순번을 구했지만, ROWNUM 이용 시에는 그룹별 순번을 구할 수는 없고 계속 증가만 할 것이다(RN). RN과 GRP 값은 비록 달라졌지만, 여전히 연속된 날짜를 묶는 기준 역할은 충실히 수행할 수 있는 값이다.

앞선 시간에 배웠던 분석함수의 특징 중 하나를 기억하는가 분석함수에서는 집계함수를 이용할 수 있지만, 집계함수에서 분석함수를 이용할 수는 없었다. SUM(SUM(sal)) OVER()는 가능하다. SUM(SUM(sal) OVER())는 불가능하다.

ROW_NUMBER 사용 시에는 분석함수를 바로 집계에 이용할 수 없기 때문에 우리는 인라인뷰를 이용해야만 했다. 하지만 ROWNUM은 분석함수가 아니므로 인라인뷰 없이 집계에 바로 사용 가능하다.

SELECT no
, MIN(dt) sdt
, MAX(dt) edt
, COUNT(*) cnt
FROM (SELECT no, dt FROM t ORDER BY no, dt)
GROUP BY no, dt - ROWNUM
;

GRP를 구한 계산식이 바로 GROUP BY 구문에 사용됐다. 이로써 단계가 한 단계 줄어 쿼리가 좀 더 간결해졌다. 또한 분석함수가 사용되지 않고도 문제를 풀어냈다.

물론, 여기서도 인라인뷰가 사용되긴 했다. ROWNUM의 특성상 정렬된 결과의 순번을 얻으려면, 인라인뷰 안에서의 정렬이 필요하기 때문이다.

문제를 해결하기 위한 다양한 여러 가지 방법을 살펴보았다.

정리하며

이번 시간에는 하나의 문제를 여러 가지 방법을 이용하여 풀어보았다. 답안을 보면 간단하지만 결코 만만하지 않은 문제다. 데이터의 특성을 파악하고 이를 적절히 이용해야만 해결할 수 있다.

그동안 몇 회에 걸쳐 분석함수를 기본적인 사용법을 다뤘으며 기본 사용법 외에 문제 해결능력을 기르기 위해 이번 문제를 제시했다. 분석함수는 이번 회로 마무리하고 다음 시간에는 다른 주제를 다루어 보고자 한다.