BDA-SQL-Aplied

연합 빅데이터 학회 BDA의 SQL 문법 심화 수업을 듣고 정리한 기록

---

실습 파일 목록

• SQL_Aplied2.sql : 조건문(CASE/IF) 기반 세그먼트 태깅, 조건부 집계, 날짜 함수 기초, CTE 구조 연습
• SQL_Aplied3.sql : DATEDIFF/DATE_ADD/DATE_FORMAT 기반 기간 지표 생성, 지연/리드타임 구간화 집계 연습
• SQL_Aplied4.sql : 브랜드 필터링 기반 월별 KPI 테이블 구성(매출/원가/마진/할인율/손익 플래그) 연습
• SQL_Aplied5.sql : orders-orderdetails 조인 기반 매출 집계 파이프라인(주문별/일별 매출) 구성, 롤링 지표 계산 연습
• SQL_Aplied6.sql : GA4 스타일 이벤트 로그 테이블 설계, JSON 컬럼/생성 칼럼/인덱스 구성, 재귀 CTE 기반 더미 이벤트 생성 구현
• SQL_Aplied7.sql : 세션 단위 퍼널 분석(단계별 플래그/전환율), 집계 기준 비교, 로그 무결성/품질 점검 쿼리 작성
• SQL_Aplied8.sql : 윈도우 함수(LAG/LEAD/PERCENT_RANK/CUME_DIST/RANK/NTILE) 기반 시계열·분포 분석 연습
• SQL_Practice5.sql : 고객별 매출 집계 후 percent_rank 기반 VVIP/VIP 세그먼트 태깅 쿼리 작성

---

주문/매출 데이터 집계부터 이벤트 로그 기반 퍼널 분석까지, “지표를 만드는 SQL”을 실습 중심으로 정리

1. 매출·마진·KPI 분석 쪽에서 한 실습들

1) 주문·주문상세로 매출 집계

(관련 파일: SQL_Aplied5.sql)

• orders, orderdetails 조인하여 매출 집계 베이스 구성함.

  • 주문상세 기준 매출 line_amount = priceEach * quantityOrdered 계산함.
  • orderNumber로 묶어서 주문별 매출(order_revenue) 계산함.
  • orderDate 기준으로 묶어서 일별 매출(daily_revenue) 집계함.
  • 위 흐름을 WITH CTE로 단계화하여 파이프라인 형태로 구성함.

• 최근 90일 조건 적용하여 최근 90일 일별 매출 추출함.

• SUM(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 적용하여 7일 롤링 매출(rolling_7d_revenue) 계산함.

→ “일별 매출 추이 + 7일 이동합” 같은 기본 매출 지표를 SQL로 계산하는 흐름을 구성/작성한 상태.

---

2) Ford 차량에 대한 마진·할인율 KPI 테이블

(관련 파일: SQL_Aplied4.sql)

• products, orders, orderdetails 조합하여 Ford 브랜드 상품만 필터링함.

• DATE_FORMAT(orderDate, '%Y-%m') 기준으로 월 단위 집계함.

• 월별 KPI를 CTE로 계산/구성함.

  • gross_sales : 총 매출 sum(qty * priceEach) 계산
  • buy_cost : 총 원가 sum(qty * buyPrice) 계산
  • gross_profit : 총 마진 sum(qty * (priceEach - buyPrice)) 계산
  • margin_rate : 마진율 gross_profit / gross_sales 계산(0 나눗셈 방지 포함)
  • discount_vs_msrp : MSRP 대비 할인율(가중 평균 형태) 계산
  • is_negative : 손실 여부 플래그(gross_profit < 0) 생성

→ 월별 마진율·할인율·손익 여부 KPI를 한 번에 산출하는 쿼리 작성/구성 경험 확보함.

---

3) 기간 지표(배송 지연/리드타임) 구간화 집계

(관련 파일: SQL_Aplied3.sql)

• orders에서 DATEDIFF(shippedDate, orderDate)로 배송 지연일(delay_days) 계산함.
• CASE WHEN으로 delay_bucket(D, D+1 … D+7 등) 형태로 구간화함.
• DATE_FORMAT(orderDate, '%Y-%m') 기준으로 월별/버킷별 COUNT() 집계 구성함.

→ 매출 외에도 운영/프로세스 KPI(리드타임)에서 기간 정의 + 구간화 + 집계 흐름 실습함.

---

2. 고객 단위 매출·세그먼트 분석

1) 고객별 매출 집계 & 상위 고객 세그먼트

(관련 파일: SQL_Practice5.sql)

• 주문상세 기반 매출을 고객(customerNumber) 기준으로 합산하여 cust_revenue 계산함.

• percent_rank() over(order by cust_revenue desc)로 매출 기준 상대 순위(백분위) 계산함.

• CASE WHEN으로 세그먼트 플래그 생성함.

  • 상위 20% → VVIP
  • 상위 80%까지 → VIP
  • 그 외 → 이탈 가능성 있음

→ 고객 매출 기여도 기반 VVIP/VIP 세그먼트 태깅 쿼리 작성함.

---

2) 윈도우 함수(순위·분포) 활용

(관련 파일: SQL_Aplied8.sql)

• payments에서 일자별 결제금액 집계 후, LAG/LEAD로 전일/익일 금액 비교 흐름 구성함.

• 고객별 결제금액 분포 분석을 위해 PERCENT_RANK, CUME_DIST 계산함.

  • PERCENT_RANK : 순위 인덱스 기반 상대 위치 확인
  • CUME_DIST : 값 기준 누적 분포(누적 비중) 확인

