실무 데이터 분석 SQL 완벽 가이드

실제 업무에서 자주 사용하는 SQL 데이터 분석 기법을 단계별로 학습합니다. 매출 집계부터 고객 세분화까지, 실전 대시보드 쿼리 작성 방법을 배워보세요.

1. 매출 데이터 일별/월별 집계

시작하며

여러분이 회사의 매출 데이터를 분석해야 하는데, 엑셀로 하나하나 날짜별로 합계를 내고 있다면 정말 비효율적이지 않나요? 매일 아침 어제 매출이 얼마인지, 이번 달 총 매출은 얼마인지 보고해야 하는데 손으로 계산하고 있다면 시간이 너무 오래 걸립니다.

이런 문제는 실제 개발 현장에서 매일 발생합니다. 데이터는 계속 쌓이는데 빠르게 집계하지 못하면 의사결정이 늦어지고, 비즈니스 기회를 놓칠 수 있습니다.

바로 이럴 때 필요한 것이 SQL 집계 함수입니다. GROUP BY와 날짜 함수를 활용하면 수백만 건의 매출 데이터도 몇 초 만에 일별, 월별로 정리할 수 있습니다.

개요

간단히 말해서, 이 개념은 흩어진 거래 데이터를 원하는 기간 단위로 묶어서 합계를 구하는 것입니다. 데이터베이스에는 주문이 발생할 때마다 기록이 쌓입니다.

하지만 우리가 보고 싶은 것은 "오늘 총 매출", "이번 달 총 매출" 같은 요약된 정보입니다. 예를 들어, 쇼핑몰에서 하루에 1000건의 주문이 발생했다면, 이걸 날짜별로 묶어서 하루 총 매출을 한눈에 볼 수 있습니다.

기존에는 데이터를 엑셀로 내려받아서 피벗 테이블을 만들었다면, 이제는 SQL 쿼리 한 번으로 실시간으로 집계할 수 있습니다. 핵심 특징은 첫째, GROUP BY로 날짜 단위를 지정하고, 둘째, SUM 같은 집계 함수로 금액을 합산하며, 셋째, DATE_TRUNC나 DATE_FORMAT으로 원하는 날짜 형식을 만듭니다.

이러한 특징들이 빠르고 정확한 매출 분석을 가능하게 합니다.

코드 예제

-- 일별 매출 집계
SELECT
    DATE(order_date) as 날짜,
    COUNT(*) as 주문건수,
    SUM(amount) as 총매출,
    AVG(amount) as 평균주문금액
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE(order_date)
ORDER BY 날짜 DESC;

-- 월별 매출 집계
SELECT
    DATE_FORMAT(order_date, '%Y-%m') as 년월,
    COUNT(DISTINCT customer_id) as 구매고객수,
    SUM(amount) as 월매출
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY 년월 DESC;

설명

이것이 하는 일: 위 쿼리는 주문 테이블에서 날짜별로 데이터를 묶어서 매출 통계를 만들어냅니다. 첫 번째로, DATE(order_date)는 주문 시간에서 날짜만 추출합니다.

예를 들어 '2024-01-15 14:30:00'이라는 주문 시간이 있다면 '2024-01-15'로 변환합니다. 이렇게 하는 이유는 같은 날짜의 모든 주문을 하나로 묶기 위해서입니다.

GROUP BY로 이 날짜를 기준으로 그룹을 만들면, 같은 날의 주문들이 한 그룹이 됩니다. 그 다음으로, SUM(amount)가 실행되면서 각 날짜 그룹의 금액을 모두 더합니다.

COUNT(*)는 그룹 안의 주문 건수를 세고, AVG(amount)는 평균 주문 금액을 계산합니다. 내부에서는 데이터베이스가 날짜별로 데이터를 분류하고, 각 그룹마다 집계 함수를 적용합니다.

월별 집계 쿼리에서는 DATE_FORMAT 함수로 '2024-01'처럼 년-월 형식을 만듭니다. DISTINCT customer_id를 사용하면 중복 제거된 고객 수를 셀 수 있어서, "이번 달에 실제로 구매한 고객이 몇 명인지" 알 수 있습니다.

여러분이 이 쿼리를 사용하면 매일 아침 5분이면 전날 매출 리포트를 만들 수 있고, 월말에도 자동으로 월간 리포트를 생성할 수 있습니다. 실무에서는 이 쿼리를 스케줄러에 등록해서 자동화하거나, 대시보드 툴과 연결해서 실시간 차트로 보여줄 수 있습니다.

실전 팁

💡 WHERE 절로 날짜 범위를 제한하면 쿼리 속도가 훨씬 빨라집니다. 특히 order_date에 인덱스가 있다면 수백만 건도 빠르게 조회됩니다.

💡 시간대(timezone) 이슈를 조심하세요. 서버 시간과 사용자 시간대가 다르면 집계 결과가 달라질 수 있습니다. UTC 기준으로 저장하고 표시할 때 변환하는 것이 안전합니다.

💡 HAVING 절을 사용하면 집계 후 필터링이 가능합니다. 예를 들어 'HAVING 총매출 >= 1000000'으로 매출 100만원 이상인 날만 볼 수 있습니다.

💡 NULL 값 처리를 잊지 마세요. COALESCE(SUM(amount), 0)으로 매출이 없는 날도 0으로 표시되게 하면 차트가 깔끔해집니다.

💡 성능이 중요하다면 집계 테이블을 따로 만드세요. 매일 밤 배치로 일별 집계를 미리 계산해두면 대시보드 조회가 훨씬 빠릅니다.

2. 성장률 계산 (전월 대비)

시작하며

여러분이 CEO에게 "이번 달 매출이 지난달보다 얼마나 성장했나요?"라는 질문을 받았을 때, 두 달의 매출을 따로 조회해서 계산기로 계산하고 있나요? 실시간으로 성장률을 보여줘야 하는데 수동으로 계산하면 너무 느립니다.

이런 문제는 비즈니스 분석에서 가장 자주 발생합니다. 단순히 이번 달 매출만 보면 좋은지 나쁜지 판단하기 어렵습니다.

전월 대비, 전년 대비 성장률을 함께 봐야 트렌드를 파악할 수 있습니다. 바로 이럴 때 필요한 것이 윈도우 함수(Window Function)입니다.

LAG 함수를 사용하면 이전 달 데이터를 현재 행에서 바로 참조해서 성장률을 계산할 수 있습니다.

개요

간단히 말해서, 이 개념은 현재 데이터와 이전 기간 데이터를 비교해서 증감률을 구하는 것입니다. 성장률은 비즈니스의 건강 상태를 보여주는 핵심 지표입니다.

매출이 100만원에서 120만원으로 늘었다면 20% 성장한 것입니다. 예를 들어, 월별 매출 추이를 볼 때 절대 금액보다 성장률이 더 중요한 경우가 많습니다.

투자자들은 "얼마나 빠르게 성장하고 있는가"를 가장 궁금해합니다. 기존에는 두 번의 쿼리로 이번 달과 지난달 매출을 각각 조회해서 애플리케이션 코드로 계산했다면, 이제는 SQL 윈도우 함수로 한 번의 쿼리로 모든 기간의 성장률을 계산할 수 있습니다.

핵심 특징은 첫째, LAG 함수로 이전 행의 값을 가져오고, 둘째, 수식으로 증감률을 계산하며, 셋째, PARTITION BY로 카테고리별 성장률도 구할 수 있다는 점입니다. 이러한 특징들이 복잡한 비교 분석을 간단하게 만들어줍니다.

코드 예제

-- 월별 매출 성장률 계산
SELECT
    년월,
    월매출,
    LAG(월매출) OVER (ORDER BY 년월) as 전월매출,
    ROUND(
        (월매출 - LAG(월매출) OVER (ORDER BY 년월))
        / LAG(월매출) OVER (ORDER BY 년월) * 100,
        2
    ) as 전월대비성장률
FROM (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') as 년월,
        SUM(amount) as 월매출
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) monthly_sales
ORDER BY 년월 DESC;

설명

이것이 하는 일: 위 쿼리는 각 월의 매출과 함께 전월 대비 성장률을 퍼센트로 보여줍니다. 첫 번째로, 서브쿼리에서 월별 매출을 먼저 집계합니다.

이것은 기본 데이터를 준비하는 단계입니다. DATE_FORMAT으로 년-월을 만들고 GROUP BY로 월별 합계를 구합니다.

그 다음으로, LAG(월매출) OVER (ORDER BY 년월) 부분이 핵심입니다. LAG는 "지연"이라는 뜻으로, 현재 행보다 한 행 이전의 값을 가져옵니다.

OVER (ORDER BY 년월)은 "년월 순서대로 정렬했을 때"라는 의미입니다. 예를 들어 2024-03월 행에서 LAG를 쓰면 2024-02월의 매출을 가져옵니다.

이렇게 하면 같은 행에 현재 월과 전월 데이터가 함께 있게 됩니다. 성장률 계산 공식은 (현재값 - 이전값) / 이전값 * 100 입니다.

월매출이 100만원에서 120만원이 됐다면 (120-100)/100*100 = 20% 성장입니다. ROUND 함수로 소수점 둘째 자리까지만 표시해서 읽기 쉽게 만듭니다.

