[SQLD] 2과목 SQL 기본 및 활용 - SQL 활용(2)

🚀 윈도우 함수(Window Function)

1️⃣ 순위함수(Ranking Function)

- 순위 함수(Ranking Function) 는 특정 기준에 따라 순위를 계산하는 함수이다.

함수 이름	설명	예
RANK	동일 순위는 같은 순위값을 가진다. 순위값은 앞 순위까지의 누적개수 +1이 된다.	1, 2, 3, 4, 4, 4, 7, ...
DENSE_RANK	동일 순위는 같은 순위값을 가진다. 순위값은 단순하게 앞순위 +1이다.	1, 2, 2, 3, 3, 3, 4, ...
ROW_NUMBER	동일 순위라도 각각의 행이 고유의 순위값을 가진다.	1, 2, 3, 4, 5, 6, 7, ...

 

✅ 순위함수 예제 (RANK)

SELECT 사원명, 부서명, 급여,
       RANK() OVER (PARTITION BY 부서명 ORDER BY 급여 DESC) AS 순위
FROM 직원;

 

✅ 결과 예시

사원명	부서명	급여	순위
김철수	개발팀	500	1
이영희	개발팀	400	2
박민수	개발팀	400	2
최지우	영업팀	600	1
강동원	영업팀	550	2

 

2️⃣ 집계함수

- OVER 절을 사용하여 특정 그룹(파티션) 별로 집계 함수를 적용할 수 있다.

함수 이름	설명
COUNT	값이 Null인 행을 제외한 행의 개수를 파티션 별로 집계하거나 누적 집계를 계산하여 반환한다.
SUM	입력된 칼럼에 대해 파티션 별 합계를 구하거나 누적 합계를 계산하여 반환한다.
AVG	입력된 칼럼에 대해 파티션 별 평균을 구하거나 누적 평균을 계산하여 반환한다.
MIN	입력된 칼럼에 대해 파티션 별 최솟값을 반환한다.
MAX	입력된 칼럼에 대해 파티션 별 최댓값을 반환한다.

 

✅ 순위함수 예제

SELECT 부서명, 사원명, COUNT(*) OVER (PARTITION BY 부서명) AS 부서별_사원수
FROM 직원;

✅ 결과 예시

부서명	사원명	부서별_사원수
개발팀	김철수	3
개발팀	이영희	3
개발팀	박민수	3
영업팀	최지우	2
영업팀	강동원	2

 

3️⃣ 행 순서함수

- 특정 행을 기준으로 앞 또는 뒤의 값을 조회하는 함수이다.

함수	설명
FIRST_VALUE	파티션 별로 그룹핑하여 가장 처음 값을 반환한다.
LAST_VALUE	파티션 별로 그룹핑하여 가장 마지막 값을 반환한다.
LAG	입력된 인자의 값만큼 이전 행의 값을 반환한다.
LEAD	입력된 인자의 값만큼 이후 행의 값을 반환한다.

 

 

✅ 행 순서함수 예제1 (FIRST_VALUE)

SELECT 사원명, 부서명, 급여,
       FIRST_VALUE(급여) OVER (PARTITION BY 부서명 ORDER BY 급여 DESC) AS 부서별_최고급여
FROM 직원;

✅ 결과 예시

사원명	부서명	급여	부서별_최고급여
김철수	개발팀	500	500
이영희	개발팀	400	500
박민수	개발팀	400	500
최지우	영업팀	600	600
강동원	영업팀	550	600

 

✅ 행 순서함수 예제2 (LAG)

SELECT 사원명, 부서명, 급여,
       LAG(급여, 1) OVER (PARTITION BY 부서명 ORDER BY 급여 DESC) AS 이전_급여
FROM 직원;

✅ 결과 예시

사원명	부서명	급여	이전_급여
김철수	개발팀	500	NULL
이영희	개발팀	400	500
박민수	개발팀	400	400
최지우	영업팀	600	NULL
강동원	영업팀	550	600

 

