[SQLD][아답터] 2과목 - 2. SQL 활용

[SQL 활용]

1-1.  서브쿼리

1-2. 서브쿼리의 종류
1-2-1. 단일행 서브쿼리
1-2-2. 다중열 서브쿼리
1-2-3. 다중행 서브쿼리
1.2.4. 스칼라 서브쿼리
1.2.5. 인라인 뷰
1-2-6. 상호연관 서브쿼리

2-1. 집합 연산자
2-1-1. 집합 연산자
2-1-2. 합집합
2-1-3. 교집합

3-1. 그룹 함수
3-1-1. ROLLUP
3-1-2. CUBE
3-1-3. GROUPING SETS

4-1. 윈도우 함수
4-1-1. 순위함수
4-1-2. 집계함수
4-1-3. 행 간의 값 참조 함수
4-1-4. 범위 지정 키워드
4-1-5. 비율함수

5-1. TOP N 쿼리
5-1-1. TOP
5-1-2. LIMIT
5-1-3. FETCH FIRST
5-1-4. ROWNUM
5-1-5. TOP N 쿼리 사용 시 유의사항

6-1. 계층형 질의와 셀프 조인
6-1-1. 계층형 질의
6-1-2. 셀프 조인

7-1. PIVOT 절과 UNPIVOT 절
7-1-1. PIVOT 절
7-1-2. UNPIVOT 절

8-1. 정규표현식
8-1-1. 정규표현식 기호
8-1-2. 정규표현식 함수

[SQL 활용]

1-1. 서브쿼리

하나의 쿼리문(=메인쿼리) 안에 포함된 또 다른 쿼리문

1-2. 서브쿼리의 종류

 

1️⃣ 단일행 서브쿼리

🔹 서브쿼리가 하나의 값만 반환

SELECT 이름, 연봉
FROM 지원
WHERE 연봉 > (SELECT AVG(연봉) FROM 직원)

 

- 직원 평균 급여는 (5000+6000+4000+7000) / 4 = 5500

➜ 연봉이 5500 이상인 직원은 (김과장, 6000) 과 (이부장, 7000)

2️⃣ 다중열 서브쿼리

🔹 서브쿼리가 두 개 이상의 값을 반환

SELECT 이름
FROM 직원
WHERE (부서ID, 직무ID) IN (SELECT 부서ID, 직무ID FROM 인사팀업무)

 

- 인사팀업무 테이블에 있는 (부서ID, 직무ID)는 (10, J1) 과 (30, J3)

➜ 해당 직무에 해당하는 직원은 (김사원, 서대리, 이부장)

3️⃣ 다중행 서브쿼리

🔹 서브쿼리가 여러 행을 반환

SELECT 이름
FROM 직원
WHERE 부서ID IN (SELECT 부서ID FROM 부서 WHERE 지역 = '서울')

 

- 서울에 있는 부서ID는 (10, 30)

➜ 해당 부서에 속하는 직원은 (김사원, 서대리, 이부장)

 

※ 참고 문법

- IN : 목록에 포함

- ANY : 하나라도 조건을 만족해야 함

- ALL : 모든 조건을 만족해야 함

- EXISTS : 결과가 존재함

4️⃣ 스칼라 서브쿼리

🔹 SELECT문 안에서 서브쿼리 결과를 하나의 컬럼으로 출력

SELECT 이름, 
	(SELECT 부서명FROM 부서 D WHERE D.부서ID = E.부서ID) AS 부서명
FROM 직원 E

 

- 각 직원의 부서명을 조회

➜ (김사원, 기획), (김과장, 개발), (서대리, 기획), (이부장, 마케팅)

5️⃣ 인라인 뷰

🔹 FROM 절에서 임시 테이블처럼 활용

SELECT E.이름, D.부서명
FROM 직원 E, 
(SELECT 부서ID, 부서명 FROM 부서 WHERE 지역='서울') D
WHERE E.부서ID = D.부서ID

 

- 지역이 서울인 부서를 참조하는 뷰를 생성 ➜ (10, 기획), (30, 마케팅)

➜ 해당 부서에 속하는 직원은 (김사원, 기획), (서대리, 기획), (이부장, 마케팅)

👉 뷰는 테이블과 달리 실제 데이터가 디스크에 저장되어 있지 않으며, 논리적으로 생성된 객체이다.

 

※ 뷰의 장점

- 보안성 : 전체 데이터를 노출하지 않음

- 독립성 : 구조 변경시 일관성 유지 가능

- 편의성 : 쿼리 단순화

❗ 인라인 뷰는 중요하여 시험에 나올 가능성이 높음

6️⃣ 상호연관 서브쿼리

🔹 외부 쿼리의 행 하나하나에 대해 서브 쿼리가 실행됨

SELECT E1.이름, E1.연봉
FROM 직원 E1
WHERE E1.연봉 < (SELECT MAX(E2.연봉) FROM 직원 E2 WHERE E2.부서ID = E1.부서ID)

 