여러분이 이 쿼리를 사용하면 매월 성장 추이를 한눈에 볼 수 있습니다. 성장률이 마이너스로 나오면 매출이 감소한 것이고, 플러스면 증가한 것입니다.

실무에서는 이 데이터를 차트로 만들어서 경영진에게 보고하거나, 알림 설정을 해서 성장률이 급격히 떨어지면 알람을 받을 수 있습니다.

실전 팁

💡 첫 달은 전월 데이터가 없어서 NULL이 나옵니다. COALESCE로 0이나 '-'로 표시하면 보고서가 깔끔해집니다.

💡 전년 동월 대비를 보려면 LAG(월매출, 12)처럼 12개월 전 데이터를 가져오세요. 계절성 비즈니스는 전년 동월 비교가 더 의미있습니다.

💡 PARTITION BY category를 추가하면 카테고리별 성장률을 따로 계산할 수 있습니다. 예를 들어 의류, 전자제품 등 각 카테고리의 성장률을 한번에 볼 수 있습니다.

💡 성장률이 비정상적으로 크게 나오면 전월 매출이 0에 가까운 경우입니다. CASE WHEN으로 전월 매출이 일정 금액 이하면 계산하지 않도록 예외 처리하세요.

💡 여러 지표를 함께 보세요. 매출은 늘었는데 주문 건수가 줄었다면 객단가가 오른 것입니다. 성장률만 보지 말고 절대값, 건수, 고객수를 함께 분석하세요.

3. 이동 평균 (Moving Average)

시작하며

여러분이 일별 매출 그래프를 보는데 너무 들쭉날쭉해서 트렌드를 파악하기 어려운 경험 있나요? 평일엔 매출이 높고 주말엔 낮아서 그래프가 지그재그로 움직이면, 전체적으로 증가 추세인지 감소 추세인지 한눈에 보이지 않습니다.

이런 문제는 일별 데이터를 다룰 때 항상 발생합니다. 단기 변동성 때문에 장기 트렌드가 가려지는 것입니다.

주식 차트를 보면 5일 이동평균선, 20일 이동평균선이 있는 이유가 바로 이것입니다. 바로 이럴 때 필요한 것이 이동 평균(Moving Average)입니다.

최근 N일의 평균을 계속 계산해서 선으로 그으면 노이즈가 제거되고 깔끔한 추세선이 나타납니다.

개요

간단히 말해서, 이 개념은 일정 기간의 평균을 계속 이동하면서 계산하여 변동성을 부드럽게 만드는 것입니다. 비즈니스 데이터는 요일, 날씨, 이벤트 등 여러 요인으로 날마다 변동합니다.

예를 들어, 음식 배달 서비스는 금요일 저녁에 주문이 폭발하고 월요일 오전엔 조용합니다. 하지만 우리가 알고 싶은 것은 "전체적으로 사업이 성장하고 있는가"입니다.

7일 이동평균을 쓰면 요일별 변동이 상쇄되어 진짜 트렌드가 보입니다. 기존에는 일별 데이터를 엑셀로 다운받아 이동평균 함수를 적용했다면, 이제는 SQL 윈도우 함수로 실시간으로 계산할 수 있습니다.

핵심 특징은 첫째, ROWS BETWEEN으로 범위를 지정하고, 둘째, AVG 같은 집계 함수와 윈도우 함수를 조합하며, 셋째, 기간을 조절해서 민감도를 조절할 수 있다는 점입니다. 이러한 특징들이 데이터의 본질적인 패턴을 드러나게 합니다.

코드 예제

