Skip to Content
이론 및 개념 (Concepts)SQL그룹화와 집계

GROUP BY를 활용한 카테고리 성과 분석

초급 중급

학습 목표

  • GROUP BY의 핵심 개념과 집계 함수 활용
  • 단일 및 다중 컬럼 그룹화 기법
  • HAVING 절로 집계 결과 필터링
  • COUNT DISTINCT로 고유값 분석
  • 집계 함수 조합으로 통합 지표 도출

BigQuery 실행 환경 설정

from google.cloud import bigquery
import pandas as pd
 
# 인증 설정 (서비스 계정 키 사용 시)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# BigQuery 클라이언트 생성
client = bigquery.Client(project='your-project-id')
 
# 쿼리 실행 함수
def run_query(query):
    return client.query(query).to_dataframe()
ℹ️
프로젝트 ID 설정

위 코드에서 your-project-id를 본인의 GCP 프로젝트 ID로 변경하세요.

예제 1: 기본 GROUP BY - 카테고리별 집계

이론

GROUP BY는 동일한 값을 가진 행들을 하나의 그룹으로 묶어 집계합니다.

기본 구문

SELECT column, AGG_FUNCTION(column)
FROM table
GROUP BY column

주요 집계 함수

함수설명예시
COUNT(*)행 개수주문 건수
COUNT(DISTINCT col)고유값 개수고유 고객 수
SUM(col)합계총 매출
AVG(col)평균평균 주문금액
MIN(col) / MAX(col)최소/최대값최저가/최고가

예제 코드

실행 결과
categoryorder_countitem_counttotal_revenueavg_price
Outerwear & Coats8,8649,0771,311,691144.51
Jeans11,98612,3651,212,71298.08
Sweaters10,74911,073837,37675.62
Swim10,90511,224646,69457.62
Suits & Sport Coats4,9725,095646,693126.93
……………
ℹ️
COUNT(*) vs COUNT(column)
  • COUNT(*): NULL 포함 모든 행 카운트
  • COUNT(column): NULL 제외한 행 카운트
  • COUNT(DISTINCT column): 고유값만 카운트

퀴즈 1: 브랜드별 매출 및 이익률 계산

문제

order_items와 products를 JOIN하여:

  1. 브랜드별 총 매출 (sale_price 합계)
  2. 브랜드별 총 원가 (cost 합계)
  3. 이익 = 매출 - 원가
  4. 이익률(%) = (이익 / 매출) * 100
  5. 매출 상위 10개 브랜드만 조회

힌트: SUM(), GROUP BY brand, ORDER BY DESC, LIMIT

정답 보기

SELECT
    p.brand,
    SUM(oi.sale_price) AS total_revenue,
    SUM(p.cost) AS total_cost,
    SUM(oi.sale_price) - SUM(p.cost) AS profit,
    ROUND((SUM(oi.sale_price) - SUM(p.cost)) * 100.0 / SUM(oi.sale_price), 2) AS profit_margin
FROM `your-project-id.thelook_ecommerce.order_items` oi
JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
GROUP BY p.brand
ORDER BY total_revenue DESC
LIMIT 10
실행 결과
brandtotal_revenuetotal_costprofitprofit_margin
Calvin Klein207,13197,083110,04953.13
Diesel202,497101,074101,42350.09
Carhartt177,72183,01094,71153.29
7 For All Mankind176,22591,90884,31747.85
True Religion175,55291,98683,56747.60
……………

해설:

  • SUM(oi.sale_price) - SUM(p.cost): 총 이익 계산
  • 이익률 = (이익 / 매출) * 100
  • ROUND(..., 2): 소수점 2자리로 반올림

비즈니스 인사이트:

  • 고이익률 브랜드: 프리미엄 포지셔닝 유지
  • 저이익률 브랜드: 볼륨 전략 또는 원가 절감 필요
  • 매출과 이익률을 함께 보면 최적 포트폴리오 구성 가능

예제 2: 다중 컬럼 GROUP BY

이론

여러 컬럼으로 그룹화하면 더 세분화된 분석이 가능합니다.

구문

GROUP BY column1, column2, column3

활용 예시

  • 카테고리 + 브랜드별 분석
  • 연도 + 월별 추세
  • 지역 + 성별 세분화

예제 코드

실행 결과
categorybranditem_counttotal_revenueavg_price
AccessoriesRay-Ban67179,911119.09
AccessoriesOakley38852,646135.69
AccessoriesTom Ford4611,043240.07
AccessoriesKate Spade698,978130.11
……………

퀴즈 2: 부서별, 카테고리별 매출 집계

문제

order_items와 products를 JOIN하여:

  1. 부서(department)와 카테고리(category)별로 그룹화
  2. 판매 아이템 수, 총 매출, 평균 가격 계산
  3. 매출 상위 20개 조합만 조회

