[코드리뷰]조인 형태에 따른 SQL 실행

ww_ung·2025년 4월 20일
JOINSKSKALASKALA1기sql

SKALA

목록 보기
23/25


패밀리 레스토랑 신규 매출을 분석하기 위한 Query문으로
주문, 예약 데이터 분석을 통해 기본적인 집계함수, 조건벌 집계, 날짜 파싱, 조인 등
SQL문에 대해 이해하기 쉬운 예제들로 구성되어 있다.

사전 지식

JOIN

  • 두 개 이상의 테이블을 특정 조건을 기준으로 연결하는 연산

GROUP BY

  • 특정 컬럼 단위로 그룹화해서 집계 수행

집계함수 (SUM, AVG, COUNT, MAX, MIN)

  • 그룹 혹은 전체 행에 대해 계산을 수행

DECODE / CASE WHEN

  • 조건문 로직을 SQL에서 처리할 수 있게 함

SUBSTR()

  • 문자열 자르기. 날짜에서 월만 추출할 때 주로 사용

TO_CHAR, TO_DATE

  • 날짜 포맷을 변경하거나 파싱할 때 사용

RANK() OVER(PARTITION BY ... ORDER BY ...)

  • 그룹별 순위를 매기는 윈도우 함수

UNION

  • 두 쿼리의 결과를 합치는 집합 연산자

ROUND()

  • 반올림 함수