- E1 직원 테이블의 데이터를 하나씩 확인하며, 같은 부서의 최고연봉보다 적은 사람 조회 

➜ (서대리, 4000)

2-1. 집합 연산자

1️⃣ 집합 연산자

🔹 두 개 이상의 SELECT 쿼리 결과에 대한 집합 연산 (합집합, 교집합, 차집합 등)을 수행하는 연산자

🔹 두 집합의 스키마(컬럼 수, 컬럼 순서, 데이터 타입)이 일치해야 동작

2️⃣ 합집합

🔹 UNION ALL : 중복 허용

🔹 UNION : 중복 제거, 내부에서 정렬 수행

3️⃣ 교집합

3-1. 그룹 함수

🔹 GROUP BY 절에서 여러 행을 하나의 결과값으로 요약하는 함수

🔹 집계함수(COUNT, SUM, AVG, MIN, MAX)와 ROLLUP, CUBE, GROUPING SET 등의 함수가 존재

1️⃣ ROLLUP

🔹 ROLLUP(컬럼 1, 컬럼 2)인 경우 (컬럼1, 컬럼2) ➜ 컬럼 1 ➜ 전체 행 순으로 그룹화

🔹 ROLLUP는 소계와 총계를 구할 때 사용하며 컬럼 순서 변경 시 결과가 달라짐

2️⃣ CUBE

🔹 CUBE(컬럼 1, 컬럼 2)인 경우, (컬럼 1, 컬럼 2) ➜ 컬럼 1 ➜ 컬럼 2 ➜ 전체 행 순으로 그룹화

🔹 CUBE는 조합 가능한 전체 경우를 고려함

🔹 조회 결과의 갯수는 2의 N 승이 됨

3️⃣ GROUPING SETS

🔹 그룹화할 대상 지정 가능

🔹 NULL 혹은 ( ) 는 모든 행에 대한 전체 그룹화를 수행함

4-1. 윈도우 함수

🔹 GROUP BY 와 달리, 그룹화하지 않고 각 행에 대하여 계산된 값을 반환하는 함수

🔹 OVER 키워드와 함께 사용 (OVER 함수 사용 = 윈도우 함수)

🔹 INPUT과 OUTPUT의 개수가 동일

1️⃣ 순위함수

함수	설명	예시
ROW_NUMBER()	무시 (중복 없이 연속 번호 부여)	1, 2, 3, 4, 5, 6, ...
RANK()	같은 값은 같은 순위를 가지고 순위는 건너뜀	1, 1, 3, 4, 4, 6, ...
DENSE_RANK()	같은 값은 같은 순위를 가지고 순위는 건너뛰지 않음	1, 1, 2, 3, 4, 5, ...

2️⃣ 집계함수

🔹 OVER에 기술된 내용으로 연산만 하고 출력 순서는 보장하지 않으며, 필요 시 ORDER BY 활용

🔹 GROUP BY 에서는 행의 수가 줄어들지만, 윈도우 함수에서는 행의 수를 그대로 유지함

3️⃣ 행 간의 값 참조 함수

함수	설명
LAG()	이전 행의 값 가져오기
LEAD()	다음 행의 값 가져오기
FIRST_VALUE()	정렬 기준 첫 번째 값 가져오기
LAST_VALUE()	정렬 기준 마지막 값 가져오기

 

❗ 윈도우 함수는 현재 행까지만 제한하기에 LAST_VALUE는 범위 지정 키워드 사용이 필요하다.

- 윈도우 함수는 한 줄씩 수행되며, 미래를 보지 못함

4️⃣ 범위 지정 키워드

🔹 ROWS / RANGE BETWEEN [시작 범위] AND [끝 범위]

🔹 범위 지정 생략 시, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 동작

※ 옵션

- N PRECEDING : N번째 이전까지 보겠다

- M FOLLOWING : M번째 미래까지 보겠다

- UNBOUNDED PRECEDING : 제한없이 모든 과거를 보겠다

- UNBOUNDED FOLLOWING : 제한없이 모든 미래를 보겠다

5️⃣ 비율함수

함수	설명
NTILE(n)	데이터를 n 등분하여 그룹 나누기
CUME_DIST()	누적 백분율
PRECENT_RANK()	백분위 순위
RATIO_TO_REPORT()	전체 대비 비율 (부분 / 전체)

 

SELECT 직원, 
	매출,
    NTILE(3) OVER (ORDER BY 매출) AS 분위,
    CUME_DIST() OVER (ORDER BY 매출) AS 누적분포,
    PERCENT_RANK() OVER (ORDER BY 매출) AS 백분위순위,
    RATIO_TO_REPORT(매출) OVER() AS 판매비율
FROM 판매

 

5-1. TOP N 쿼리

🔹 상위 N 개의 데이터를 추출하는 쿼리

1️⃣ TOP

-

SQL Server 에서 사용

-

 사용 예시

