[SQL] SQL 활용 1

언교동·2025년 5월 29일

SQL

목록 보기

6/6

서브쿼리

하나의 쿼리 안에 존재하는 또 다른 쿼리
ORDER BY 절, INSERT 문의 VALUE 절 등에 사용 가능
다중 행 서브쿼리의 경우 '=' 조건과 함께 사용할 수 ❌
다중 컬럼 서브쿼리의 경우 IN 절과 함께 사용할 수 🅾️

위치에 따른 분류

SQL 절	사용 가능한 서브쿼리 유형
SELECT 절	스칼라 서브쿼리 (Scalar Subquery)
FROM 절	인라인 뷰 (Inline View)
WHERE 절 / HAVING 절	중첩 서브쿼리 (Nested Subquery)

스칼라 서브쿼리(Scalar Subquery)

스칼라: 하나의 값

주로 SELECT 절에 위치하지만 컬럼이 올 수 있는 대부분 위치에서 사용 가능
하나의 값만 반환해야 함

예시

employee 테이블

employee_id	name	salary	department_id
1	철수	3000	10
2	영희	3500	10
3	민수	4000	20

실행쿼리

SELECT 
    employee_id,
    name,
    salary,
    (SELECT AVG(salary) 
     FROM employees 
     WHERE department_id = e.department_id) AS dept_avg_salary
FROM 
    employees e;

최종 결과 테이블

employee_id	name	salary	dept_avg_salary
1	철수	3000	3250
2	영희	3500	3250
3	민수	4000	4000

인라인 뷰(Inline View)

인라인 뷰: 일회용 가상 테이블

FROM 절 등 테이블명이 올 수 있는 위치에서 사용

예시

위의 employee 테이블에 대하여 아래의 쿼리문을 적용시켜 봅니다.

SELECT 
    dept_avg.department_id,
    dept_avg.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg;

최종 결과 테이블

department_id	avg_salary
10	3250
20	4000

중첩 서브쿼리(Nested Subquery)

WHERE 절과 HAVING 절에서 사용

연관 서브쿼리와 비연관 서브쿼리

서브쿼리 유형	설명
연관 서브쿼리	메인쿼리의 컬럼이 서브쿼리 내부에서 사용됨
비연관 서브쿼리	메인쿼리의 컬럼이 서브쿼리 내부에서 사용되지 않음

비연관 서브쿼리 예시

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

➡️ 서브쿼리는 메인쿼리의 컬럼에 의존하지 않습니다.

연관 서브쿼리 예시

SELECT name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

➡️ 서브쿼리는 메인쿼리의 department_id 라는 컬럼에 의존합니다.

반환하는 데이터 형태에 따른 분류

유형	설명	비교 연산자 예시
단일 행 (Single Row) 서브쿼리	- 서브쿼리가 1건 이하의 데이터를 반환 - 단일 행 비교 연산자와 함께 사용	`=`, `<`, `>`, `<=`, `>=`, `<>`
다중 행 (Multi Row) 서브쿼리	- 서브쿼리가 여러 건의 데이터를 반환 - 다중 행 비교 연산자와 함께 사용	`IN`, `ALL`, `ANY`, `SOME`, `EXISTS`
다중 컬럼 (Multi Column) 서브쿼리	- 서브쿼리가 여러 컬럼의 데이터를 반환	(비교 연산자는 상황에 따라 다름)

단일 행 서브쿼리 예시

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

➡️ 직원들의 평균 급여만 반환

다중 행 서브쿼리 예시

SELECT name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Seoul'
);

➡️ 서울에 있는 부서id 들을 반환

SELECT name, department_id, job_id
FROM employees
WHERE (department_id, job_id) IN (
    SELECT department_id, job_id
    FROM job_assignments
    WHERE project = 'AI Project'
);

➡️ AI 프로젝트에 참여하는 부서-직무 조합에 해당되는 직원만 조회

뷰(View)

SELECT 문으로 정의된 가상의 테이블
가상 테이블이기 때문에 실제 데이터로는 저장되지 ❌
특정한 조회 결과를 마치 테이블처럼 쓸 수 있음

목적

복잡한 쿼리를 간단하게 재사용
보안(민감한 컬럼은 빼고 보여줄 때)
읽기 전용 뷰를 만들어서 원본 테이블 보호

구분	설명
실제 테이블	진짜로 데이터를 저장함
뷰(View)	데이터 저장은 안 함, 오직 SELECT문만 저장되어 있음
따라서	데이터를 요청할 때마다 원본 테이블에서 다시 조회함

