예제 테이블 데이터

EMP 테이블:

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

1. 그룹함수

그룹함수는 여러 행으로부터 하나의 결과값을 반환하는 함수다. 주로 GROUP BY절과 함께 사용되며, NULL 값은 계산에서 제외된다.

함수	설명	파라미터	예시	결과
COUNT	행의 개수를 반환한다. NULL 값은 세지 않는다. COUNT(*)는 모든 행의 수를 센다.	컬럼 또는 *	SELECT COUNT(COMM) FROM EMP;	4 (NULL이 아닌 COMM 값의 수)
SUM	합계를 계산한다.	숫자 컬럼	SELECT SUM(SAL) FROM EMP;	29025 (모든 직원의 급여 합계)
AVG	평균을 계산한다.	숫자 컬럼	SELECT AVG(SAL) FROM EMP;	2073.21 (모든 직원의 급여 평균)
MAX	최댓값을 반환한다.	컬럼	SELECT MAX(SAL) FROM EMP;	5000 (최고 급여)
MIN	최솟값을 반환한다.	컬럼	SELECT MIN(SAL) FROM EMP;	800 (최저 급여)
VARIANCE	분산을 계산한다.	숫자 컬럼	SELECT VARIANCE(SAL) FROM EMP;	1398313.87 (급여의 분산)
STDDEV	표준편차를 계산한다.	숫자 컬럼	SELECT STDDEV(SAL) FROM EMP;	1182.5 (급여의 표준편차)

그룹별 집계 예제

SELECT DEPTNO, COUNT(*) 직원수, SUM(SAL) 급여합계, AVG(SAL) 평균급여,
       MAX(SAL) 최대급여, MIN(SAL) 최소급여
FROM EMP
GROUP BY DEPTNO;

결과

DEPTNO	직원수	급여합계	평균급여	최대급여	최소급여
10	3	8750	2916.67	5000	1300
20	5	10875	2175.00	3000	800
30	6	9400	1566.67	2850	950

GROUP BY 함수

함수	설명	파라미터	예시	결과
ROLLUP	지정된 컬럼의 소계와 총계를 계산한다. 지정한 컬럼 순서대로 계층적 집계 수행	컬럼 리스트	SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);	부서별 소계와 전체 총계 포함
CUBE	지정된 컬럼의 모든 조합에 대한 소계와 총계를 계산한다.	컬럼 리스트	SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB);	모든 조합에 대한 소계와 총계 포함
GROUPING SETS	지정된 컬럼 조합에 대한 그룹화만 수행한다.	컬럼 리스트 또는 컬럼 그룹	SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB);	부서별, 직무별 그룹화만 수행

ROLLUP

SELECT DEPTNO, JOB, SUM(SAL) AS 급여합계
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

결과

DEPTNO	JOB	급여합계
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10	NULL	8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20	NULL	10875
...	...	...
NULL	NULL	29025

CUBE

SELECT DEPTNO, JOB, SUM(SAL) AS 급여합계
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

결과

DEPTNO	JOB	급여합계
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10	NULL	8750
20	ANALYST	6000
...	...	...
NULL	CLERK	4150
NULL	MANAGER	8275
NULL	PRESIDENT	5000
NULL	SALESMAN	5600
NULL	ANALYST	6000
NULL	NULL	29025

2. 윈도우함수

윈도우 함수는 행과 행 간의 관계를 쉽게 정의하기 위해 제공되는 함수다. 집계, 순위, 비율 등을 계산할 수 있다.

기본 구문:

SELECT 윈도우함수(인수) OVER (
    [PARTITION BY 컬럼] 
    [ORDER BY 컬럼] 
    [ROWS|RANGE BETWEEN 시작점 AND 끝점]
) FROM 테이블;

순위 관련 함수

함수	설명	파라미터	예시	결과 예시
RANK	순위를 계산하며, 동일한 값은 동일한 순위를 가진다.	없음	SELECT ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) AS RANK FROM EMP;	동일 순위 후 다음 순위는 건너뜀 (1,1,3,4...)
DENSE_RANK	동일한 값에 동일한 순위를 부여하지만 다음 순위를 건너뛰지 않는다.	없음	SELECT ENAME, SAL, DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK FROM EMP;	동일 순위 후 다음 순위는 연속적 (1,1,2,3...)
ROW_NUMBER	고유한 순위를 부여한다.	없음	SELECT ENAME, SAL, ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROW_NUM FROM EMP;	항상 고유한 순위 (1,2,3,4...)
NTILE(n)	데이터를 n개의 그룹으로 나눈다.	그룹 수(정수)	SELECT ENAME, SAL, NTILE(4) OVER(ORDER BY SAL) AS NTILE FROM EMP;	데이터를 4개 그룹으로 균등 분할

