SQL 분석 함수란 무엇인가?
SQL 분석 함수는 데이터베이스에서 고급 분석 작업을 수행하는 데 필수적인 도구입니다. 일반적인 집계 함수와 달리, 분석 함수는 결과 집합의 각 행에 대한 계산을 수행하면서도 행을 그룹화하지 않습니다. 이는 각 행이 전체 데이터 집합 내에서 어떤 위치에 있는지, 어떤 트렌드를 보이는지 등을 파악하는 데 매우 유용합니다.
SQL 분석 함수의 중요성
- 성능 향상: 복잡한 분석 쿼리를 보다 효율적으로 처리하여 데이터베이스 성능을 향상시킵니다.
- 유연성: 다양한 분석 요구 사항을 충족할 수 있는 다양한 함수를 제공합니다.
- 실무 적용성: 실제 비즈니스 문제 해결에 바로 적용 가능한 분석 기능을 제공합니다.
기본적인 SQL 분석 함수
다음은 오라클 데이터베이스에서 자주 사용되는 기본적인 SQL 분석 함수입니다.
1. ROW_NUMBER()
결과 집합의 각 행에 고유한 순번을 할당합니다.
SELECT employee_id, last_name,
ROW_NUMBER() OVER (ORDER BY last_name) AS row_num
FROM employees;
실행 결과 예시:
EMPLOYEE_ID LAST_NAME ROW_NUM
---------- ---------- ----------
178 Abel 1
116 Ande 2
103 Ernst 3
...2. RANK(), DENSE_RANK()
결과 집합 내에서 특정 열의 값에 대한 순위를 계산합니다. RANK()는 동일한 값에 대해 동일한 순위를 부여하고, 다음 순위는 건너뜁니다. DENSE_RANK()는 순위를 건너뛰지 않습니다.
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;
실행 결과 예시:
EMPLOYEE_ID SALARY RANK_NUM DENSE_RANK_NUM
----------- -------- ---------- --------------
100 24000 1 1
101 17000 2 2
102 17000 2 2
...3. LAG(), LEAD()
현재 행을 기준으로 이전 행(LAG()) 또는 다음 행(LEAD())의 값을 가져옵니다. 시간 흐름에 따른 데이터 분석에 유용합니다.
SELECT employee_id, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary,
LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary
FROM employees;
실행 결과 예시:
EMPLOYEE_ID HIRE_DATE SALARY PREV_SALARY NEXT_SALARY
----------- ---------- ------- ----------- ----------
101 21/09/01 17000 0 24000
100 13/01/03 24000 17000 17000
102 03/01/06 17000 24000 9000
...4. FIRST_VALUE(), LAST_VALUE()
파티션의 첫 번째 행(FIRST_VALUE()) 또는 마지막 행(LAST_VALUE())의 값을 반환합니다.
SELECT department_name, employee_id, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS highest_salary,
LAST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS lowest_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
실행 결과 예시:
DEPARTMENT_NAME EMPLOYEE_ID SALARY HIGHEST_SALARY LOWEST_SALARY
---------------- --------------- -------- --------------- -------------
Accounting 205 12000 12000 12000
Administration 200 9000 9000 9000
...5. RATIO_TO_REPORT()
전체 합계에 대한 각 행의 비율을 계산합니다.
SELECT department_name, SUM(salary) AS total_salary,
RATIO_TO_REPORT(SUM(salary)) OVER () AS salary_ratio
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY department_name;
실행 결과 예시:
DEPARTMENT_NAME TOTAL_SALARY SALARY_RATIO
---------------- ------------ ------------
Accounting 130000 .1000
Administration 60000 .0500
...실무에 바로 적용 가능한 예시
다음은 SQL 분석 함수를 활용하여 실제 비즈니스 시나리오를 해결하는 예시입니다.
예시 1: 월별 매출 성장률 분석
각 월별 매출액과 이전 달 대비 성장률을 계산합니다.
SELECT month, revenue,
(revenue - LAG(revenue, 1, 0) OVER (ORDER BY month)) / LAG(revenue, 1, 1) OVER (ORDER BY month) AS growth_rate
FROM monthly_sales;
예시 2: 상위 N개 제품 판매 분석
각 카테고리별 상위 3개 제품의 판매량을 분석합니다.
SELECT category, product_name, sales,
RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS product_rank
FROM product_sales
WHERE product_rank <= 3;
예시 3: 고객 유지율 분석
각 월별 고객 수를 기준으로 고객 유지율을 계산합니다.
SELECT month, customer_count,
(customer_count / LAG(customer_count, 1, 1) OVER (ORDER BY month)) AS retention_rate
FROM customer_summary;
주의사항 및 성능 고려 사항
- 분석 함수는 쿼리 성능에 영향을 미칠 수 있으므로, 적절한 인덱스를 활용하고 파티션 설계를 고려해야 합니다.
- OVER() 절 내의 ORDER BY 절은 성능에 큰 영향을 줄 수 있으므로, 신중하게 선택해야 합니다.
- 특정 분석 함수는 특정 상황에서만 유용하므로, 분석 요구 사항에 맞는 함수를 선택해야 합니다.
결론
SQL 분석 함수는 데이터 분석 능력을 향상시키고, 복잡한 비즈니스 문제를 해결하는 데 매우 효과적인 도구입니다. 본 가이드에서 제시된 기본적인 함수와 실무 예시를 통해 SQL 분석 함수를 완벽하게 이해하고 활용하여 데이터 분석 전문가로 거듭나시길 바랍니다.