08. 윈도우 함수 · 그룹 함수 · TOP-N · DCL#

2과목 · SQL 활용 + DCL. ★ = 시험 빈출

1. 데이터 분석 함수 분류#

종류	특징
Aggregate Function	집계 (SUM, AVG, COUNT...)
Group Function	그룹 (ROLLUP, CUBE, GROUPING SETS)
Window Function	행 단위 분석 (OVER 절 사용)

2. ★ 그룹 함수 (Group Function)#

ROLLUP#

N개 칼럼 → N+1 레벨 Subtotal 생성, 계층 구조 → 인수 순서 영향

sql SELECT DNAME, JOB, COUNT(*), SUM(SAL) FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY ROLLUP(DNAME, JOB); -- (DNAME, JOB) 별 / DNAME 별 / 전체

CUBE#

모든 결합 다차원 집계, 인수 순서 무관

sql GROUP BY CUBE(DNAME, JOB); -- (DNAME, JOB) / DNAME / JOB / 전체

GROUPING SETS#

원하는 부분의 소계만 선택적 추출, 인수 순서 무관

sql GROUP BY GROUPING SETS(DNAME, JOB); -- DNAME / JOB (전체 X)

★ 비교#

그룹 함수	생성 레벨	순서 영향
`ROLLUP(A, B)`	(A,B), A, 전체	있음
`CUBE(A, B)`	(A,B), A, B, 전체	없음
`GROUPING SETS(A, B)`	A, B만	없음

GROUPING 함수#

GROUPING(칼럼) = 1: 소계 행
GROUPING(칼럼) = 0: 일반 집계 행

3. ★ 윈도우 함수 (Window Function)#

sql SELECT WINDOW_FUNCTION(인수) OVER ( [PARTITION BY 칼럼] -- 그룹 기준 [ORDER BY 칼럼] -- 정렬 (PARTITION 내부) [WINDOWING 절] -- 범위 ) FROM 테이블;

순서 중요: PARTITION BY → ORDER BY → ROWS/RANGE

4. ★ WINDOWING 절 (출제 단골)#

ROWS vs RANGE#

종류	동작
`ROWS`	물리적 행 수로 계산 (각 행 개별)
`RANGE`	값의 범위로 계산 (값이 같으면 같이 묶음, 기본값)

범위 키워드#

키워드	의미
`UNBOUNDED PRECEDING`	처음부터
`N PRECEDING`	N행 앞부터
`CURRENT ROW`	현재 행
`N FOLLOWING`	N행 뒤까지
`UNBOUNDED FOLLOWING`	마지막까지

예시#

```sql -- 누적 합계 (처음 ~ 현재 행) SUM(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 이동 평균 (앞 2행 ~ 뒤 2행) AVG(SAL) OVER (ORDER BY EMPNO ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

-- 현재행 + 다음 1행 SUM(AMOUNT) OVER (ORDER BY DATE ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) ```

5. ★ 순위 함수 (Rank Function)#

함수	동일값 처리	예시 (1,1,1,2)
`RANK()`	같은 순위, 다음 건너뜀	1, 1, 1, 4
`DENSE_RANK()`	같은 순위, 다음 안 건너뜀	1, 1, 1, 2
`ROW_NUMBER()`	고유 순위	1, 2, 3, 4

sql SELECT JOB, ENAME, SAL, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) RNK, DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) DRNK, ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL DESC) RN FROM EMP;

6. ★ 행 순서 함수#

FIRST_VALUE / LAST_VALUE#

함수	설명
`FIRST_VALUE`	정렬된 윈도우의 첫 번째 값
`LAST_VALUE`	정렬된 윈도우의 마지막 값

★★ 시험 단골: LAST_VALUE 함정#

sql -- 같은 부서 최저 급여 / 최고 급여 SELECT NAME, DEPT, SAL, FIRST_VALUE(SAL) OVER (PARTITION BY DEPT ORDER BY SAL) AS LOW, LAST_VALUE(SAL) OVER (PARTITION BY DEPT ORDER BY SAL) AS HIGH FROM EMP;

