[Oracle] 레스토랑 신규 매출 분석

※ 250417 SKALA 참고사항

순서대로 sql 파일을 실행하면, 5.reservation.sql 단계에서 아래의 오류 3개가 발생합니다!

ORA-02291: 무결성 제약조건(SKALA.FK_ORDER_INFO_RESERV_NO)이 위배되었습니다- 부모 키가 없습니다

따라서 3개의 customer 더미 데이터를 추가하여 해결하였습니다

참고하시면 좋을 것 같습니다 (:

INSERT INTO customer VALUES ('W1333008','고객200','010-1111-1300','scust200@skala.ai',TO_DATE('17/11/01','RR/MM/DD'),'M','19900101','학생','12010');
INSERT INTO customer VALUES ('W1330024','고객201','010-1111-1301','scust201@skala.ai',TO_DATE('17/11/01','RR/MM/DD'),'M','19900101','학생','12010');
INSERT INTO customer VALUES ('W359004','고객202','010-1111-1302','scust202@skala.ai',TO_DATE('17/11/01','RR/MM/DD'),'M','19900101','학생','12010');

+ [ERDCloud용 import DDL 쿼리문]

CREATE TABLE `item` (
	`item_id`	VARCHAR2(10)	NOT NULL	COMMENT 'PK',
	`product_name`	VARCHAR2(30)	NULL,
	`product_desc`	VARCHAR2(50)	NULL,
	`category_id`	VARCHAR2(10)	NULL,
	`price`	NUMBER	NULL
);

CREATE TABLE `reservation` (
	`reserv_no`	VARCHAR2(30)	NOT NULL	COMMENT 'PK',
	`reserv_date`	VARCHAR2(8)	NULL,
	`reserv_time`	VARCHAR2(4)	NULL,
	`customer_id`	VARCHAR2(10)	NOT NULL	COMMENT 'FK',
	`branch`	VARCHAR2(20)	NULL,
	`visitor_cnt`	NUMBER	NULL,
	`cancel`	VARCHAR2(1)	NULL
);

CREATE TABLE `order_info` (
	`order_no`	VARCHAR2(30)	NOT NULL	COMMENT 'PK',
	`item_id`	VARCHAR2(10)	NOT NULL	COMMENT 'PK, FK',
	`reserv_no`	VARCHAR2(30)	NOT NULL	COMMENT 'FK',
	`quantity`	NUMBER	NULL,
	`sales`	NUMBER	NULL
);

CREATE TABLE `address` (
	`zip_code`	VARCHAR2(5)	NOT NULL	COMMENT 'PK',
	`address_detail`	VARCHAR2(20)	NULL
);

CREATE TABLE `customer` (
	`customer_id`	VARCHAR2(10)	NOT NULL	COMMENT 'PK',
	`customer_name`	VARCHAR2(20)	NULL,
	`phone_number`	VARCHAR2(15)	NULL,
	`email`	VARCHAR2(20)	NULL,
	`first_reg_date`	DATE	NULL,
	`gender_code`	VARCHAR2(2)	NULL,
	`birth`	VARCHAR2(8)	NULL,
	`job`	VARCHAR2(20)	NULL,
	`zip_code`	VARCHAR2(5)	NOT NULL	COMMENT 'FK'
);

ALTER TABLE `item` ADD CONSTRAINT `PK_ITEM` PRIMARY KEY (
	`item_id`
);

ALTER TABLE `reservation` ADD CONSTRAINT `PK_RESERVATION` PRIMARY KEY (
	`reserv_no`
);

ALTER TABLE `order_info` ADD CONSTRAINT `PK_ORDER_INFO` PRIMARY KEY (
	`order_no`,
	`item_id`
);

ALTER TABLE `address` ADD CONSTRAINT `PK_ADDRESS` PRIMARY KEY (
	`zip_code`
);

ALTER TABLE `customer` ADD CONSTRAINT `PK_CUSTOMER` PRIMARY KEY (
	`customer_id`
);

ALTER TABLE `reservation` ADD CONSTRAINT `FK_customer_TO_reservation_1` FOREIGN KEY (
	`customer_id`
)
REFERENCES `customer` (
	`customer_id`
);

ALTER TABLE `order_info` ADD CONSTRAINT `FK_item_TO_order_info_1` FOREIGN KEY (
	`item_id`
)
REFERENCES `item` (
	`item_id`
);

ALTER TABLE `order_info` ADD CONSTRAINT `FK_reservation_TO_order_info_1` FOREIGN KEY (
	`reserv_no`
)
REFERENCES `reservation` (
	`reserv_no`
);

ALTER TABLE `customer` ADD CONSTRAINT `FK_address_TO_customer_1` FOREIGN KEY (
	`zip_code`
)
REFERENCES `address` (
	`zip_code`
);

여기서 청록색(item_id) key는 UNIQUE 제약조건이 있는 컬럼을 참조하는 외래키를 의미한다!

[SQL#1] 전체 상품의 주문 완료 건수, 총 매출, 평균 매출, 최고 매출, 최저 매출 출력하기

SELECT 
    COUNT(DISTINCT order_no) AS 주문건수,     -- 고유 주문 번호 기준으로 주문 건수 집계
    SUM(sales)               AS 총매출,       -- 전체 주문 매출 합계
    ROUND(AVG(sales), 0)     AS 평균매출,     -- 주문당 평균 매출 (소수점 첫째 자리에서 반올림)
    MAX(sales)               AS 최고매출,     -- 한 건의 주문 중 최고 매출
    MIN(sales)               AS 최저매출      -- 한 건의 주문 중 최저 매출
FROM order_info;

(주의)

하나의 order_no가 여러 item_id와 함께 등장해서 한 주문 번호에 여러 품목이 담기는 구조
그래서 "주문 건수"를 집계할 때는 단순히 COUNT(*)가 아니라 고유한 주문 번호 기준으로 세야 한다!

따라서 DISTINCT 키워드를 사용해야 함

[SQL#2] 전체 상품의 총 판매량과 총 매출액, 전용 상품의 판매량과 매출액 출력하기

-- 전체 판매량, 전체 매출액
SELECT 
    SUM(quantity) AS 전체판매량,
    SUM(sales) AS 전체매출액
FROM order_info;

-- 전용 상품의 판매량, 매출액
SELECT
    SUM(oi.quantity) AS 전체_판매량,
    SUM(oi.sales) AS 전체_매출액,
    -- '온라인_전용상품' 조건을 만족하는 경우만 누적
    SUM(CASE WHEN i.product_desc = '온라인_전용상품' THEN oi.quantity ELSE 0 END) AS 전용_판매량,
    SUM(CASE WHEN i.product_desc = '온라인_전용상품' THEN oi.sales ELSE 0 END) AS 전용_매출액
FROM order_info oi
JOIN item i ON oi.item_id = i.item_id; -- 상품 설명 참조를 위해 item 테이블과 조인(전용 상품 매출/수량)

[SQL#3] 각 상품별 전체 매출액을 내림차순으로 출력하기

SELECT 
    i.product_name,
    SUM(o.sales) AS total_sales
FROM order_info o
JOIN item i ON o.item_id = i.item_id -- 주문 정보와 상품 정보를 item_id 기준으로 조인
GROUP BY i.product_name
ORDER BY total_sales DESC;

[SQL#4] 모든 상품의 월별 매출액 출력하기

월별로 구분: reservation.reserv_date → 'YYYYMM' 형태에서 MM 추출
상품별 매출액을 월 기준으로 묶기!
SUBSTR() 함수 활용 (Oracle)

SELECT 
    SUBSTR(r.reserv_date, 5, 2) AS sales_month,
    i.product_name,
    SUM(o.sales) AS monthly_sales
FROM order_info o
JOIN reservation r ON o.reserv_no = r.reserv_no
JOIN item i ON o.item_id = i.item_id
GROUP BY SUBSTR(r.reserv_date, 5, 2), i.product_name
ORDER BY sales_month, i.product_name;

[SQL#5] 월별 총 매출액과 전용 상품 매출액 출력 (SUBSTR, DECODE, SUM, GROUP BY)

-- 월별 총 매출액과 온라인 전용 상품 매출액 집계
SELECT
    SUBSTR(r.reserv_date, 1, 6) AS month, -- 예약일 기준 YYYYMM 추출
    SUM(oi.sales) AS total_sales,
    SUM(
        CASE 
            WHEN i.product_desc = '온라인_전용상품' THEN oi.sales
            ELSE 0
        END
    ) AS online_only_sales
FROM order_info oi
JOIN reservation r ON oi.reserv_no = r.reserv_no
JOIN item i ON oi.item_id = i.item_id
GROUP BY SUBSTR(r.reserv_date, 1, 6)
ORDER BY month;

[SQL#6] 5번 SQL문에 매출 기여율 추가 (기여율은 소수점 아래 두 번째 자리에서 반올림 후 출력, ROUND)

-- 월별 총 매출액, 전용 상품 매출액, 전용 상품 매출 기여율 (소수점 둘째 자리 반올림)
SELECT
    SUBSTR(r.reserv_date, 1, 6) AS month,
    SUM(oi.sales) AS total_sales,
    SUM(
        CASE 
            WHEN i.product_desc = '온라인_전용상품' THEN oi.sales
            ELSE 0
        END
    ) AS online_only_sales,
    ROUND(
        SUM(
            CASE 
                WHEN i.product_desc = '온라인_전용상품' THEN oi.sales
                ELSE 0
            END
        ) / SUM(oi.sales) * 100, 
        2
    ) AS contribution_rate -- 전용상품 매출 기여율 (%)
FROM order_info oi
JOIN reservation r ON oi.reserv_no = r.reserv_no
JOIN item i ON oi.item_id = i.item_id
GROUP BY SUBSTR(r.reserv_date, 1, 6)
ORDER BY month;

[SQL#7] 6번 SQL문에 총 예약 건수, 예약 취소 건수를 추가해서 출력하기

WITH 월별_총매출 AS (
    SELECT
        SUBSTR(r.reserv_date, 1, 6) AS 월,
        SUM(oi.sales) AS 총_매출액
    FROM order_info oi
    JOIN reservation r ON oi.reserv_no = r.reserv_no
    GROUP BY SUBSTR(r.reserv_date, 1, 6)
),
월별_전용매출 AS (
    SELECT
        SUBSTR(r.reserv_date, 1, 6) AS 월,
        SUM(oi.sales) AS 전용_매출액
    FROM order_info oi
    JOIN reservation r ON oi.reserv_no = r.reserv_no
    JOIN item i ON oi.item_id = i.item_id
    WHERE i.product_desc = '온라인_전용상품'
    GROUP BY SUBSTR(r.reserv_date, 1, 6)
),
월별_예약건수 AS (
    SELECT
        SUBSTR(r.reserv_date, 1, 6) AS 월,
        COUNT(DISTINCT r.reserv_no) AS 총_예약건수,
        SUM(CASE WHEN r.cancel = 'Y' THEN 1 ELSE 0 END) AS 취소_건수
    FROM reservation r
    GROUP BY SUBSTR(r.reserv_date, 1, 6)
)

SELECT
    t.월,
    t.총_매출액,
    NVL(d.전용_매출액, 0) AS 전용_매출액,
    ROUND(NVL(d.전용_매출액, 0) * 100 / t.총_매출액, 1) AS 매출_기여율,
    c.총_예약건수,
    c.취소_건수
FROM 월별_총매출 t
LEFT JOIN 월별_전용매출 d ON t.월 = d.월
LEFT JOIN 월별_예약건수 c ON t.월 = c.월
ORDER BY t.월;

[SQL#8] 7번 SQL문에 총 매출 대비 전용 상품의 판매율, 총 예약 건 대비 예약 취소율을 추가해서 출력하기 여기에서 소수점이 나올 경우 소수점 아래 두 번째 자리에서 반올림하여 0.00% 형식으로 출력(ROUND, ||)

WITH 월별_집계 AS (
    SELECT
        SUBSTR(r.reserv_date, 1, 6) AS 월,
        SUM(oi.sales) AS 총_매출액,
        SUM(CASE WHEN i.product_desc = '온라인_전용상품'
                 THEN oi.sales ELSE 0 END) AS 전용_매출액,
        COUNT(DISTINCT r.reserv_no) AS 총_예약건수,
        SUM(CASE WHEN r.cancel = 'Y' THEN 1 ELSE 0 END) AS 취소_건수
    FROM reservation r
    LEFT JOIN order_info oi ON oi.reserv_no = r.reserv_no
    LEFT JOIN item i ON oi.item_id = i.item_id
    GROUP BY SUBSTR(r.reserv_date, 1, 6)
)
SELECT
    월,
    총_매출액,
    전용_매출액,
    TO_CHAR(
        ROUND(전용_매출액 * 100 / 총_매출액, 1), '00.0'
    ) || '%' AS 전용_판매율,
    총_예약건수,
    취소_건수,
    TO_CHAR(
        ROUND(취소_건수 * 100 / 총_예약건수, 1), '00.0'
    ) || '%' AS 예약_취소율
FROM 월별_집계
ORDER BY 월;

[SQL#9] 월별 전용 상품 매출액을 일요일부터 토요일까지 구분해서 출력하기 (IN-LINE VIEW, TO_CHAR, TO_DATE)

SELECT
    월,
    요일,
    SUM(전용_매출액) AS 전용_매출액
FROM (
    SELECT
        SUBSTR(r.reserv_date, 1, 6) AS 월,
        CASE TO_CHAR(TO_DATE(r.reserv_date, 'YYYYMMDD'), 'D')
            WHEN '1' THEN '일요일'
            WHEN '2' THEN '월요일'
            WHEN '3' THEN '화요일'
            WHEN '4' THEN '수요일'
            WHEN '5' THEN '목요일'
            WHEN '6' THEN '금요일'
            WHEN '7' THEN '토요일'
        END AS 요일,
        oi.sales AS 전용_매출액
    FROM order_info oi
    JOIN reservation r ON oi.reserv_no = r.reserv_no
    JOIN item i ON oi.item_id = i.item_id
    WHERE i.product_desc = '온라인_전용상품'
)
GROUP BY 월, 요일
ORDER BY 월, 
    CASE 요일
        WHEN '일요일' THEN 1
        WHEN '월요일' THEN 2
        WHEN '화요일' THEN 3
        WHEN '수요일' THEN 4
        WHEN '목요일' THEN 5
        WHEN '금요일' THEN 6
        WHEN '토요일' THEN 7
    END;

[SQL#10] 월별 전용 상품 매출 1위부터 3위까지 지점이 어디인지 확인하기 (RANK, PARTITION BY)

SELECT
  월,
  지점,
  전용_매출액,
  순위
FROM (
  SELECT
    SUBSTR(r.reserv_date, 1, 6) AS 월,
    r.branch AS 지점,
    SUM(oi.sales) AS 전용_매출액,
    RANK() OVER (
      PARTITION BY SUBSTR(r.reserv_date, 1, 6)
      ORDER BY SUM(oi.sales) DESC
    ) AS 순위
  FROM order_info oi
  JOIN reservation r ON oi.reserv_no = r.reserv_no
  JOIN item i ON oi.item_id = i.item_id
  WHERE i.product_desc = '온라인_전용상품'
  GROUP BY SUBSTR(r.reserv_date, 1, 6), r.branch
)
WHERE 순위 <= 3
ORDER BY 월, 순위;

[SQL#11] 8번 SQL 결과와 10번의 결과 항목을 월별로 합쳐서 리포트 만들기 (UNION, MAX)

-- 월별 요약 + TOP3 지점(순위·판매건수) 리포트
WITH 월별_요약 AS (
  SELECT
    SUBSTR(r.reserv_date, 1, 6) AS 월,
    SUM(oi.sales) AS 총_매출액,
    TO_CHAR(
      ROUND(
        SUM(CASE WHEN i.product_desc = '온라인_전용상품' THEN oi.sales ELSE 0 END) * 100
        / SUM(oi.sales), 1
      ), '00.0'
    ) || '%' AS 전용_판매율,
    TO_CHAR(
      ROUND(
        SUM(CASE WHEN r.cancel = 'Y' THEN 1 ELSE 0 END) * 100
        / COUNT(DISTINCT r.reserv_no), 1
      ), '00.0'
    ) || '%' AS 예약_취소율
  FROM reservation r
  LEFT JOIN order_info oi ON oi.reserv_no = r.reserv_no
  LEFT JOIN item        i  ON oi.item_id   = i.item_id
  GROUP BY SUBSTR(r.reserv_date, 1, 6)
),
월별_TOP3 AS (
  SELECT
    TO_CHAR(TO_DATE(r.reserv_date, 'YYYYMMDD'), 'YYYYMM') AS 월,
    r.branch AS 지점,
    COUNT(*) AS 판매건수,
    RANK() OVER (
      PARTITION BY TO_CHAR(TO_DATE(r.reserv_date, 'YYYYMMDD'), 'YYYYMM')
      ORDER BY COUNT(*) DESC
    ) AS 순위
  FROM order_info oi
  JOIN reservation r ON oi.reserv_no = r.reserv_no
  JOIN item        i ON oi.item_id   = i.item_id
  WHERE i.product_desc = '온라인_전용상품'
  GROUP BY
    TO_CHAR(TO_DATE(r.reserv_date, 'YYYYMMDD'), 'YYYYMM'),
    r.branch
)
SELECT
  y.월,
  y.총_매출액,
  y.전용_판매율,
  y.예약_취소율,
  t.지점,
  t.순위,
  t.판매건수
FROM 월별_요약 y
JOIN (
  SELECT * FROM 월별_TOP3 WHERE 순위 <= 3
) t ON y.월 = t.월
ORDER BY y.월, t.순위;

#SK, #SKALA, #SKALA1기

저작자표시 비영리 변경금지 (새창열림)

'DBMS > Oracle' 카테고리의 다른 글

[Oracle] 온라인 쇼핑몰 DB: JOIN/INDEX 실습 (1)	2025.04.16
[Oracle] 기업 탄소 배출 데이터를 활용한 데이터 분석(Data Warehouse → Data Mining 흐름) (4)	2025.04.15
[Oracle] 맥북(m3칩↑)에서 오라클 SQL Developer 사용법(w/도커) (0)	2025.04.15

Go for IT

[Oracle] 레스토랑 신규 매출 분석

[SQL#1] 전체 상품의 주문 완료 건수, 총 매출, 평균 매출, 최고 매출, 최저 매출 출력하기

[SQL#2] 전체 상품의 총 판매량과 총 매출액, 전용 상품의 판매량과 매출액 출력하기

[SQL#3] 각 상품별 전체 매출액을 내림차순으로 출력하기

[SQL#4] 모든 상품의 월별 매출액 출력하기

[SQL#5] 월별 총 매출액과 전용 상품 매출액 출력 (SUBSTR, DECODE, SUM, GROUP BY)

[SQL#6] 5번 SQL문에 매출 기여율 추가 (기여율은 소수점 아래 두 번째 자리에서 반올림 후 출력, ROUND)

[SQL#7] 6번 SQL문에 총 예약 건수, 예약 취소 건수를 추가해서 출력하기

[SQL#8] 7번 SQL문에 총 매출 대비 전용 상품의 판매율, 총 예약 건 대비 예약 취소율을 추가해서 출력하기 여기에서 소수점이 나올 경우 소수점 아래 두 번째 자리에서 반올림하여 0.00% 형식으로 출력(ROUND, ||)

[SQL#9] 월별 전용 상품 매출액을 일요일부터 토요일까지 구분해서 출력하기 (IN-LINE VIEW, TO_CHAR, TO_DATE)

[SQL#10] 월별 전용 상품 매출 1위부터 3위까지 지점이 어디인지 확인하기 (RANK, PARTITION BY)

[SQL#11] 8번 SQL 결과와 10번의 결과 항목을 월별로 합쳐서 리포트 만들기 (UNION, MAX)

'DBMS > Oracle' 카테고리의 다른 글

티스토리툴바

[Oracle] 레스토랑 신규 매출 분석

[SQL#1] 전체 상품의 주문 완료 건수, 총 매출, 평균 매출, 최고 매출, 최저 매출 출력하기

[SQL#2] 전체 상품의 총 판매량과 총 매출액, 전용 상품의 판매량과 매출액 출력하기

[SQL#3] 각 상품별 전체 매출액을 내림차순으로 출력하기

[SQL#4] 모든 상품의 월별 매출액 출력하기

[SQL#5] 월별 총 매출액과 전용 상품 매출액 출력 (SUBSTR, DECODE, SUM, GROUP BY)

[SQL#6] 5번 SQL문에 매출 기여율 추가 (기여율은 소수점 아래 두 번째 자리에서 반올림 후 출력, ROUND)

[SQL#7] 6번 SQL문에 총 예약 건수, 예약 취소 건수를 추가해서 출력하기

[SQL#8] 7번 SQL문에 총 매출 대비 전용 상품의 판매율, 총 예약 건 대비 예약 취소율을 추가해서 출력하기 여기에서 소수점이 나올 경우 소수점 아래 두 번째 자리에서 반올림하여 0.00% 형식으로 출력(ROUND, ||)

[SQL#9] 월별 전용 상품 매출액을 일요일부터 토요일까지 구분해서 출력하기 (IN-LINE VIEW, TO_CHAR, TO_DATE)

[SQL#10] 월별 전용 상품 매출 1위부터 3위까지 지점이 어디인지 확인하기 (RANK, PARTITION BY)

[SQL#11] 8번 SQL 결과와 10번의 결과 항목을 월별로 합쳐서 리포트 만들기 (UNION, MAX)

'DBMS > Oracle' 카테고리의 다른 글

관련글

티스토리툴바