순위 함수 비교 예제

SELECT ENAME, SAL,
       RANK() OVER(ORDER BY SAL DESC) AS RANK,
       DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK,
       ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROW_NUM
FROM EMP;

결과

ENAME	SAL	RANK	DENSE_RANK	ROW_NUM
KING	5000	1	1	1
SCOTT	3000	2	2	2
FORD	3000	2	2	3
JONES	2975	4	3	4
BLAKE	2850	5	4	5
...	...	...	...	...

집계 함수

함수	설명	파라미터	예시	결과 예시
SUM OVER	누적 합계를 계산한다.	숫자 컬럼	SELECT ENAME, SAL, SUM(SAL) OVER(ORDER BY SAL) AS CUM_SUM FROM EMP;	급여 순으로 누적 합계
AVG OVER	이동 평균을 계산한다.	숫자 컬럼	SELECT ENAME, SAL, AVG(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS AVG FROM EMP;	현재 행, 이전 행, 다음 행의 급여 평균
COUNT OVER	윈도우 내 행 수를 계산한다.	컬럼 또는 *	SELECT ENAME, DEPTNO, COUNT(*) OVER(PARTITION BY DEPTNO) AS CNT FROM EMP;	같은 부서 직원 수
MAX OVER	윈도우 내 최댓값을 계산한다.	컬럼	SELECT ENAME, DEPTNO, SAL, MAX(SAL) OVER(PARTITION BY DEPTNO) AS MAX_SAL FROM EMP;	부서별 최고 급여
MIN OVER	윈도우 내 최솟값을 계산한다.	컬럼	SELECT ENAME, DEPTNO, SAL, MIN(SAL) OVER(PARTITION BY DEPTNO) AS MIN_SAL FROM EMP;	부서별 최저 급여

누적 합계

SELECT ENAME, SAL, 
       SUM(SAL) OVER(ORDER BY SAL) AS CUM_SUM
FROM EMP;

결과

ENAME	SAL	CUM_SUM
SMITH	800	800
JAMES	950	1750
ADAMS	1100	2850
WARD	1250	5350
MARTIN	1250	5350
...	...	...

부서별 집계

SELECT ENAME, DEPTNO, SAL,
       AVG(SAL) OVER(PARTITION BY DEPTNO) AS DEPT_AVG,
       MAX(SAL) OVER(PARTITION BY DEPTNO) AS DEPT_MAX,
       MIN(SAL) OVER(PARTITION BY DEPTNO) AS DEPT_MIN
FROM EMP;

결과

ENAME	DEPTNO	SAL	DEPT_AVG	DEPT_MAX	DEPT_MIN
CLARK	10	2450	2916.67	5000	1300
KING	10	5000	2916.67	5000	1300
MILLER	10	1300	2916.67	5000	1300
SMITH	20	800	2175.00	3000	800
...	...	...	...	...	...

행 이동 함수

함수	설명	파라미터	예시	결과 예시
LAG	현재 행에서 이전 행의 값을 참조한다.	컬럼, 이동할 행 수(기본값 1), NULL 대체값(선택)	SELECT ENAME, SAL, LAG(SAL, 1, 0) OVER(ORDER BY SAL) AS PREV_SAL FROM EMP;	현재 행보다 급여가 낮은 직원의 급여
LEAD	현재 행에서 다음 행의 값을 참조한다.	컬럼, 이동할 행 수(기본값 1), NULL 대체값(선택)	SELECT ENAME, SAL, LEAD(SAL, 1, 0) OVER(ORDER BY SAL) AS NEXT_SAL FROM EMP;	현재 행보다 급여가 높은 직원의 급여
FIRST_VALUE	윈도우의 첫 번째 값을 반환한다.	컬럼	SELECT ENAME, DEPTNO, SAL, FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS FIRST_SAL FROM EMP;	부서별 가장 낮은 급여
LAST_VALUE	윈도우의 마지막 값을 반환한다.	컬럼	SELECT ENAME, DEPTNO, SAL, LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_SAL FROM EMP;	부서별 가장 높은 급여

LAG와 LEAD

SELECT ENAME, SAL,
       LAG(SAL, 1, 0) OVER(ORDER BY SAL) AS PREV_SAL,
       LEAD(SAL, 1, 0) OVER(ORDER BY SAL) AS NEXT_SAL
FROM EMP;

결과

ENAME	SAL	PREV_SAL	NEXT_SAL
SMITH	800	0	950
JAMES	950	800	1100
ADAMS	1100	950	1250
WARD	1250	1100	1250
MARTIN	1250	1250	1300
...	...	...	...

FIRST_VALUE와 LAST_VALUE

SELECT ENAME, DEPTNO, SAL,
       FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS LOWEST_SAL_EMP,
       LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL 
           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_SAL_EMP
FROM EMP;

결과

ENAME	DEPTNO	SAL	LOWEST_SAL_EMP	HIGHEST_SAL_EMP
MILLER	10	1300	MILLER	KING
CLARK	10	2450	MILLER	KING
KING	10	5000	MILLER	KING
SMITH	20	800	SMITH	SCOTT
...	...	...	...	...

비율 관련 함수

함수	설명	파라미터	예시	결과 예시
PERCENT_RANK	백분위 순위를 계산한다(0~1 사이).	없음	SELECT ENAME, SAL, PERCENT_RANK() OVER(ORDER BY SAL) AS PCT_R FROM EMP;	전체 데이터에서의 상대적 위치 (0~1)
CUME_DIST	누적 분포(현재 행까지의 행 수 / 전체 행 수)값을 계산한다(0~1 사이).	없음	SELECT ENAME, SAL, CUME_DIST() OVER(ORDER BY SAL) AS CUME FROM EMP;	누적 비율 (0~1)
RATIO_TO_REPORT	전체 합계에 대한 현재 값의 비율을 계산한다.	컬럼	SELECT ENAME, SAL, RATIO_TO_REPORT(SAL) OVER() AS RATIO FROM EMP;	전체 급여 합계 대비 현재 급여 비율

비율 함수

SELECT ENAME, SAL,
       PERCENT_RANK() OVER(ORDER BY SAL) AS PERCENT_RANK,
       CUME_DIST() OVER(ORDER BY SAL) AS CUME_DIST,
       RATIO_TO_REPORT(SAL) OVER() AS RATIO
FROM EMP;

결과

ENAME	SAL	PERCENT_RANK	CUME_DIST	RATIO
SMITH	800	0	0.0714	0.0276
JAMES	950	0.0769	0.1429	0.0327
ADAMS	1100	0.1538	0.2143	0.0379
WARD	1250	0.2308	0.3571	0.0431
MARTIN	1250	0.2308	0.3571	0.0431
...	...	...	...	...

윈도우 절 옵션

PARTITION BY: 데이터를 그룹으로 나눈다.
ORDER BY: 정렬 순서를 지정한다.
ROWS|RANGE: 윈도우 프레임(연산 범위)을 정의한다.

윈도우 프레임 옵션	설명	예시 결과
UNBOUNDED PRECEDING	파티션의 첫 번째 행부터	현재 행까지의 모든 행 포함
CURRENT ROW	현재 행	현재 행만 포함
n PRECEDING	현재 행에서 n행 이전	현재 행과 이전 n개 행 포함
n FOLLOWING	현재 행에서 n행 이후	현재 행과 이후 n개 행 포함
UNBOUNDED FOLLOWING	파티션의 마지막 행까지	현재 행부터 마지막 행까지 포함

윈도우 프레임

-- 현재 행과 이전 2개 행까지 포함한 급여 평균
SELECT ENAME, SAL, 
       AVG(SAL) OVER(ORDER BY SAL ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS AVG_3ROWS
FROM EMP;

결과

ENAME	SAL	AVG_3ROWS
SMITH	800	800.00
JAMES	950	875.00
ADAMS	1100	950.00
WARD	1250	1100.00
MARTIN	1250	1200.00
...	...	...

-- 부서별 급여 순으로 누적 합계
SELECT ENAME, DEPTNO, SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS DEPT_CUM_SUM
FROM EMP;

결과

ENAME	DEPTNO	SAL	DEPT_CUM_SUM
MILLER	10	1300	1300
CLARK	10	2450	3750
KING	10	5000	8750
SMITH	20	800	800
ADAMS	20	1100	1900
...	...	...	...

ROWS vs RANGE의 차이

ROWS (물리적 행)

• 실제 행 단위로 계산
• 같은 값이라도 물리적 행 순서에 따라 다른 결과 가능

RANGE (논리적 범위) - 기본값

• 같은 값을 하나의 범위로 간주
• ORDER BY 컬럼의 값이 같으면 모두 동일한 결과

차이점 비교 예제

테이블 데이터:

COL1	COL2
A	10
A	20
A	20
B	30
B	40
B	40

-- RANGE 방식 (기본값)
SELECT COL1, COL2,
       SUM(COL2) OVER(PARTITION BY COL1 ORDER BY COL2) AS RANGE_SUM
FROM TAB1;

-- ROWS 방식 (명시적)
SELECT COL1, COL2,
       SUM(COL2) OVER(PARTITION BY COL1 ORDER BY COL2 ROWS UNBOUNDED PRECEDING) AS ROWS_SUM
FROM TAB1;

결과

COL1	COL2	RANGE_SUM	ROWS_SUM
A	10	10	10
A	20	50	30
A	20	50	50
B	30	30	30
B	40	110	70
B	40	110	110

차이점 설명:

• RANGE: 같은 값(20, 40)을 가진 행들은 모두 동일한 결과
• ROWS: 같은 값이라도 물리적 행 순서에 따라 다른 결과

3. TOP N 쿼리

TOP N 쿼리는 정렬된 결과에서 특정 개수(N)의 최상위 행을 추출하는 기법이다. 페이징 처리나 순위 기반 조회 시 효과적으로 사용할 수 있다.

구현 방법

방법	설명	지원 DBMS	예시
ROWNUM	오라클에서 각 행에 번호를 부여하는 가상 컬럼	Oracle	SELECT * FROM (SELECT * FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= 5;
FETCH	ANSI 표준 SQL:2008부터 도입된 방식	Oracle 12c 이상, PostgreSQL	SELECT * FROM EMP ORDER BY SAL DESC FETCH FIRST 5 ROWS ONLY;
TOP	SQL Server에서 제공하는 TOP N 구문	SQL Server	SELECT TOP 5 * FROM EMP ORDER BY SAL DESC;
LIMIT	MySQL, PostgreSQL 등에서 사용하는 방식	MySQL, PostgreSQL	SELECT * FROM EMP ORDER BY SAL DESC LIMIT 5;

ROWNUM을 이용한 TOP N 쿼리

ROWNUM은 Oracle에서 제공하는 가상 컬럼으로, 쿼리 결과의 각 행에 1부터 시작하는 번호를 부여한다.

-- 급여가 높은 순서로 상위 5명의 직원 정보 출력 (올바른 방법)
SELECT *
FROM (SELECT EMPNO, ENAME, SAL
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM <= 5;

서브쿼리에서 먼저 정렬한 후 바깥 쿼리에서 ROWNUM 조건을 적용한다.

실행 결과

실제 급여 상위 5명:

EMPNO	ENAME	SAL
7839	KING	5000
7788	SCOTT	3000
7902	FORD	3000
7566	JONES	2975
7698	BLAKE	2850

주의사항

• ROWNUM은 WHERE 절에서 행이 선택될 때 할당된다.
• "ROWNUM > n" 조건은 직접 사용할 수 없다(이미 n보다 큰 번호가 할당되지 않았기 때문).
• 정렬된 결과에서 TOP N을 추출하려면 서브쿼리를 사용해야 한다.

-- 급여가 높은 순서로 상위 5명의 직원 정보 출력 (잘못된 방법)
SELECT EMPNO, ENAME, SAL, ROWNUM
FROM EMP
WHERE ROWNUM <= 5
ORDER BY SAL DESC;

위 쿼리의 문제점은 먼저 ROWNUM 조건으로 5개 행을 선택한 후 정렬하기 때문에, 실제 급여 상위 5명이 아닌 임의의 5명이 선택된다.

실행 결과

임의의 5명이 선택되고 그 중에서 급여 순으로 정렬됨:

EMPNO	ENAME	SAL	ROWNUM
7839	KING	5000	3
7566	JONES	2975	2
7782	CLARK	2450	4
7369	SMITH	800	1
7876	ADAMS	1100	5

페이징 처리

특정 범위의 행을 추출할 때(예: 4~6번째 행), 단일 ROWNUM 조건으로는 불가능하다. 이때는 중첩 서브쿼리를 사용한다.

-- 급여가 높은 순서로 4~6번째 직원 정보 출력
SELECT *
FROM (SELECT EMPNO, ENAME, SAL, ROWNUM AS RN
      FROM (SELECT EMPNO, ENAME, SAL
            FROM EMP
            ORDER BY SAL DESC))
WHERE RN BETWEEN 4 AND 6;

1. 가장 안쪽 서브쿼리: 급여 내림차순으로 정렬
2. 중간 서브쿼리: ROWNUM 할당하여 RN이라는 별칭 부여
3. 바깥 쿼리: RN 값으로 범위 지정

실행 결과

급여 순위 4~6위:

EMPNO	ENAME	SAL	RN
7566	JONES	2975	4
7698	BLAKE	2850	5
7782	CLARK	2450	6

FETCH 절

Oracle 12c 이상에서는 ANSI 표준 SQL의 FETCH 절을 사용할 수 있다.

-- 급여가 높은 순서로 상위 5명의 직원 정보 출력
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROWS ONLY;

실행 결과

급여 상위 5명:

EMPNO	ENAME	SAL
7839	KING	5000
7788	SCOTT	3000
7902	FORD	3000
7566	JONES	2975
7698	BLAKE	2850

-- OFFSET과 함께 사용하여 페이징 처리
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
OFFSET 3 ROWS         -- 처음 3행 건너뜀
FETCH NEXT 3 ROWS ONLY; -- 다음 3행 추출

실행 결과

급여 순위 4~6위:

EMPNO	ENAME	SAL
7566	JONES	2975
7698	BLAKE	2850
7782	CLARK	2450

FETCH 절의 장점은 OFFSET과 함께 사용하여 간결하게 페이징 처리를 할 수 있다는 점이다.

SQL Server의 TOP 절

SQL Server에서는 TOP 절을 사용하여 쉽게 상위 N개 행을 추출할 수 있다.

-- 급여가 높은 순서로 상위 5명의 직원 정보 출력
SELECT TOP 5 EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

실행 결과

급여 상위 5명:

EMPNO	ENAME	SAL
7839	KING	5000
7788	SCOTT	3000
7902	FORD	3000
7566	JONES	2975
7698	BLAKE	2850

-- 동일한 값이 있을 경우 함께 반환 (WITH TIES)
SELECT TOP 5 WITH TIES EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC;

실행 결과

SCOTT와 FORD가 같은 급여로 공동 2위이므로 6명 출력:

EMPNO	ENAME	SAL
7839	KING	5000
7788	SCOTT	3000
7902	FORD	3000
7566	JONES	2975
7698	BLAKE	2850
7782	CLARK	2450

WITH TIES 옵션을 사용하면 ORDER BY 절에 지정된 컬럼의 값이 같을 경우 모두 함께 반환한다.

TOP N 쿼리와 인덱스

인덱스를 활용하면 전체 테이블을 정렬하지 않고도 Top N 쿼리를 효율적으로 수행할 수 있다.

-- SAL 컬럼에 내림차순 인덱스가 있는 경우
-- 옵티마이저는 인덱스를 활용하여 전체 정렬 없이 상위 5개만 빠르게 추출 가능
SELECT *
FROM (SELECT EMPNO, ENAME, SAL
      FROM EMP
      ORDER BY SAL DESC)
WHERE ROWNUM <= 5;

TOP N 쿼리 성능 고려사항

• 대용량 데이터에서 전체 정렬 후 상위 N개만 추출하는 것은 비효율적일 수 있다.
• 인덱스를 활용한 정렬이 중요하며, ORDER BY 절의 컬럼에 적절한 인덱스가 있어야 한다.
• ROWNUM 조건은 가능한 빨리 적용되도록 쿼리를 구성해야 한다.
• 페이징 처리 시 OFFSET이 큰 경우 성능이 저하될 수 있으므로 주의해야 한다.

TOP N 쿼리는 SQLD 시험에서 자주 출제되는 중요한 주제이므로, 각 DBMS별 구현 방법과 주의사항을 잘 이해하는 것이 중요하다.