SQL 데이터 분석 : 2.5.2 실전쿼리 / 종합 쿼리

yeppi1802·2024년 7월 18일
SQL데이터분석제로베이스

SQL 데이터 분석_제로베이스 데이터 분석 스쿨

목록 보기
14/37

📖 실전 쿼리1

🔆 매출 지표(주문 건수, 건당 주문 금액)

🔆 관련 테이블

  • 참고, 데이터가 브라질 이커머스에서 제공하여 돈 단위는 브라질 헤알

/* 기본 주문 정보 (주문 수, 총 금액 ) */
SELECT
	COUNT(DISTINCT order_id) AS unique_orders, -- 전체 주문 건수
	SUM(price) AS ord_amt, -- 주문 금액 총합
FROM `olist.olist_order_items`;
/* 기본 주문 정보 (주문 수, 총 금액 ) */
SELECT
	COUNT(DISTINCT order_id) AS unique_orders, -- 전체 주문 건수
	SUM(price) AS ord_amt, -- 주문 금액 총합
	SUM(price) / COUNT(DISTINCT order_id) AS amount_per_order -- 주문 금액 총합 / 주문 건수 
FROM `olist.olist_order_items`;

🔆 테이블 사용 시 유의 사항

  • Customer_id는 매 주문건마다 새로 생성됨
  • order_item_id는 주문건 내 동일 상품이 있는 경우 증가함

  • 주문정보 - 고객 번호 유의 사항

    • 주문 테이블 내 주문건수와 고객번호수는 일치한다

    • customer_id가 달라도 같은 고객일 수 있다

    • 고객정보 테이블에 customer_unique_id가 있다

  • 주문 상품 정보 - 품목 수(동일제품의 구매번호) 유의 사항

    • order_item_id를 모두 더하면 안된다!

☁️ olist_order_items 테이블에서 쿼리 선택 → 새탭에서 열기 선택

  • 해당 테이블을 이용하여 바로 쿼리 작성 가능

☁️ FROM절 부터 쓰면 SELECT문 쓸때 컬럼들이 자동완성

  • 이때 백틱 사용( ` ), 콤마( ‘ ) 아님!
SELECT
FROM `olist.olist_order_items`

☁️ 쿼리 작성

SELECT
	SUM(price) AS ord_amt, -- 총매출 
	COUNT(DISTINCT order_id) AS ord_cnt, -- 전체 주문 건수
FROM `olist.olist_order_items`;
  • 결과

SELECT
	SUM(price) AS ord_amt, -- 총매출 
	COUNT(DISTINCT order_id) AS ord_cnt, -- 전체 주문 건수
	SUM(price) / COUNT(DISTINCT order_id) AS amount_per_order -- 주문 1건당 평균 가격
FROM `olist.olist_order_items`;
  • 결과

📖 실전 쿼리 2

🔆 주문 수 지표 (고객 수, 주문빈도)

🔆 관련 테이블

  • 주의, customer_id는 매 주문 건마다 생성! 고객의 고유 고객번호가 아님!

🔆 테이블 조인하기

/* 테이블 조인하기 */
SELECT
	ord.order_id,
	ord.customer_id,
	cust.customer_unique_id
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
 ON ord.customer_id = cust.customer_id;
/* 주문 고객 unique 세기 */
SELECT
	COUNT(DISTINCT ord.order_id) AS ord_cnt, -- 주문건수 세기
	COUNT(DISTINCT ord.customer_id) AS cust_cnt, -- 참고. 주문건의 고객id 세기(주문건수와 같음)
	COUNT(DISTINCT cust.customer_unique_id) AS unique_cust_cnt-- 고객 고유 id 세기
FROM `olist.olist_orders` AS ord -- 주문정보 테이블
LEFT JOIN `olist.olist_customers` AS cust -- 고객정보 테이블
 ON ord.customer_id = cust.customer_id; -- join 조건 : 주문 건의 고객 id
/* 주문빈도까지 조회 */
SELECT
 COUNT(DISTINCT ord.order_id) AS ord_cnt, -- 주문건수 세기
 COUNT(DISTINCT cust.customer_unique_id) AS unique_cust_cnt, -- 고객 고유 id 세기
 COUNT(DISTINCT ord.order_id) / COUNT(DISTINCT cust.customer_unique_id) AS frequency -- 주문빈도
FROM `olist.olist_orders` AS ord -- 주문정보 테이블
LEFT JOIN `olist.olist_customers` AS cust -- 고객정보 테이블
 ON ord.customer_id = cust.customer_id; -- join 조건 : 주문 건의 고객 id

☁️ 테이블 조인

SELECT
	ord.order_id,
	ord.customer_id,
	cust.customer_unique_id
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
 ON ord.customer_id = cust.customer_id;
  • 결과

  • 한개의 주문건에는 한개의 행만 존재한다.
    • 테이블에 중복값이 없다! 확인 가능
SELECT 
  COUNT(order_id),
  COUNT(DISTINCT order_id)
FROM `olist.olist_orders` AS ord

☁️ 주문 고객 unique 세기

SELECT
	COUNT(DISTINCT ord.order_id) AS ord_cnt, -- 주문건수 세기
	COUNT(DISTINCT ord.customer_id) AS cust_cnt, -- 고객 id 세기
	COUNT(DISTINCT cust.customer_unique_id) AS unique_cust_cnt -- 고객 고유 id 세기
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
 ON ord.customer_id = cust.customer_id;
  • 결과

☁️ 주문 빈도 조회

  • 백틱( ` )으로 별칭 한글 지정 가능
