고급 SQL 분석(Analytics)을 마스터하는 9가지 방법

https://2stndard.tistory.com/notice/203

[발간예정][EPL과 유튜브로 배우는 DuckDB] 실습 코드와 데이터

 

데이터 분석가로서 여러분은 기하급수적으로 증가하는 데이터를 다루고 있으며, 분석 요구사항은 이전보다 훨씬 더 커졌습니다.

현대적인 SQL 엔진은 이제 데이터를 외부 시스템으로 옮기지 않고도 고급 분석을 실행할 수 있는 기능을 지원합니다. SQL 쿼리만으로 행의 순위를 매기고, 누적 합계를 계산하고, 중첩 JSON을 파싱하고, 계층 구조를 분석하며, 통계 분석까지 수행할 수 있습니다.

그 방법은 다음과 같습니다.

윈도우 함수(Window Functions)

순위 함수(Ranking Functions)

RANK(), DENSE_RANK(), ROW_NUMBER()는 파티션(Partition) 내의 각 행에 순차적인 번호를 부여합니다.

• ROW_NUMBER()는 모든 행에 고유한 정수를 부여합니다.
• RANK()는 동일한 값을 가진 행에 같은 순위를 부여하며, 이후 순위 번호를 건너뜁니다. 예를 들어 두 행이 공동 1등이면 다음 행은 3등이 됩니다.
• DENSE_RANK()는 동일한 값을 가진 행에 같은 순위를 부여하지만 순위 번호를 건너뛰지 않습니다. 예를 들어 두 행이 공동 1등이면 다음 행은 2등이 됩니다.
• NTILE()은 행들을 지정한 개수의 그룹으로 가능한 한 균등하게 나누고, 각 행에 그룹 번호를 부여합니다.

-- Example:  
SELECT  
region,  
salesperson,  
sales,  
RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rnk,  
FROM  
quarterly_sales;

오프셋 함수(Offset Functions): LAG와 LEAD

LAG()와 LEAD()는 동일한 파티션 내에서 이전 행 또는 다음 행의 값을 참조할 수 있게 해줍니다.

• LAG()는 이전 행의 값을 가져옵니다.
• LEAD()는 다음 행의 값을 가져옵니다.
• 두 함수 모두 선택적으로 오프셋(offset) 인수를 받을 수 있으며, 몇 개의 행을 건너뛸지 지정할 수 있습니다.

FIRST_VALUE()와 LAST_VALUE()는 현재 윈도우 프레임(Window Frame)에서 첫 번째 값 또는 마지막 값을 반환합니다.

FIRST_VALUE()를 ORDER BY와 함께 사용하면 사용자 세션에서 가장 처음 발생한 이벤트를 찾을 수 있고, LAST_VALUE()를 사용하면 워크플로우에서 가장 최근 상태 변경을 찾을 수 있습니다.

-- Example:  
SELECT  
date,  
close_price,  
LAG(close_price) OVER (ORDER BY date) AS prev_close,  
close_price - LAG(close_price) OVER (ORDER BY date) AS change  
FROM  
stock_prices;

LAG()와 LEAD()는 코호트 유지율(Cohort Retention) 분석, 퍼널(Funnel) 단계별 이탈 분석, 그리고 순차 데이터(Sequential Data)의 이상 탐지(Anomaly Detection)에 유용하게 활용할 수 있습니다.

집계 윈도우 함수(Aggregate Window Functions):

SUM(), AVG(), COUNT(), MIN(), MAX()와 같은 표준 집계 함수는 OVER() 절과 함께 사용하면 윈도우 함수(Window Function) 로 동작합니다.

이 방식은 GROUP BY 집계와 다르게 결과에서 모든 행을 그대로 유지합니다. 즉, 행을 하나로 합치지 않고 각 행마다 집계 결과를 함께 계산하여 반환합니다.

SELECT  
department,  
employee,  
salary,  
AVG(salary) OVER (PARTITION BY department) AS dept_avg,  
FROM  
employees;

공통 테이블 표현식(Common Table Expressions, CTE)

공통 테이블 표현식(CTE)은 하나의 SQL 문 안에서만 존재하는 임시 결과 집합입니다.

WITH 키워드를 사용해 정의하며, 이후 이어지는 메인 쿼리에서 해당 결과를 참조할 수 있습니다. CTE는 SQL 문 실행이 끝나면 자동으로 사라지며, 데이터베이스에 영구 객체를 생성하지 않습니다.

