집계 함수는 행을 줄이고 윈도우 함수는 행을 남깁니다. 이 한 줄 차이가 SQL로 할 수 있는 일의 범위를 크게 넓혔습니다.
풀고 시작
문제 1. 공동 3등이 두 명 나왔을 때 다음 순위를 5등으로 매기는 함수와 4등으로 매기는 함수를 순서대로 짝지은 것은?
RANK는 같은 값에 같은 등수를 주고 그만큼 다음 등수를 건너뜁니다. DENSE RANK는 건너뛰지 않고 바로 다음 등수를 씁니다. ROW NUMBER는 값이 같아도 서로 다른 번호를 매겨 공동 등수를 만들지 않습니다.
행을 남긴다는 것
4강의 집계 함수는 여러 행을 하나로 줄입니다. 부서별 평균 급여를 구하면 부서 수만큼의 행만 남고 개별 사원은 사라지죠. 그런데 실무에서 자주 필요한 것은 이런 결과입니다. 사원 목록을 그대로 보여 주면서 각 사원 옆에 자기 부서 평균 급여를 붙여 주는 것.
옛 방식으로는 부서별 평균을 구하는 인라인 뷰를 만들어 다시 조인해야 했습니다. 윈도우 함수(window function)는 이 일을 한 문장으로 처리합니다. 행을 줄이지 않고, 각 행마다 정해진 범위를 보고 계산한 값을 옆에 붙여 줍니다.
문법의 뼈대는 이렇습니다. 함수 뒤에 OVER 절을 붙이고, 그 안에 PARTITION BY로 계산 범위를 나누고 ORDER BY로 범위 안의 순서를 정합니다. PARTITION BY가 GROUP BY의 역할을 하는데, 결정적으로 행을 줄이지 않는다는 점이 다릅니다.
순위 함수 세 자매
가장 자주 나오는 것이 순위 함수입니다. 셋의 차이를 정확히 알아야 합니다.
함수
같은 값 처리
공동 3등이 둘일 때 다음 등수
RANK
같은 등수를 주고 건너뜀
5등
DENSE RANK
같은 등수를 주고 건너뛰지 않음
4등
ROW NUMBER
같은 값도 서로 다른 번호
4번
RANK는 우리가 스포츠에서 보는 방식입니다. 공동 3등이 두 명이면 다음은 5등이죠. DENSE RANK는 등수를 촘촘히 매겨 빈 자리를 만들지 않습니다. ROW NUMBER는 순위가 아니라 일련번호입니다. 값이 같아도 하나씩 다른 번호를 주므로, 정렬 기준이 같은 행들 사이의 번호는 실행할 때마다 달라질 수 있습니다. 순서를 확정해야 한다면 정렬 기준을 더 추가해 동순위를 없애야 합니다.
이 밖에 NTILE은 지정한 개수의 그룹으로 균등 분할해 몇 번째 그룹인지를 돌려주고, CUME DIST와 PERCENT RANK는 누적 비율과 백분위를 돌려줍니다.
집계 함수를 윈도우로 쓰기
SUM, AVG, COUNT, MAX, MIN 같은 집계 함수에 OVER 절을 붙이면 그대로 윈도우 함수가 됩니다. 여기서 강력한 기능이 하나 나옵니다. 누적 합계입니다.
OVER 절 안에 ORDER BY를 쓰면 기본 범위가 첫 행부터 현재 행까지가 됩니다. 그래서 그냥 정렬 기준만 주어도 누적 합계가 나옵니다. 월별 매출에 ORDER BY를 걸고 SUM을 씌우면 누적 매출이 되는 것이죠. 반대로 ORDER BY 없이 PARTITION BY만 쓰면 파티션 전체가 범위가 되어 전체 합계가 모든 행에 똑같이 붙습니다.
범위를 직접 지정할 수도 있습니다. ROWS는 행의 개수로 범위를 세고, RANGE는 값의 범위로 셉니다. 앞의 두 행부터 현재 행까지의 이동 평균을 구하는 식이죠. 둘의 차이가 시험에 나옵니다. ROWS는 정렬 기준 값이 같은 행들을 각각 별개로 세고, RANGE는 값이 같은 행들을 한 덩어리로 봅니다. 그래서 같은 값이 여럿일 때 두 결과가 달라집니다.
행 순서 함수와 실전 감각
LAG와 LEAD는 현재 행을 기준으로 앞이나 뒤의 행 값을 가져옵니다. 전월 대비 증감을 구하는 문제가 이 함수 하나로 풀립니다. 앞 행이 없으면 값이 없는 상태가 되므로 기본값을 지정하는 인자를 함께 쓰는 것이 관례입니다.
FIRST VALUE와 LAST VALUE는 범위 안의 첫 값과 마지막 값을 가져옵니다. 여기에 함정이 있습니다. ORDER BY를 쓴 상태에서 기본 범위는 현재 행까지이므로, LAST VALUE가 파티션의 마지막 값이 아니라 현재 행의 값이 되어 버립니다. 파티션 끝까지 보려면 범위를 명시적으로 파티션 전체로 넓혀야 합니다. 실무에서 이 함정에 걸린 쿼리를 자주 봅니다.
마지막으로 윈도우 함수의 처리 위치를 짚어 두죠. 윈도우 함수는 WHERE와 GROUP BY와 HAVING이 끝난 뒤에 계산됩니다. 그래서 WHERE 절에서 윈도우 함수의 결과를 조건으로 쓸 수 없습니다. 순위가 3 이내인 행만 뽑으려면 8강에서 배운 인라인 뷰로 한 겹 감싸고 바깥에서 조건을 걸어야 합니다. 이 패턴이 다음 강의 Top N 쿼리와 곧바로 이어집니다.
인출 문제
문제 1. 윈도우 함수와 집계 함수의 가장 근본적인 차이는?
집계 함수는 여러 행을 하나로 줄이지만 윈도우 함수는 행을 그대로 남기고 계산 결과를 옆에 붙입니다. 그래서 개별 데이터와 요약값을 한 화면에 함께 보여 줄 수 있습니다.
문제 2. OVER 절에 ORDER BY만 지정하고 범위를 명시하지 않은 상태에서 SUM을 사용하면?
ORDER BY가 있으면 기본 범위가 첫 행부터 현재 행까지이므로 누적 합계가 나옵니다. 파티션 전체 합계를 모든 행에 붙이려면 ORDER BY를 빼거나 범위를 파티션 전체로 명시해야 합니다.
문제 3. WHERE 절에서 윈도우 함수의 결과를 조건으로 사용할 수 없는 이유는?
처리 순서상 윈도우 함수는 조건 절과 그룹 처리가 끝난 뒤에 계산됩니다. 그 결과로 걸러 내려면 인라인 뷰로 한 겹 감싸 바깥에서 조건을 걸어야 합니다.
문제 4. LAST VALUE 함수가 파티션의 마지막 값이 아니라 현재 행의 값을 돌려주는 경우가 생기는 이유는?
기본 범위가 첫 행부터 현재 행까지이므로 그 범위의 마지막은 곧 현재 행입니다. 파티션 끝까지 보려면 범위를 파티션 전체로 명시해 넓혀야 합니다.
더 풀기
출제기준의 세세항목을 따라 이 강의 범위에서 새로 낸 문제입니다. 모의고사도 여기에서 뽑습니다.
묶음 1
문제 1. 윈도우 함수의 가장 큰 특징은?
집계 함수는 행을 줄이지만 윈도우 함수는 줄이지 않습니다. 개별 값과 전체 맥락을 한 화면에서 함께 보게 해 줍니다.
문제 2. PARTITION BY가 하는 일로 가장 알맞은 것은?
PARTITION BY는 윈도우를 부서별처럼 구간으로 나눕니다. GROUP BY와 달리 행은 그대로 남습니다.
문제 3. 윈도우 함수의 OVER 절에서 ORDER BY가 하는 일로 가장 알맞은 것은?
윈도우 안의 순서와 결과 출력 순서는 별개입니다. 출력 순서는 질의 맨 끝의 ORDER BY가 정합니다.
문제 4. 급여가 300, 200, 200, 100인 네 사원에게 급여 내림차순으로 RANK를 매기면 세 번째 사원의 순위는?
이백을 받는 두 사원이 공동 이위이므로 세 번째 사원도 이위입니다. 값이 같으면 같은 순위를 받는 것이 RANK의 성질입니다.
문제 5. 같은 데이터에서 RANK를 매길 때 마지막 사원의 순위는?
RANK는 공동 순위 다음 등수를 건너뜁니다. 일위 하나와 이위 둘 다음은 사위입니다.
문제 6. 같은 데이터에서 DENSE_RANK를 쓰면 마지막 사원의 순위는?
DENSE_RANK는 등수를 건너뛰지 않습니다. 일위와 이위 다음이 삼위입니다.
문제 7. 같은 데이터에서 ROW_NUMBER를 쓰면 어떤 값이 나오나요?
ROW_NUMBER는 값이 같아도 무조건 서로 다른 일련번호를 매깁니다. 동점의 순서는 정렬 기준이 부족하면 보장되지 않습니다.
문제 8. 값이 같은데도 서로 다른 번호가 필요한 상황에서 쓰는 함수는?
페이지 나누기처럼 유일한 번호가 필요할 때 ROW_NUMBER를 씁니다.
문제 9. NTILE 함수를 가장 잘 설명한 것은?
상위 몇 퍼센트 같은 구간을 만들 때 씁니다. 행 수가 나누어떨어지지 않으면 앞쪽 그룹에 한 행씩 더 들어갑니다.
문제 10. 열 개의 행을 NTILE로 세 그룹으로 나누면 각 그룹의 행 수는?
나머지가 있으면 앞쪽 그룹부터 한 행씩 더 배정됩니다.
묶음 2
문제 1. 집계 함수를 윈도우 함수로 쓸 때 OVER 절을 비워 두면 어떤 범위가 되나요?
범위를 나누지 않았으므로 전체가 하나의 윈도우입니다. 각 행 옆에 전체 합계가 붙습니다.
문제 2. 각 사원의 급여와 그 사원이 속한 부서의 평균 급여를 한 행에 함께 보려면?
그룹으로 묶으면 사원별 행이 사라집니다. 윈도우 함수는 행을 남기므로 개별 급여와 부서 평균을 나란히 볼 수 있습니다.
문제 3. 윈도우 함수의 OVER 절에 ORDER BY를 넣고 집계 함수를 쓰면 어떤 결과가 되나요?
순서가 생기면 기본 범위가 시작부터 현재 행까지가 되어 누적 집계가 만들어집니다.
문제 4. 급여가 순서대로 100, 200, 300인 세 행에 누적 합계를 구하면 세 번째 행의 값은?
첫 행부터 현재 행까지를 더하므로 육백입니다. 앞의 두 행은 백과 삼백입니다.
문제 5. 윈도우 프레임에서 ROWS와 RANGE의 차이로 가장 알맞은 것은?
동점이 있을 때 결과가 갈립니다. RANGE는 같은 값의 행을 모두 포함하고 ROWS는 개수로만 셉니다.
문제 6. 정렬값에 동점이 있는데 누적 합계가 예상보다 크게 나온다면 가장 가능성이 높은 이유는?
동점 행이 모두 현재 행 취급을 받아 함께 더해집니다. 행 단위로 누적하려면 ROWS를 명시해야 합니다.
문제 7. 앞의 두 행과 현재 행의 평균을 구하는 이동 평균을 만들려면?
프레임을 명시해 범위를 좁히면 이동 평균이 됩니다. 지정하지 않으면 처음부터 누적됩니다.
문제 8. LAG 함수가 하는 일로 가장 알맞은 것은?
이전 값과 비교해 증감을 계산할 때 씁니다. 다음 행은 LEAD입니다.
문제 9. 첫 번째 행에서 LAG 함수의 결과는?
이전 행이 없으므로 NULL이 되고 세 번째 인자로 기본값을 줄 수 있습니다.
문제 10. 매출이 순서대로 100, 150, 120일 때 LAG로 이전 값을 가져와 증감을 구하면 세 번째 행의 증감은?
백이십에서 이전 값 백오십을 빼면 마이너스 삼십입니다.
묶음 3
문제 1. FIRST_VALUE 함수의 결과가 예상과 다를 때 가장 먼저 확인할 것은?
FIRST_VALUE는 시작이 고정이라 문제가 적지만 LAST_VALUE는 기본 프레임 때문에 현재 행이 나옵니다. 프레임을 끝까지로 지정해야 합니다.
문제 2. LAST_VALUE로 파티션의 마지막 값을 얻으려면?
기본 프레임은 현재 행에서 끝나므로 명시하지 않으면 현재 행의 값이 나옵니다.
문제 3. 윈도우 함수를 WHERE 절에서 쓸 수 있나요?
처리 순서상 조건 평가 시점에는 아직 계산되지 않았습니다. 순위로 걸러 내려면 한 번 감싸야 합니다.
문제 4. 부서별 급여 상위 세 명을 뽑으려면 어떤 구조가 필요한가요?
순위를 먼저 만들고 그 결과를 대상으로 걸러야 합니다. 이것이 윈도우 함수의 대표적인 활용입니다.
문제 5. 부서별 상위 세 명을 뽑을 때 동점을 모두 포함하고 싶다면 어떤 순위 함수가 알맞은가요?
ROW_NUMBER는 동점 중 하나만 남깁니다. 동점을 함께 보려면 순위를 같게 매기는 함수를 써야 합니다.
문제 6. 정확히 세 행만 필요하고 동점은 임의로 잘라도 된다면 알맞은 함수는?
ROW_NUMBER는 항상 서로 다른 번호를 매기므로 행 수를 정확히 통제할 수 있습니다.
문제 7. 윈도우 함수의 PARTITION BY와 GROUP BY를 함께 쓸 수 있나요?
그룹 결과가 곧 행이므로 그 행들에 윈도우를 적용할 수 있습니다. 부서별 합계에 전체 대비 비중을 붙이는 식입니다.
문제 8. 각 부서 합계가 전체 합계에서 차지하는 비율을 구하려면?
그룹 결과에 전체 합계를 윈도우로 붙이면 한 번의 질의로 비율을 구할 수 있습니다.
문제 9. 윈도우 함수에서 NULL 값은 순위 계산에서 어떻게 취급되나요?
NULL도 정렬 대상이므로 순위를 받습니다. 위치가 중요하다면 NULL 위치를 명시해야 합니다.