SELECT
	COUNT(DISTINCT ord.order_id) AS `주문건수`, -- 주문건수 세기
	COUNT(DISTINCT cust.customer_unique_id) AS  `주문고객수`, -- 고객 고유 id 세기
	COUNT(DISTINCT ord.order_id) / COUNT(DISTINCT cust.customer_unique_id) AS `주문빈도` -- 주문빈도
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust
 ON ord.customer_id = cust.customer_id;
  • 결과

☁️ 쿼리 저장하기

  • 저장 버튼 선택 → 쿼리 저장 선택

📖 실습 쿼리3

🔆 건당 주문 금액 쪼개기

🔆 지표 계산 정리

🔆 관련 테이블

  • 주문건수 = 고유 order_id 수 세기
  • 총 판매 상품 수 = 판매 품목 수 세기
  • 매출 = 판매가격 전부 더하기

🔆 테이블 사용시 유의사항

  • 주문 상품 정보 - order_item_id
    • order_item_id를 모두 더하면 안된다!
    • order_item_id를 세어야 한다!

🔆 쿼리 작성

  • 건당 주문 금액

/* 총 매출, 주문건수 집계 */
SELECT
	SUM(price) AS ord_amt, -- 총 매출
	COUNT(DISTINCT order_id) AS ord_cnt, -- 주문 건수
	SUM(price) / COUNT(DISTINCT order_id) AS amount_per_order -- 주문 당 평균 가격
FROM `olist.olist_order_items` AS ord; -- 주문정보 테이블
  • 주문 건당 판매 상품 수

/* 총 판매 상품 수, 주문건수 집계 */
SELECT
	COUNT(order_item_id) AS prd_cnt, -- 총 판매 상품 수(합치는것X, 세는방식으로 구해야함)
	COUNT(DISTINCT order_id) AS ord_cnt, -- 주문 건수
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS unit_per_order -- 주문 당 평균 판매상품 수
FROM `olist.olist_order_items` AS ord; -- 주문정보 테이블
  • 상품 평균 가격

/* 상품 평균 가격, 총 판매 상품 수 집계 */
SELECT
	SUM(price) AS ord_amt, -- 총 매출
	COUNT(order_item_id) AS prd_cnt, -- 총 판매 상품 수
	SUM(price) / COUNT(order_item_id) AS average_selling_price -- 판매 제품 당 평균 가격
FROM `olist.olist_order_items` AS ord; -- 주문정보 테이블
  • 합치면
/* 합치면 */
SELECT
	SUM(price) AS ord_amt, -- 총 매출
	COUNT(DISTINCT order_id) AS ord_cnt, -- 주문 건수
	COUNT(order_item_id) AS prd_cnt, -- 총 판매 상품 수
	
	SUM(price) / COUNT(DISTINCT order_id) AS amount_per_order, -- 주문 당 평균 가격
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS unit_per_order, -- 주문 당 평균 판매상품 수
	SUM(price) / COUNT(order_item_id) AS average_selling_price -- 판매 제품 당 평균 가격