🔍 [SQL#1] 전체 주문 건수, 총매출, 평균/최고/최저 매출

SELECT COUNT(*)             AS 주문건수,
       SUM(B.sales)         AS 총매출,
       ROUND(AVG(B.sales))  AS 평균매출,
       MAX(B.sales)         AS 최고매출,
       MIN(B.sales)         AS 최저매출
FROM reservation A
JOIN order_info B ON A.reserv_no = B.reserv_no;

INNER JOIN
-> 예약 테이블과 주분 테이블을 예약 번호를 기준으로 조인합니다
COUNT(*)
-> 총 주문 건수를 계산합니다
SUM(B.sales)
-> 주문 매출 합계
ROUND(AVG(B.sales)
-> 평균 매출을 반올림해서 출력
MAX(B.sales) / MIN(B.sales)
-> 가장 높은 매출, 가장 낮은 매출

🔍 [SQL#2] 전체 판매건수, 전용상품(M0001) 집계

SELECT COUNT(*) AS 전체판매건수,
       ROUND(SUM(B.sales), 2) AS 총매출액,
       SUM(CASE WHEN B.item_id = 'M0001' THEN 1 ELSE 0 END) AS 전용상품판매건수,
       ROUND(SUM(CASE WHEN B.item_id = 'M0001' THEN B.sales ELSE 0 END), 2) AS 전용상품매출
FROM reservation A
JOIN order_info B ON A.reserv_no = B.reserv_no
WHERE A.cancel = 'N';

CASE WHEN
-> 특정 조건을 만족할 때만 값을 계산
SUM(CASE WHEN ... THEN 1 ...)
-> 전용 상품 판매 횟수 (M0001)
SUM(CASE WHEN ... THEN B.sales ...)
-> 전용 상품 매출
ROUND(..., 2)
-> 소수점 둘째 자리까지 반올림
WHERE A.cancel = 'N'
-> 예약취소 건은 포함시키지 않아야 하니

🔍 [SQL#3] 상품별 매출 내림차순 정렬

SELECT C.item_id,
       C.product_name,
       SUM(B.sales) AS 상품매출
FROM reservation A
JOIN order_info B ON A.reserv_no = B.reserv_no
JOIN item C ON B.item_id = C.item_id
WHERE A.cancel = 'N'
GROUP BY C.item_id, C.product_name
ORDER BY 상품매출 DESC;

3개 테이블 조인
-> 예약 → 주문 → 상품
GROUP BY
-> 상품 ID, 이름으로 묶어 집계
ORDER BY ... DESC
-> 매출이 높은 순으로 정렬

🔍 [SQL#4] 월별 상품 매출 집계

# 기존 코드
SELECT SUBSTR(A.reserv_date,1,6) AS 월,
       SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS SPECIAL_SET,
       SUM(DECODE(B.item_id,'M0002',B.sales,0)) AS PASTA,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS PIZZA,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS SEA_FOOD,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS STEAK,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS SALAD_BAR,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS SALAD,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS SANDWICH,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS WINE,
       SUM(DECODE(B.item_id,'M0003',B.sales,0)) AS JUICE
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND   A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

SUBSTR(A.reserv_date,1,6)
-> YYYYMM 형태로 월 추출
DECODE(item_id, 'M0001', sales, 0)
-> item_id가 M0001일 경우에만 매출을 더함
GROUP BY 월
-> 월별로 집계

🔍 [SQL#5] 월별 총매출과 전용상품 매출

SELECT SUBSTR(A.reserv_date,1,6) AS 월,
       SUM(B.sales) AS 총매출,
       SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 전용상품매출
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND   A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

SUBSTR(A.reserv_date,1,6) AS 월
-> 예약일자에서 연월(YYYYMM) 형태 추출 (예: 202501)
SUM(B.sales)
-> 월 전체 매출 합계
SUM(DECODE(B.item_id,'M0001',B.sales,0))
-> 전용 상품('M0001')에 해당하는 매출만 합산
WHERE A.cancel = 'N'
-> 취소되지 않은 예약만 집계
GROUP BY / ORDER BY SUBSTR(...)
-> 연월 기준으로 그룹화하고 오름차순 정렬

🔍 [SQL#6] 전용상품 매출 기여율 추가

SELECT SUBSTR(A.reserv_date,1,6) AS 월,
       SUM(B.sales) - SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 기타매출,
       SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 전용매출,
       ROUND(SUM(DECODE(B.item_id,'M0001',B.sales,0))/SUM(B.sales)*100,1) AS 기여율
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no
AND   A.cancel = 'N'
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

SUM(B.sales) - SUM(DECODE(...))
-> 기타 상품 매출 = 총매출 - 전용상품 매출
ROUND(...,1)
-> 기여율을 소수 첫째 자리까지 반올림

🔍 [SQL#7] 예약/취소 건수 포함 월별 매출 요약

SELECT SUBSTR(A.reserv_date,1,6) AS 월,
       SUM(B.sales) AS 총매출,
       SUM(B.sales) - SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 기타상품매출,
       SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 전용상품매출,
       ROUND(SUM(DECODE(B.item_id,'M0001',B.sales,0))/SUM(B.sales)*100,1) AS 기여율,
       COUNT(A.reserv_no) AS 예약건수,
       SUM(DECODE(A.cancel,'N',1,0)) AS 완료건,
       SUM(DECODE(A.cancel,'Y',1,0)) AS 취소건
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no(+)
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

A.reserv_no = B.reserv_no(+)
-> LEFT OUTER JOIN: 예약은 존재하지만 주문이 없을 수도 있음 (예: 예약만 했다가 취소된 경우 포함)
SUM(DECODE(A.cancel, 'N', 1, 0))
→ 예약 상태(N/Y)에 따라 완료건, 취소건 카운팅

🔍 [SQL#8] 퍼센트 표시 추가 (기여율 + 취소율)

SELECT SUBSTR(A.reserv_date,1,6) AS 월,
       SUM(B.sales) AS 총매출,
       SUM(B.sales) - SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 기타상품매출,
       SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 전용상품매출,
       ROUND(SUM(DECODE(B.item_id,'M0001',B.sales,0))/SUM(B.sales)*100,1)||'%' AS 전용상품기여율,
       COUNT(A.reserv_no) AS 예약건수,
       SUM(DECODE(A.cancel,'N',1,0)) AS 완료건,
       SUM(DECODE(A.cancel,'Y',1,0)) AS 취소건,
       ROUND(SUM(DECODE(A.cancel,'Y',1,0))/COUNT(A.reserv_no)*100,1)||'%' AS 취소율
FROM reservation A, order_info B
WHERE A.reserv_no = B.reserv_no(+)
GROUP BY SUBSTR(A.reserv_date,1,6)
ORDER BY SUBSTR(A.reserv_date,1,6);

% 기호를 붙이기 위해 || '%' 문자열 연결 사용

🔍 [SQL#9] 월별 전용상품 매출을 요일별로 출력

SELECT SUBSTR(reserv_date,1,6) AS 월,
       A.product_name AS 상품명,
       SUM(DECODE(A.WEEK,'1',A.sales,0)) AS 일,
       SUM(DECODE(A.WEEK,'2',A.sales,0)) AS 월,
       SUM(DECODE(A.WEEK,'3',A.sales,0)) AS 화,
       SUM(DECODE(A.WEEK,'4',A.sales,0)) AS 수,
       SUM(DECODE(A.WEEK,'5',A.sales,0)) AS 목,
       SUM(DECODE(A.WEEK,'6',A.sales,0)) AS 금,
       SUM(DECODE(A.WEEK,'7',A.sales,0)) AS 토
FROM (
    SELECT A.reserv_date,
           C.product_name,
           TO_CHAR(TO_DATE(A.reserv_date, 'YYYYMMDD'),'D') AS WEEK,
           B.sales
    FROM reservation A, order_info B, item C
    WHERE A.reserv_no = B.reserv_no
    AND   B.item_id = C.item_id
    AND   B.item_id = 'M0001'
) A
GROUP BY SUBSTR(reserv_date,1,6), A.product_name
ORDER BY SUBSTR(reserv_date,1,6);

서브쿼리 내부
TO_DATE(A.reserv_date, 'YYYYMMDD')
-> 문자열 날짜 → 날짜 형식 변환
TO_CHAR(..., 'D')
-> 요일 숫자 추출 ('1': 일요일, '7': 토요일 / DB 설정에 따라 다름)
'M0001'
-> 전용상품만 필터링
상품명, 매출, 요일 추출

바깥 쿼리
SUBSTR(reserv_date, 1, 6)
-> 연월 추출
DECODE(WEEK, '1', ..., '2', ..., ...)
-> 요일별 매출 분리 집계
GROUP BY 월, 상품명

🔍 [SQL#10] 월별 전용상품 매출 1~3위 지점 출력

SELECT *
FROM (
    SELECT SUBSTR(A.reserv_date,1,6) AS 월,
           A.branch AS 지점,
           SUM(B.sales) AS 전용상품매출,
           RANK() OVER(
               PARTITION BY SUBSTR(A.reserv_date,1,6)
               ORDER BY SUM(B.sales) DESC
           ) AS 순위
    FROM reservation A, order_info B
    WHERE A.reserv_no = B.reserv_no
      AND A.cancel = 'N'
      AND B.item_id = 'M0001'
    GROUP BY SUBSTR(A.reserv_date,1,6), A.branch
)
WHERE 순위 <= 3;

RANK() OVER(PARTITION BY 월 ORDER BY 매출 DESC)
→ 월별 지점 매출 순위 계산
PARTITION BY SUBSTR(A.reserv_date,1,6)
→ 각 월별로 RANK 따로 계산
WHERE 순위 <= 3
→ 1~3위만 필터링

🔍 [SQL#11] 월별 요약 + 전용상품 1위 지점 리포트

SELECT A.월, MAX(총매출), MAX(전용매출), MAX(기여율), MAX(예약건수),
       MAX(완료건수), MAX(취소건수), MAX(취소율), MAX(상위지점), MAX(지점매출)
FROM (
    SELECT SUBSTR(A.reserv_date,1,6) AS 월,
           SUM(B.sales) AS 총매출,
           SUM(DECODE(B.item_id,'M0001',B.sales,0)) AS 전용매출,
           ROUND(SUM(DECODE(B.item_id,'M0001',B.sales,0))/SUM(B.sales)*100,1)||'%' AS 기여율,
           COUNT(A.reserv_no) AS 예약건수,
           SUM(DECODE(A.cancel,'N',1,0)) AS 완료건수,
           SUM(DECODE(A.cancel,'Y',1,0)) AS 취소건수,
           ROUND(SUM(DECODE(A.cancel,'Y',1,0))/COUNT(A.reserv_no)*100,1)||'%' AS 취소율,
           '' AS 상위지점,
           0 AS 지점매출
    FROM reservation A, order_info B
    WHERE A.reserv_no = B.reserv_no(+)
    GROUP BY SUBSTR(A.reserv_date,1,6)
UNION
    SELECT 월, 0, 0, '', 0, 0, 0, '', A.branch, A.지점매출
    FROM (
        SELECT SUBSTR(A.reserv_date,1,6) AS 월,
               A.branch,
               SUM(B.sales) AS 지점매출,
               ROW_NUMBER() OVER(
                   PARTITION BY SUBSTR(A.reserv_date,1,6)
                   ORDER BY SUM(B.sales) DESC
               ) AS 순위
        FROM reservation A, order_info B
        WHERE A.reserv_no = B.reserv_no
          AND A.cancel = 'N'
          AND B.item_id = 'M0001'
        GROUP BY SUBSTR(A.reserv_date,1,6), A.branch
    ) A
    WHERE A.순위 = 1
) A
GROUP BY A.월
ORDER BY A.월;

두 가지 데이터를 UNION으로 합친 후, 월별로 MAX()를 통해 통합 출력
1. 매출 요약 정보 (총매출, 예약건수 등)
2. 전용상품 매출 1위 지점과 매출

MAX()는 UNION 결과를 월별 하나로 합치기 위해 사용된 집계 함수

profile
ww_ung
이전 포스트

MSA에서 Kafka, Elasticsearch, Redis 쉽게 이해하기

다음 포스트

에이전트 시대의 시작: MCP와 A2A가 바꿀 비즈니스

0개의 댓글