분석 쿼리 작성
지표를 나누고, 세분화하고, 집계합니다
분석 쿼리 작성은(는) CoddyKit의 무료 SQL Academy 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.
분석 쿼리란 무엇인가요?
분석 쿼리는 단순한 행 조회를 넘어섭니다. 고객 42가 어떤 주문을 했나요?라고 묻는 대신, 지역과 분기별 총수익은 얼마인가요? 또는 이번 달은 지난달과 어떻게 비교되나요?라고 묻습니다.
스타 스키마를 기반으로 구축한 데이터 웨어하우스에서 분석 쿼리는 팩트에서 비즈니스 통찰을 찾기 위해 슬라이스(하나의 차원 필터링), 다이스(여러 차원 필터링), 롤업(더 거친 세부 수준으로 집계)을 수행합니다.
스타 스키마 복습
스타 스키마에는 하나의 중앙 팩트 테이블(예: fact_sales)과 이를 둘러싼 차원 테이블(예: dim_date, dim_product, dim_store)이 있습니다. 분석 쿼리는 현재 분석에 필요한 차원만 팩트 테이블과 조인합니다.
SELECT
s.store_name,
d.year,
d.quarter,
SUM(f.revenue) AS total_revenue,
SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY
s.store_name,
d.year,
d.quarter
ORDER BY
d.year,
d.quarter,
s.store_name;슬라이싱: 하나의 차원 필터링
슬라이싱은 한 차원의 단일 값으로 결과 집합을 제한하는 것을 의미합니다. 예를 들어 2024년 데이터만 조회하는 경우입니다. WHERE 절이 슬라이싱 도구입니다.
슬라이싱을 일찍 적용하면 데이터베이스가 집계해야 할 행 수가 줄어들어 대규모 팩트 테이블에서도 쿼리가 빠르게 실행됩니다.
-- Slice: only year 2024
SELECT
p.category,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;다이싱: 여러 차원 필터링
다이싱은 두 개 이상의 차원에 필터를 동시에 적용하는 것을 의미합니다. 예를 들어 1분기 북부 지역의 전자 제품 매출을 조회하는 경우입니다. 각 추가 WHERE 조건은 데이터 큐브에서 더 작은 영역을 잘라 냅니다.
-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
d.month,
SUM(f.revenue) AS revenue,
SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE
p.category = 'Electronics'
AND s.region = 'North'
AND d.year = 2024
AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;롤업: 더 높은 세부 수준으로 집계
롤업은 세부 수준(매장별 일일 매출)에서 더 거친 수준(지역별 월간 매출)으로 이동하는 것을 의미합니다. 하위 수준의 GROUP BY 열을 제거하고 다시 집계하면 됩니다.
ROLLUP 수정자를 사용하면 여러 UNION ALL 블록을 작성하지 않고도 하나의 쿼리에서 소계와 총계를 함께 만들 수 있습니다.
-- Roll up from store/month to region/quarter with subtotals
SELECT
s.region,
d.quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;LAG를 사용한 기간별 비교
가장 흔한 분석 패턴 중 하나는 지표를 이전 기간의 동일한 지표와 비교하는 것입니다. 윈도 함수 LAG()를 사용하면 자체 조인 없이 이전 행의 값을 현재 행으로 직접 가져올 수 있습니다.
여기서는 전월 대비 수익 성장률을 백분율로 계산합니다.
WITH monthly AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
/ NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;SUM OVER를 사용한 누적 합계
누적 합계(누적 합)는 정의된 순서에 따라 각 행의 값을 앞선 모든 행의 합계에 더한 값입니다. 연간 누적 수익을 추적하거나 예산 소진 현황을 모니터링할 때 유용합니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 프레임 절을 사용하면 윈도를 명시적이고 모호하지 않게 정의할 수 있습니다.
SELECT
d.year,
d.month,
SUM(f.revenue) AS monthly_revenue,
SUM(SUM(f.revenue)) OVER (
PARTITION BY d.year
ORDER BY d.month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;DENSE_RANK로 차원 순위 매기기
순위를 매기면 그룹 안에서 가장 성과가 좋거나 나쁜 항목을 찾을 수 있습니다. DENSE_RANK()는 동률일 때 순위가 건너뛰지 않도록 연속 순위를 지정하므로 BI 보고서의 순위표에 적합합니다.
순위가 매겨진 결과를 CTE로 감싸고 순위로 필터링하면 상위 N개 패턴을 깔끔하고 읽기 쉽게 작성할 수 있습니다.
WITH ranked_products AS (
SELECT
p.product_name,
p.category,
SUM(f.revenue) AS revenue,
DENSE_RANK() OVER (
PARTITION BY p.category
ORDER BY SUM(f.revenue) DESC
) AS rnk
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;윈도 함수 SUM을 사용한 기여율
상품의 절대적인 수익도 유용하지만, 해당 상품이 카테고리 수익의 38%를 차지한다는 사실이 더 실질적인 도움이 될 수 있습니다. 전체 파티션에 대한 윈도 함수 SUM()을 사용하면 하위 쿼리 조인 없이 분모를 구할 수 있습니다.
SELECT
p.category,
p.product_name,
SUM(f.revenue) AS product_revenue,
SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
ROUND(
100.0 * SUM(f.revenue)
/ SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
1) AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;추세 평활화를 위한 이동 평균
일별 또는 주별 매출 수치는 잡음이 많습니다. 이동 평균은 단기 변동을 평활화하여 기저 추세를 볼 수 있게 합니다. 여기서는 이동 윈도 프레임을 사용하여 3개월 이동 평균을 계산합니다.
WITH monthly_rev AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
ROUND(
AVG(revenue) OVER (
ORDER BY year, month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
),
2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;모든 차원 조합을 위한 CUBE
CUBE는 계층적 롤업 경로뿐 아니라 나열된 차원의 모든 가능한 조합에 대한 소계도 계산하여 ROLLUP의 기능을 확장합니다. 이렇게 하면 한 번에 전체 다차원 요약을 만들 수 있으므로 사용자가 자유롭게 축을 바꿀 수 있는 다차원 대시보드에 유용합니다.
그룹화 열의 NULL은 해당 차원의 모든 값을 의미합니다. 데이터에 있는 의도적인 NULL과 롤업으로 생성된 NULL을 구분하려면 GROUPING()을 사용하세요.
SELECT
CASE WHEN GROUPING(s.region) = 1 THEN 'ALL REGIONS' ELSE s.region END AS region,
CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category END AS category,
CASE WHEN GROUPING(d.quarter) = 1 THEN 'ALL QUARTERS' ELSE d.quarter::TEXT END AS quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;어떤 연산이 결과를 하나의 차원 값으로 제한하나요?
데이터 웨어하우징에서 사용하는 분석 쿼리 용어에 대한 이해도를 확인해 보세요.
요약: 분석 쿼리 작성
이 레슨에서는 스타 스키마를 대상으로 분석 쿼리를 작성하는 핵심 패턴을 살펴보았습니다:
- 슬라이스 — WHERE를 사용하여 하나의 차원을 필터링하고 특정 세그먼트에 집중합니다.
- 다이스 — 여러 차원을 동시에 필터링하여 정밀한 데이터 큐브를 구성합니다.
- 롤업 — 더 거친 세부 수준으로 집계합니다. 여러 수준의 소계를 만들 때는
ROLLUP또는CUBE를 사용합니다. - LAG / LEAD — 자체 조인 없이 기간별 비교를 수행합니다.
- 누적 합계 & 이동 평균 — 윈도 프레임을 사용하여 누적 지표와 평활화된 지표를 계산합니다.
- DENSE_RANK — 파티션 내에서 깔끔하게 상위 N개 순위를 매깁니다.
- 기여율 — 비중 계산의 분모로 윈도 함수 SUM을 사용합니다.
이러한 패턴을 조합하면 운영 데이터 웨어하우스에서 접하게 될 BI 및 보고 요구 사항의 대부분을 처리할 수 있습니다.
AI 튜터와 함께 SQL을(를) 배우세요 — 무료
브라우저에서 실제 코드를 작성하고 실행하며, 24/7 AI 튜터로부터 즉각적인 도움을 받고, 웹이나 앱에서 중단한 부분부터 계속 학습하세요.
- 코스
- 46
- 레슨
- 183
자주 묻는 질문
“분석 쿼리 작성” 강의는 무료인가요?
네 — “분석 쿼리 작성” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Academy 강의 전체를 잠금 해제할 수 있습니다. SQL Academy 강의에는 총 4개의 강의가 포함되어 있습니다.
“분석 쿼리 작성”에서 뭘 배우나요?
지표를 나누고, 세분화하고, 집계합니다 브라우저에서 직접 실행하는 실습 코드로 SQL Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.
SQL Academy을(를) 시작하는 데 경험이 필요한가요?
사전 경험은 필요하지 않습니다. CoddyKit의 SQL Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 4번째 강의입니다.
“분석 쿼리 작성” 강의는 얼마나 걸리나요?
대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.
이 SQL Academy 강의에서 코드를 작성하고 실행할 수 있나요?
네. 모든 SQL Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.