FROM `olist.olist_order_items` AS ord -- 주문정보 테이블
  • 체크
/* 체크 */
with tb AS (
SELECT
	SUM(price) AS ord_amt, -- 총 매출
	COUNT(DISTINCT order_id) AS ord_cnt, -- 주문 건수
	COUNT(order_item_id) AS prd_cnt, -- 총 판매 상품 수
	SUM(price) / COUNT(DISTINCT order_id) AS amount_per_order, -- 주문 당 평균 가격
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS unit_per_order, -- 주문 당 평균 판매상품 수
	SUM(price) / COUNT(order_item_id) AS average_selling_price -- 판매 제품 당 평균 가격
FROM `olist.olist_order_items` AS ord -- 주문정보 테이블
)
SELECT
	tb.amount_per_order, -- 건당 주문 금액
	tb.unit_per_order * tb.average_selling_price AS check_1 , -- 주문 당 평균 판매상품 수 * 판매 상품 당 평균 가격
	tb.prd_cnt, -- 총 판매 상품 수
	tb.unit_per_order * tb.ord_cnt AS check_2 , -- 주문 당 평균 판매상품 수 * 주문 수
	tb.ord_amt, -- 총 매출
	tb.prd_cnt * tb.average_selling_price AS check_3 -- 총 판매 상품 수 * 판매 상품 당 평균 금액
FROM tb;

☁️ 총 매출, 주문 건수 > 주문 1건당 평균 가격

/* 총 매출, 주문 건수 > 주문 1건당 평균 가격 */
SELECT
	SUM(price) AS `총 매출`,
	COUNT(DISTINCT order_id) AS `총 주문수`,
	SUM(price) / COUNT(DISTINCT order_id) AS `주문당 평균 가격`
FROM `olist.olist_order_items` AS ord;
  • 결과

☁️ 총 판매 상품수, 총 주문건수 > 주문 1건당 평균 판매상품 수

SELECT
	COUNT(order_item_id) AS `총 판매상품수`,
	COUNT(DISTINCT order_id) AS `총 주문수`,
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS `평균판매상품수`
FROM `olist.olist_order_items` AS ord;
  • 결과

☁️ 총 매출, 총 판매상품수 > 판매제품 1개당 평균 가격

SELECT
	SUM(price) AS `총 매출`,
	COUNT(order_item_id) AS `총 판매상품수`,
	SUM(price) / COUNT(order_item_id) AS `제품개당평균가격`
FROM `olist.olist_order_items` AS ord;

☁️ 합치기

SELECT
	SUM(price) AS `총 매출`,
	COUNT(DISTINCT order_id) AS `총 주문수`,
	COUNT(order_item_id) AS `총 판매상품수`,

	SUM(price) / COUNT(DISTINCT order_id) AS `주문당 평균 가격`,
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
	SUM(price) / COUNT(order_item_id) AS `제품개당평균가격`
FROM `olist.olist_order_items` AS ord;
  • 결과

📖 Appendix, 쿼리로 지표 복습해보기

🔆 with 절

  • 임시 테이블을 만드는 절 SELECT절 위에 작성
WITH tb AS (
SELECT column1, column2, ...
FROM tablename
)
  • 2가지 임시 테이블 만들기
WITH tb AS (
SELECT column1, column2, ...
FROM tablename
)
, tb2 AS (
SELECT column3, column4, ...
FROM tablename2
)
  • 강사님 tip : 두번째 테이블 내용 확인하고 싶을때 주석 처리하기 좋게 콤마는 두번째 테이블 앞에 작성하는 것이 좋다!
WITH tb AS (
SELECT column1, column2, ...
FROM tablename
)
-- , tb2 AS (
SELECT column3, column4, ...
FROM tablename2
-- )

☁️ with절 실습

