SQL (재귀 쿼리, LEFT JOIN, LAG, COALESCE ) - 이틀치 누적 출고량 시뮬레이션

문제

• 물류팀은 악천후로 인해 하루 동안 출고 처리가 중단될 경우, 해당 물량이 다음 날로 이월될 수 있다고 가정하고 있습니다. 이에 따라 특정 기간 동안 연속된 이틀간 처리해야 할 출고량을 계산해, 물류 센터의 최대 처리 부담을 사전에 파악하려고 합니다.
• shipments 테이블에는 다양한 주문 채널과 상태의 출고 기록이 저장되어 있습니다. 이 중 실제 배송이 필요한 온라인 주문만을 대상으로, 2024년 2월 한 달간 일자별 출고량을 집계하려고 합니다.
• 매장 방문 주문이나 배송이 필요 없는 주문, 그리고 출고가 완료되지 않은 건은 분석 대상에서 제외합니다.
• 출력 결과에는 해당 기간 내 모든 날짜가 빠짐없이 포함되어야 하며, 출고 기록이 없는 날짜의 경우 출고 건수는 0으로 표시되어야 합니다. 각 날짜에 대해 당일 출고 건수와 함께, 해당 날짜와 바로 전날을 포함한 연속된 이틀간의 출고 건수 합계를 계산해 주세요.

테이블: shipments

shipment_id	BIGINT	출고(발송) 고유 ID
shipped_at	DATETIME	출고 처리 시간
channel	VARCHAR	주문 채널 ('web', 'app', 'store')
requires_delivery	TINYINT	배송 필요 여부 (1=배송 필요, 0=매장 픽업 등)
status	VARCHAR	상태 ('shipped', 'canceled', 'returned')

출력 요구사항

• ship_date: 날짜(YYYY-MM-DD)
• weekday: 요일의 영문 전체 이름 (Sunday, Monday, …, Saturday)
• num_shipments_today: 해당 날짜의 출고 건수
• num_shipments_2day_total: 전날과 당일을 합산한 출고 건수

정렬 기준

1. • ship_date 기준 오름차순

 

목표/풀이

• 목표: 연속된 이틀간 처리해야 할 출고량
  
  1. 조건1: 24년 2월 한달간 일자별 출고량
     • WHERE ship_date >= '2024-02-01' AND ship_date < '2024-03-01'
  2. 조건2: 매장 방문 주문이나 배송이 필요 없는 주문, 그리고 출고가 완료되지 않은 건은 분석 대상에서 제외
     • (= 웹이나 모바일 주문건, 배송 필요한 주문건, 출고 완료된 주문건)
  3. 조건3: 모든 날짜가 빠짐없이 포함되어야 하며, 출고 기록이 없는 날짜의 경우 출고 건수는 0
  4. 정렬: ship_date 오름차순

코드

WITH RECURSIVE calendar AS (
	SELECT DATE('2024-02-01') AS ship_date
	
	UNION ALL 
	
	SELECT DATE_ADD(ship_date, INTERVAL 1 DAY)
	FROM calendar 
	WHERE ship_date < '2024-02-29'
),
daily AS (
	SELECT DATE(shipped_at) AS ship_date,
    		COUNT(*) AS num_shipments_today
	FROM shipments 
	WHERE shipped_at >= '2024-02-01' 
	AND   shipped_at < '2024-03-01'
	AND   channel IN ('web', 'app') 
	AND   requires_delivery = 1 
	AND   status = 'shipped'
	GROUP BY DATE(shipped_at) 
	),
filled AS (
	SELECT c.ship_date,
    		COALESCE(d.num_shipments_today, 0) AS num_shipments_today 
	FROM calendar c 
	LEFT JOIN daily d 
		ON c.ship_date = d.ship_date
)
  SELECT ship_date,
  		DAYNAME(ship_date) AS weekday,
        num_shipments_today,
        num_shipments_today 
          + COALESCE(LAG(num_shipments_today) OVER(ORDER BY ship_date), 0) AS num_shipments_2day_total
FROM filled
ORDER BY ship_date;

 

주의사항

1. 달력 기준 연속 2일 집계는 캘린더가 필요
   • 출고가 없는 날짜도 결과에 포함되어야 하므로 날짜 시퀀스(캘린더)를 만든다.
   • 캘린더에 일별 출고 집계 결과를 LEFT JOIN으로 붙이고, 없는 날은 0으로 채운다.
2. 재귀 CTE로 캘린더 만들기(구조)
   • Anchor: 최초 1행 생성
   • Recursive: 방금 생성된 결과를 입력으로 다시 실행
   • WHERE 조건이 FALSE가 되면 반복 종료
   • UNION ALL을 사용해 테이블 결합
3. 연속된 이틀 합계(전날+당일) 계
   1. num_shipments_2day_total = 오늘 출고건수 + 전날 출고건수
   2. 전날 출고건수는 LAG(num_shipments_today)로 가져온다.
   3. 첫 날짜는 전날 행이 없어 LAG() 결과가 NULL이므로, COALESCE(..., 0)로 0 처리한다.