15가지 필수 SQL 쿼리 실전/응용 사례로 창의적 발상
- 재귀 CTE(Recursive CTEs)
- 데이터 피벗(Pivoting Data)
- 러닝 합계(Running Totals)
- 문자열 집계(String Aggregation)
- 백분위수 계산(Percentile Calculations)
- 간격과 군집 탐지(Gaps and Islands Detection)
- 파티션 내 마지막 값(Last Value in Partition)
- 중복 제거(Removing Duplicates Efficiently)
- 코호트 분석(Cohort Analysis)
- 윈도우 함수로 데이터 중복 제거(Data Deduplication with Window Functions)
- 보간(Interpolation within SQL)
- 이동 평균(Calculating Moving Averages)
- 달력 날짜 생성(Generating Calendar Dates)
- JSON 데이터 조작(JSON Data Manipulation)
- 동적 SQL(Dynamic SQL)
1. 재귀 CTE(Recursive CTEs)
이론
재귀 CTE(Common Table Expression)는 계층형 데이터 탐색이나 프로시저 코드 없이 시퀀스를 생성하는 데 사용됩니다.
예시 쿼리
WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;
출력
n
1
2
3
4
5
실무 시나리오
조직도, 파일 디렉토리와 같은 계층형 데이터 탐색에 활용.
2. 데이터 피벗(Pivoting Data)
이론
피벗은 행을 열로 변환하여 가독성을 높입니다.
예시 쿼리 (PostgreSQL)
SELECT * FROM crosstab(
$SELECT year, category, amount FROM sales$,
$VALUES ('Electronics'), ('Books'), ('Clothing')$
) AS ct(year INT, electronics INT, books INT, clothing INT);
실무 시나리오
거래 데이터를 요약 보고서 형태로 변환.
3. 러닝 합계(Running Totals)
이론
누적 데이터를 추적하는 데 필수적입니다.
예시 쿼리
SELECT
order_id,
amount,
SUM(amount) OVER (ORDER BY order_id) AS running_total
FROM orders;
실무 시나리오
재무 보고, 매출 분석에 활용.
4. 문자열 집계(String Aggregation)
이론
여러 행의 문자열을 하나의 구분자 문자열로 결합합니다.
예시 쿼리
SELECT customer_id,
STRING_AGG(product_name, ', ') AS products
FROM orders
GROUP BY customer_id;
출력
| customer_id | products |
|---|---|
| 1 | Phone, Laptop |
| 2 | Book, Pen, Notebook |
실무 시나리오
고객별 구매 품목 리스트 생성.
5. 백분위수 계산(Percentile Calculations)
이론
데이터 분포 분석에 핵심적인 지표.
예시 쿼리
SELECT DISTINCT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER() AS median_score
FROM test_scores;
실무 시나리오
중위 매출, 성과 지표 분석.
6. 간격과 군집 탐지(Gaps and Islands Detection)
이론
연속 구간(군집)과 비연속 구간(간격)을 식별합니다.
예시 쿼리
WITH grouped AS (
SELECT id, date,
date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date) AS grp
FROM events
)
SELECT MIN(date) AS start_date, MAX(date) AS end_date
FROM grouped
GROUP BY grp;
실무 시나리오
사용자 비활동 기간 탐지.
7. 파티션 내 마지막 값(Last Value in Partition)
이론
그룹 내 마지막 값을 조회합니다.
예시 쿼리
SELECT customer_id,
product_name,
LAST_VALUE(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_purchase
FROM purchases;
실무 시나리오
고객별 마지막 구매일 추적.
8. 중복 제거(Removing Duplicates Efficiently)
이론
윈도우 함수를 활용해 효율적으로 중복 데이터를 삭제합니다.
예시 쿼리
DELETE FROM customers
WHERE id IN (
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rnk
FROM customers
) t WHERE t.rnk > 1
);
실무 시나리오
고객 레코드 고유성 유지.
9. 코호트 분석(Cohort Analysis)
이론
첫 활동 시점을 기준으로 사용자 유지율을 추적합니다.
예시 쿼리
WITH first_purchase AS (
SELECT user_id, MIN(purchase_date) AS first_date
FROM purchases
GROUP BY user_id
)
SELECT a.user_id, a.purchase_date, b.first_date,
EXTRACT(DAY FROM a.purchase_date - b.first_date) AS days_since_first
FROM purchases a
JOIN first_purchase b ON a.user_id = b.user_id;
실무 시나리오
구독 서비스의 유지율 분석.
10. 윈도우 함수로 데이터 중복 제거
이론
최신 데이터를 유지하면서 중복 제거.
예시 쿼리
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY email ORDER BY last_updated DESC) AS rn
FROM users
) AS temp
WHERE rn = 1;
실무 시나리오
데이터 정제 작업.
11. 보간(Interpolation within SQL)
이론
이전 또는 다음 값을 사용해 결측치 채우기.
예시 쿼리
SELECT id, value,
LAST_VALUE(value IGNORE NULLS) OVER (ORDER BY id) AS filled_value
FROM data;
실무 시나리오
시계열 데이터 보간.
12. 이동 평균(Calculating Moving Averages)
이론
시계열 데이터를 부드럽게 처리.
예시 쿼리
SELECT date, sales,
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM daily_sales;
실무 시나리오
주식 시장 분석.
13. 달력 날짜 생성(Generating Calendar Dates)
이론
프로그래밍 없이 날짜 시퀀스를 생성.
예시 쿼리
WITH RECURSIVE dates AS (
SELECT CAST('2024-01-01' AS DATE) AS day
UNION ALL
SELECT day + 1 FROM dates WHERE day < '2024-01-10'
)
SELECT * FROM dates;
출력
day
2024-01-01
2024-01-02
…
2024-01-10
실무 시나리오
시간 차원 데이터 조인.
14. JSON 데이터 조작(JSON Data Manipulation)
이론
SQL에서 JSON 필드 추출.
예시 쿼리
SELECT data->>'name' AS name, data->>'age' AS age
FROM json_table;
실무 시나리오
반구조적 데이터 파싱(PostgreSQL).
15. 동적 SQL(Dynamic SQL)
이론
프로그래밍적으로 쿼리를 생성.
예시 쿼리 (PostgreSQL PL/pgSQL)
DO $
DECLARE
sql_query TEXT;
BEGIN
sql_query := 'SELECT * FROM ' || quote_ident('employees') || ' LIMIT 5';
EXECUTE sql_query;
END $;
실무 시나리오
저장 프로시저, 자동 보고서 생성에 활용.