힌트: GROUP BY department, category

정답 보기

SELECT
    p.department,
    p.category,
    COUNT(*) AS item_count,
    SUM(oi.sale_price) AS total_revenue,
    ROUND(AVG(oi.sale_price), 2) AS avg_price
FROM `your-project-id.thelook_ecommerce.order_items` oi
JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
GROUP BY p.department, p.category
ORDER BY total_revenue DESC
LIMIT 20

해설:

  • GROUP BY department, category: 두 차원으로 동시 그룹화
  • 부서-카테고리 조합별 매출 현황 파악

비즈니스 활용:

  • 부서별 강점 카테고리 파악
  • 교차 판매(cross-selling) 기회 발견
  • 재고 배분 전략 수립

예제 3: HAVING 절 - 집계 결과 필터링

이론

WHERE는 그룹화 전 필터링, HAVING은 그룹화 후 필터링합니다.

실행 순서

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
              ↑                    ↑
         그룹화 전 필터링      그룹화 후 필터링

WHERE vs HAVING

구분WHEREHAVING
적용 시점GROUP BY 전GROUP BY 후
대상개별 행그룹
집계 함수사용 불가사용 가능

예제 코드

실행 결과
categoryorder_counttotal_revenueavg_price
Outerwear & Coats8,8641,311,691144.51
Jeans11,9861,212,71298.08
Sweaters10,749837,37675.62
… (22개 카테고리)………

퀴즈 3: 매출 상위 10개 브랜드만 출력

문제

order_items와 products를 JOIN하여:

  1. 브랜드별 총 매출, 주문 건수, 판매 아이템 수 계산
  2. HAVING으로 주문 건수가 100건 이상인 브랜드만 필터링
  3. 매출 기준 내림차순 정렬
  4. 상위 10개 브랜드만 조회

힌트: HAVING COUNT(DISTINCT ...) >= 100

정답 보기

SELECT
    p.brand,
    COUNT(DISTINCT oi.order_id) AS order_count,
    COUNT(*) AS item_count,
    SUM(oi.sale_price) AS total_revenue
FROM `your-project-id.thelook_ecommerce.order_items` oi
JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
GROUP BY p.brand
HAVING COUNT(DISTINCT oi.order_id) >= 100
ORDER BY total_revenue DESC
LIMIT 10
실행 결과
brandorder_countitem_counttotal_revenue
Calvin Klein3,2153,244207,131
Diesel1,4681,481202,497
Carhartt2,5462,562177,721
7 For All Mankind1,1181,120176,225
True Religion881882175,552
…………

해설:

  • HAVING COUNT(DISTINCT oi.order_id) >= 100: 그룹화 후 주문 건수 필터링
  • WHERE는 집계 전, HAVING은 집계 후 적용
  • LIMIT으로 최종 결과 개수 제한

실무 활용:

  • 일정 규모 이상의 브랜드만 분석 (노이즈 제거)
  • KPI 기준치 이상 세그먼트 추출
  • 상위 N개 집중 분석

예제 4: COUNT DISTINCT - 고유값 집계

이론

중복을 제거한 고유 값의 개수를 계산합니다.

구문

COUNT(DISTINCT column)

활용 예시

  • 고유 고객 수 (unique customers)
  • 고유 제품 수 (unique products)
  • 고유 거래 일수 (unique transaction dates)

예제 코드

실행 결과
categoryunique_productsunique_brandstotal_items_soldtotal_revenue
Outerwear & Coats1,4162869,0771,311,691
Jeans1,99822212,3651,212,712
Sweaters1,73334711,073837,376
……………

퀴즈 4: 카테고리별 고유 고객 수

문제

세 테이블을 JOIN하여:

  1. orders, order_items, products 조인
  2. 카테고리별 고유 고객 수 계산
  3. 고객당 평균 구매 금액 계산
  4. 총 주문 건수도 함께 조회
  5. 고유 고객 수 상위 10개 카테고리만 조회

힌트: COUNT(DISTINCT o.user_id), SUM(sale_price) / COUNT(DISTINCT user_id)

정답 보기

SELECT
    p.category,
    COUNT(DISTINCT o.user_id) AS unique_customers,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.sale_price) AS total_revenue,
    ROUND(SUM(oi.sale_price) / COUNT(DISTINCT o.user_id), 2) AS avg_revenue_per_customer
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
GROUP BY p.category
ORDER BY unique_customers DESC
LIMIT 10
실행 결과
categoryunique_customersorder_counttotal_revenueavg_revenue_per_customer
Jeans11,36011,9861,212,712106.75
Intimates11,26412,435449,36339.89
Tops & Tees10,91111,486488,32444.76
Fashion Hoodies & Sweatshirts10,76711,300632,60458.75
……………

