041 데이터분석 함수로 순위 출력하기 (RANK)
데이터의 순위를 출력하기 위해서 RANK 함수를 사용합니다. RANK() 뒤에 OVER 다음에 나오는 괄호에 안에 출력하고 싶은 데이터를 정렬하는 SQL 문장을 넣으면 그 컬럼 값에 대한 데이터의 순위가 출력됩니다. 또한 오름차순, 또는 내림차순에 대한 옵션을 사용자가 설정할 수 있습니다.
예를 들어 'column' 컬럼에 대해여 순위에 대한 RANK 함수를 사용하기 위해서 다음과 같이 사용합니다.
RANK() OVER (ORDER BY 'column' DESC/ESC)
EX ) 직업이 SALESMAN인 사원들의 이름, 직업, 커미션, 커미션의 순위를 출력하라
위 문제에서 직업을 SALESMAN으로 제한 하기 때문에 조건문(WHERE)에 이에 대한 조건문을 작성합니다. 이와 같은 점을 반영하여 위 문제에 대한 SQL을 작성하면 다음과 같습니다.
SELECT ENAME, JOB, COMM, RANK() OVER(ORDER BY COMM DESC) 순위
FROM EMP
WHERE JOB = 'SALESMAN';
Q. 직업이 ANALYST, MANAGER인 사원들의 이름, 직업, 월급, 월급의 순위를 출력하라
문제에서 직업을 ANALYST, MANAGER인 사원에 한정하므로 이에 대한 정보를 조건문(WHERE)에 반영하여 작성합니다.
SELECT ENAME, JOB, SAL, RANK() OVER (ORDER BY SAL DESC) 순위
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');
-- 또는 WHERE JOB = ' ANALYST' OR JOB = 'MANAGER'
위 문제에서 출력된 순위는 1등이 두명이기 때문에 2등이 출력되지 않고 바로 3등이 출력됩니다. 만약 위와 같은 상황에서 바로 2등을 출력하고 싶은 경우 다음에 배울 DENSE_RANK 함수를 이용하면 됩니다.
EX ) 직업별로 월급이 높은 순서대로 순위를 부여해서 각각 출력하라
직업별로 순위를 매길경우 우리가 배웠던 GROUP BY 또는 ORDER BY 문을 생각할 수 있겠지만, RANK 함수 내에서 특정 컬럼별로 순위를 매길때와는 다른 결과를 출력합니다.
1. ORDER BY 문을 사용해 정렬할 경우
SELECT ENAME, JOB, SAL, RANK() OVER (ORDER BY SAL DESC) 순위
FROM EMP
ORDER BY JOB;
ORDER BY 할 경우 직업별로 묶여 출력하기는 하지만 순위를 보면 직업에 상관없이 전체 데이터에서의 순위를 출력하는 것을 확인할 수 있습니다. 따라서 위 문제와 같은 경우 다른 방법으로 출력해야 합니다.
2. RANK 함수에서 PARTITION 할 경우
SELECT ENAME, JOB, SAL, RANK() OVER (PARTITION BY JOB
ORDER BY SAL DESC) 순위
FROM EMP ;
두 가지 모두 직업 순으로 정렬하여 출력하지만 1번의 경우 전체 데이터에 대한 순위를 출력하고, 2번의 경우 직업별로 순위를 매겨 출력하는 것을 확인할 수 있습니다.
042 데이터분석 함수로 순위 출력하기 (DENSE_RANK)
데이터의 순위를 상세하게 출력하기 위해서 DENSE_RANK 함수를 사용합니다. RANK 함수에서는 순위 1위가 두 명이어서 다음에 바로 3위를 출력했지만, 같은 경우 DENSE_RANK 함수를 사용할 경우 3위 대신 2위로 출력합니다.
Q. 직업이 ANALYST, MANAGER인 사원들의 이름, 직업, 월급, 월급의 순위를 출력하시오, 단 순위가 1위인 사원이 두 명이 있을 경우 다음 순위가 3위로 출력되지 않고 2위로 출력하시오
SELECT ENAME, JOB, SAL, RANK() OVER (ORDER BY SAL DESC) RANK_순위,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK순위
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');
RANK 함수를 이용한 순위와 DENSE_RANK 함수를 이용한 순위를 비교하면 위와 같다.
Q. 81년도에 입사한 사원들의 직업, 이름, 월급, 월급 순위를 출력하시오. 단 월급 순위는 직업별로 월급이 높은 순서대로 순서를 부여하시오
WHERE 조건 : 81년도에 입사(HIREDATE)한 사원 → WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/31'
RANK 조건 : 직업별로 월급이 높은 순서 → PARTITION BY JOB ORDER BY SAL DESC
SELECT ENAME, JOB, SAL, DENSE_RANK() OVER (PARTITION BY JOB
ORDER BY SAL DESC) 순위
FROM EMP
WHERE HIREDATE BETWEEN '81/01/01' AND '81/12/31';
DENSE_RANK() WITHIN GROUP ()
DENSE_RANK 함수는 바로 다음에 나오는 괄호 안에 값을 넣을 경우, 이 값이 데이터 전체에서의 순위가 어떻게 되는지 출력하는 쿼리입니다. WITHIN은 '~이내' 라는 뜻으로 WITHIN GROUP은 DENSE_RANK 바로 다음의 괄호에 들어가는 값이 어느 그룹 이내에서의 순위가 어떻게 되는지 확인할 수 있는 함수입니다. 다음 예제를 통해 좀더 자세히 알아보겠습니다.
EX ) 월급(SAL)이 2975인 사원이 사원테이블(EMP)에서 월급의 순위가 어떻게 되는 지 출력하시오
SELECT DENSE_RANK(2975) WITHIN GROUP (ORDER BY SAL DESC) 순위
FROM EMP;
Q. 입사일이 81년 11월 17일 인 사원은 사원테이블 전체 사원들 중 몇 번째로 입사한 것인지 출력하시오
검색하려는 입사일을 DENSE_RANK 괄호 한에 입력하고 WITHIN GROUP 을 이용해 사원 테이블 내 순위를 확인할 수 있습니다. 주의할 점은 몇 번째로 입사했는지가 문제이기 때문에 정렬기준이 내림차순(DESC)이 아닌 오름차순(ASC)으로 정렬합니다.
SELECT DENSE_RANK('81/11/17') WITHIN GROUP (ORDER BY HIREDATE ASC) 순위
FROM EMP;
043 데이터분석 함수로 등급 출력하기 (NTILE)
데이터의 등급을 출력하기 위해서는 NTILE 함수를 사용합니다.
NTITLE(n) OVER (ORDER BY ____ DESC/ASC (NULLS LAST))NTITLE 다음의 괄호에는 나눌 등급의 수를 입력합니다. 예를 들어 3등급으로 나눌 경우 NTITLE(3) 과 같이 입력할 수 있습니다.
EX ) 직업이 ANALYST, MANAGER, CLERK인 사원의 이름과 월급, 직업, 월급의 등급을 출력하시오. 단 월급의 등급은 4등급으로 나눠 출력합니다.
SELECT ENAME, JOB, SAL,
NTILE(4) OVER (ORDER BY SAL DESC NULLS LAST) 등급
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER', 'CLERK');
4등급의 경우 1등급(0%~25%), 2등급(25%~50%), 3등급(50%~75%), 4등급(75%~100%)로 나눠지며, 5등급의 경우 같은 원리로 등급이 나눠집니다.
NULLS LAST
NULLS LAST의 경우 NULL 값을 맨 아래 출력하는 경우 입력합니다. 만약 NULL 값을 맨 처음에 출력하고 싶은 경우에는 생략합니다.
044 데이터분석 함수로 순위의 비율 출력하기(CUME_DIST)
전체 데이터에서 특정 데이터의 순위의 비율을 출력하기 위해서 CUME_DIST 함수를 사용합니다.
EX ) 이름과 월급, 월급의 순위, 월급 순위 비율을 출력하시오
순위는 RANK와 DENSE_RANK 함수 모두를 사용하여, 순위의 비율은 CUME_DIST함수를 사용하여 출력해 보겠습니다.
SELECT ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) AS RANK_순위,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK_순위,
CUME_DIST() OVER (ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;
위의 결과에서 순위는 1등부터 14등 까지 있다.(RANK 기준) 사원 KING의 CUME_DIST의 값이 0.071428.. 의 값은 1/14로 계산된 결과입니다. 따라서 7등은 7/14 로 0.5, 14등은 14/14로 1이 되는 것을 확인할 수 있습니다. 즉 CUME_DIST의 값은 RANK를 기준으로 비율을 정한다고 할 수 있습니다.
만약 어떤 순위의 사원이 한 명이면 전체 등수로 해당 등수를 나눠서 계산합니다. 같은 순위의 사원이 여러 명 있으면 그중 마지막 순위로 계산합니다. 예를 들어 위의 결과에서 2등이 2명이므로 3을 14로 나눈 값인 0.21428..이 되는 것을 알 수 있습니다.
Q. 사원들의 이름, 직업, 월급, 직업별로 월급의 CUME_DIST을 출력하시오
직업별로 나누어 순위의 비율을 계산해야 하므로 PARTITION을 활용합니다.
SELECT ENAME, JOB, SAL, RANK() OVER (PARTITION BY JOB ORDER BY SAL) AS 순위,
CUME_DIST() OVER (PARTITION BY JOB ORDER BY SAL) CUME_DIST
FROM EMP;
045 데이터분석 함수로 데이터를 가로로 출력하기(LISTAGG)
특정 컬럼에 속한 데이터를 가로로 출력하기 위해서 LISTAGG 함수를 사용합니다.
EX ) 직업을 출력하고, 직업 옆에 해당 직업에 해당하는 사원들의 이름을 가로로 출력하시오
SELECT JOB, LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEE
FROM EMP
GROUP BY JOB;LISTAGG 함수는 데이터를 가로로 출력하는 함수입니다. LISTAGG 바로 옆 괄호에는 나열할 컬럼과 구분자를 입력합니다. 만약 예제와 다르게 슬래쉬(/)로 구분하고자 하는 경우 LISTAGG(ENAME,'/') 와 같이 입력합니다.또한 WITHIN GROUP 이후 괄호에는 출력할 데이터를 입력합니다. 위 예제의 경우 ORDER BY ENAME 이므로 이름이 A,B,C 순으로 정렬되어 출력된 것을 확인할 수 있습니다.
* LISTAGG와 GROUP BY 절이 필수로 기술되어야 합니다. GROUP BY 절은 그루핑의 기준이 되는 컬럼을 입력합니다.
Q. 부서 번호를 출력하고, 부서 번호 옆에 해당 부서 번호에 해당하는 사원들의 이름을 가로로 출력하시오.
부서 번호가 기준이 되어 사원들의 이름을 가로로 정렬하므로
→ LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) GROUP BY DEPTNO
SELECT DEPTNO, LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEE
FROM EMP
GROUP BY DEPTNO;
연결 연산자를 이용하면 다른 컬럼의 정보도 함께 확인할 수 있습니다. 예를 들어 부서 번호를 기준으로 사원들의 이름을 가로로 출력하고, 사원 이름 옆에 사원 번호를 함께 출력해 보겠습니다.
SELECT DEPTNO, LISTAGG(ENAME||'(' ||EMPNO|| ')', ',' ) WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;
046 데이터분석 함수로 바로 전 행과 다음 행 출력하기 (LAG, LEAD)
데이터의 바로 전 행과 바로 다음 행을 출력하기 위해서 LAG와 LEAD함수를 사용합니다.
EX ) 직업이 ANALYST, MANAGER 인 사원의 사원 번호, 이름, 월급을 출력하고, 그 옆에 바로 전 행의 월급을 출력하고, 또 옆에 바로 다음행의 월급을 출력해 보겠습니다.
SELECT EMPNO, ENAME, SAL,
LAG(SAL, 1) OVER (ORDER BY SAL ASC) AS 전_행,
LEAD(SAL,1) OVER (ORDER BY SAL ASC) AS 다음_행
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');
LAG 함수는 바로 전 행의 데이터를 출력하는 함수입니다. 숫자 1을 사용하면 바로 전 행, 숫자 2를 사용하면 전 전 행이 출력됩니다.
LEAD 함수는 바로 다음 행의 데이터를 출력하는 함수입니다. 숫자 1을 사용하면 바로 다음 행, 숫자 2를 사용하면 다음 다음 행이 출력됩니다.
Q. 직업이 ANALYST 또는 MANAGER인 사원들의 사원 번호, 이름, 입사일, 바로 전에 입사한 사원의 입사일, 바로 다음에 입사한 사원의 입사일을 출력하시오.
검색 조건 : 직업이 ANALYST 또는 MANAGER → WHERE JOB IN ('ANALYST', 'MANAGER')
바로 전에 입사한 사원의 입사일 → LAG(HIREDATE,1) OVER (ORDER BY HIREDATE ASC) AS 바로_전_입사 바로 다음에 입사한 사원의 입사일 → LEAD(HIREDATE,1) OVER (ORDER BY HIREDATE) AS 바로_다음_입사
SELECT EMPNO, ENAME, HIREDATE,
LAG(HIREDATE, 1) OVER (ORDER BY HIREDATE ASC) AS 바로_전_입사,
LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE ASC) AS 바로_다음_입사
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');
Q. 부서 번호, 사원 번호, 이름, 입사일, 바로 전에 입사한 사원의 입사일을 출력하고 바로 다음에 입사한 사원의 입사일을 출력하는데, 부서 번호별로 구분해서 출력하시오.
바로 전에 입사한 사원의 입사일, 바로 다음에 입사한 사원의 입사일 모두 부서 번호별로 구분하여 출력해야 하므로 LAG함수와 LEAD 함수 모두에 PARTITION을 기술하여 작성합니다.
→ OVER (PARTITION BY DEPTNO ORDER BY HIREDATE ASC)
SELECT DEPTNO, ENAME, HIREDATE,
LAG(HIREDATE, 1) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE ASC) AS 바로_전_입사,
LEAD(HIREDATE, 1) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE ASC) AS 바로_다음_입사
FROM EMP;
