1. SQL 심화
WITH문
- 복잡한 쿼리를 단계별로 만들 수 있음
- 가독성이 좋음
- 같은 결과를 여러 번 사용 가능함
- 디버깅하기 좋음
WITH 임시 테이블 명 AS (
WITH 문으로 저장하고 싶은 SQL 쿼리문
)
SELECT *
FROM 임시 테이블 명
WITH문 언제 쓰는가?
- JOIN이 많을 때
- GROUP BY 여러 단계일 때
- 데이터 전처리 단계가 있을 때
- 쿼리가 길어질 때
WHERE 1=1
WHERE 1=1 은 SQL에서 조건을 쉽게 붙이기 위한 관용적인 패턴- 사실 논리적으로 아무 의미가 없음 (항상 참)
- 단순 쿼리에서는 굳이 필요 없음
SELECT *
FROM table
WHERE 1=1
# 위에 쿼리문이랑 완전히 같음
SELECT *
FROM table
WHERE 1=1 그럼 왜 쓰느냐?
- 조건을 쉽게 추가하려고 쓰는 SQL 습관
- 조건을 AND로 계속 붙이기 편하게 하기 위해 사용
2. SQL 심화 - 실무에서 많이 쓰이는 고급 SQL 기술
윈도우 함수(Window Function)
- 윈도우 함수는 특정 그룹 또는 범위 안에서 계산을 수행하는 함수
OVER() 안에 3가지 요소는 윈도우 함수가 어떤 범위에서 계산할지 결정하는 역할을 함
SELECT 컬럼 명,
WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;
- ARGUMENTS : 윈도우 함수에 따라서 필요한 인수
- PARTITION BY 컬럼 : 데이터 전체 집합에서 소그룹으로 나누는 기준, GROUP BY와 비슷하지만 차이점은 행을 합치지 않고 그래도 유지한다는 것
- ORDER BY 절 : 소그룹에 대한 계산 순서를 결정하는 정렬 기준
- WINDOWING 절 : 행에 대한 범위 기준, 계산 범위
**# WINDOWING 절의 형태**
SELECT 컬럼 명,
행 순서 함수(기준 컬럼) OVER ([PARTITION BY 절] [ORDER BY 절] **[ROWS BETWEEN A AND B]**)
FROM 테이블 명
| 구조 |
설명 |
| CURRENT ROW |
현재 행 |
| UNBOUNDED PRECEDING |
윈도우의 시작 위치가 첫번째 행 |
| UNBOUNDED FOLLOWING |
윈도우의 마지막 위치가 마지막 행 |
| n PRECEDING |
이전 n행 |
| n FOLLOWING |
다음 n행 |
순위함수(RANK, DENSE_RANK, ROW_NUMBER)
- 순위 함수에는 ARGUMENTS(인수)를 별도로 넣지 않음
SELECT 컬럼 명,
RANK() OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;
| 함수 |
동점 처리 |
다음 순위 |
| RANK |
같은 순위 |
건너뜀 |
| DENSE_RANK |
같은 순위 |
건너뛰지 않음 |
| ROW_NUMBER |
없음 |
그대로, 통상적으로 Primary Key 오름차순으로 순위 부여 |
집계함수(SUM, AVG, MAX, MIN)
- 집계 함수에는 ARGUMENTS(인수)를 반드시 넣어야 함
- 일반 집계와 다른 점은 GROUP BY처럼 행을 하나로 줄이지 않고 각 행에 계산 결과를 붙여 준다는 것
SELECT 컬럼 명,
집계 함수(컬럼) OVER ([PARTITION BY 절] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명
| 함수 |
윈도우 함수 역할 |
| SUM(컬럼) |
컬럼을 기준으로 합계 |
| AVG(컬럼) |
컬럼을 기준으로 그룹 평균 / 이동 평균 |
| MAX(컬럼) |
컬럼을 기준으로 그룹 최대값 |
| MIN(컬럼) |
컬럼을 기준으로 그룹 최소값 |
행 순서 집계함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD)
- 특정 행을 기준으로 앞이나 뒤의 값을 가져오는 함수
- 주로 데이터 비교, 이전값/다음값 분석, 첫 값/마지막 값 확인 등에 사용
SELECT 컬럼 명,
행 순서 함수(기준 컬럼) OVER ([PARTITION BY 절] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명
| 행 순서 함수 |
설명 |
| FIRST_VALUE(컬럼) |
조건을 만족하는 윈도우 가장 첫번째 값 |
| LAST_VALUE(컬럼) |
조건을 만족하는 윈도우 가장 마지막 값 |
| LAG(컬럼, n) |
이전 n번째 행 값 |
| LEAD(컬럼, n) |
이후 n번째 행 값 |
그룹 함수(ROLLUP, CUBE, GROUPING SETS)
- GROUP BY의 확장 기능으로 집계 결과에 “소계(subtotal)”와 “총계(grand total)”를 자동으로 계산해주는 기능
- 주로 데이터 분석 / 보고서 / OLAP 분석에서 사용
SELECT 컬럼 명,
집계 함수(인수)
FROM 테이블 명
GROUP 그룹 함수(컬럼1, 컬럼2)
| 구조 |
설명 |
| ROLLUP |
소그룹 간 소계 계산(형식 정해져 있음) |
| CUBE |
다차원적 소계 계산(형식 정해져 있음) |
| GROUPING SETS |
특정 항목의 소계 계산(형식 X) |
# GROUPING SETS : 원하는 소계와 총계 값을 정할 수 있음
SELECT department, job, SUM(salary)
FROM `modulabs.employees`
GROUP BY GROUPING SETS((department, job), (department), ()) -> ROLLUP 함수랑 같은 결과
ROLLUP - (department, job), (department), (총계)
CUBE - (department, job), (department), (job), (총계)
GROUPING SETS - (department), (job)
복잡한 데이터 처리(JSON Formatting)
로그 데이터란?
- 로그 데이터(Log Data)는 시스템, 프로그램, 앱, 웹사이트, 서버 등에서 어떤 일이 언제 발생했는지 기록해 둔 데이터
- 컴퓨터가 남기는 활동 기록
- 시스템이나 사용자의 활동 기록
- 언제, 누가, 무엇을 했는지 저장
- 오류 추적, 사용자 분석, 보안 점검에 사용
JSON(JavaScript Object Notation)이란?
- 데이터를 표현하는 형식
- 보통 키(key) : 값(value) 형태로 이루어져 있음
- 구조가 분명하고 프로그램이 읽기 쉽고 항목 추가가 쉽고 분석하기 편하기 때문에 많이 사용하는 형식
JSON Formatting이란?
- JSON 데이터를 형식에 맞게 정리해서 쓰는 것
- JSON을 보기 좋고, 규칙에 맞게 작성하는 방식
- 들여쓰기, 큰따옴표, 쉼표, 괄호 구조가 중요
# JSON 구조의 로그 데이터
{"user": "user1", "action": "login", "timestamp": "2023-11-01T08:00:00"}
{"user": "user2", "action": "logout", "timestamp": "2023-11-01T09:30:00"}
{"user": "user1", "action": "purchase", "timestamp": "2023-11-01T10:15:00"}
JSON_EXTRACT : JSON 데이터 안에서 특정 필드(값)를 추출하는 함수
# 기본 문법
SELECT *,
JSON_EXTRACT(log, '$.user') AS user,
JSON_EXTRACT(log, '$.action') AS action,
JSON_EXTRACT(log, '$.timestamp') AS timestamp
FROM `modulabs.user_logs`
| 함수 |
결과 |
| JSON_EXTRACT |
JSON 형식 그대로 반환(예 : "user1" 따옴표 포함) |
| JSON_EXTRACT_SCALAR |
문자열 값만 반환(예 : user1) |
# JSON 로그 데이터에 배열이 있는 경우
{
"user_id": "u01",
"items": ["apple", "banana"]
}
# 문법
SELECT JSON_EXTRACT(log_data, '$.items[0]')
FROM logs
# JSON 로그 데이터에 중첩이 있는 경우
{
"user": {
"id": "u01",
"age": 25
}
}
# 문법
SELECT JSON_EXTRACT(log_data, '$.user.id')
FROM logs
🚨 오늘의 이슈
WITH문이랑 FROM 서브쿼리랑 뭐가 다름? 똑같은거 아님?
FROM에 넣는 서브쿼리 (Inline Subquery)
- 한 번만 사용
- 안쪽으로 계속 중첩됨
- 복잡해지면 읽기 힘듦
WITH문 (CTE: Common Table Expression)
- 가짜 테이블을 하나 만든 느낌
- 쿼리가 위에서 아래로 읽힘
- 여러 번 재사용 가능
# 실무에서 보통
서브쿼리 → 간단할 때
WITH → 복잡할 때
서브쿼리 = 바로 넣는 임시 테이블
WITH = 이름 붙인 임시 테이블
- 예시 : 이렇게 비교해보니 WITH 왜 쓰는지 납득 완!
# 서브쿼리 -> 중첩 지옥
SELECT *
FROM (
SELECT *
FROM (
SELECT *
FROM table
)
)
# WITH -> 단계별 처리
WITH step1 AS (...),
step2 AS (...),
step3 AS (...)
SELECT *
FROM step3
