[SQL] SQL 활용 2
Top-N 쿼리
- 특정 조건에서 상위 N개의 행만 추출하는 쿼리
ROWNUM
- 번호를 매기는 가상 컬럼
- WHERE ROWNUM=5 와 같은 건너뛰기 조건 성립 ❌
- 반드시 < 조건이나 <= 조건으로 사용해야 함
- Oracle 에서 사용
예제
SELECT *
FROM (
SELECT *
FROM employees
ORDER BY salary DESC
)
WHERE ROWNUM <= 3;➡️ 급여가 세 번째로 높은 사원에 대한 정보 추출
윈도우 함수의 순위 함수
ROW_NUMBER
- 정렬 순서대로 고유한 순번을 부여하는 함수
예제
SELECT *
FROM (
SELECT
employee_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn <= 3;RANK()
- 같은 값은 같은 순위, 다음 순위는 건너뜀
예제
SELECT *
FROM (
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
) t
WHERE rk <= 3;DENSE_RANK()
- 같은 값은 같은 순위, 다음 순위를 건너뛰지 ❌
예제
SELECT *
FROM (
SELECT
employee_id,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
FROM employees
) t
WHERE drk <= 3;셀프 조인(self join)
- 자기 자신과의 join
- FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막기 위해 ALIAS 를 반드시 표기
예제
직원 테이블
| emp_id | emp_name | manager_id | salary |
|---|---|---|---|
| 1 | CEO | NULL | 9000 |
| 2 | Alice | 1 | 6000 |
| 3 | Bob | 1 | 5500 |
| 4 | Carol | 2 | 4000 |
| 5 | Dave | 2 | 3800 |
쿼리문
SELECT
e.emp_name AS employee,
m.emp_name AS manager
FROM
employees e
LEFT JOIN
employees m
ON
e.manager_id = m.emp_id;결과
| employee | manager |
|---|---|
| CEO | NULL |
| Alice | CEO |
| Bob | CEO |
| Carol | Alice |
| Dave | Alice |
➡️ 직원과 관리자 이름 같이 보기
계층 쿼리
계층 구조를 이루는 컬럼이란?
한 행이 다른 행을 부모로 참조하는 구조
계층구조를 이루는 컬럼이 들어간 테이블 예시
| EMPLOYEE_ID | NAME | MANAGER_ID |
|---|---|---|
| 100 | Steven | (NULL) |
| 101 | Neena | 100 |
| 102 | Lex | 101 |
➡️ MANAGER_ID 가 같은 테이블의 EMPLOYEE_ID 를 참조함(자기 참조)
계층 쿼리란?
계층 구조를 따라 위아래 관계를 재귀적으로 조회하는 쿼리
✅ 각 키워드 설명
| 키워드 | 의미 |
|---|---|
LEVEL | 현재 계층 깊이 |
SYS_CONNECT_BY_PATH(컬럼, 구분자) | 루트부터 현재 노드까지의 경로 문자열 생성 |
START WITH | 최상위 카테고리부터 시작 (부모가 없는 항목) |
CONNECT BY PRIOR ... = ... | 부모 → 자식 관계 정의 |
CONNECT_BY_ROOT 컬럼 | 루트 노드의 주어진 컬럼 값 반환 |
CONNECT_BY_ISLEAF | 가장 하위 노드인 경우: 1 반환, 그 외: 0 반환 |
예제 1️⃣: 직원의 상사 경로 문자열로 출력하기
EMPLOYEE 테이블
| EMPLOYEE_ID | NAME | MANAGER_ID |
|---|---|---|
| 1 | CEO | (NULL) |
| 2 | CTO | 1 |
| 3 | Dev1 | 2 |
| 4 | Dev2 | 2 |
| 5 | CFO | 1 |
쿼리문
SELECT LEVEL,
EMPLOYEE_ID,
NAME,
SYS_CONNECT_BY_PATH(NAME, ' -> ') AS HIERARCHY_PATH
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;결과 테이블
| LEVEL | EMPLOYEE_ID | NAME | HIERARCHY_PATH |
|---|---|---|---|
| 1 | 1 | CEO | → CEO |
| 2 | 2 | CTO | → CEO → CTO |
| 3 | 3 | Dev1 | → CEO → CTO → Dev1 |
| 3 | 4 | Dev2 | → CEO → CTO → Dev2 |
| 2 | 5 | CFO | → CEO → CFO |
루트 -> 리프
쿼리문
SELECT LEVEL,
EMPLOYEE_ID,
NAME,
MANAGER_ID,
CONNECT_BY_ROOT NAME AS ROOT_MANAGER,
CONNECT_BY_ISLEAF AS IS_LEAF
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;결과 테이블
| LEVEL | EMPLOYEE_ID | NAME | MANAGER_ID | ROOT_MANAGER | IS_LEAF |
|---|---|---|---|---|---|
| 1 | 1 | CEO | NULL | CEO | 0 |
| 2 | 2 | CTO | 1 | CEO | 0 |
| 3 | 3 | Dev1 | 2 | CEO | 1 |
| 3 | 4 | Dev2 | 2 | CEO | 1 |
| 2 | 5 | CFO | 1 | CEO | 1 |
리프 -> 루트
예시 테이블
| EMPLOYEE_ID | NAME | MANAGER_ID |
|---|---|---|
| 1 | CEO | NULL |
| 2 | Manager | 1 |
| 3 | Staff | 2 |
구조 설명
CEO
└── Manager
└── Staff쿼리문
SELECT LEVEL,
EMPLOYEE_ID,
NAME,
SYS_CONNECT_BY_PATH(NAME, ' → ') AS HIERARCHY_PATH
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 3 -- 리프 직원부터 시작
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;결과 테이블
| LEVEL | EMPLOYEE_ID | NAME | HIERARCHY_PATH |
|---|---|---|---|
| 1 | 3 | Staff | → Staff |
| 2 | 2 | Manager | → Staff → Manager |
| 3 | 1 | CEO | → Staff → Manager → CEO |
✅ 설명
START WITH EMPLOYEE_ID = 3: Staff부터 시작 (리프 노드)
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID: 자식 → 부모로 올라감
SYS_CONNECT_BY_PATH: 시작 노드 기준으로 이름을 이어붙임
정렬
- ORDER BY 를 쓰면 ❌: 계층 구조와는 전혀 상관없이 정렬됨
- ORDER SIBLINGS BY 사용
- 계층 쿼리에서 같은 부모를 가진 형제 노드들 간의 정렬
예제
EMPLOYEE 테이블
| EMPLOYEE_ID | NAME | MANAGER_ID |
|---|---|---|
| 1 | CEO | NULL |
| 2 | CTO | 1 |
| 3 | CFO | 1 |
| 4 | Dev1 | 2 |
| 5 | Dev2 | 2 |
| 6 | Finance1 | 3 |
| 7 | Finance2 | 3 |
구조
CEO
├── CFO
│ ├── Finance1
│ └── Finance2
└── CTO
├── Dev1
└── Dev2쿼리문
SELECT LEVEL, NAME
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY NAME;결과 테이블
| LEVEL | NAME |
|---|---|
| 1 | CEO |
| 2 | CFO |
| 3 | Finance1 |
| 3 | Finance2 |
| 2 | CTO |
| 3 | Dev1 |
| 3 | Dev2 |
PIVOT 절과 UNPIVOT 절
PIVOT 절
- 행 -> 열로 데이터를 변형하여 특정값 기준으로 그룹화와 집계를 할 수 있도록 해주는 기능
- 원하는 데이터만 필터링할 수 있는 기능 🅾️: WHERE 절 사용
- PIVOT 절 안에서 한 번에 여러 개의 집계함수 사용 🅾️
- FOR 절에서 컬럼 추가🅾️: 그루핑의 기준을 늘려 가로로 출력
- CASE WHEN 구문을 사용하여 PIVOT 절을 사용한 것과 동일한 결과 도출 🅾️
SELECT * FROM (원본 테이블 또는 서브쿼리) PIVOT(집계함수: SUM() FOR ~ IN ~);
집계함수: 결과 데이터에 표시할 집계 데이터
FOR절: PIVOT 할 컬럼
IN절: PIVOT 할 컬럼을 지정
예시
| 부서 | 월 | 매출 |
|---|---|---|
| A | 1월 | 100 |
| A | 2월 | 150 |
| B | 1월 | 200 |
이런 매출 데이터를
| 부서 | 1월 | 2월 |
|---|---|---|
| A | 100 | 150 |
| B | 200 | NULL |
이런 식으로 가로로 회전(pivot) 시켜주는 것 !
예제
PIVOT 쿼리문
SELECT *
FROM (
SELECT 부서, 월, 매출
FROM SALES
)
PIVOT (
SUM(매출)
FOR 월 IN ('1월', '2월')
);=
CASE WHEN 쿼리문
SELECT
부서,
SUM(CASE WHEN 월 = '1월' THEN 매출 END) AS "1월",
SUM(CASE WHEN 월 = '2월' THEN 매출 END) AS "2월"
FROM SALES
GROUP BY 부서;결과 테이블
| 부서 | '1월' | '2월' |
|---|---|---|
| A | 100 | 150 |
| B | 200 | NULL |
Alias in pivot절
- 기본적으로 언더바(_) 와 함께 표시
SELECT *
FROM (
SELECT 부서, 월, 매출
FROM SALES
)
PIVOT (
SUM(매출) AS SAL
FOR 월 IN ('1월' AS JAN, '2월')
);결과 테이블
| 부서 | JAN_SAL | '2월' |
|---|---|---|
| A | 100 | 150 |
| B | 200 | NULL |
UNPIVOT 절
- 열 -> 행으로 변환하는 기능
- WHERE 절을 이용하여 필터링 🅾️
-UNPIVOT INCLUDE NULLS:NULL 데이터도 함께 출력
SELECT * FROM(~) UNPIVOT(~ FOR ~ IN ~);
예제
원본 테이블(열이 넓은 형태)
| 부서 | "1월" | "2월" |
|---|---|---|
| A | 100 | 150 |
| B | 200 | NULL |
UNPIVOT 으로 세로로 변환
| 부서 | 월 | 매출 |
|---|---|---|
| A | 1월 | 100 |
| A | 2월 | 150 |
| B | 1월 | 200 |
| B | 2월 | NULL |
쿼리문
SELECT 부서, 월, 매출
FROM (
SELECT *
FROM SALES_PIVOTED
)
UNPIVOT (
매출 FOR 월 IN ("1월", "2월")
);UNPIVOT: 열 -> 행 변환 선언
매출 FOR 월 IN (기존 컬럼명): 매출 값을 모을 열 이름 = 매출, 원래의 열 이름은 월
FOR 절: UNPIVOT 된 값에 대한 설명이 들어갈 컬럼 지정
IN 절: FOR 절에서 생성한 컬럼에 표시될 데이터 값 지정
UNPIVOT 절에서 Alias
- IN 절에 Alias 지정 시 FOR 절에 표시된 열의 데이터를 다르게 출력 🅾️
원본 테이블
| 부서 | "JAN" | "FEB" | "MAR" |
|---|---|---|---|
| A | 100 | 150 | 200 |
| B | 80 | 120 | 160 |
목표: 영문 표기된 월을 '1월', '2월', '3월' 로 변경
쿼리문
SELECT 부서, 월, 매출
FROM (
SELECT *
FROM SALES_PIVOTED
)
UNPIVOT (
매출 FOR 월 IN (
"JAN" AS '1월',
"FEB" AS '2월',
"MAR" AS '3월'
)
);결과 테이블
| 부서 | 월 | 매출 |
|---|---|---|
| A | 1월 | 100 |
| A | 2월 | 150 |
| A | 3월 | 200 |
| B | 1월 | 80 |
| B | 2월 | 120 |
| B | 3월 | 160 |
정규표현식
- 특정 규칙에 맞는 문자열 패턴을 정의하는 식
REGEXP_SUBSTR 함수
- 문자열에서 특정 패턴에 맞는 부분 추출하는 함수
REGEXP_SUBSTR(source_string, pattern, position, occurrence, match_parameter, subexpr)
| 인자 | 의미 |
|---|---|
| source_string | 검색할 문자열 |
| pattern | 정규표현식 |
| position | 검색 시작 위치 |
| occurrence | 몇 번째 매칭 결과 |
| match_parameter | 'i'는 대소문자 무시 |
| subexpr | 괄호 ( )로 감싼 몇 번째 그룹을 추출할지 지정 |
기본연산자
| 연산자 | 의미 | 예시 |
|---|---|---|
. | 임의의 한 문자 | a.b는 "aab", "acb"와 일치하지만 "bca"와는 일치하지 않음 |
| ` | ` | or의 의미를 가짐 |
\ | 뒤에 오는 문자를 일반 문자로 취급 | 연산자로 쓰이는 기호 앞에 붙으면 해당 문자는 연산자가 아닌 문자로 해석 |
^ | 문자열의 시작을 나타냄 | ^a는 "apple"과 일치하지만 "banana"와는 일치하지 않음 |
$ | 문자열의 끝을 나타냄 | n$는 "melon"과 일치하지만 "banana"와는 일치하지 않음 |
| 연산자 | 의미 | 예시 패턴 | 매치되는 문자열 예시 |
|---|---|---|---|
? | 앞 문자가 0개 또는 1개 | colou?r | ✅ color, ✅ colour, ❌ colouur (u가 2번이라 불일치) |
* | 앞 문자가 0개 이상 | go*gle | ✅ ggle (o 0개), ✅ gogle (o 1개), ✅ gooogle (o 여러 개), ❌ gle (g 빠짐) |
+ | 앞 문자가 1개 이상 | go+gle | ❌ ggle (o 없음), ✅ gogle (o 1개), ✅ goooooogle (o 여러 개) |
패턴을 나타내는 연산자
| 연산자 | 의미 |
|---|---|
[] | 대괄호 안의 문자 중 하나와 일치 |
[-] | 연속 문자의 범위를 지정 |
[^] | 대괄호 안의 문자들을 제외한 나머지 문자 중 하나와 일치 |
() | 소괄호로 묶인 표현식을 한 단위로 취급 |
POSIX 문자 클래스
| 연산자 | 의미 | 동일 표현 |
|---|---|---|
[:digit:] | 숫자 | [0–9] |
[:lower:] | 소문자 | [a–z] |
[:upper:] | 대문자 | [A–Z] |
[:alpha:] | 영문자 | [a–zA–Z] |
[:alnum:] | 영문자와 숫자 | [0–9a–zA–Z] |
[:xdigit:] | 16진수 | [0–9a–fA–F] |
[:punct:] | 구두점 문자 | [^[:alnum:][:cntrl:]] |
[:blank:] | 공백 문자 (Space, Tab) | |
[:space:] | 공간 문자 (Space, Enter, Tab) |
예시: 소괄호 이해하기
SELECT
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) AS R1,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2) AS R2,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 3) AS R3,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) AS R4
FROM DUAL;정규 표현식 해석
| 그룹 번호 | 내용 |
|---|---|
| (1) | 123 |
| (2) | 4(56)(78) → 결과는 45678 |
| (3) | 56 |
| (4) | 78 |
결과
| 컬럼 | 추출된 그룹 | 값 |
|---|---|---|
| R1 | 1번째 그룹 | 123 |
| R2 | 2번째 그룹 | 45678 |
| R3 | 3번째 그룹 | 56 |
| R4 | 4번째 그룹 | 78 |
✅ 예시: 이메일 주소 중 도메인만 추출
SELECT REGEXP_SUBSTR('user@example.com', '@[a-zA-Z0-9.]+') AS domain
FROM dual;🔍 설명
@[a-zA-Z0-9.]+: @ 뒤에 영문자, 숫자, 점(.)이 하나 이상 오는 패턴
결과: @example.com
REGEXP_REPLACE 함수
- 문자열 내에서 정규표현식 패턴과 일치하는 부분을 찾아 이를 지정하고, 다른 문자열로 대체하는 함수
REGEXP_REPLACE(source_string, pattern, replacement_string [, position [, occurrence [, match_parameter]]])
| 인자 | 설명 |
|---|---|
source_string | 원본 문자열 |
pattern | 정규표현식 (찾을 패턴) |
replacement_string | 치환할 문자열 |
position (선택) | 검색 시작 위치 (기본값: 1) |
occurrence (선택) | 치환할 번째 항목 (기본값: 0 → 모두) |
match_parameter (선택) | 정규표현식 옵션 (예: 'i': 대소문자 무시) |
REGEXP_INSTR 함수
- 문자열에서 정규표현식 패턴과 일치하는 부분의 위치 반환
REGEXP_INSTR(source_string, pattern [, position [, occurrence [, return_option [, match_parameter [, sub_expression]]]])
| 인자 | 설명 |
|---|---|
source_string | 검색 대상 문자열 |
pattern | 정규표현식 패턴 |
position (선택) | 검색 시작 위치 (기본값: 1) |
occurrence (선택) | 찾을 번째 발생 (기본값: 1) |
return_option (선택) | 0: 일치한 위치 반환 (기본값) 1: 일치한 문자열 다음 위치 반환 |
match_parameter (선택) | 'i': 대소문자 구분 안 함'c': 대소문자 구분 |
sub_expression (선택) | 특정 괄호 그룹의 위치를 반환 (1부터 시작) |
REGEXP_COUNT 함수
- 정규표현식 패턴과 일치하는 부분이 몇 번 나타나는지 계산
REGEXP_COUNT(source_string, pattern [, position [, match_parameter]])
| 인자 | 설명 |
|---|---|
source_string | 검색 대상 문자열 |
pattern | 찾고자 하는 정규표현식 |
position | (선택) 검색 시작 위치 (기본: 1) |
match_parameter | (선택) 검색 조건 ('i' 등: 대소문자 무시 등) |
REGEXP_LIKE 조건
- 정규표현식을 사용하여 문자열 패턴과 일치하는지 여부 확인
REGEXP_LIKE(column_or_string, pattern [, match_parameter])
| 인자 | 설명 |
|---|---|
column_or_string | 검사할 문자열 또는 컬럼명 |
pattern | 정규표현식 |
match_parameter | (선택) 매칭 옵션 (예: 'i': 대소문자 무시) |
예제: 숫자로만 이뤄진 행 찾기
SELECT * FROM users
WHERE REGEXP_LIKE(phone, '^[0-9]+$');