메인 쿼리는 서브쿼리와 달리 동일한 CTE를 여러 번 참조할 수 있습니다. 따라서 복잡하게 중첩된 서브쿼리 대신 CTE를 사용하면 쿼리의 가독성과 유지보수성이 좋아집니다.

CTE는 크게 두 가지 형태가 있습니다.

• 재귀 CTE(Recursive CTE)
• 비재귀 CTE(Non-Recursive CTE)

비재귀 CTE(Non-recursive CTE)

비재귀 CTE는 서브쿼리(Subquery)나 파생 테이블(Derived Table)를 대체하는 용도로 사용하는 것이 좋습니다.

CTE를 사용하면 여러 단계의 로직을 이름 있는 블록으로 분리할 수 있어, 복잡하게 중첩된 서브쿼리보다 읽기 쉽고 이해하기 쉬운 SQL을 작성할 수 있습니다.

--example  
WITH regional_sales AS (  
SELECT region, SUM(amount) AS total_sales  
FROM orders  
GROUP BY region  
),  
top_regions AS (  
SELECT region  
FROM regional_sales  
WHERE total_sales > 1000000  
)  
SELECT  
o.*  
FROM  
orders o  
JOIN  
top_regions t  
ON o.region = t.region;

재귀 CTE(Recursive CTE):

재귀 CTE는 계층 구조(Hierarchical Structure)나 그래프 구조(Graph-Structured Data) 데이터를 다룰 때 자기 자신을 참조하는 방식으로 사용됩니다.

재귀 CTE는 두 부분으로 구성됩니다.

• 기본 쿼리(Base Query)
• CTE 내부에서 자기 자신을 참조하는 재귀 부분(Recursive Part)

재귀 CTE를 활용하면 조직도(Organizational Chart), 트리(Tree), 파일 시스템(File System), 경로(Path) 탐색, 네트워크 그래프(Network Graph)를 탐색할 수 있습니다.

-- Example:  
WITH RECURSIVE org_chart AS (  
   SELECT id, name, manager_id, 1 AS level  
   FROM employees  
   WHERE manager_id IS NULL  
   UNION ALL  
   SELECT e.id, e.name, e.manager_id, o.level + 1  
   FROM employees e  
   JOIN org_chart o  
   ON e.manager_id = o.id  
)  
SELECT  
*  
FROM  
org_chart;

재귀 CTE(Recursive CTE)는 네트워크에서 특정 시작 지점으로부터 연결된 모든 노드(Node)를 탐색하는 데 활용할 수 있습니다.

이 패턴은 공급망(Supply Chain)의 의존성(Dependency) 추적 및 해결, 소셜 네트워크에서 사용자 간 거리(Distance) 조회, 경로(Route) 탐색 및 연결 관계 분석 문제에 동일하게 적용됩니다.

패턴 매칭(Pattern Matching):

패턴 매칭은 정규표현식(Regular Expressions)과 유사한 개념이지만, 문자열의 문자 대신 테이블의 행(Row)을 대상으로 동작합니다.

사용자가 정의한 패턴과 일치하는 행의 연속된 시퀀스(sequence) 를 찾아낼 수 있습니다. 이를 활용하면 다음과 같은 분석이 가능합니다.

• 시계열(Time-Series) 이상 탐지(Anomaly Detection)
• 사용자 행동(User Behavior) 분석
• 이벤트 간 상관관계(Event Correlation) 분석
• 퍼널(Funnel) 분석

예를 들어 다음과 같은 사용자 행동 흐름을 추적할 수 있습니다.

LOGIN → SEARCH → ADD_TO_CART → PURCHASE

SELECT *  
FROM sales  
MATCH_RECOGNIZE (  
   PARTITION BY user_id  
   ORDER BY event_time  
   MEASURES A.event_time AS start_time,  
      LAST(B.event_time) AS end_time,  
      COUNT(B.*) AS steps  
   ONE ROW PER MATCH  
   AFTER MATCH SKIP PAST LAST ROW  
   PATTERN (A B+ C)  
   DEFINE  
      A AS event_type = 'login',  
     B AS event_type = 'page_view',  
     C AS event_type = 'purchase'  
)

이 기능은 현재 Oracle과 Snowflake에서만 지원됩니다.

고급 집계(Advanced Aggregations):

조건부 집계(Conditional Aggregation):

집계 함수 내부에서 CASE 문을 사용하면 하나의 데이터 스캔만으로 여러 지표(Metrics)를 동시에 계산할 수 있습니다.

예를 들어 조건에 따라 특정 행만 합계·개수·평균 계산에 포함할 수 있어, 여러 번 쿼리를 실행하지 않고도 다양한 집계 결과를 한 번에 얻을 수 있습니다.