뷰 생성: CREATE VIEW 뷰이름 AS SELECT문;
뷰 조회: SELECT``` * FROM 뷰이름;
뷰 변경: CREATE OR REPLACE VIEW 뷰이름 AS SELECT문;
뷰 삭제: DROP VIEW* 뷰이름;
업데이트 가능한 뷰에서 수정: UPDATE 뷰이름 SET 컬럼=값 WHERE 조건
INSERT INTO 뷰이름 (...) VALUES (...);
DELETE FROM 뷰이름 (...) WHERE 조건;

예시

CREATE VIEW sales_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'Sales';

➡️ Sales 부서만 모아놓은 sales_employees 뷰 만들기

집합 연산자

연산자	설명
UNION ALL	각 쿼리의 결과 집합의 합집합. 중복된 행도 그래도 출력
UNION	각 쿼리의 결과 집합의 합집합. 중복 제거
INTERSECT	각 쿼리의 결과 집합의 교집합. 중복 제거
MINUS / EXCEPT	앞 쿼리 결과 집합에서 뒤 쿼리 결과 집합을 뺀 차집합. 중복 제거

그룹 함수

테이블의 여러 행에 대해 계산을 수행해, 하나의 결과를 반환하는 함수
집계함수: 테이블 전체 또는 그룹에 대해 요약 정보를 계산하는 함수
소계함수: 그룹별로 부분합(소계)을 계산하는 기능 또는 결과

ROLLUP

지정된 컬럼들의 계층적인 집계를 계산해주는 연산자
GROUP BY 절에서 사용되는 집계 함수 확장

ROLLUP 구문	포함되는 그룹핑 단계
`ROLLUP(A)`	- A로 그룹핑 - 총합계
`ROLLUP(A, B)`	- A, B로 그룹핑 - A로 그룹핑 - 총합계
`ROLLUP(A, B, C)`	- A, B, C로 그룹핑 - A, B로 그룹핑 - A로 그룹핑 - 총합계

예시

sales 테이블

region	product	amount
서울	A	100
서울	B	200
부산	A	150
부산	B	250

기본 GROUP BY 예제

SELECT region, product, SUM(amount)
FROM sales
GROUP BY region, product;

➡️ 지역별 생산 제품의 합계를 보여줌

결과 테이블

region	product	sum
서울	A	100
서울	B	200
부산	A	150
부산	B	250

1️⃣ ROLLUP(region) 사용

SELECT region, SUM(amount)
FROM sales
GROUP BY ROLLUP(region);

결과 테이블

region	sum
서울	300
부산	400
NULL	700 ← 전체 총계

2️⃣ ROLLUP(region, product) 사용

SELECT region, product, SUM(amount)
FROM sales
GROUP BY ROLLUP(region, product);

결과 테이블

region	product	sum
서울	A	100
서울	B	200
서울	NULL	300 ← 서울 지역 소계
부산	A	150
부산	B	250
부산	NULL	400 ← 부산 지역 소계
NULL	NULL	700 ← 전체 총계

➡️ 작동 방식
1. region, product 별 합계
2. region 별 소계(product 가 NULL 로 나옴)
3. 전체 합계(region 과 product 둘 다 NULL)

3️⃣ ROLLUP(region, product, year)

예시 테이블

region	product	year	amount
서울	A	2022	100
서울	A	2023	150
서울	B	2022	200
부산	A	2022	120
부산	A	2023	180

쿼리문

SELECT region, product, year, SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(region, product, year);

결과 테이블

region	product	year	total_amount	설명
서울	A	2022	100	개별 데이터
서울	A	2023	150
서울	A	NULL	250	서울-A 제품의 소계
서울	B	2022	200
서울	B	NULL	200	서울-B 제품의 소계
서울	NULL	NULL	450	서울 전체 소계
부산	A	2022	120
부산	A	2023	180
부산	A	NULL	300	부산-A 제품의 소계
부산	NULL	NULL	300	부산 전체 소계
NULL	NULL	NULL	750	전체 합계 (총합)

➡️ 작동 방식
1. region + product + year 별 total_amout
2. region + product 별 total_amout(year 은 NULL 로 표시)
3. region 별 total_amout(product, year 은 NULL 로 표시)
4. 전체 총합(region, product, year 은 NULL 로 표시)

CUBE

조합할 수 있는 모든 그룹에 대한 소계를 집계하는 함수

CUBE 구문	그룹핑 내용
CUBE (A)	- A로 그룹핑 - 총합계
CUBE (A, B)	- A, B로 그룹핑 - A로 그룹핑 - B로 그룹핑 - 총합계
CUBE (A, B, C)	- A, B, C로 그룹핑 - A, B로 그룹핑 - A, C로 그룹핑 - B, C로 그룹핑 - A로 그룹핑 - B로 그룹핑 - C로 그룹핑 - 총합계

GROUPING SETS

특정 항목에 대한 소계를 계산하는 함수
인자값으로 ROLLUP 이나 CUBE 사용 🅾️

GROUPING SETS 구문	그룹핑 내용
GROUPING SETS (A, B)	- A로 그룹핑 - B로 그룹핑
GROUPING SETS (A, B, ( ))	- A로 그룹핑 - B로 그룹핑 - 총합계
GROUPING SETS (A, ROLLUP(B))	- A로 그룹핑 - B로 그룹핑 - 총합계
GROUPING SETS (A, ROLLUP(B, C))	- A로 그룹핑 - B, C로 그룹핑 - B로 그룹핑 - 총합계
GROUPING SETS (A, B, ROLLUP(C))	- A로 그룹핑 - B로 그룹핑 - C로 그룹핑 - 총합계

GROUPING

해당 컬럼이 소계/총계로 만들어지는지 확인하는 함수
- 소계/총계 행이면: 1 반환
- 일반 그룹 행이면: 0 반환
ROLLUP, CUBE, GROUPING SETS 등과 함께 쓰임
이전 함수들은 소계를 나타내는 row 에서 그루핑의 기준을 제외한 나머지 컬럼들은 NULL 로 표시
하지만 GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트 출력 가능

예제

테이블: sales

region	product	amount
East	Pen	100
East	Pencil	150
West	Pen	200
West	Pencil	250

GROUPING

SELECT 
  region,
  product,
  SUM(amount) AS total_amount,
  GROUPING(region) AS grp_region,
  GROUPING(product) AS grp_product
FROM sales
GROUP BY ROLLUP(region, product);

결과 테이블

region	product	total_amount	grp_region	grp_product
East	Pen	100	0	0
East	Pencil	150	0	0
East	NULL	250	0	1
West	Pen	200	0	0
West	Pencil	250	0	0
West	NULL	450	0	1
NULL	NULL	700	1	1

CASE 문을 활용하여 원하는 텍스트로 바꾸기

SELECT 
  CASE 
    WHEN GROUPING(region) = 1 AND GROUPING(product) = 1 THEN '전체 총계'
    WHEN GROUPING(region) = 0 AND GROUPING(product) = 1 THEN region || ' 소계'
    ELSE region
  END AS region_label,
  
  CASE 
    WHEN GROUPING(product) = 1 THEN '전체'
    ELSE product
  END AS product_label,

  SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(region, product);

DECODE문을 사용하여 원하는 텍스트로 바꾸기

SELECT 
  DECODE(GROUPING(region), 1, '전체', region) AS region_label,
  DECODE(GROUPING(product), 1, '전체', product) AS product_label,
  SUM(amount) AS total_amount
FROM sales
GROUP BY ROLLUP(region, product);

윈도우 함수

윈도우함수 범위와 기준

범위

범위	의미
`UNBOUNDED PRECEDING`	위쪽 끝 행 (처음 행)
`UNBOUNDED FOLLOWING`	아래쪽 끝 행 (마지막 행)
`CURRENT ROW`	현재 행
`n PRECEDING`	현재 행에서 위로 `n`만큼 이동
`n FOLLOWING`	현재 행에서 아래로 `n`만큼 이동

기준

기준	의미
`ROWS`	행 자체가 기준이 된다.
`RANGE`	행이 가지고 있는 데이터 값이 기준이 된다.

순위 함수

RANK

순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뜀
OVER() 에 어떤 기준으로 순위를 매길 것인지에 대한 조건을 알려주어야 함

예제 테이블: sales

name	region	amount
Alice	East	300
Bob	East	200
Carol	East	300
Dave	East	100

예시

1️⃣ 전체 데이터에 순위 매기기

SELECT 
  name,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

결과 테이블

name	amount	rank
Alice	300	1
Carol	300	1
Bob	200	3
Dave	100	4

2️⃣ 지역(region) 별로 따로 순위 매기기

예제 테이블: sales

name	region	amount
Alice	East	300
Bob	East	200
Carol	East	300
Dave	West	400
Erin	West	300
Frank	West	300

SELECT 
  name,
  region,
  amount,
  RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;

결과 테이블

name	region	amount	region_rank
Alice	East	300	1
Carol	East	300	1
Bob	East	200	3
Dave	West	400	1
Erin	West	300	2
Frank	West	300	2

➡️ 동작 방식
1. PARTITION BY region: East, West 별로 그룹 나눔
2. 각 지역 안에서 ORDER BY amount DESC 기준으로 정렬하여 RANK() 수행
3. 같은 amount 를 가질 경우 동일한 순위
4. 순위는 RANK() 니까 중복 순위 뒤는 건너뜀

PARTITION BY 컬럼명: 컬럼 값에 따라 그룹별로 나눔

DENSE_RANK

순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매김

예시

RANK의 2️⃣번 예제에서 RANK() 를 DENSE_RANK() 로만 바꿔주면 결과테이블은 다음과 같다.

결과 테이블

name	region	amount	region_rank
Alice	East	300	1
Carol	East	300	1
Bob	East	200	2
Dave	West	400	1
Erin	West	300	2
Frank	West	300	2

ROW_NUMBER

동일한 값이라도 다른 순위 부여

예시

RANK의 2️⃣번 예제에서 RANK() 대신 ROW_NUMBER() 로 바꿔주면 결과테이블은 다음과 같다.

결과 테이블

name	region	amount	region_rank
Alice	East	300	1
Carol	East	300	2
Bob	East	200	3
Dave	West	400	1
Erin	West	300	2
Frank	West	300	3

집계함수

SUM

데이터의 합계를 구하는 함수

예시

예제 테이블

id	employee	department	amount
1	Alice	A	100
2	Bob	A	150
3	Carol	B	200
4	Dave	A	120
5	Erin	B	180

1️⃣ 전체 누적 합계

SELECT 
  employee,
  amount,
  SUM(amount) OVER () AS total_sales
FROM sales;

결과 테이블

employee	amount	total_sales
Alice	100	750
Bob	150	750
Carol	200	750
Dave	120	750
Erin	180	750

2️⃣ 부서 별 합계

SELECT 
  employee,
  department,
  amount,
  SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM sales;

결과 테이블

employee	department	amount	dept_total
Alice	A	100	370
Bob	A	150	370
Dave	A	120	370
Carol	B	200	380
Erin	B	180	380

3️⃣ 입사 순 누적 합계

OVER 절 내에 ORDER BY 사용
명확한 누적합계를 구하기 위해 RANGE UNBOUNDED PRECEDING 사용하는 것이 안전.

SELECT 
  employee,
  amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

결과 테이블

employee	amount	running_total
Alice	100	100
Bob	150	250
Carol	200	450
Dave	120	570
Erin	180	750

MAX

데이터의 최댓값을 구하는 함수

MIN

데이터의 최솟값을 구하는 함수

AVG

데이터의 평균을 구하는 함수

COUNT

데이터의 건수를 구하는 함수

행 순서 함수

FIRST_VALUE

파티션 별 가장 선두에 위치한 데이터를 구하는 함수
SQL Server(MSSQL) 은 지원 ❌

LAST_VALUE

파티션 별 가장 끝에 위치한 데이터를 구하는 함수
SQL Server(MSSQL) 은 지원 ❌

윈도우 프레임	실제로 가져오는 값
`RANGE UNBOUNDED PRECEDING` (기본값)	현재 행까지의 범위 내에서 "마지막 값"
`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	전체 윈도우의 마지막 행 값을 가져옴 (진짜 맨 마지막 값)