/* 체크 */
WITH tb AS (
SELECT
	SUM(price) AS `총 매출`,
	COUNT(DISTINCT order_id) AS `총 주문수`,
	COUNT(order_item_id) AS `총 판매상품수`,

	SUM(price) / COUNT(DISTINCT order_id) AS `주문당 평균 가격`,
	COUNT(order_item_id) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
	SUM(price) / COUNT(order_item_id) AS `제품개당평균가격`
FROM `olist.olist_order_items` AS ord
)

SELECT *
FROM tb;
  • 결과

☁️ check

SELECT 
	tb.`주문당 평균 가격`,
	tb.`평균판매상품수` * tb.`제품개당평균가격` AS check_1,

	tb.`총 판매상품수`,
	tb.`평균판매상품수` * tb.`총 주문수` AS check_2

	tb.`총 매출`,
	tb.`주문당 평균 가격` * tb.`총 판매상품수` AS check_3
FROM tb;
  • 결과

📖 종합 쿼리

🔆 지표를 한번에 집계하기

  • 주문당 정보를 나타내는 테이블이 있으면?

🔆 하나씩 하면 된다!

  • 레고를 쌓아가듯이 하나하나

🔆 종합 쿼리 작성 고려 사항

  1. 주문 1건당 주문 금액, 판매상품의 수 구하기
  2. 주문 정보 테이블에 1번 결과와, 고객 unique ID를 붙이기
  3. 2번 결과를 집계하기

☁️ 1. 주문 1건당 주문 금액, 판매상품의 수 구하기

SELECT
  item.order_id, 
  SUM(item.price) AS ord_amt, 
  COUNT(item.order_item_id) AS prd_cnt
FROM `olist.olist_order_items` AS item -- 주문 상품 정보 테이블
GROUP BY item.order_id; -- 집계 기준 : 주문 번호
  • 결과

☁️ 2. 주문 정보 테이블에 1번 결과와, 고객 unique ID를 붙이기

/* 첫번째 */
WITH tb AS (
  SELECT
    item.order_id, 
    SUM(item.price) as ord_amt, 
    COUNT(item.order_item_id) as prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)

/* 두번째 */
SELECT
  ord.order_id, 
  ord.order_status,
  ord.customer_id,
  cust.customer_unique_id, 
  tb.ord_amt, 
  tb.prd_cnt
FROM `olist.olist_orders` AS ord
LEFT JOIN `olist.olist_customers` AS cust -- 고객 unique ID
  ON ord.customer_id = cust.customer_id
INNER JOIN tb                             -- 1번 결과 
  ON ord.order_id = tb.order_id
  • 결과

☁️ 3. 2번 결과를 집계하기

/* 첫번째 */
WITH tb AS (
  SELECT
    item.order_id, 
    SUM(item.price) as ord_amt, 
    COUNT(item.order_item_id) as prd_cnt
  FROM `olist.olist_order_items` AS item
  GROUP BY item.order_id
)

/* 두번째 */
, base AS (
  SELECT
    ord.order_id, 
    ord.order_status,
    ord.customer_id,
    cust.customer_unique_id, 
    tb.ord_amt, 
    tb.prd_cnt
  FROM `olist.olist_orders` AS ord
  LEFT JOIN `olist.olist_customers` AS cust -- 고객 unique ID
    ON ord.customer_id = cust.customer_id
  INNER JOIN tb                             -- 1번 결과 
    ON ord.order_id = tb.order_id
)

/* 세번째 */
SELECT
  SUM(ord_amt) AS `총 매출`,
	COUNT(DISTINCT order_id) AS `총 주문수`,
	COUNT(prd_cnt) AS `총 판매상품수`,

	SUM(ord_amt) / COUNT(DISTINCT order_id) AS `주문당 평균 가격`,
	COUNT(prd_cnt) / COUNT(DISTINCT order_id) AS `평균판매상품수`,
	SUM(ord_amt) / COUNT(prd_cnt) AS `제품개당평균가격`,

  COUNT(DISTINCT customer_unique_id) AS `주문고객수`,
  COUNT(DISTINCT order_id) / COUNT(DISTINCT customer_unique_id) AS `주문빈도`
FROM base
  • 결과

profile
yeppi1802
이전 포스트

SQL 데이터 분석 : 2.5. 지표 분석

다음 포스트

SQL 데이터 분석 : 2.5.3 빅쿼리에서 날짜 다루기

0개의 댓글