SELECT region, COUNT(*) AS total_orders,  
   COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returns,  
   SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END)  
   AS completed_revenue  
FROM orders  
GROUP BY region;

다차원 그룹화(Multidimensional Grouping):

GROUPING SETS, ROLLUP, CUBE를 사용하면 하나의 데이터 스캔으로 여러 개의 GROUP BY 집계를 동시에 수행할 수 있습니다.

이 방식은 여러 개의 독립적인 집계 쿼리를 작성한 뒤 UNION ALL로 결합하는 것보다 일반적으로 더 효율적입니다.

1. ROLLUP

ROLLUP은 점점 더 높은 수준의 집계 단계로 부분 합계(Subtotal)를 계산하는 기능입니다.

예를 들어 지역 → 도시 → 매장 순으로 그룹화했다면, ROLLUP은 매장별 합계뿐 아니라 도시별 합계, 지역별 합계, 전체 합계까지 함께 계산해 반환합니다.

-- Example  
SELECT year, month, SUM(revenue) AS total_revenue,  
   GROUPING(year) AS year_is_total,  
   GROUPING(month) AS month_is_total  
FROM sales  
GROUP BY ROLLUP(year, month)  
ORDER BY year, month;

계층 구조(Hierarchy)를 따라 상위 수준으로 집계를 탐색하고 싶을 때는 ROLLUP을 사용합니다.

2. CUBE

CUBE는 지정한 컬럼들의 가능한 모든 조합에 대해 집계를 계산하는 기능입니다.

예를 들어 컬럼이 3개라면, CUBE는 각 컬럼 조합에 대한 집계를 포함하여 하나의 쿼리에서 총 8개의 결과 집합(2³) 을 생성합니다.

즉, ROLLUP이 계층 구조를 따라 단계적으로 집계한다면, CUBE는 계층 여부와 관계없이 모든 차원의 조합을 분석할 때 사용합니다.

SELECT region, year, SUM(revenue) AS total_revenue,  
   GROUPING(region) AS region_total,  
   GROUPING(year) AS year_total  
FROM sales  
GROUP BY CUBE(region, year)  
ORDER BY region, year;

3. GROUPING SETS

GROUPING SETS는 CUBE와 ROLLUP의 개념을 확장하여, 원하는 그룹화 조합만 직접 지정할 수 있는 기능입니다.

SELECT region, year, SUM(revenue) AS total_revenue  
FROM sales  
GROUP BY GROUPING SETS (  
   (region), -- subtotal per region  
   (year), -- subtotal per year  
   () -- grand total  
)  
ORDER BY region, year;

거의 모든 SQL 방언(SQL Dialect)은 이 세 가지 기능(GROUPING SETS, ROLLUP, CUBE)을 지원합니다. 다만 SQLite와 MySQL은 일부 제한이 있거나 완전하게 지원하지 않습니다.

피벗(Pivoting)과 언피벗(Unpivoting)

엑셀(Excel)에서처럼 SQL에서도 테이블의 행과 열 구조를 변환(Pivot) 할 수 있습니다.

SELECT product_id, q1_sales, q2_sales, q3_sales, q4_sales  
FROM quarterly_revenue  
PIVOT (  
   SUM(revenue)  
   FOR quarter_name  
   IN ('Q1' AS q1_sales, 'Q2' AS q2_sales, 'Q3' AS q3_sales, 'Q4' AS q4_sales));

SQL은 반대로 언피벗(Unpivot) 기능도 제공합니다.

SELECT product_id, quarter_name, revenue  
FROM wide_sales_table  
UNPIVOT (  
   revenue  
   FOR quarter_name IN (q1_sales, q2_sales, q3_sales, q4_sales)  
);

대부분의 SQL 방언(SQL Dialect)은 이러한 기능을 지원하지만, PostgreSQL과 MySQL은 기본 PIVOT / UNPIVOT 구문을 제공하지 않습니다.

또한 이러한 기능은 계산 비용(Computational Cost)이 높을 수 있으므로 신중하게 사용하는 것이 좋습니다.

통계 및 분포 함수(Statistical and Distribution Functions):

연속 백분위수(Continuous Percentiles)와 이산 백분위수(Discrete Percentiles):

SQL에서 백분위수(Percentile)를 계산하는 방법은 두 가지가 있습니다.