★ LAST_VALUE는 WINDOWING 절을 안 쓰면
RANGE 기본값(UNBOUNDED PRECEDING ~ CURRENT ROW) → 현재 행을 반환

진짜 최댓값을 얻으려면 명시적으로 범위 지정:

sql LAST_VALUE(SAL) OVER ( PARTITION BY DEPT ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

LAG / LEAD#

함수	설명
`LAG(칼럼, n, default)`	이전 n번째 행 (기본 1)
`LEAD(칼럼, n, default)`	이후 n번째 행 (기본 1)

sql -- 전월 대비 증감 SELECT YYYYMM, SALES, LAG(SALES, 1, 0) OVER (ORDER BY YYYYMM) AS PREV, SALES - LAG(SALES, 1, 0) OVER (ORDER BY YYYYMM) AS DIFF FROM MONTHLY_SALES;

7. ★ 비율 함수#

함수	설명
`RATIO_TO_REPORT`	전체 SUM(칼럼) 대비 행별 비율
`PERCENT_RANK`	행 순서의 백분율 (0 ~ 1)
`CUME_DIST`	현재 행 이하 누적 백분율 (0 < x ≤ 1)
`NTILE(n)`	전체를 n등분한 구간 번호

NTILE 동작#

sql -- 14개 행을 3등분 → 5, 5, 4 (앞 그룹이 큰 수) SELECT NAME, SAL, NTILE(3) OVER (ORDER BY SAL DESC) AS TIER FROM EMP;

계산 예시#

데이터: 65000, 71000

함수	65000 값	계산
`CUME_DIST`	0.5	½ (65000 이하 1건 / 전체 2건)
`PERCENT_RANK`	0.0	(1-1)/(2-1)
`RATIO_TO_REPORT`	0.478	65000 / (65000+71000)

8. ★ TOP-N 쿼리 (2024 개정)#

Oracle: ROWNUM#

Pseudo Column — 결과 집합의 각 행에 자동 부여 (1부터)

ROWNUM 사용 규칙#

패턴	가능?
`WHERE ROWNUM = 1`	✓
`WHERE ROWNUM = 2`	✗ (1이 안 잡혀서 다시 1번이 됨)
`WHERE ROWNUM <= 3`	✓
`WHERE ROWNUM > 0`	✓
`WHERE ROWNUM > 3`	✗
`WHERE ROWNUM < 4`	✓

결론: ROWNUM 조건에 1이 포함되어야 정상 동작 (1부터 순차 부여이므로)

★ 자주 출제: ROWNUM + ORDER BY 함정#

```sql -- ❌ 잘못된 쿼리 - 정렬 전에 ROWNUM 잡힘 SELECT * FROM EMP WHERE ROWNUM <= 3 ORDER BY SAL DESC;

-- ✓ 올바른 쿼리 - 인라인뷰로 먼저 정렬 SELECT * FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <= 3;

-- ✓ 4~6등 출력 SELECT * FROM ( SELECT EMP.*, ROWNUM AS RN FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) EMP ) WHERE RN BETWEEN 4 AND 6; ```

★ Oracle 12c+ : FETCH 절 (권장)#

```sql SELECT * FROM EMP ORDER BY SAL DESC OFFSET 0 ROWS -- 처음부터 FETCH FIRST 3 ROWS ONLY; -- 3건만

-- 4~6등 SELECT * FROM EMP ORDER BY SAL DESC OFFSET 3 ROWS -- 3건 건너뛰기 FETCH NEXT 3 ROWS ONLY; -- 다음 3건

-- 동률 포함 ORDER BY SAL DESC FETCH FIRST 3 ROWS WITH TIES; ```

SQL Server: TOP#

```sql SELECT TOP(3) * FROM EMP ORDER BY SAL DESC; SELECT TOP(10) PERCENT * FROM EMP ORDER BY SAL DESC;

-- ★ WITH TIES: 동률 포함 SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC; -- 5000(KING), 3000(SCOTT), 3000(FORD) — 3000 동률 모두 포함 ```

★ 순위 함수 활용#

sql SELECT * FROM ( SELECT EMP.*, RANK() OVER (ORDER BY SAL DESC) AS RNK FROM EMP ) WHERE RNK BETWEEN 4 AND 6;

9. ★ DCL (Data Control Language)#

권한의 종류#

분류	의미	예시
시스템 권한	DB 전반에 대한 권한	`CREATE SESSION`, `CREATE TABLE`, `CREATE ANY TABLE`, `DROP ANY TABLE`
객체 권한	특정 객체에 대한 권한	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `REFERENCES`, `EXECUTE`

GRANT / REVOKE#

```sql -- 시스템 권한 부여 GRANT CREATE TABLE TO henry;

-- 객체 권한 부여 (여러 권한 동시) GRANT SELECT, INSERT, UPDATE ON SCOTT.EMP TO henry;

-- 권한 회수 REVOKE SELECT, INSERT ON SCOTT.EMP FROM henry; ```

10. ★★ WITH GRANT OPTION vs WITH ADMIN OPTION (출제 포인트)#

대상#

옵션	대상
`WITH GRANT OPTION`	객체 권한 (SELECT, INSERT 등)
`WITH ADMIN OPTION`	시스템 권한 또는 ROLE

★★★ 핵심 차이#

시나리오	WITH GRANT OPTION	WITH ADMIN OPTION
중간관리자가 부여한 권한을 최상위가 회수 가능?	불가 (중간관리자만)	가능
중간관리자 권한 회수 시 제3계정 권한도?	같이 회수됨 (연쇄)	남아 있음

예시: WITH GRANT OPTION (객체 권한)#

```sql -- SCOTT이 HR에게 SELECT + 재부여권 부여 GRANT SELECT ON SCOTT.EMP TO HR WITH GRANT OPTION;

-- HR이 USER1에게 부여 가능 GRANT SELECT ON SCOTT.EMP TO USER1;

-- SCOTT은 USER1 권한 직접 회수 불가 (HR을 통해서만) -- SCOTT이 HR 권한 회수 → USER1 권한도 같이 회수 (CASCADE) REVOKE SELECT ON SCOTT.EMP FROM HR; ```

예시: WITH ADMIN OPTION (시스템/ROLE)#

```sql -- 시스템 권한 GRANT CREATE TABLE TO HR WITH ADMIN OPTION;

-- ROLE GRANT MANAGER_ROLE TO HR WITH ADMIN OPTION;

-- HR이 USER1에게 부여 가능 -- SYS는 USER1 권한 직접 회수 가능 -- HR 권한 회수해도 USER1 권한 유지 (독립적) ```

11. ★ ROLE (롤)#

개요#

권한의 집합 — 여러 권한을 한 번에 부여/회수
관리자가 ROLE 생성 → ROLE에 권한 묶음 → ROLE을 유저에게 부여

예시#

```sql -- ROLE 생성 CREATE ROLE MANAGER_ROLE;

-- ROLE에 권한 추가 GRANT SELECT, INSERT, UPDATE ON SCOTT.EMP TO MANAGER_ROLE; GRANT CREATE SESSION, CREATE TABLE TO MANAGER_ROLE;

-- ROLE을 사용자에게 부여 GRANT MANAGER_ROLE TO HR;

-- ★ 주의: ROLE 안의 권한을 개별 회수 불가능 -- ROLE 자체를 회수해야 함 REVOKE MANAGER_ROLE FROM HR; ```

Oracle 사전 정의 ROLE#

ROLE	권한
`CONNECT`	기본 접속
`RESOURCE`	객체 생성
`DBA`	모든 권한