-- 7일 이동 평균 매출 계산
SELECT
    날짜,
    일매출,
    -- 현재 행 포함 이전 6일, 총 7일 평균
    ROUND(AVG(일매출) OVER (
        ORDER BY 날짜
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) as 7일이동평균,
    -- 30일 이동 평균
    ROUND(AVG(일매출) OVER (
        ORDER BY 날짜
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 2) as 30일이동평균
FROM (
    SELECT
        DATE(order_date) as 날짜,
        SUM(amount) as 일매출
    FROM orders
    GROUP BY DATE(order_date)
) daily_sales
ORDER BY 날짜 DESC;

설명

이것이 하는 일: 위 쿼리는 각 날짜마다 최근 7일간의 평균 매출과 30일간의 평균 매출을 함께 보여줍니다. 첫 번째로, 서브쿼리에서 일별 매출을 집계합니다.

이것은 기본 데이터를 만드는 단계입니다. 날짜별로 그날의 총 매출을 구합니다.

그 다음으로, AVG(일매출) OVER (ORDER BY 날짜 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 부분이 핵심입니다. 이것을 쉽게 풀어서 설명하면 "날짜 순서대로 정렬했을 때, 현재 행부터 이전 6개 행까지, 즉 총 7개 행의 일매출 평균을 구하라"는 뜻입니다.

예를 들어 1월 7일 행에서 이 함수를 실행하면 1월 1일부터 1월 7일까지 7일간의 평균을 계산합니다. 1월 8일 행으로 가면 1월 2일부터 1월 8일까지로 범위가 이동합니다.

30일 이동평균은 범위를 29 PRECEDING으로 늘린 것입니다. 7일 이동평균은 단기 트렌드를, 30일 이동평균은 중장기 트렌드를 보여줍니다.

주식 차트처럼 두 선이 교차하는 지점에서 추세 전환을 감지할 수 있습니다. 여러분이 이 쿼리를 사용하면 일일 매출의 급등락에 속지 않고 진짜 성장세를 파악할 수 있습니다.

실무에서는 7일 선이 30일 선을 상향 돌파하면 상승 추세가 시작된 신호로 보고, 하향 돌파하면 주의 신호로 봅니다. 대시보드에 두 선을 겹쳐서 그리면 경영진이 직관적으로 이해할 수 있습니다.

실전 팁

💡 초반 6일은 7일치 데이터가 없어서 평균이 정확하지 않습니다. WHERE 절로 충분한 데이터가 쌓인 후부터만 표시하세요.

💡 계절성 비즈니스는 365일 이동평균을 쓰면 전년 동일 시즌과 비교할 수 있습니다. 크리스마스 시즌 같은 이벤트 효과를 제거할 수 있습니다.

💡 가중 이동평균도 유용합니다. 최근 데이터에 더 높은 가중치를 주려면 CASE WHEN으로 날짜별 가중치를 곱해서 계산하세요.

💡 성능 최적화: 이동평균을 매번 계산하면 느릴 수 있으니, 배치로 계산해서 별도 테이블에 저장하고 대시보드는 그 테이블을 조회하세요.

💡 이동평균이 실제 값보다 항상 늦게 반응합니다. 급격한 변화를 빨리 감지하려면 짧은 기간(3일, 5일)을, 안정적인 트렌드를 보려면 긴 기간(30일, 90일)을 쓰세요.

4. 사용자 코호트 분석

시작하며

여러분이 1월에 가입한 고객들이 2월, 3월에도 계속 구매하는지 추적하고 싶은데, 어떻게 분석해야 할지 막막한 경험 있나요? 단순히 월별 매출만 보면 신규 고객 때문에 늘어난 건지, 기존 고객이 재구매해서 늘어난 건지 구분이 안 됩니다.

이런 문제는 구독 서비스나 커머스 비즈니스에서 매우 중요합니다. 신규 고객을 열심히 유치했는데 다음 달에 다 이탈하면 밑 빠진 독에 물 붓기입니다.

고객 유지율(Retention)을 측정해야 사업의 지속 가능성을 판단할 수 있습니다. 바로 이럴 때 필요한 것이 코호트 분석(Cohort Analysis)입니다.

같은 시기에 가입한 사용자 그룹을 추적하면서 시간이 지나도 얼마나 많이 남아있는지 분석하는 기법입니다.

개요

간단히 말해서, 이 개념은 사용자를 가입 시기별로 그룹을 나누고, 각 그룹이 시간이 지남에 따라 어떻게 행동하는지 추적하는 것입니다. 코호트는 "동일한 특성을 가진 집단"을 의미합니다.

예를 들어, 2024년 1월에 가입한 사용자 1000명을 하나의 코호트로 봅니다. 이 1000명 중 2월에 몇 명이 구매했는지(2주차 리텐션), 3월에 몇 명이 구매했는지(3주차 리텐션)를 추적합니다.

Netflix 같은 구독 서비스는 코호트 분석으로 "1월 가입자의 3개월 후 구독 유지율"을 정확히 파악합니다. 기존에는 애플리케이션 코드로 복잡한 루프를 돌려서 계산했다면, 이제는 SQL 셀프 조인과 윈도우 함수로 직접 계산할 수 있습니다.

핵심 특징은 첫째, 가입 월(코호트)을 기준으로 그룹을 만들고, 둘째, 각 월의 활동을 추적하며, 셋째, 비율로 계산해서 유지율을 구한다는 점입니다. 이러한 특징들이 고객 생애 가치(LTV)를 예측하고 마케팅 효율을 측정하는 기반이 됩니다.

코드 예제

-- 월별 가입 코호트의 재구매율 분석
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_FORMAT(MIN(order_date), '%Y-%m') as 가입월
    FROM orders
    GROUP BY user_id
),
cohort_activity AS (
    SELECT
        uc.가입월,
        DATE_FORMAT(o.order_date, '%Y-%m') as 활동월,
        COUNT(DISTINCT o.user_id) as 활성사용자수
    FROM user_cohorts uc
    JOIN orders o ON uc.user_id = o.user_id
    GROUP BY uc.가입월, DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT
    가입월,
    활동월,
    활성사용자수,
    FIRST_VALUE(활성사용자수) OVER (
        PARTITION BY 가입월 ORDER BY 활동월
    ) as 코호트크기,
    ROUND(활성사용자수 * 100.0 / FIRST_VALUE(활성사용자수) OVER (
        PARTITION BY 가입월 ORDER BY 활동월
    ), 2) as 유지율
FROM cohort_activity
ORDER BY 가입월, 활동월;

설명

이것이 하는 일: 위 쿼리는 각 가입 월별 코호트가 이후 매월 얼마나 활성 상태를 유지하는지 퍼센트로 보여줍니다. 첫 번째로, user_cohorts CTE에서 각 사용자의 첫 구매 월을 찾습니다.

MIN(order_date)로 가장 빨리 주문한 날짜를 찾고, 이것을 가입월로 정의합니다. 예를 들어 어떤 사용자가 2024-01-15에 첫 구매를 했다면 이 사용자의 가입월은 '2024-01'입니다.

그 다음으로, cohort_activity CTE에서 각 코호트가 매월 얼마나 활동했는지 집계합니다. 가입월로 사용자를 그룹화하고, 그들이 실제로 주문한 월을 활동월로 기록합니다.

2024-01 코호트가 2024-02에 몇 명 주문했는지, 2024-03에 몇 명 주문했는지 세는 것입니다. 마지막 SELECT에서 FIRST_VALUE 윈도우 함수가 핵심입니다.

PARTITION BY 가입월로 각 코호트별로 구분하고, ORDER BY 활동월로 정렬했을 때 첫 번째 값(즉, 가입월의 사용자 수)을 가져옵니다. 이것이 코호트의 전체 크기입니다.

유지율은 (현재 활성 사용자 수 / 코호트 전체 크기) * 100으로 계산합니다. 여러분이 이 쿼리를 사용하면 "1월 가입자 1000명 중 3개월 후에도 300명이 활동 중(30% 유지율)"처럼 명확한 인사이트를 얻습니다.

실무에서는 이 데이터를 히트맵으로 시각화하면 어떤 코호트가 유지율이 높은지 한눈에 보입니다. 마케팅 캠페인 시기와 비교하면 어떤 채널로 온 고객이 충성도가 높은지 파악할 수 있습니다.

실전 팁

💡 주간 코호트나 일간 코호트도 만들 수 있습니다. 빠르게 성장하는 서비스는 월간보다 주간 코호트가 더 유용합니다.

💡 단순 활동 여부뿐만 아니라 매출 기준 유지율도 계산하세요. 사용자는 남아있는데 구매 금액이 줄어들 수도 있습니다.

💡 코호트 크기를 함께 표시하세요. 100명 코호트의 50% 유지율과 10,000명 코호트의 50% 유지율은 의미가 다릅니다.

💡 첫 달(Month 0)은 항상 100%입니다. 비교는 Month 1부터 하세요. Month 3 유지율이 30% 이상이면 좋은 편입니다.

💡 코호트 분석은 무거운 쿼리입니다. 매일 배치로 계산해서 집계 테이블에 저장하고, 대시보드는 그 테이블을 읽도록 최적화하세요.

5. RFM 분석으로 고객 세분화

시작하며

여러분이 100만 명의 고객 데이터베이스를 가지고 있는데, 프로모션 이메일을 누구에게 보내야 할지 고민한 적 있나요? 전체 고객에게 다 보내면 비용이 너무 많이 들고, 아무에게나 보내면 스팸으로 분류됩니다.

이런 문제는 마케팅 효율성과 직결됩니다. 모든 고객이 똑같지 않습니다.

최근에 비싼 물건을 자주 사는 VIP 고객과, 1년 전에 한 번 사고 안 온 고객을 같게 대우하면 안 됩니다. 고객을 세분화해서 맞춤형 전략을 써야 투자 대비 효과(ROI)가 높아집니다.

바로 이럴 때 필요한 것이 RFM 분석입니다. Recency(최근성), Frequency(빈도), Monetary(금액) 세 가지 지표로 고객을 점수화하고 등급을 나누는 기법입니다.

개요

간단히 말해서, 이 개념은 고객의 구매 패턴을 세 가지 측면에서 점수로 환산하여 가치 있는 고객을 찾아내는 것입니다. RFM은 마케팅의 고전적인 분석 기법입니다.

R(Recency)은 "마지막 구매가 얼마나 최근인가"로, 어제 산 고객이 1년 전에 산 고객보다 점수가 높습니다. F(Frequency)는 "얼마나 자주 구매하는가"로, 월 10회 구매 고객이 연 1회 고객보다 점수가 높습니다.

M(Monetary)은 "총 얼마나 썼는가"로, 100만원 쓴 고객이 1만원 쓴 고객보다 점수가 높습니다. 예를 들어, 스타벅스는 RFM으로 고객을 분류해서 VIP에게는 무료 음료 쿠폰을, 이탈 위험 고객에게는 할인 쿠폰을 보냅니다.

기존에는 데이터를 엑셀로 내려받아 수동으로 점수를 매겼다면, 이제는 SQL로 자동화할 수 있습니다. 핵심 특징은 첫째, NTILE 함수로 고객을 5등급이나 10등급으로 나누고, 둘째, 세 지표를 조합해서 종합 점수를 만들며, 셋째, 세그먼트별로 다른 마케팅 전략을 적용한다는 점입니다.

이러한 특징들이 데이터 기반 마케팅의 기초가 됩니다.

코드 예제

-- RFM 점수 계산 및 고객 세분화
WITH rfm_calc AS (
    SELECT
        customer_id,
        -- R: 마지막 구매 후 경과 일수 (작을수록 좋음)
        DATEDIFF(CURDATE(), MAX(order_date)) as recency,
        -- F: 총 구매 횟수
        COUNT(*) as frequency,
        -- M: 총 구매 금액
        SUM(amount) as monetary
    FROM orders
    GROUP BY customer_id
),
rfm_score AS (
    SELECT
        customer_id,
        recency,
        frequency,
        monetary,
        -- 5등급으로 나누기 (5가 가장 좋음)
        NTILE(5) OVER (ORDER BY recency DESC) as r_score,
        NTILE(5) OVER (ORDER BY frequency ASC) as f_score,
        NTILE(5) OVER (ORDER BY monetary ASC) as m_score
    FROM rfm_calc
)
SELECT
    customer_id,
    r_score,
    f_score,
    m_score,
    r_score + f_score + m_score as rfm_total,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'VIP고객'
        WHEN r_score >= 4 AND f_score <= 2 THEN '신규유망고객'
        WHEN r_score <= 2 AND f_score >= 4 THEN '이탈위험고객'
        ELSE '일반고객'
    END as 고객등급
FROM rfm_score
ORDER BY rfm_total DESC;

설명

이것이 하는 일: 위 쿼리는 모든 고객에게 RFM 점수를 매기고, 점수 조합으로 VIP, 신규유망, 이탈위험 등으로 분류합니다. 첫 번째로, rfm_calc CTE에서 각 고객의 원시 RFM 값을 계산합니다.

DATEDIFF(CURDATE(), MAX(order_date))는 "오늘 날짜와 마지막 구매일의 차이"로 며칠 전에 구매했는지 계산합니다. 2일 전이면 2, 365일 전이면 365입니다.

COUNT(*)는 총 주문 횟수, SUM(amount)는 총 구매 금액입니다. 그 다음으로, rfm_score CTE에서 NTILE 함수로 점수를 매깁니다.

NTILE(5)는 데이터를 5개 그룹으로 나눕니다. ORDER BY recency DESC로 정렬하면, recency가 작은(최근에 구매한) 고객이 5점, 큰(오래된) 고객이 1점을 받습니다.

frequency와 monetary는 ASC로 정렬해서 많을수록 높은 점수를 받습니다. 이렇게 각 지표마다 1~5점 사이의 점수가 부여됩니다.

마지막 SELECT에서 CASE 문으로 고객을 분류합니다. r_score, f_score, m_score가 모두 4 이상이면 모든 면에서 우수한 'VIP고객'입니다.

r_score는 높은데 f_score가 낮으면 최근에 가입했지만 아직 구매를 많이 안 한 '신규유망고객'입니다. r_score가 낮고 f_score가 높으면 과거엔 많이 샀는데 요즘 안 사는 '이탈위험고객'입니다.

여러분이 이 쿼리를 사용하면 10만 명 고객을 의미 있는 세그먼트로 나눌 수 있습니다. VIP고객에게는 신제품 우선 구매 기회를, 이탈위험고객에게는 "오랜만이에요, 20% 할인 쿠폰" 같은 리인게이지먼트 캠페인을, 신규유망고객에게는 온보딩 가이드를 보낼 수 있습니다.

실무에서는 이 세그먼트를 CRM 툴이나 이메일 마케팅 플랫폼과 연동해서 자동화합니다.

실전 팁

💡 NTILE 구간을 업종에 맞게 조정하세요. 명품 브랜드는 3등급, 대중 이커머스는 10등급처럼 고객 분포에 따라 달라집니다.

💡 가중치를 다르게 줄 수 있습니다. r_score * 3 + f_score * 2 + m_score처럼 최근성에 더 높은 가중치를 주면 이탈 방지에 집중하게 됩니다.

💡 카테고리별 RFM도 유용합니다. 전체적으로는 VIP가 아니어도 특정 카테고리에서는 VIP일 수 있습니다. PARTITION BY category 추가하세요.

💡 시간에 따른 등급 변화를 추적하세요. 이번 달 VIP였다가 다음 달 일반고객으로 내려가면 이탈 신호입니다. 월별 스냅샷을 저장하세요.

💡 RFM은 과거 데이터 기반입니다. 미래 구매 가능성을 예측하려면 머신러닝 모델과 결합하세요. RFM 점수를 피처로 사용하면 좋은 시작점입니다.

6. 실전 대시보드 쿼리 작성

시작하며

여러분이 CEO에게 "오늘 아침 회의에서 주요 지표 대시보드를 보여달라"는 요청을 받았는데, 여러 개의 쿼리를 돌려서 엑셀에 복붙하고 계신가요? 매출, 신규 고객, 유지율, 인기 상품을 각각 따로 조회하면 시간도 오래 걸리고 실수도 생깁니다.

이런 문제는 경영진에게 보고할 때 항상 발생합니다. 단편적인 숫자들을 모아놓으면 전체 그림을 파악하기 어렵습니다.

한 화면에 핵심 지표들이 일관된 기준으로 정리되어야 의사결정이 빨라집니다. 바로 이럴 때 필요한 것이 실전 대시보드 쿼리입니다.

하나의 쿼리로 여러 지표를 한번에 계산하거나, 효율적인 여러 쿼리를 조합해서 완성된 대시보드를 만드는 것입니다.

개요

간단히 말해서, 이 개념은 비즈니스에 필요한 모든 핵심 지표를 빠르고 정확하게 조회할 수 있도록 쿼리를 설계하는 것입니다. 대시보드는 회사의 건강 상태를 한눈에 보여주는 계기판입니다.

자동차 계기판에 속도, 연료, 엔진 상태가 함께 표시되듯이, 비즈니스 대시보드에는 매출, 고객 수, 전환율 등이 함께 표시되어야 합니다. 예를 들어, Tableau나 Metabase 같은 BI 툴도 결국 SQL 쿼리로 데이터를 가져옵니다.

쿼리가 느리면 대시보드가 느려지고, 쿼리가 부정확하면 잘못된 의사결정을 하게 됩니다. 기존에는 각 지표를 별도 쿼리로 조회하고 애플리케이션에서 조합했다면, 이제는 CTE와 윈도우 함수를 활용해 효율적으로 설계할 수 있습니다.

핵심 특징은 첫째, 여러 CTE를 조합해서 복잡한 로직을 단계별로 분리하고, 둘째, 인덱스를 고려해서 성능을 최적화하며, 셋째, 재사용 가능한 뷰나 함수로 만들어 유지보수를 쉽게 한다는 점입니다. 이러한 특징들이 프로덕션 환경에서 매일 사용되는 안정적인 대시보드를 만들어줍니다.

코드 예제

-- 일일 비즈니스 대시보드 종합 쿼리
WITH daily_metrics AS (
    -- 오늘 매출 및 주문
    SELECT
        DATE(order_date) as 날짜,
        COUNT(*) as 주문건수,
        COUNT(DISTINCT customer_id) as 구매고객수,
        SUM(amount) as 총매출,
        AVG(amount) as 평균주문금액
    FROM orders
    WHERE order_date >= CURDATE() - INTERVAL 30 DAY
    GROUP BY DATE(order_date)
),
growth_metrics AS (
    -- 전일 대비 성장률
    SELECT
        날짜,
        주문건수,
        총매출,
        LAG(총매출) OVER (ORDER BY 날짜) as 전일매출,
        ROUND((총매출 - LAG(총매출) OVER (ORDER BY 날짜))
            / LAG(총매출) OVER (ORDER BY 날짜) * 100, 2) as 전일대비성장률
    FROM daily_metrics
),
top_products AS (
    -- 오늘의 인기 상품 TOP 5
    SELECT
        product_id,
        product_name,
        COUNT(*) as 판매건수,
        SUM(amount) as 매출,
        RANK() OVER (ORDER BY SUM(amount) DESC) as 순위
    FROM orders
    WHERE DATE(order_date) = CURDATE()
    GROUP BY product_id, product_name
    LIMIT 5
)
-- 최종 종합 대시보드
SELECT
    '오늘' as 구분,
    dm.주문건수,
    dm.구매고객수,
    dm.총매출,
    dm.평균주문금액,
    gm.전일대비성장률,
    (SELECT COUNT(*) FROM customers WHERE DATE(created_at) = CURDATE()) as 신규가입고객,
    (SELECT GROUP_CONCAT(product_name ORDER BY 순위) FROM top_products) as 인기상품TOP5
FROM daily_metrics dm
LEFT JOIN growth_metrics gm ON dm.날짜 = gm.날짜
WHERE dm.날짜 = CURDATE();

설명

이것이 하는 일: 위 쿼리는 오늘의 모든 핵심 비즈니스 지표를 한 행으로 보여주는 완성된 대시보드 데이터를 생성합니다. 첫 번째로, daily_metrics CTE에서 최근 30일간의 일별 기본 지표를 계산합니다.

이것은 기초 데이터입니다. 날짜별로 주문 건수, 구매한 고객 수(중복 제거), 총 매출, 평균 주문 금액을 집계합니다.

WHERE 절로 최근 30일만 조회해서 성능을 최적화합니다. 그 다음으로, growth_metrics CTE에서 LAG 함수로 전날과 비교합니다.

앞서 배운 성장률 계산 기법을 적용한 것입니다. 이렇게 하면 "오늘 매출이 어제보다 15% 증가했다" 같은 인사이트를 즉시 얻을 수 있습니다.

top_products CTE는 오늘 판매된 상품 중 매출 순으로 상위 5개를 뽑습니다. RANK() 윈도우 함수로 순위를 매기고 LIMIT 5로 상위만 가져옵니다.

경영진은 "오늘 어떤 상품이 잘 팔리고 있는가"를 궁금해하므로 이 정보가 중요합니다. 마지막 SELECT에서 모든 CTE를 조합합니다.

LEFT JOIN으로 growth_metrics를 연결하고, 서브쿼리로 신규 가입 고객 수를 추가합니다. GROUP_CONCAT으로 인기 상품 5개를 쉼표로 연결한 문자열로 만들면 대시보드에 "아이폰, 갤럭시, 에어팟..." 처럼 표시할 수 있습니다.

WHERE dm.날짜 = CURDATE()로 오늘 데이터만 최종 선택합니다. 여러분이 이 쿼리를 사용하면 매일 아침 한 번의 실행으로 모든 핵심 지표를 얻을 수 있습니다.

실무에서는 이 쿼리를 뷰(VIEW)로 만들어두고 'SELECT * FROM daily_dashboard'처럼 간단하게 호출합니다. Grafana나 Metabase 같은 BI 툴에 연결하면 자동으로 차트가 그려지고, 슬랙 봇으로 매일 아침 자동 전송할 수도 있습니다.

성능이 중요하다면 order_date, customer_id, product_id 컬럼에 인덱스를 걸어야 합니다.

실전 팁

💡 CTE는 가독성은 좋지만 MySQL 버전에 따라 성능 이슈가 있을 수 있습니다. 서브쿼리나 임시 테이블과 비교 테스트하세요.

💡 대시보드 쿼리는 자주 실행되므로 캐싱이 중요합니다. Redis에 5분간 캐시하면 DB 부하를 90% 줄일 수 있습니다.

💡 인덱스 전략: WHERE 절의 order_date, GROUP BY의 customer_id에 복합 인덱스를 만드세요. EXPLAIN으로 실행 계획을 확인하세요.

💡 실시간성이 중요하지 않다면 배치로 집계 테이블을 만드세요. 매시간 집계를 미리 계산해두면 대시보드가 즉시 로드됩니다.

💡 에러 처리를 추가하세요. COALESCE로 NULL을 0으로 바꾸고, IFNULL로 0 나누기 오류를 방지하세요. 대시보드가 깨지면 안 됩니다.

#SQL#데이터분석#집계함수#윈도우함수#코호트분석#SQL,Database,데이터분석

실무 데이터 분석 SQL 완벽 가이드

# 실무 데이터 분석 SQL 완벽 가이드 실제 업무에서 자주 사용하는 SQL 데이터 분석 기법을 단계별로 학습합니다. 매출 집계부터 고객 세분화까지, 실전 대시보드 쿼리 작성 방법을 배워보세요. --- ## 목차 1. [매출 데이터 일별/월별 집계](#매출-데이터-일별/월별-집계) 2. [성장률 계산 (전월 대비)](#성장률-계산-(전월-대비)) 3. [이동 평균 (Moving Average)](#이동-평균-(moving-average)) 4. [사용자 코호트 분석](#사용자-코호트-분석) 5. [RFM 분석으로 고객 세분화](#rfm-분석으로-고객-세분화) 6. [실전 대시보드 쿼리 작성](#실전-대시보드-쿼리-작성) --- ## 1. 매출 데이터 일별/월별 집계 ### 시작하며 여러분이 회사의 매출 데이터를 분석해야 하는데, 엑셀로 하나하나 날짜별로 합계를 내고 있다면 정말 비효율적이지 않나요? 매일 아침 어제 매출이 얼마인지, 이번 달 총 매출은 얼마인지 보고해야 하는데 손으로 계산하고 있다면 시간이 너무 오래 걸립니다. 이런 문제는 실제 개발 현장에서 매일 발생합니다. 데이터는 계속 쌓이는데 빠르게 집계하지 못하면 의사결정이 늦어지고, 비즈니스 기회를 놓칠 수 있습니다. 바로 이럴 때 필요한 것이 SQL 집계 함수입니다. GROUP BY와 날짜 함수를 활용하면 수백만 건의 매출 데이터도 몇 초 만에 일별, 월별로 정리할 수 있습니다. ### 개요 간단히 말해서, 이 개념은 흩어진 거래 데이터를 원하는 기간 단위로 묶어서 합계를 구하는 것입니다. 데이터베이스에는 주문이 발생할 때마다 기록이 쌓입니다. 하지만 우리가 보고 싶은 것은 "오늘 총 매출", "이번 달 총 매출" 같은 요약된 정보입니다. 예를 들어, 쇼핑몰에서 하루에 1000건의 주문이 발생했다면, 이걸 날짜별로 묶어서 하루 총 매출을 한눈에 볼 수 있습니다. 기존에는 데이터를 엑셀로 내려받아서 피벗 테이블을 만들었다면, 이제는 SQL 쿼리 한 번으로 실시간으로 집계할 수 있습니다. 핵심 특징은 첫째, GROUP BY로 날짜 단위를 지정하고, 둘째, SUM 같은 집계 함수로 금액을 합산하며, 셋째, DATE_TRUNC나 DATE_FORMAT으로 원하는 날짜 형식을 만듭니다. 이러한 특징들이 빠르고 정확한 매출 분석을 가능하게 합니다. ### 코드 예제 ```sql -- 일별 매출 집계 SELECT DATE(order_date) as 날짜, COUNT(*) as 주문건수, SUM(amount) as 총매출, AVG(amount) as 평균주문금액 FROM orders WHERE order_date >= '2024-01-01' GROUP BY DATE(order_date) ORDER BY 날짜 DESC; -- 월별 매출 집계 SELECT DATE_FORMAT(order_date, '%Y-%m') as 년월, COUNT(DISTINCT customer_id) as 구매고객수, SUM(amount) as 월매출 FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY 년월 DESC; ``` ### 설명 이것이 하는 일: 위 쿼리는 주문 테이블에서 날짜별로 데이터를 묶어서 매출 통계를 만들어냅니다. 첫 번째로, DATE(order_date)는 주문 시간에서 날짜만 추출합니다. 예를 들어 '2024-01-15 14:30:00'이라는 주문 시간이 있다면 '2024-01-15'로 변환합니다. 이렇게 하는 이유는 같은 날짜의 모든 주문을 하나로 묶기 위해서입니다. GROUP BY로 이 날짜를 기준으로 그룹을 만들면, 같은 날의 주문들이 한 그룹이 됩니다. 그 다음으로, SUM(amount)가 실행되면서 각 날짜 그룹의 금액을 모두 더합니다. COUNT(*)는 그룹 안의 주문 건수를 세고, AVG(amount)는 평균 주문 금액을 계산합니다. 내부에서는 데이터베이스가 날짜별로 데이터를 분류하고, 각 그룹마다 집계 함수를 적용합니다. 월별 집계 쿼리에서는 DATE_FORMAT 함수로 '2024-01'처럼 년-월 형식을 만듭니다. DISTINCT customer_id를 사용하면 중복 제거된 고객 수를 셀 수 있어서, "이번 달에 실제로 구매한 고객이 몇 명인지" 알 수 있습니다. 여러분이 이 쿼리를 사용하면 매일 아침 5분이면 전날 매출 리포트를 만들 수 있고, 월말에도 자동으로 월간 리포트를 생성할 수 있습니다. 실무에서는 이 쿼리를 스케줄러에 등록해서 자동화하거나, 대시보드 툴과 연결해서 실시간 차트로 보여줄 수 있습니다. ### 실전 팁 💡 WHERE 절로 날짜 범위를 제한하면 쿼리 속도가 훨씬 빨라집니다. 특히 order_date에 인덱스가 있다면 수백만 건도 빠르게 조회됩니다. 💡 시간대(timezone) 이슈를 조심하세요. 서버 시간과 사용자 시간대가 다르면 집계 결과가 달라질 수 있습니다. UTC 기준으로 저장하고 표시할 때 변환하는 것이 안전합니다. 💡 HAVING 절을 사용하면 집계 후 필터링이 가능합니다. 예를 들어 'HAVING 총매출 >= 1000000'으로 매출 100만원 이상인 날만 볼 수 있습니다. 💡 NULL 값 처리를 잊지 마세요. COALESCE(SUM(amount), 0)으로 매출이 없는 날도 0으로 표시되게 하면 차트가 깔끔해집니다. 💡 성능이 중요하다면 집계 테이블을 따로 만드세요. 매일 밤 배치로 일별 집계를 미리 계산해두면 대시보드 조회가 훨씬 빠릅니다. --- ## 2. 성장률 계산 (전월 대비) ### 시작하며 여러분이 CEO에게 "이번 달 매출이 지난달보다 얼마나 성장했나요?"라는 질문을 받았을 때, 두 달의 매출을 따로 조회해서 계산기로 계산하고 있나요? 실시간으로 성장률을 보여줘야 하는데 수동으로 계산하면 너무 느립니다. 이런 문제는 비즈니스 분석에서 가장 자주 발생합니다. 단순히 이번 달 매출만 보면 좋은지 나쁜지 판단하기 어렵습니다. 전월 대비, 전년 대비 성장률을 함께 봐야 트렌드를 파악할 수 있습니다. 바로 이럴 때 필요한 것이 윈도우 함수(Window Function)입니다. LAG 함수를 사용하면 이전 달 데이터를 현재 행에서 바로 참조해서 성장률을 계산할 수 있습니다. ### 개요 간단히 말해서, 이 개념은 현재 데이터와 이전 기간 데이터를 비교해서 증감률을 구하는 것입니다. 성장률은 비즈니스의 건강 상태를 보여주는 핵심 지표입니다. 매출이 100만원에서 120만원으로 늘었다면 20% 성장한 것입니다. 예를 들어, 월별 매출 추이를 볼 때 절대 금액보다 성장률이 더 중요한 경우가 많습니다. 투자자들은 "얼마나 빠르게 성장하고 있는가"를 가장 궁금해합니다. 기존에는 두 번의 쿼리로 이번 달과 지난달 매출을 각각 조회해서 애플리케이션 코드로 계산했다면, 이제는 SQL 윈도우 함수로 한 번의 쿼리로 모든 기간의 성장률을 계산할 수 있습니다. 핵심 특징은 첫째, LAG 함수로 이전 행의 값을 가져오고, 둘째, 수식으로 증감률을 계산하며, 셋째, PARTITION BY로 카테고리별 성장률도 구할 수 있다는 점입니다. 이러한 특징들이 복잡한 비교 분석을 간단하게 만들어줍니다. ### 코드 예제 ```sql -- 월별 매출 성장률 계산 SELECT 년월, 월매출, LAG(월매출) OVER (ORDER BY 년월) as 전월매출, ROUND( (월매출 - LAG(월매출) OVER (ORDER BY 년월)) / LAG(월매출) OVER (ORDER BY 년월) * 100, 2 ) as 전월대비성장률 FROM ( SELECT DATE_FORMAT(order_date, '%Y-%m') as 년월, SUM(amount) as 월매출 FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m') ) monthly_sales ORDER BY 년월 DESC; ``` ### 설명 이것이 하는 일: 위 쿼리는 각 월의 매출과 함께 전월 대비 성장률을 퍼센트로 보여줍니다. 첫 번째로, 서브쿼리에서 월별 매출을 먼저 집계합니다. 이것은 기본 데이터를 준비하는 단계입니다. DATE_FORMAT으로 년-월을 만들고 GROUP BY로 월별 합계를 구합니다. 그 다음으로, LAG(월매출) OVER (ORDER BY 년월) 부분이 핵심입니다. LAG는 "지연"이라는 뜻으로, 현재 행보다 한 행 이전의 값을 가져옵니다. OVER (ORDER BY 년월)은 "년월 순서대로 정렬했을 때"라는 의미입니다. 예를 들어 2024-03월 행에서 LAG를 쓰면 2024-02월의 매출을 가져옵니다. 이렇게 하면 같은 행에 현재 월과 전월 데이터가 함께 있게 됩니다. 성장률 계산 공식은 (현재값 - 이전값) / 이전값 * 100 입니다. 월매출이 100만원에서 120만원이 됐다면 (120-100)/100*100 = 20% 성장입니다. ROUND 함수로 소수점 둘째 자리까지만 표시해서 읽기 쉽게 만듭니다. 여러분이 이 쿼리를 사용하면 매월 성장 추이를 한눈에 볼 수 있습니다. 성장률이 마이너스로 나오면 매출이 감소한 것이고, 플러스면 증가한 것입니다. 실무에서는 이 데이터를 차트로 만들어서 경영진에게 보고하거나, 알림 설정을 해서 성장률이 급격히 떨어지면 알람을 받을 수 있습니다. ### 실전 팁 💡 첫 달은 전월 데이터가 없어서 NULL이 나옵니다. COALESCE로 0이나 '-'로 표시하면 보고서가 깔끔해집니다. 💡 전년 동월 대비를 보려면 LAG(월매출, 12)처럼 12개월 전 데이터를 가져오세요. 계절성 비즈니스는 전년 동월 비교가 더 의미있습니다. 💡 PARTITION BY category를 추가하면 카테고리별 성장률을 따로 계산할 수 있습니다. 예를 들어 의류, 전자제품 등 각 카테고리의 성장률을 한번에 볼 수 있습니다. 💡 성장률이 비정상적으로 크게 나오면 전월 매출이 0에 가까운 경우입니다. CASE WHEN으로 전월 매출이 일정 금액 이하면 계산하지 않도록 예외 처리하세요. 💡 여러 지표를 함께 보세요. 매출은 늘었는데 주문 건수가 줄었다면 객단가가 오른 것입니다. 성장률만 보지 말고 절대값, 건수, 고객수를 함께 분석하세요. --- ## 3. 이동 평균 (Moving Average) ### 시작하며 여러분이 일별 매출 그래프를 보는데 너무 들쭉날쭉해서 트렌드를 파악하기 어려운 경험 있나요? 평일엔 매출이 높고 주말엔 낮아서 그래프가 지그재그로 움직이면, 전체적으로 증가 추세인지 감소 추세인지 한눈에 보이지 않습니다. 이런 문제는 일별 데이터를 다룰 때 항상 발생합니다. 단기 변동성 때문에 장기 트렌드가 가려지는 것입니다. 주식 차트를 보면 5일 이동평균선, 20일 이동평균선이 있는 이유가 바로 이것입니다. 바로 이럴 때 필요한 것이 이동 평균(Moving Average)입니다. 최근 N일의 평균을 계속 계산해서 선으로 그으면 노이즈가 제거되고 깔끔한 추세선이 나타납니다. ### 개요 간단히 말해서, 이 개념은 일정 기간의 평균을 계속 이동하면서 계산하여 변동성을 부드럽게 만드는 것입니다. 비즈니스 데이터는 요일, 날씨, 이벤트 등 여러 요인으로 날마다 변동합니다. 예를 들어, 음식 배달 서비스는 금요일 저녁에 주문이 폭발하고 월요일 오전엔 조용합니다. 하지만 우리가 알고 싶은 것은 "전체적으로 사업이 성장하고 있는가"입니다. 7일 이동평균을 쓰면 요일별 변동이 상쇄되어 진짜 트렌드가 보입니다. 기존에는 일별 데이터를 엑셀로 다운받아 이동평균 함수를 적용했다면, 이제는 SQL 윈도우 함수로 실시간으로 계산할 수 있습니다. 핵심 특징은 첫째, ROWS BETWEEN으로 범위를 지정하고, 둘째, AVG 같은 집계 함수와 윈도우 함수를 조합하며, 셋째, 기간을 조절해서 민감도를 조절할 수 있다는 점입니다. 이러한 특징들이 데이터의 본질적인 패턴을 드러나게 합니다. ### 코드 예제 ```sql -- 7일 이동 평균 매출 계산 SELECT 날짜, 일매출, -- 현재 행 포함 이전 6일, 총 7일 평균 ROUND(AVG(일매출) OVER ( ORDER BY 날짜 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ), 2) as 7일이동평균, -- 30일 이동 평균 ROUND(AVG(일매출) OVER ( ORDER BY 날짜 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW ), 2) as 30일이동평균 FROM ( SELECT DATE(order_date) as 날짜, SUM(amount) as 일매출 FROM orders GROUP BY DATE(order_date) ) daily_sales ORDER BY 날짜 DESC; ``` ### 설명 이것이 하는 일: 위 쿼리는 각 날짜마다 최근 7일간의 평균 매출과 30일간의 평균 매출을 함께 보여줍니다. 첫 번째로, 서브쿼리에서 일별 매출을 집계합니다. 이것은 기본 데이터를 만드는 단계입니다. 날짜별로 그날의 총 매출을 구합니다. 그 다음으로, AVG(일매출) OVER (ORDER BY 날짜 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 부분이 핵심입니다. 이것을 쉽게 풀어서 설명하면 "날짜 순서대로 정렬했을 때, 현재 행부터 이전 6개 행까지, 즉 총 7개 행의 일매출 평균을 구하라"는 뜻입니다. 예를 들어 1월 7일 행에서 이 함수를 실행하면 1월 1일부터 1월 7일까지 7일간의 평균을 계산합니다. 1월 8일 행으로 가면 1월 2일부터 1월 8일까지로 범위가 이동합니다. 30일 이동평균은 범위를 29 PRECEDING으로 늘린 것입니다. 7일 이동평균은 단기 트렌드를, 30일 이동평균은 중장기 트렌드를 보여줍니다. 주식 차트처럼 두 선이 교차하는 지점에서 추세 전환을 감지할 수 있습니다. 여러분이 이 쿼리를 사용하면 일일 매출의 급등락에 속지 않고 진짜 성장세를 파악할 수 있습니다. 실무에서는 7일 선이 30일 선을 상향 돌파하면 상승 추세가 시작된 신호로 보고, 하향 돌파하면 주의 신호로 봅니다. 대시보드에 두 선을 겹쳐서 그리면 경영진이 직관적으로 이해할 수 있습니다. ### 실전 팁 💡 초반 6일은 7일치 데이터가 없어서 평균이 정확하지 않습니다. WHERE 절로 충분한 데이터가 쌓인 후부터만 표시하세요. 💡 계절성 비즈니스는 365일 이동평균을 쓰면 전년 동일 시즌과 비교할 수 있습니다. 크리스마스 시즌 같은 이벤트 효과를 제거할 수 있습니다. 💡 가중 이동평균도 유용합니다. 최근 데이터에 더 높은 가중치를 주려면 CASE WHEN으로 날짜별 가중치를 곱해서 계산하세요. 💡 성능 최적화: 이동평균을 매번 계산하면 느릴 수 있으니, 배치로 계산해서 별도 테이블에 저장하고 대시보드는 그 테이블을 조회하세요. 💡 이동평균이 실제 값보다 항상 늦게 반응합니다. 급격한 변화를 빨리 감지하려면 짧은 기간(3일, 5일)을, 안정적인 트렌드를 보려면 긴 기간(30일, 90일)을 쓰세요. --- ## 4. 사용자 코호트 분석 ### 시작하며 여러분이 1월에 가입한 고객들이 2월, 3월에도 계속 구매하는지 추적하고 싶은데, 어떻게 분석해야 할지 막막한 경험 있나요? 단순히 월별 매출만 보면 신규 고객 때문에 늘어난 건지, 기존 고객이 재구매해서 늘어난 건지 구분이 안 됩니다. 이런 문제는 구독 서비스나 커머스 비즈니스에서 매우 중요합니다. 신규 고객을 열심히 유치했는데 다음 달에 다 이탈하면 밑 빠진 독에 물 붓기입니다. 고객 유지율(Retention)을 측정해야 사업의 지속 가능성을 판단할 수 있습니다. 바로 이럴 때 필요한 것이 코호트 분석(Cohort Analysis)입니다. 같은 시기에 가입한 사용자 그룹을 추적하면서 시간이 지나도 얼마나 많이 남아있는지 분석하는 기법입니다. ### 개요 간단히 말해서, 이 개념은 사용자를 가입 시기별로 그룹을 나누고, 각 그룹이 시간이 지남에 따라 어떻게 행동하는지 추적하는 것입니다. 코호트는 "동일한 특성을 가진 집단"을 의미합니다. 예를 들어, 2024년 1월에 가입한 사용자 1000명을 하나의 코호트로 봅니다. 이 1000명 중 2월에 몇 명이 구매했는지(2주차 리텐션), 3월에 몇 명이 구매했는지(3주차 리텐션)를 추적합니다. Netflix 같은 구독 서비스는 코호트 분석으로 "1월 가입자의 3개월 후 구독 유지율"을 정확히 파악합니다. 기존에는 애플리케이션 코드로 복잡한 루프를 돌려서 계산했다면, 이제는 SQL 셀프 조인과 윈도우 함수로 직접 계산할 수 있습니다. 핵심 특징은 첫째, 가입 월(코호트)을 기준으로 그룹을 만들고, 둘째, 각 월의 활동을 추적하며, 셋째, 비율로 계산해서 유지율을 구한다는 점입니다. 이러한 특징들이 고객 생애 가치(LTV)를 예측하고 마케팅 효율을 측정하는 기반이 됩니다. ### 코드 예제 ```sql -- 월별 가입 코호트의 재구매율 분석 WITH user_cohorts AS ( SELECT user_id, DATE_FORMAT(MIN(order_date), '%Y-%m') as 가입월 FROM orders GROUP BY user_id ), cohort_activity AS ( SELECT uc.가입월, DATE_FORMAT(o.order_date, '%Y-%m') as 활동월, COUNT(DISTINCT o.user_id) as 활성사용자수 FROM user_cohorts uc JOIN orders o ON uc.user_id = o.user_id GROUP BY uc.가입월, DATE_FORMAT(o.order_date, '%Y-%m') ) SELECT 가입월, 활동월, 활성사용자수, FIRST_VALUE(활성사용자수) OVER ( PARTITION BY 가입월 ORDER BY 활동월 ) as 코호트크기, ROUND(활성사용자수 * 100.0 / FIRST_VALUE(활성사용자수) OVER ( PARTITION BY 가입월 ORDER BY 활동월 ), 2) as 유지율 FROM cohort_activity ORDER BY 가입월, 활동월; ``` ### 설명 이것이 하는 일: 위 쿼리는 각 가입 월별 코호트가 이후 매월 얼마나 활성 상태를 유지하는지 퍼센트로 보여줍니다. 첫 번째로, user_cohorts CTE에서 각 사용자의 첫 구매 월을 찾습니다. MIN(order_date)로 가장 빨리 주문한 날짜를 찾고, 이것을 가입월로 정의합니다. 예를 들어 어떤 사용자가 2024-01-15에 첫 구매를 했다면 이 사용자의 가입월은 '2024-01'입니다. 그 다음으로, cohort_activity CTE에서 각 코호트가 매월 얼마나 활동했는지 집계합니다. 가입월로 사용자를 그룹화하고, 그들이 실제로 주문한 월을 활동월로 기록합니다. 2024-01 코호트가 2024-02에 몇 명 주문했는지, 2024-03에 몇 명 주문했는지 세는 것입니다. 마지막 SELECT에서 FIRST_VALUE 윈도우 함수가 핵심입니다. PARTITION BY 가입월로 각 코호트별로 구분하고, ORDER BY 활동월로 정렬했을 때 첫 번째 값(즉, 가입월의 사용자 수)을 가져옵니다. 이것이 코호트의 전체 크기입니다. 유지율은 (현재 활성 사용자 수 / 코호트 전체 크기) * 100으로 계산합니다. 여러분이 이 쿼리를 사용하면 "1월 가입자 1000명 중 3개월 후에도 300명이 활동 중(30% 유지율)"처럼 명확한 인사이트를 얻습니다. 실무에서는 이 데이터를 히트맵으로 시각화하면 어떤 코호트가 유지율이 높은지 한눈에 보입니다. 마케팅 캠페인 시기와 비교하면 어떤 채널로 온 고객이 충성도가 높은지 파악할 수 있습니다. ### 실전 팁 💡 주간 코호트나 일간 코호트도 만들 수 있습니다. 빠르게 성장하는 서비스는 월간보다 주간 코호트가 더 유용합니다. 💡 단순 활동 여부뿐만 아니라 매출 기준 유지율도 계산하세요. 사용자는 남아있는데 구매 금액이 줄어들 수도 있습니다. 💡 코호트 크기를 함께 표시하세요. 100명 코호트의 50% 유지율과 10,000명 코호트의 50% 유지율은 의미가 다릅니다. 💡 첫 달(Month 0)은 항상 100%입니다. 비교는 Month 1부터 하세요. Month 3 유지율이 30% 이상이면 좋은 편입니다. 💡 코호트 분석은 무거운 쿼리입니다. 매일 배치로 계산해서 집계 테이블에 저장하고, 대시보드는 그 테이블을 읽도록 최적화하세요. --- ## 5. RFM 분석으로 고객 세분화 ### 시작하며 여러분이 100만 명의 고객 데이터베이스를 가지고 있는데, 프로모션 이메일을 누구에게 보내야 할지 고민한 적 있나요? 전체 고객에게 다 보내면 비용이 너무 많이 들고, 아무에게나 보내면 스팸으로 분류됩니다. 이런 문제는 마케팅 효율성과 직결됩니다. 모든 고객이 똑같지 않습니다. 최근에 비싼 물건을 자주 사는 VIP 고객과, 1년 전에 한 번 사고 안 온 고객을 같게 대우하면 안 됩니다. 고객을 세분화해서 맞춤형 전략을 써야 투자 대비 효과(ROI)가 높아집니다. 바로 이럴 때 필요한 것이 RFM 분석입니다. Recency(최근성), Frequency(빈도), Monetary(금액) 세 가지 지표로 고객을 점수화하고 등급을 나누는 기법입니다. ### 개요 간단히 말해서, 이 개념은 고객의 구매 패턴을 세 가지 측면에서 점수로 환산하여 가치 있는 고객을 찾아내는 것입니다. RFM은 마케팅의 고전적인 분석 기법입니다. R(Recency)은 "마지막 구매가 얼마나 최근인가"로, 어제 산 고객이 1년 전에 산 고객보다 점수가 높습니다. F(Frequency)는 "얼마나 자주 구매하는가"로, 월 10회 구매 고객이 연 1회 고객보다 점수가 높습니다. M(Monetary)은 "총 얼마나 썼는가"로, 100만원 쓴 고객이 1만원 쓴 고객보다 점수가 높습니다. 예를 들어, 스타벅스는 RFM으로 고객을 분류해서 VIP에게는 무료 음료 쿠폰을, 이탈 위험 고객에게는 할인 쿠폰을 보냅니다. 기존에는 데이터를 엑셀로 내려받아 수동으로 점수를 매겼다면, 이제는 SQL로 자동화할 수 있습니다. 핵심 특징은 첫째, NTILE 함수로 고객을 5등급이나 10등급으로 나누고, 둘째, 세 지표를 조합해서 종합 점수를 만들며, 셋째, 세그먼트별로 다른 마케팅 전략을 적용한다는 점입니다. 이러한 특징들이 데이터 기반 마케팅의 기초가 됩니다. ### 코드 예제 ```sql -- RFM 점수 계산 및 고객 세분화 WITH rfm_calc AS ( SELECT customer_id, -- R: 마지막 구매 후 경과 일수 (작을수록 좋음) DATEDIFF(CURDATE(), MAX(order_date)) as recency, -- F: 총 구매 횟수 COUNT(*) as frequency, -- M: 총 구매 금액 SUM(amount) as monetary FROM orders GROUP BY customer_id ), rfm_score AS ( SELECT customer_id, recency, frequency, monetary, -- 5등급으로 나누기 (5가 가장 좋음) NTILE(5) OVER (ORDER BY recency DESC) as r_score, NTILE(5) OVER (ORDER BY frequency ASC) as f_score, NTILE(5) OVER (ORDER BY monetary ASC) as m_score FROM rfm_calc ) SELECT customer_id, r_score, f_score, m_score, r_score + f_score + m_score as rfm_total, CASE WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'VIP고객' WHEN r_score >= 4 AND f_score <= 2 THEN '신규유망고객' WHEN r_score <= 2 AND f_score >= 4 THEN '이탈위험고객' ELSE '일반고객' END as 고객등급 FROM rfm_score ORDER BY rfm_total DESC; ``` ### 설명 이것이 하는 일: 위 쿼리는 모든 고객에게 RFM 점수를 매기고, 점수 조합으로 VIP, 신규유망, 이탈위험 등으로 분류합니다. 첫 번째로, rfm_calc CTE에서 각 고객의 원시 RFM 값을 계산합니다. DATEDIFF(CURDATE(), MAX(order_date))는 "오늘 날짜와 마지막 구매일의 차이"로 며칠 전에 구매했는지 계산합니다. 2일 전이면 2, 365일 전이면 365입니다. COUNT(*)는 총 주문 횟수, SUM(amount)는 총 구매 금액입니다. 그 다음으로, rfm_score CTE에서 NTILE 함수로 점수를 매깁니다. NTILE(5)는 데이터를 5개 그룹으로 나눕니다. ORDER BY recency DESC로 정렬하면, recency가 작은(최근에 구매한) 고객이 5점, 큰(오래된) 고객이 1점을 받습니다. frequency와 monetary는 ASC로 정렬해서 많을수록 높은 점수를 받습니다. 이렇게 각 지표마다 1~5점 사이의 점수가 부여됩니다. 마지막 SELECT에서 CASE 문으로 고객을 분류합니다. r_score, f_score, m_score가 모두 4 이상이면 모든 면에서 우수한 'VIP고객'입니다. r_score는 높은데 f_score가 낮으면 최근에 가입했지만 아직 구매를 많이 안 한 '신규유망고객'입니다. r_score가 낮고 f_score가 높으면 과거엔 많이 샀는데 요즘 안 사는 '이탈위험고객'입니다. 여러분이 이 쿼리를 사용하면 10만 명 고객을 의미 있는 세그먼트로 나눌 수 있습니다. VIP고객에게는 신제품 우선 구매 기회를, 이탈위험고객에게는 "오랜만이에요, 20% 할인 쿠폰" 같은 리인게이지먼트 캠페인을, 신규유망고객에게는 온보딩 가이드를 보낼 수 있습니다. 실무에서는 이 세그먼트를 CRM 툴이나 이메일 마케팅 플랫폼과 연동해서 자동화합니다. ### 실전 팁 💡 NTILE 구간을 업종에 맞게 조정하세요. 명품 브랜드는 3등급, 대중 이커머스는 10등급처럼 고객 분포에 따라 달라집니다. 💡 가중치를 다르게 줄 수 있습니다. r_score * 3 + f_score * 2 + m_score처럼 최근성에 더 높은 가중치를 주면 이탈 방지에 집중하게 됩니다. 💡 카테고리별 RFM도 유용합니다. 전체적으로는 VIP가 아니어도 특정 카테고리에서는 VIP일 수 있습니다. PARTITION BY category 추가하세요. 💡 시간에 따른 등급 변화를 추적하세요. 이번 달 VIP였다가 다음 달 일반고객으로 내려가면 이탈 신호입니다. 월별 스냅샷을 저장하세요. 💡 RFM은 과거 데이터 기반입니다. 미래 구매 가능성을 예측하려면 머신러닝 모델과 결합하세요. RFM 점수를 피처로 사용하면 좋은 시작점입니다. --- ## 6. 실전 대시보드 쿼리 작성 ### 시작하며 여러분이 CEO에게 "오늘 아침 회의에서 주요 지표 대시보드를 보여달라"는 요청을 받았는데, 여러 개의 쿼리를 돌려서 엑셀에 복붙하고 계신가요? 매출, 신규 고객, 유지율, 인기 상품을 각각 따로 조회하면 시간도 오래 걸리고 실수도 생깁니다. 이런 문제는 경영진에게 보고할 때 항상 발생합니다. 단편적인 숫자들을 모아놓으면 전체 그림을 파악하기 어렵습니다. 한 화면에 핵심 지표들이 일관된 기준으로 정리되어야 의사결정이 빨라집니다. 바로 이럴 때 필요한 것이 실전 대시보드 쿼리입니다. 하나의 쿼리로 여러 지표를 한번에 계산하거나, 효율적인 여러 쿼리를 조합해서 완성된 대시보드를 만드는 것입니다. ### 개요 간단히 말해서, 이 개념은 비즈니스에 필요한 모든 핵심 지표를 빠르고 정확하게 조회할 수 있도록 쿼리를 설계하는 것입니다. 대시보드는 회사의 건강 상태를 한눈에 보여주는 계기판입니다. 자동차 계기판에 속도, 연료, 엔진 상태가 함께 표시되듯이, 비즈니스 대시보드에는 매출, 고객 수, 전환율 등이 함께 표시되어야 합니다. 예를 들어, Tableau나 Metabase 같은 BI 툴도 결국 SQL 쿼리로 데이터를 가져옵니다. 쿼리가 느리면 대시보드가 느려지고, 쿼리가 부정확하면 잘못된 의사결정을 하게 됩니다. 기존에는 각 지표를 별도 쿼리로 조회하고 애플리케이션에서 조합했다면, 이제는 CTE와 윈도우 함수를 활용해 효율적으로 설계할 수 있습니다. 핵심 특징은 첫째, 여러 CTE를 조합해서 복잡한 로직을 단계별로 분리하고, 둘째, 인덱스를 고려해서 성능을 최적화하며, 셋째, 재사용 가능한 뷰나 함수로 만들어 유지보수를 쉽게 한다는 점입니다. 이러한 특징들이 프로덕션 환경에서 매일 사용되는 안정적인 대시보드를 만들어줍니다. ### 코드 예제 ```sql -- 일일 비즈니스 대시보드 종합 쿼리 WITH daily_metrics AS ( -- 오늘 매출 및 주문 SELECT DATE(order_date) as 날짜, COUNT(*) as 주문건수, COUNT(DISTINCT customer_id) as 구매고객수, SUM(amount) as 총매출, AVG(amount) as 평균주문금액 FROM orders WHERE order_date >= CURDATE() - INTERVAL 30 DAY GROUP BY DATE(order_date) ), growth_metrics AS ( -- 전일 대비 성장률 SELECT 날짜, 주문건수, 총매출, LAG(총매출) OVER (ORDER BY 날짜) as 전일매출, ROUND((총매출 - LAG(총매출) OVER (ORDER BY 날짜)) / LAG(총매출) OVER (ORDER BY 날짜) * 100, 2) as 전일대비성장률 FROM daily_metrics ), top_products AS ( -- 오늘의 인기 상품 TOP 5 SELECT product_id, product_name, COUNT(*) as 판매건수, SUM(amount) as 매출, RANK() OVER (ORDER BY SUM(amount) DESC) as 순위 FROM orders WHERE DATE(order_date) = CURDATE() GROUP BY product_id, product_name LIMIT 5 ) -- 최종 종합 대시보드 SELECT '오늘' as 구분, dm.주문건수, dm.구매고객수, dm.총매출, dm.평균주문금액, gm.전일대비성장률, (SELECT COUNT(*) FROM customers WHERE DATE(created_at) = CURDATE()) as 신규가입고객, (SELECT GROUP_CONCAT(product_name ORDER BY 순위) FROM top_products) as 인기상품TOP5 FROM daily_metrics dm LEFT JOIN growth_metrics gm ON dm.날짜 = gm.날짜 WHERE dm.날짜 = CURDATE(); ``` ### 설명 이것이 하는 일: 위 쿼리는 오늘의 모든 핵심 비즈니스 지표를 한 행으로 보여주는 완성된 대시보드 데이터를 생성합니다. 첫 번째로, daily_metrics CTE에서 최근 30일간의 일별 기본 지표를 계산합니다. 이것은 기초 데이터입니다. 날짜별로 주문 건수, 구매한 고객 수(중복 제거), 총 매출, 평균 주문 금액을 집계합니다. WHERE 절로 최근 30일만 조회해서 성능을 최적화합니다. 그 다음으로, growth_metrics CTE에서 LAG 함수로 전날과 비교합니다. 앞서 배운 성장률 계산 기법을 적용한 것입니다. 이렇게 하면 "오늘 매출이 어제보다 15% 증가했다" 같은 인사이트를 즉시 얻을 수 있습니다. top_products CTE는 오늘 판매된 상품 중 매출 순으로 상위 5개를 뽑습니다. RANK() 윈도우 함수로 순위를 매기고 LIMIT 5로 상위만 가져옵니다. 경영진은 "오늘 어떤 상품이 잘 팔리고 있는가"를 궁금해하므로 이 정보가 중요합니다. 마지막 SELECT에서 모든 CTE를 조합합니다. LEFT JOIN으로 growth_metrics를 연결하고, 서브쿼리로 신규 가입 고객 수를 추가합니다. GROUP_CONCAT으로 인기 상품 5개를 쉼표로 연결한 문자열로 만들면 대시보드에 "아이폰, 갤럭시, 에어팟..." 처럼 표시할 수 있습니다. WHERE dm.날짜 = CURDATE()로 오늘 데이터만 최종 선택합니다. 여러분이 이 쿼리를 사용하면 매일 아침 한 번의 실행으로 모든 핵심 지표를 얻을 수 있습니다. 실무에서는 이 쿼리를 뷰(VIEW)로 만들어두고 'SELECT * FROM daily_dashboard'처럼 간단하게 호출합니다. Grafana나 Metabase 같은 BI 툴에 연결하면 자동으로 차트가 그려지고, 슬랙 봇으로 매일 아침 자동 전송할 수도 있습니다. 성능이 중요하다면 order_date, customer_id, product_id 컬럼에 인덱스를 걸어야 합니다. ### 실전 팁 💡 CTE는 가독성은 좋지만 MySQL 버전에 따라 성능 이슈가 있을 수 있습니다. 서브쿼리나 임시 테이블과 비교 테스트하세요. 💡 대시보드 쿼리는 자주 실행되므로 캐싱이 중요합니다. Redis에 5분간 캐시하면 DB 부하를 90% 줄일 수 있습니다. 💡 인덱스 전략: WHERE 절의 order_date, GROUP BY의 customer_id에 복합 인덱스를 만드세요. EXPLAIN으로 실행 계획을 확인하세요. 💡 실시간성이 중요하지 않다면 배치로 집계 테이블을 만드세요. 매시간 집계를 미리 계산해두면 대시보드가 즉시 로드됩니다. 💡 에러 처리를 추가하세요. COALESCE로 NULL을 0으로 바꾸고, IFNULL로 0 나누기 오류를 방지하세요. 대시보드가 깨지면 안 됩니다. ---

카테고리: SQL,Database,데이터분석

언어: SQL

태그: SQL, 데이터분석, 집계함수, 윈도우함수, 코호트분석, SQL,Database,데이터분석

작성자: AI Generated

프리미엄 콘텐츠 - 3개월 무료 체험 가능

실무 데이터 분석 SQL 완벽 가이드

목차

1. 매출 데이터 일별/월별 집계

시작하며

개요

코드 예제

설명

실전 팁

2. 성장률 계산 (전월 대비)

시작하며

개요

코드 예제

설명

실전 팁

3. 이동 평균 (Moving Average)

시작하며

개요

코드 예제

설명

실전 팁

4. 사용자 코호트 분석

시작하며

개요

코드 예제

설명

실전 팁

5. RFM 분석으로 고객 세분화

시작하며

개요

코드 예제

설명

실전 팁

6. 실전 대시보드 쿼리 작성

시작하며

개요

코드 예제

설명

실전 팁

이 카드뉴스가 포함된 코스

SQL 실무 완전 정복

댓글 (0)

함께 보면 좋은 카드 뉴스

SQL 실전 종합 프로젝트 완벽 가이드

데이터 모델링과 정규화 완벽 가이드

트랜잭션과 ACID 원칙 완벽 가이드

인덱스와 쿼리 성능 최적화 완벽 가이드

SQL 날짜/시간 함수 완벽 가이드