• PERCENTILE_CONT()는 데이터셋에 해당 값이 실제 존재하지 않더라도 백분위수를 계산하여 반환합니다. 결과가 두 값 사이에 위치하면 중간 값을 보간(interpolation)하여 생성합니다.
• PERCENTILE_DISC()는 데이터베이스에 실제 존재하는 값 중에서 해당 백분위수에 해당하는 레코드 값을 반환합니다.

이 함수들은 윈도우 파티션(Window Partition) 과 함께 사용할 수 있으며, 이를 통해 기준 프로파일(Baseline Profile) 을 구축할 수 있습니다.

SELECT department_id, employee_id, salary,  
   PERCENTILE_CONT(0.5) WITHIN GROUP  
      (ORDER BY salary) OVER (PARTITION BY department_id)  
      AS median_salary_cont,  
   PERCENTILE_DISC(0.75) WITHIN GROUP  
      (ORDER BY salary) OVER (PARTITION BY department_id)  
      AS p75_salary_disc  
FROM employee_payroll;

상관관계(Correlation):

CORR() 함수는 하나의 그룹 내에서 두 개의 수치형 지표(Numeric Indicators) 사이의 피어슨 상관계수(Pearson Correlation Coefficient)를 계산합니다.

SELECT region, campaign_month, 
   CORR(marketing_spend, revenue) OVER (PARTITION BY region)  
      AS spend_to_revenue_correlation  
FROM regional_marketing_performance;

SQL 서버는 이 함수를 지원하지 않습니다.

가설 분석(Hypothetical Analysis):

Oracle과 PostgreSQL은 가설 집합 계산(Hypothetical Set Calculations) 을 통해 운영 변화 시나리오를 시뮬레이션할 수 있습니다.

사용자가 가상의 새로운 값(Hypothetical Value) 을 입력하면, SQL 엔진은 그 값을 기존 데이터셋에 추가했다고 가정했을 때 해당 값이 어디에 위치하는지 계산합니다.

SELECT  
   RANK(100000) WITHIN GROUP (ORDER BY salary DESC)  
      AS hypothetical_rank_at_100k,  
   PERCENT_RANK(50000) WITHIN GROUP (ORDER BY salary DESC)  
      AS hypothetical_percent_rank_at_50k  
FROM employee_payroll

코호트 분석(Cohort Analysis)과 유지율 모델링(Retention Modeling):

코호트 분석을 사용하면 사용자를 시작 시점(예: 최초 가입일 또는 최초 활동일)을 기준으로 그룹화하고, 이후 기간 동안의 행동 변화를 추적할 수 있습니다.

다음은 3단계 코호트 분석 수행 방법입니다.

1. 코호트 기준점(Cohort Anchor) 정의
   각 사용자의 최초 활동 날짜(First Activity Date) 를 찾습니다.
   이를 위해 MIN() 함수를 사용하고 사용자 ID 기준으로 그룹화합니다.
2. 활동 데이터를 코호트와 연결(Link Activity to Cohorts)
   생성한 코호트 기준 테이블을 원본 활동 테이블과 다시 조인합니다.
   이를 통해 각 사용자의 이후 행동 기록을 모두 추적할 수 있습니다.
3. 기간 오프셋(Period Offset) 계산
   절대적인 달력 날짜를 상대적인 시간 단위로 변환합니다.
   예를 들어 최초 활동 시점을 Month 0, 다음 달을 Month 1, 그다음을 Month 2처럼 표현합니다.

이 과정을 통해 사용자 유지율(Retention), 재방문 패턴, 이탈 시점 등을 시간 흐름에 따라 분석할 수 있습니다.

WITH cohort_anchor AS (  
   SELECT user_id,  
      DATEADD(month, DATEDIFF(month, 0, MIN(activity_date)), 0)  
         AS cohort_month  
FROM user_activity  
GROUP BY user_id  
),  

linked_activity AS (  
SELECT a.user_id, c.cohort_month,  
   DATEADD(month, DATEDIFF(month, 0, a.activity_date), 0)  
      AS activity_month  
FROM user_activity a  
JOIN cohort_anchor c ON a.user_id = c.user_id  
),  

period_offsets AS (  
SELECT cohort_month, activity_month,  
   DATEDIFF(month, cohort_month, activity_month) AS period_offset,  
   COUNT(DISTINCT user_id) AS active_users  
FROM linked_activity  
GROUP BY cohort_month, activity_month  
)  

SELECT cohort_month,  
   MAX(CASE WHEN period_offset = 0 THEN active_users END)  
      OVER(PARTITION BY cohort_month) AS cohort_size,  
   period_offset, active_users,  
   ROUND(100.0 * active_users /  
      MAX(CASE WHEN period_offset = 0 THEN active_users END)  
         OVER(PARTITION BY cohort_month), 2) AS retention_rate  