4️⃣ 비율함수

- 파티션 별로 합계를 구한 후, 그에 대한 비율을 구하는 함수

- 누적 백분율, 순서별 백분율 등 다양한 연산을 수행하는 함수

함수	설명
CUME_DIST	파티션 별 합계에 대한 누적 백분율을 소수점 단위로 계산하여 반환한다. 마지막 행의 값이 1이 된다.
PERCENT_RANK	파티션 별로 그룹핑하여 백분위 순위값을 반환한다. 가장 첫 행이 0, 마지막 행이 1이 된다.
NTILE	파티션을 N등분 하여 1부터 N까지의 등급값을 반환한다. 전체 개수를 N으로 나누고 나머지 앞 등급 순서로 1씩 배분하여 등급별 개수를 정한다. 예) 10개를 4등급으로 나누면 각 등급별 개수는 3, 3, 2, 2가 된다.
RATIO_TO_REPORT	파티션 별 합계에 대한 비율을 계산하여 반환한다.

 

✅ 예제 테이블 (직원 테이블)

사원	급여
김철수	500
이영희	400
박민수	400
최지우	600
강동원	550

✅ 비율함수 예제

SELECT 사원명, 급여,
       CUME_DIST() OVER (ORDER BY 급여 DESC) AS 누적백분율,
       PERCENT_RANK() OVER (ORDER BY 급여 DESC) AS 백분위순위,
       NTILE(3) OVER (ORDER BY 급여 DESC) AS 등급,  -- 3등분 (1~3등급)
       RATIO_TO_REPORT(급여) OVER () AS 급여비율
FROM 직원;

✅ 결과 예시

사원명	급여	누적백분율	백분위순위	등급	급여비율
최지우	600	1.0	0.0	1	0.2667 (600/2250)
강동원	550	0.8	0.25	1	0.2444 (550/2250)
김철수	500	0.6	0.50	2	0.2222 (500/2250)
이영희	400	0.2	0.75	3	0.1778 (400/2250)
박민수	400	0.2	0.75	3	0.1778 (400/2250)

 

⭐ 정리

함수 유형	주요 함수
순위 함수	RANK, DENSE_RANK, ROW_NUMBER
집계 함수	COUNT, SUM, AVG, MIN, MAX
행 순서 함수	FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율 함수	CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

📌 CUME_DIST: Cumulative Distribution

📌 LAG: Look At the Gap, or Lagging Value

📌 LEAD: Look Ahead, or Leading Value

 

 

 

---

🚀 Top N 쿼리

- 빌보드 HOT100과 같이 상위 N 순위까지 추출하는 쿼리

- 기본적으로 순위함수(RANK, DENSE_RANK, ROW_NUMBER를 사용

 

1️⃣ ROWNUM 함수

 

- ROW_NUMBER와 달리 현재 저장된 데이터를 그대로 두고 각 행에 순차적인 번호를 붙여주는 함수

SELECT ROWNUM, EMPNO, ENAME, SAL
FROM EMP
WHERE ROWNUM <= 5;

 

2️⃣ 윈도우함수의 순위함수

- RANK(), DENSE_RANK(), ROW_NUMBER()

 

---

🚀 계층형 질의와 셀프 조인

- 회사의 조직도(본부-사업부-팀)와 같이 계측적으로 구성된 데이터에서 연쇄적으로 접근해 결과를 조회할 때

1️⃣ 계층형 질의

키워드	설명
LEVEL	전체 계층에서 현재의 레벨 또는 깊이(Depth)를 반환한다. 루트 노드가 1이 되며 한 레벨 내려갈 때마다 1씩 증가한다.
SYS_CONNECT_BY_PATH	최상위 루트 노드로부터 현재 노드까지의 경로를 출력하는 함수이다.
START WITH	경로의 시작이 되는 루트 노드가 되는 조건을 지정한다.
CONNECT BY	부모 노드로부터 자식 노드로의 연결을 지정한다.
CONNECT_BY_ROOT	루트 노드의 지정된 칼럼값을 반환한다.
CONNEXT_BY_ISLEAF	가장 말단 노드이면 1을 반환하고 그 외에는 0을 반환한다.
PRIOR	바로 상위의 부모 노드를 반환한다.
NOCYCLE	사이클 발생 이후의 데이터를 전개하지 않음으로써 무한루프를 방지한다.
ORDER SIBLINGS BY	ORDER BY절이 전체를 정렬하는 것에 비하여 ORDER SIBLINGS BY는 같은 레벨의 형제(Sibling) 노드끼리 정렬한다.

 

 

2️⃣ 셀프 조인

- 계층적인 분류 값을 가지는 칼럼을 다룰 때 

- 예) 자식=부모(EMPNO=MGR) 관계

 

 