예제

1️⃣ DEFAULT = RANGE UNBOUNDED PRECEDING

예제 테이블

CREATE TABLE scores (
  id INT,
  name VARCHAR(10),
  score INT
);

INSERT INTO scores VALUES
(1, 'Alice', 80),
(2, 'Bob',   90),
(3, 'Carol', 70),
(4, 'Dave',  95),
(5, 'Eve',   85);

쿼리문

SELECT 
  name,
  score,
  LAST_VALUE(score) OVER (
    ORDER BY score
  ) AS last_val_default
FROM scores;

2️⃣ 진짜 맨 마지막 값을 원할 때

SELECT 
  name,
  score,
  LAST_VALUE(score) OVER (
    ORDER BY score
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_val_true
FROM scores;

✅ 결과 비교

name	score	last_val_default	last_val_true
Carol	70	70	95
Alice	80	80	95
Eve	85	85	95
Bob	90	90	95
Dave	95	95	95

LAG

LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

파티션별로 특정 수만큼 앞선 데이터를 구하는 함수
두 번째 인자값을 생략하면 default: 1
SQL Server(MSSQL) 은 지원 ❌

예시

예시 테이블은 LAST_VALUE 에서 사용한 예시테이블을 이어서 쓰도록 하겠습니다.

쿼리문

SELECT 
  id,
  name,
  score,
  LAG(score, 1) OVER (ORDER BY id) AS prev_score
FROM scores;

결과 테이블

id	name	score	prev_score
1	Alice	80	NULL
2	Bob	90	80
3	Carol	70	90
4	Dave	95	70
5	Eve	85	95

LEAD

파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수
두 번째 인자값을 생략하면 default: 1
SQL Server(MSSQL) 은 지원 ❌

비율 함수

RATIO_TO_REPORT

RATIO_TO_REPORT(column) OVER (PARTITION BY ...)

파티션별 합계에서 차지하는 비율을 구하는 함수
SQL Server(MSSQL) 은 지원 ❌

작동방식
현재 행의 값 / 파티션(또는 전체)의 총합

예시

여기서도 예시 테이블은 LAST_VALUE 에서 사용한 예시테이블을 이어서 쓰도록 하겠습니다.

쿼리문

SELECT
  name,
  score,
  RATIO_TO_REPORT(score) OVER () AS score_ratio
FROM scores;

결과 테이블

name	score	score_ratio
Alice	80	0.1860
Bob	90	0.2093
Carol	70	0.1628
Dave	95	0.2209
Eve	85	0.1977

PERCENT_RANK

해당 파티션 내에서 현재 행이 위치하는 백분위 순위 값을 구하는 함수
SQL Server(MSSQL) 은 지원 ❌

PERCENT_RANK() OVER (PARTITION BY column1 ORDER BY column2)

작동 방식
PERCENT_RANK = (RANK - 1) / (COUNT - 1)

예제

기준 테이블

name	score
Carol	70
Alice	80
Eve	85
Bob	90
Dave	95

쿼리문

SELECT
  name,
  score,
  PERCENT_RANK() OVER (ORDER BY score) AS percent_rank
FROM scores;

결과 테이블

name	score	percent_rank
Carol	70	0.0000
Alice	80	0.25
Eve	85	0.5
Bob	90	0.75
Dave	95	1.0

CUME_DIST

해당 파티션에서의 누적 백분율을 구하는 함수
0보다 크고 1보다 작거나 같은 값을 가짐
SQL Server(MSSQL) 은 지원 ❌

✅ 작동방식

현재 행보다 작거나 같은 값의 개수 / 전체 행 수

CUME_DIST() OVER (
[PARTITION BY partition_expression]
ORDER BY order_expression
)

예제

기준 테이블

employee_id	department_id	salary
101	10	3000
102	10	4000
103	10	4000
104	10	5000
105	20	2500
106	20	3000

쿼리문

SELECT 
    employee_id,
    department_id,
    salary,
    CUME_DIST() OVER (
        PARTITION BY department_id
        ORDER BY salary
    ) AS salary_cume_dist
FROM employees
WHERE department_id=10;

결과 테이블

employee_id	salary	CUME_DIST
101	3000	0.2
102	4000	0.6
103	4000	0.6
104	5000	1.0

NTILE

주어진 수만큼 행들을 n 등분한 후 각 행이 어느 등급에 속하는지 알려주는 함수
할당할 행이 남았을 경우 맨 앞의 그룹부터 하나씩 더 채워짐

NTILE(n) OVER (
[PARTITION BY partition_expression]
ORDER BY order_expression
)

예제

기준 테이블

employee_id	salary
1	3000
2	3500
3	4000
4	5000
5	7000

쿼리문

SELECT 
  employee_id,
  salary,
  NTILE(3) OVER (ORDER BY salary) AS salary_group
FROM employees;

결과 테이블

employee_id	salary	salary_group
1	3000	1
2	3500	1
3	4000	2
4	5000	2
5	7000	3

언교동

이전 포스트

[SQL] SQL 활용 1

SQL

서브쿼리

위치에 따른 분류

스칼라 서브쿼리(Scalar Subquery)

인라인 뷰(Inline View)

중첩 서브쿼리(Nested Subquery)

반환하는 데이터 형태에 따른 분류

뷰(View)

집합 연산자

그룹 함수

ROLLUP

CUBE

GROUPING SETS

GROUPING

CASE 문을 활용하여 원하는 텍스트로 바꾸기

DECODE문을 사용하여 원하는 텍스트로 바꾸기

윈도우 함수

윈도우함수 범위와 기준

범위

기준

순위 함수

RANK

DENSE_RANK

ROW_NUMBER

집계함수

SUM

MAX

MIN

AVG

COUNT

행 순서 함수

FIRST_VALUE

LAST_VALUE

LAG

LEAD

비율 함수

RATIO_TO_REPORT

PERCENT_RANK

CUME_DIST

NTILE

[SQL] SQL 기본 2

0개의 댓글