→ “상위 몇 % 고객인지”, “분포에서 어느 구간인지” 같은 분포 기반 지표 계산/해석 실습함.

---

3. 앱 로그/퍼널(전환율) 분석 실습

1) 이벤트 로그 테이블 설계 & 더미 데이터 생성

(관련 파일: SQL_Aplied6.sql)

• shop_events GA4 스타일 이벤트 로그 테이블 설계/생성 SQL 작성함.

  • event_id, user_id, session_id, event_name, event_time, event_properties(JSON) 구성

• device, country를 JSON에서 추출해 **생성 칼럼(GENERATED COLUMN)**으로 분리하고 인덱스 추가함.

  • JSON_VALID 체크 포함하여 JSON 무결성 고려함.

• WITH RECURSIVE로 세션 1~380 생성함.

• 세션별로 app_open / view_item / add_to_cart / purchase 이벤트를 확률 기반으로 생성하도록 구성함.

• 상품 정보(sku, qty, price)를 JSON_OBJECT/JSON_ARRAY로 event_properties에 포함시키는 로직 구현함.

→ “로그 조회”가 아니라 로그 스키마 설계 + 샘플 데이터 생성까지 SQL로 구현함.

---

2) JSON 컬럼에서 속성 추출 연습

(관련 파일: SQL_Aplied6.sql)

• JSON_EXTRACT, JSON_UNQUOTE로 event_properties에서 device, geo.country 추출/조회함.
• 특정 디바이스(iOS)만 필터링하는 조건 작성함.

→ JSON 기반 로그에서 속성 추출/필터링하는 기본 패턴 실습함.

---

3) 세션 단위 퍼널 분석 (앱 행동 흐름)

(관련 파일: SQL_Aplied7.sql)

• shop_events 기반으로 CTE를 여러 단계로 쌓아 퍼널 계산 구성함.

  1. session_events : 세션별 최초 app_open/view_item/add_to_cart/purchase 시간 계산함.
  2. session_flags : 단계 도달/정상 흐름 여부 플래그(has_open, valid_view, valid_cart, valid) 생성함.
  3. agg : 날짜별 세션 수와 단계별 세션 수 집계함.

• 단계별 전환율 계산함.

  • open_to_view_pct
  • view_to_cart_pct
  • cart_to_purchase_pct

• event_id 기준 / user_id 기준 등 집계 기준을 바꿔 퍼널 결과가 달라지는 점 비교함.

• DAU, 세션 수, 국가/디바이스 트래픽 등 대시보드 지표 개념도 함께 다룸.

→ 세션 단위 퍼널을 CTE로 설계하고 전환율까지 계산하는 흐름 작성함.

---

4) 데이터 품질·무결성 점검 쿼리

(관련 파일: SQL_Aplied7.sql)

• 분석 전 로그 신뢰도 점검 쿼리 작성함.

  • 전체 행 수, event_id/user_id/session_id distinct 개수 계산
  • event_id 중복 여부 점검
  • 한 세션에 user_id가 섞인 이상 세션 점검
  • 완전히 동일한 행 중복 삽입 케이스 점검

• 점검 항목을 CTE로 분리하여 한 번에 확인 가능하도록 구성함.

→ 분석뿐 아니라 로그 품질을 검증하는 관점까지 포함하여 실습함.

---

📌 요약: 이 레포에서 실습한 SQL 범위는

• 집계/분석 쿼리

  • 일별 매출, 7일 롤링 매출, 월별 마진율/할인율, 배송 지연(리드타임) 구간화, 고객별 매출·세그먼트

• 지표/퍼널

  • VVIP/VIP 세그먼트, 단계별 전환율, 세션 단위 퍼널, 집계 기준에 따른 퍼널 비교, DAU/트래픽 개념

• 고급 SQL 문법

  • WITH CTE, CASE WHEN, 윈도우 함수(LAG/LEAD/PERCENT_RANK/CUME_DIST), JSON 처리(JSON_EXTRACT/JSON_UNQUOTE/JSON_VALID), GENERATED COLUMN, 인덱스, WITH RECURSIVE

• 데이터 품질 관점

  • 중복/무결성 점검, 이상 세션 탐지, 로그 신뢰도 점검 흐름

---

📌 파일별 강조된 Point 모음

• WITH CTE 사용 시 계산 단계를 분리하여 읽기/수정/재사용 용이해짐 (관련 파일: SQL_Aplied5.sql, SQL_Aplied4.sql, SQL_Aplied7.sql)
• 전환율/비율 지표 산출 시 NULLIF(분모, 0) 또는 CASE WHEN 분모=0 THEN NULL로 0 나눗셈 방지 필요함 (관련 파일: SQL_Aplied4.sql, SQL_Aplied7.sql)
• PERCENT_RANK와 CUME_DIST는 계산 방식과 해석이 다름(순위 인덱스 기반 vs 누적분포 기반) (관련 파일: SQL_Aplied8.sql, SQL_Practice5.sql)
• 로그 분석은 집계 전에 품질 점검이 우선이며, 중복/이상 세션 검증이 분석 신뢰도를 좌우함 (관련 파일: SQL_Aplied7.sql)
• JSON 로그는 자주 조회하는 속성을 GENERATED COLUMN + INDEX로 분리하면 조회/성능 모두 유리함 (관련 파일: SQL_Aplied6.sql)

---