FROM period_offsets  
ORDER BY cohort_month, period_offset;

시계열 분석(Time-series Analysis):

SQL은 타임스탬프(Timestamp)를 처리하고 변형하기 위한 다양한 날짜 함수를 제공합니다.

• DATE_TRUNC는 타임스탬프를 월(Month), 분기(Quarter) 와 같은 일정한 기간 단위로 그룹화합니다.
• DATEDIFF와 DATEADD는 날짜 간의 간격을 계산하거나 날짜를 앞뒤로 이동시키는 데 사용됩니다.
• 윈도우 함수(Window Functions) 는 이동 평균, 누적 합계와 같은 롤링 통계(Rolling Statistics) 를 계산할 수 있습니다.

예를 들어 현재 기간의 지표와 이전 기간의 지표를 비교하여 증감 추이(Period-over-Period Analysis) 를 분석할 수 있습니다.

SELECT  
   CAST(current_day.activity_datetime AS DATE) AS activity_date,  
   COUNT(DISTINCT current_day.user_id) AS active_users_today,  
   COUNT(DISTINCT prior_day.user_id) AS active_users_7_days_ago,  
   (COUNT(DISTINCT current_day.user_id) - COUNT(DISTINCT prior_day.user_id))  
      AS net_change_vs_last_week  
FROM user_activity current_day  
LEFT JOIN user_activity prior_day  
   ON prior_day.user_id = current_day.user_id  
      AND prior_day.activity_datetime >=  
         DATEADD(day, -7, current_day.activity_datetime)  
      AND prior_day.activity_datetime <  
         DATEADD(day, -6, current_day.activity_datetime)  
   WHERE current_day.activity_datetime >= '2026-05-01'  
   GROUP BY CAST(current_day.activity_datetime AS DATE)  
   ORDER BY activity_date;

벡터 검색(Vector Search):

BigQuery와 SQL Server 2025는 고차원 벡터(High-Dimensional Vector)를 네이티브 데이터 타입으로 저장할 수 있습니다.

이제 더 이상 관계형 컬럼을 추출하고, 외부 Python 스크립트로 텍스트를 임베딩(Embedding)으로 변환한 뒤, 별도의 서드파티 벡터 데이터베이스에서 계산을 수행하는 복잡한 워크플로우가 필요하지 않습니다.

이제는 비정형 데이터(Unstructured Data) 와 관계형 지표(Relational Metrics) 를 동일한 데이터 엔진 안에서 함께 관리할 수 있습니다.

즉,

• 텍스트 → 벡터 변환 후 저장
• 유사도 검색(Vector Similarity Search)
• SQL 기반 분석 및 집계
• 기존 관계형 데이터와 결합 분석

을 하나의 플랫폼에서 수행할 수 있게 되면서, 분석 파이프라인이 훨씬 단순해졌습니다.

벡터 인덱스(Vector Indexes)와 유사도 검색(Similarity Searches):

다음 명령을 실행하여 벡터 인덱스를 만들고 의미 기반 검색(Semantic Search) 쿼리를 실행할 수 있습니다.

BigQuery:

1. 거리 인덱스(Distance Index)를 설정합니다.

CREATE VECTOR INDEX IF NOT EXISTS  
text_embedding_idx  
ON  
my_dataset.knowledge_base(embedding_col)  
OPTIONS  
(distance_type = 'COSINE', index_type = 'IVF');

2. 가장 유사한 문서 찾기:

SELECT document_id, text_content  
FROM  
   VECTOR_SEARCH( TABLE my_dataset.knowledge_base, 'embedding_col',  
   (SELECT embedding_col  
    FROM my_dataset.query_embeddings  
    WHERE query_id = 45), top_k => 5);

SQL Server 2025:

1) 최근접 이웃(Nearest-Neighbor) 검색 속도를 높이기 위해 벡터 인덱스를 생성합니다:

CREATE VECTOR INDEX  
   text_vector_idx  
ON  
   dbo.knowledge_base(embedding_col)  
WITH  
   (DISTANCE_METRIC = 'COSINE');

다음 구문을 실행하여 유사도 함수(Similarity Function) 로 일치하는 콘텐츠를 가져옵니다.

SELECT TOP (5)  
   document_id,  
   text_content  
FROM dbo.knowledge_base  
ORDER BY VECTOR_DISTANCE('COSINE', embedding_col, @query_vector);