오늘은 분석함수와 TOP-N 분석법에 대해 배웠다.
분석함수
: 행(row)에 대해 특정 그룹별로 집계값을 산출할 때 주로 사용한다. 분석함수에 사용되는 함수는 주로 그룹함수(sum, avg, max, min, ...)인 경우가 많다.
기본적인 형태는 다음과 같다.
분석함수 over()
분석함수 over(ORDER BY 컬럼)
분석함수 over(partition by 컬럼)
분석함수 over(partition by 컬럼1 ORDER BY 컬럼2)실제 예시를 가지고 하나씩 체크해보자.
over()
SELECT
employee_id,
salary,
avg(salary) over() 전체평균
FROM employees;employees 테이블에서 employee_id, salary, 그리고 급여의 전체평균을 출력하라는 뜻이다. (아래에 후술할) partition by를 쓰지 않고 분석함수 over()를 사용하면 쿼리 결과 전체를 집계한다.
over(ORDER BY 컬럼)
SELECT
employee_id,
salary,
sum(salary) over() 전체합
sum(salary) over(ORDER BY employee_id) 누적합
FROM employees;employees 테이블에서 employee_id, salary, 그리고 급여의 전체합과 누적합을 출력하라는 뜻이다. 분석함수에서 'ORDER BY 컬럼'을 사용하면 해당 컬럼을 기준으로 쿼리의 결과가 정렬된다. 예제에서는 employee_id를 기준으로 정렬하여 급여의 합을 구하라고 했는데, 이 말인 즉슨 누적합계를 구하라는 뜻이다. 주로 누적합계, 누적주문금액 등을 구할 때 분석함수 over(ORDER BY 컬럼)을 많이 이용한다.
over(partition by 컬럼)
SELECT
employee_id,
salary,
department_id,
sum(salary) over() 전체합,
sum(salary) over(partition by department_id) 부서별합
FROM employees;employees 테이블에서 employee_id, salary, 부서 id, 그리고 급여의 전체합과 부서별합을 출력하라는 뜻이다. 분석함수에서 'partition by 컬럼'을 사용하면 해당 컬럼을 기준으로 기존의 데이터와 집계된 값을 함께 나란히 볼 수 있다.
partition by는 그룹핑(군집)을 한다는 점에서 GROUP BY와 유사하다. 그러나 PARTITION BY는 GROUP BY와 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지시킨다는 것이 가장 큰 차이점이다. GROUP BY를 하면 해당 컬럼을 기준으로 군집하기 때문에 여러 행의 결과의 집계값으로 결과가 출력되었는데, partition by는 군집을 시키지만 기존 행에 대한 정보는 그대로 남는다.
over(partition by 컬럼1 ORDER BY 컬럼2)
SELECT
employee_id,
salary,
department_id,
sum(salary) over() 전체합,
sum(salary) over(partition by department_id) 부서별합,
sum(salary) over(partition by department_id ORDER BY employee_id) 부서별누적합
FROM employees;employees 테이블에서 employee_id, salary, 부서 id, 그리고 급여의 전체합과 부서별합과 부서별 누적합을 출력하라는 뜻이다. 분석함수에서 'partition by 컬럼1 ORDER BY 컬럼2'를 사용하면 컬럼1을 기준으로 기존의 데이터와 집계된 값을 함께 나란히 보고, 컬럼2를 기준으로 쿼리 결과가 정렬된다.
TOP-N
: 컬럼에서 상위 n개의 값 또는 하위 n개의 값을 요청할 때 쓰는 분석법.
아래에 후술하겠지만, TOP-N 분석법을 이용하려면 rank()나 dense_rank() 함수를 이용하는 것이 훨씬 수월하다. 그렇지만 일단, 해당 순위 분석함수들을 사용하지 않고 급여를 가장 많이 받고 있는 사원 상위 10명의 사원 id와 연봉을 출력하자고 가정해보자.
우선 급여를 기준으로 내림차순 정렬해본다. 나중에 이 쿼리문을 INLINE VIEW로 만들어서 메인 쿼리의 FROM 절에 넣어보자.
SELECT employee_id, salary
FROM employees
ORDER BY 2 desc;
이 결과를 가지고 상위 10개의 값을 출력한다고 했을 때, 한 가지 알아야 할 것이 있다. 바로 rownum 가상컬럼이다. rownum은 SELECT 결과를 화면에 출력할 때의 fetch 번호를 말한다.
rownum과 INLINE VIEW를 이용해서 SELECT 문을 작성해보자.
SELECT rownum, employee_id, salary
FROM (SELECT employee_id, salary
FROM employees
ORDER BY 2 desc)
WHERE rownum <= 10;
rownum 가상컬럼을 이용하면 fetch 번호가 나열됨으로 인해 WHERE 절로 조건을 걸어서 데이터를 출력해줄 수는 있지만, 사실 이 방법은 그다지 좋은 방법이 아니다. 왜냐하면 rownum을 사용하면 동일값(동일순위)에 대한 작업이 불가능하기 때문이다.
때문에 동일한 순위를 처리할 땐 rank()나 dense_rank()와 같은 순위 분석함수들을 이용하는 것이 훨씬 적합한 방법이다.
rank()
: 순위를 구하는 함수, 동일한 순위가 있을 경우 다음 순위의 갭이 생긴다.
rank() over(ORDER BY 컬럼)위의 예제를 rank()를 이용하여 풀어보자.
급여를 가장 많이 받고 있는 사원 상위 10명의 사원 id와 연봉을 출력하자고 가정해보자.
SELECT
employee_id,
salary,
rank() over(ORDER BY salary) rank
FROM employees
WHERE rank <= 10;
그러나 이렇게 풀면 오류가 발생한다. 왜냐하면 전체 SQL문을 해석할 때 SELECT 절보다 WHERE 절이 먼저 돌아가기 때문에, SELECT 절에서 열 별칭으로 쓰인 rank를 WHERE 절에서 쓴다고 했을 때 이게 무슨 rank인지 WHERE 절 입장에선 이해하지 못하기 때문이다. 따라서 이럴 땐 INLINE VIEW를 이용하는 것이 좋다.
SELECT *
FROM (SELECT
employee_id,
salary,
rank() over(ORDER BY salary) rank
FROM employees)
WHERE rank <= 10; 
rank()는 동일한 순위가 있을 경우 다음 순위의 갭이 생긴다고 했으므로, 예제에서 2위가 2명이므로 그 다음 순위는 4위로 출력된다. 그리고 WHERE 절에서 rank가 10 이하인 값들만 출력한다고 했으므로 rank()로 추출한 10위까지의 결과 건수는 딱 10개로 떨어진다.
dense_rank()
: 순위를 구하는 함수, 동일한 순위가 있더라도 연이은 순위를 구한다.
dense_rank() over(ORDER BY 컬럼)위의 예제를 dense_rank()를 이용하여 풀어보자.
SELECT *
FROM (SELECT
employee_id,
salary,
dense_rank() over(ORDER BY salary) rank
FROM employees)
WHERE rank <= 10;
dense_rank()는 동일한 순위가 있더라도 연이은 순위를 구한다고 했으므로, 예제에서 2위가 2명이어도 그 다음 순위는 3위로 출력된다. 그리고 WHERE 절에서 rank가 10 이하인 값들만 출력한다고 했으므로 dense_rank()로 추출한 10위까지의 결과 건수는 10 이하인 값 모두 출력되어서 10개가 아닌 15개로 나타난다.
rank()나 dense_rank()를 사용할 땐 순위를 구하므로 반드시 over()의 괄호 안에 ORDER BY 절로 정렬해야하고, 정렬 기준 컬럼은 위치표기법으로 나타낼 수 없으니 반드시 컬럼의 이름을 정확히 명시해준다.