---

🚀 PIVOT절과 UNPIVOT절

- 행과 열을 재구성하는 것

 

1️⃣ PIVOT절

- 행을 열로 바꾼다.

 

✅ PIOVOT 예제

- 월별 총 판매량을 행(ROW)에서 열(COL)로 변환

SELECT * FROM (
    SELECT EXTRACT(MONTH FROM order_date) AS month, category, sales
    FROM sales_data
)
PIVOT (
    SUM(sales) 
    FOR month IN (1 AS "JAN", 2 AS "FEB", 3 AS "MAR")
);

→ sales_data 테이블에서 order_date의 월별 category별 총 sales를 구해
→ PIVOT을 사용하여 JAN, FEB, MAR 열로 변환함.

 

2️⃣ UNPIVOT절

- 열을 행으로 바꾼다.

✅ PIOVOT 예제

- 월별 총 판매량을 행(ROW)에서 열(COL)로 변환

SELECT category, month, sales FROM sales_data
UNPIVOT (
    sales FOR month IN ("JAN", "FEB", "MAR")
);

→ sales_data 테이블에서 JAN, FEB, MAR로 구분된 월별 sales 데이터를
→ month 컬럼으로 변환하여 행(Row) 단위로 표현.

 

---

🚀 정규표현식

- 

- 

 

1️⃣ 주요 메타 문자

메타 문자	의미	예
\	메타 문자를 리터럴 문자로 표시하거나 리터럴 문자와 결합하여 정해진 메타 문자를 표시	\\: \ \n: 줄바꿈(개행) 문자
^	개행으로 나뉜 문자열의 시작 지점	^The: The로 시작하는 문자열
$	개행으로 나뉜 문자열의 끝 지점	ing$: ing로 끝나는 문자열
.	임의의 한 문자(개행 문자는 제외)	a.b: acb, a-b, a1b
?	선행 문자 0또는 1개	no?: n, no
*	선행 문자 0개 이상	no?: n, no, noo, nooo, ...
+	선행 문자 1개 이상	no+: no, noo, nooo, ...

메타 문자	의미	예
|	선택적 일치	a|b: a, b
[]	대괄호 안의 문자들 중 하나와 일치	[abc]: a, b, c
[-]	연속 문자의 범위를 지정	[a-z]: a부터 z까지 소문자 알파벳 문자
[^]	대괄호 안의 문자들을 제외한 나머지 문자들 중 하나와 일치	[^abc]: d, e, z, ...(a, b, c 제외 나머지 문자)
()	소괄호로 묶인 표현식을 한  단위로 취급	(ab): ab

 

2️⃣ 정규표현식  함수(Oracle)

정규표현식 함수	설명
REGEXP_LIKE	정규표현식을 사용한 LIKE 연산
REGEXP_REPLACE	정규표현식을 사용하여 문자열 대체
REGEXP_INSTR	정규표현식을 사용하여 문자열 검색 후 위치 반환
REGEXP_SUBSTR	정규표현식을 사용하여 부분 문자열 반환
REGEXP_COUNT	정규표현식을 사용하여 특정 패턴의 문자열 개수 반환