SELECT TOP 5 * 
FROM 판매 
ORDER BY 매출 DESC

2️⃣ LIMIT

- MySQL, PostgreSQL 등에서 사용
- 사용 예시

SELECT *
FROM 판매
ORDER BY 매출 DESC
LIMIT 5

3️⃣ FETCH FIRST

- DB2, Oracle 등에서 사용

- 사용 예시

SELECT *
FROM 판매
ORDER BY 매출 DESC
FETCH FIRST 5 ROWS ONLY

4️⃣ ROWNUM

- Oracle 에서 사용

- 사용 예시

SELECT *
FROM (SELECT * FROM 제품 ORDER BY 매출 DESC)
WHERE ROWNUM <= 5

5️⃣ TOP N 쿼리 사용 시 유의사항

🔹 ORDER BY 사용 필수

➜ ORDER  BY 절을 사용하지 않으면 최상위 N 건을 출력

🔹 TOP 사용 시 동일한 순위도 함께 출력하려면 WITH TIES 사용

🔹 ROWNUM = 1 은 사용 가능하나, 큰 숫자에 대한 =, > 연산자는 사용 불가

➜ ROWNUM은 조회 결과에 순차 번호 부여하는 가상의 컬럼

6-1. 계층형 질의와 셀프 조인

1️⃣ 계층형 질의

🔹 데이터가 계층 구조를 가지고 있을 때, 계층 구조를 탐색하기 위하여 사용

 

키워드	설명
LEVEL	계층 구조에서 현재의 레벨을 반환
START WITH	계층 구조의 시작 지점(루트 노트)을 지정
CONNECT BY	부모-자식 관계를 정의하는 절
PRIOR	1) PRIOR 자식 = 부모 : 순방향 (부모 ➜ 자식) 2) PRIOR 부모 = 자식 : 역방향 (자식 ➜ 부모)
NOCYCLE	순환구조가 발생할 경우 무한 루프를 방지
ORDER SIBLINGS BY	동일한 수준(LEVEL) 내에서 정렬
CONNECT_BY_ROOT	데이터의 최상위 루트노드 정보를 반환
CONNECT_BY_ISLEAF	말단(리프) 노드이면 1, 아니면 0 반환
CONNECT_BY_ISCYCLE	순환이 존재하면 1, 아니면 0 반환
SYS_CONNECT_BY_PATH	루트 데이터로부터 현재 위치까지의 경로 표시

 

2️⃣ 셀프 조인

🔹 같은 테이블에 대하여 조인을 수행

7-1. PIVOT 절과 UNPIVOT 절

 

1️⃣ PIVOT 절

🔹 행 데이터를 특정 컬럼 기준으로 열 방향으로 재구성

🔹 LONG DATA를 WIDE DATA로 변경

2️⃣ UNPIVOT 절

🔹 열 데이터를 행으로 변환

🔹 WIDE DATA를 LONG DATA로 변경

8-1. 정규 표현식

문자와 기호를 조합하여 특정 패턴을 검색하거나 일치 여부를 확인할 때 사용

1️⃣ 정규표현식 기호

기호	설명	예시
.	임의의 한 문자	a.c ➜ abc, aic 가능 / ac 불가능
^	문자열의 시작	^010 ➜ 010으로 시작하는 문자열
$	문자열의 끝	com$ ➜ com으로 끝나는 문자열
*	앞 문자가 0번 이상 반복	ho* ➜ h, ho, hoo, hooo 등
+	앞 문자가 1번 이상 반복	ho+ ➜ ho, hoo, hooo 등
?	앞 문자의 0 또는 1회	ho? ➜ h, ho
[ ]	문자 집합	[abc] ➜  a, b, c [a-z] ➜  a부터 z 까지
[^]	부정 문자 집합	[^abc] ➜ abc를 제외한 나머지 문자
{n}	n회 반복	a{3} ➜ aaa
{n, m}	n ~ m회 반복	a{2, 4} ➜ aa, aaa, aaaa
( )	그룹핑	(ab) + ➜  ab, abab, ababab 등
|	둘 중 하나 이상 일치	^ab|cd$ ➜  ab로 시작 또는 cd로 끝남
\	기능 무효화(이스케이프)	a \ |b ➜ a|b 문자가 출력(|는 일반문자)

 

※ 한글은 한 음절이 하나의 문자 단위

2️⃣ 정규표현식 함수

🔹 REGEXP_LIKE

- 문자열이 정규식과 일치하는지 여부 판단

- ex) REGEXP_LIKE('hello123', '^[a-z]+[0-9]+$') ➜ TRUE

➜ 소문자 + 숫자 형식이 맞으므로 TRUE 반환

🔹 REGEXP_REPLACE

- 정규식에 일치하는 부분 대체

- ex) REGEXP_REPLACE('010/1234/5678', '/', '-') ➜ 010-1234-5678

➜ ' ' 문자열을 '-'로 대체

---

🔗 출처

[SQLD 완벽 요약강의]