해설:

  • COUNT(DISTINCT o.user_id): 중복 제거한 고유 고객 수
  • SUM(sale_price) / COUNT(DISTINCT user_id): 고객당 평균 매출 (ARPU)
  • 세 테이블 JOIN으로 통합 지표 산출

비즈니스 인사이트:

  • 고객 수 많음 + 평균 매출 낮음: 대중적 카테고리, 볼륨 전략
  • 고객 수 적음 + 평균 매출 높음: 프리미엄 카테고리, 고객 확대 필요
  • 이상적: 고객 수와 평균 매출 모두 높은 카테고리

예제 5: 집계 함수 조합 - 종합 분석

이론

여러 집계 함수를 조합하여 다차원 인사이트를 도출합니다.

주요 집계 함수

함수용도
SUM()합계
AVG()평균
MIN() / MAX()최소/최대
COUNT()개수
COUNT(DISTINCT)고유 개수
STDDEV()표준편차
APPROX_QUANTILES()백분위수 (BigQuery)

예제 코드

실행 결과
categorytotal_itemstotal_revenueavg_pricemin_pricemax_priceprice_rangeprice_stddev
Outerwear & Coats9,0771,311,691144.5110.51999.00988.49136.41
Jeans12,3651,212,71298.089.99389.00379.0164.21
Sweaters11,073837,37675.625.99588.00582.0159.62
……………………

퀴즈 5: 카테고리별 가격 범위 및 평균 분석

문제

products 테이블에서:

  1. 카테고리별로 retail_price 기준 통계 계산
    • 평균, 최소, 최대, 중앙값(50분위수), 표준편차
  2. 제품 수가 10개 이상인 카테고리만 필터링
  3. 평균 가격 기준 내림차순 정렬
  4. 상위 10개 조회

힌트: APPROX_QUANTILES(retail_price, 100)[OFFSET(50)] AS median

정답 보기

SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(retail_price), 2) AS avg_price,
    MIN(retail_price) AS min_price,
    MAX(retail_price) AS max_price,
    APPROX_QUANTILES(retail_price, 100)[OFFSET(50)] AS median_price,
    ROUND(STDDEV(retail_price), 2) AS price_stddev
FROM `your-project-id.thelook_ecommerce.products`
GROUP BY category
HAVING COUNT(*) >= 10
ORDER BY avg_price DESC
LIMIT 10
실행 결과
categoryproduct_countavg_pricemin_pricemax_pricemedian_priceprice_stddev
Outerwear & Coats1,420146.0210.51999.00109.95138.25
Suits & Sport Coats739126.569.99698.0099.5098.77
Suits188116.1613.99239.72122.6046.29
Jeans1,99997.859.99389.0078.0063.85
…………………

해설:

  • APPROX_QUANTILES(retail_price, 100)[OFFSET(50)]: 중앙값 계산
  • STDDEV(): 가격 변동성 측정
  • HAVING COUNT(*) >= 10: 통계적 유의미성 확보

비즈니스 인사이트:

  • 표준편차 큼: 가격 다양성이 높음 (저가~고가 제품 혼재)
  • 표준편차 작음: 가격대가 일정함 (타겟 고객층 명확)
  • 평균 > 중앙값: 고가 제품이 평균을 끌어올림
  • 평균 < 중앙값: 저가 제품이 많음

요약

학습한 GROUP BY 기법

기법용도예시
GROUP BY column단일 컬럼 집계카테고리별 매출
GROUP BY col1, col2다중 컬럼 집계카테고리+브랜드별 매출
HAVING집계 결과 필터링매출 $100K 이상만
COUNT(DISTINCT)고유값 개수고유 고객 수
MIN/MAX/AVG/SUM기본 집계 함수가격 통계

WHERE vs HAVING

-- WHERE: 그룹화 전 필터링 (개별 행)
WHERE created_at >= '2024-01-01'
 
-- HAVING: 그룹화 후 필터링 (집계 결과)
HAVING SUM(revenue) > 100000

실무 활용 시나리오

  1. 카테고리 성과 분석: GROUP BY category
  2. 브랜드 포트폴리오 최적화: GROUP BY brand + HAVING
  3. 고객 세분화: COUNT(DISTINCT user_id)
  4. 가격 전략 수립: MIN/MAX/AVG/STDDEV
  5. 교차 분석: GROUP BY 다중 컬럼
💡
다음 단계
  • 윈도우 함수로 순위 및 누적 집계
  • CTE와 결합하여 복잡한 분석 파이프라인 구축
  • ROLLUP/CUBE로 다차원 집계
Last updated on
날짜/시간 함수NULL 처리
🤖AI 모의면접실전처럼 연습하기