0112 SQLD 학습 (9/10): 2과목 - 고급 SQL과 DCL/TCL
✅ 1. 윈도우 함수 (Window Functions)
-
윈도우 함수는
GROUP BY처럼 행들을 그룹화하여 집계하는 것은 유사하지만,GROUP BY와 달리 기존 행들을 하나로 합치지 않고, 각 행에 대해 원본 행의 관계를 유지하며 계산 결과를 반환하는 강력한 함수입니다. -
핵심 개념: 행들의 "창(Window)"을 정의하고, 그 창 내에서 순위, 합계, 평균 등을 계산합니다.
-
기본 문법:
함수명() OVER ( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )PARTITION BY:GROUP BY와 유사하게, 계산을 수행할 대상 그룹(파티션)을 나눕니다.ORDER BY: 파티션 내에서 순서를 정합니다. (순위 함수 등에서 필수)WINDOWING: 계산을 수행할 행의 범위를 더 세부적으로 지정합니다. (ROWS BETWEEN ...)
➕ 윈도우 함수의 종류
| 구분 | 함수 | 설명 |
|---|---|---|
| 순위 함수 | RANK() | • 동일 순위가 있으면 다음 등수는 건너뜀. (1, 2, 2, 4) |
DENSE_RANK() | • 동일 순위가 있어도 다음 등수를 건너뛰지 않음. (1, 2, 2, 3) | |
ROW_NUMBER() | • 동일 순위 없이 고유한 순번을 부여. (1, 2, 3, 4) | |
| 집계 함수 | SUM(), AVG(), COUNT() | • OVER() 절과 함께 사용하여, 파티션 내의 누적 합계, 이동 평균 등을 계산. |
| 행 순서 함수 | FIRST_VALUE(), LAST_VALUE() | • 파티션 내의 첫 번째 또는 마지막 값을 반환. |
LAG(), LEAD() | • 현재 행을 기준으로 이전 행(LAG) 또는 다음 행(LEAD)의 값을 가져옴. |
-- 부서(DEPTNO)별로 급여(SAL)가 높은 순서대로 순위를 매기는 예시
SELECT
ENAME,
DEPTNO,
SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DEPT_RANK,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DENSE_DEPT_RANK,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DEPT_ROW_NUM
FROM EMP;✅ 2. DCL (Data Control Language, 데이터 제어어)
-
DCL은 데이터베이스 사용자에게 특정 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 명령어입니다. 데이터베이스의 보안을 담당합니다.
-
GRANT(권한 부여):-
문법:
GRANT [권한] ON [객체] TO [사용자]; -
예시:
-- 사용자 'SCOTT'에게 EMP 테이블에 대한 SELECT 권한 부여 GRANT SELECT ON EMP TO SCOTT; -- 'SCOTT'에게 접속(SESSION) 권한과 테이블 생성 권한 부여 GRANT CREATE SESSION, CREATE TABLE TO SCOTT;
-
-
REVOKE(권한 회수):- 문법:
REVOKE [권한] ON [객체] FROM [사용자]; - 예시:
-- 사용자 'SCOTT'로부터 EMP 테이블의 SELECT 권한 회수 REVOKE SELECT ON EMP FROM SCOTT;
- 문법:
✅ 3. TCL (Transaction Control Language, 트랜잭션 제어어)
-
트랜잭션(Transaction)이란 데이터베이스의 상태를 변화시키는 하나의 논리적인 작업 단위입니다. 이 작업 단위 내의 모든 DML(INSERT, UPDATE, DELETE) 연산은 모두 성공(Commit)하거나, 하나라도 실패하면 모두 실패(Rollback)해야 합니다 (All or Nothing).
-
TCL은 이러한 트랜잭션의 결과를 데이터베이스에 어떻게 반영할지를 제어하는 명령어입니다.
-
COMMIT:- 트랜잭션 내에서 수행된 모든 변경 사항을 데이터베이스에 영구적으로 저장합니다.
COMMIT을 실행하면, 이전 데이터는 더 이상 복구할 수 없습니다.
-
ROLLBACK:- 트랜잭션 내에서 수행된 모든 변경 사항을 취소하고, 트랜잭션이 시작되기 이전 상태로 되돌립니다.
-
SAVEPOINT:- 트랜잭션 내에 중간 저장 지점을 만듭니다.
ROLLBACK TO [SAVEPOINT_이름];구문을 사용하여, 전체 트랜잭션을 롤백하는 대신 특정SAVEPOINT까지만 롤백할 수 있습니다.
➕ 트랜잭션의 4대 특성 (ACID)
| 특성 | 이름 | 설명 |
|---|---|---|
| A | 원자성 (Atomicity) | 트랜잭션의 연산은 모두 반영되거나, 아니면 모두 반영되지 않아야 한다. (All or Nothing) |
| C | 일관성 (Consistency) | 트랜잭션 실행 후에도 데이터베이스는 항상 일관된 상태를 유지해야 한다. |
| I | 격리성 (Isolation) | 둘 이상의 트랜잭션이 동시에 실행될 때, 서로의 작업에 영향을 주지 않아야 한다. |
| D | 지속성 (Durability) | 성공적으로 완료된(COMMIT된) 트랜잭션의 결과는 영구적으로 저장되어야 한다. |
📌 요약
- 윈도우 함수는
GROUP BY와 달리 원본 행을 유지하면서,PARTITION BY로 정의된 그룹 내에서 순위, 누적 합계 등 복잡한 분석 연산을 수행하는 강력한 기능입니다. - DCL (
GRANT,REVOKE)은 사용자에게 데이터베이스 객체에 대한 접근 권한을 제어하여 보안을 관리합니다. - TCL (
COMMIT,ROLLBACK)은 DML 작업들을 하나의 트랜잭션 단위로 묶어, 데이터의 원자성과 일관성을 보장합니다. - 성공적인 트랜잭션은 ACID라는 4가지 특성을 모두 만족해야 합니다.
