#1 customers 와 orders 테이블 관계 → 주문내역이 없는 고객은 없으므로 inner join 가능
-- Q1. 주문내역이 없는 고객 있는지 (고객 & 주문내역 테이블 관계성)
-- A1. 주문내역이 없는 고객 확인 -> 없음
SELECT customer_id
FROM customers c
WHERE customer_id NOT IN (SELECT customer_id
FROM orders
GROUP BY customer_id)
#2 products 와 orders 테이블 관계 → 주문내역이 없는 제품은 없으므로 inner join 가능
-- Q2. 주문내역이 없는 상품 확인
-- A2. 없음
SELECT product_id
FROM orders o
WHERE product_id NOT IN (SELECT product_id FROM products GROUP BY product_id)
고객 테이블에서 고객 아이디당 이름 이 1:1 매칭이 되는지 ( 동명이인 처리 확인) WHY? 조인키는 id 지만 출력은 name 이기때문에, 그룹바이 바로 적용해 줄수있는지 여부 확인을 위해
#3 customer_id 와 customername 대응 관계 → 1:1 관계 이므로 customername으로 그룹핑 가능
# Q1. 고객테이블 ID 와 NAME 관계
# A1. 출력값 없으므로 1:1 관계임
SELECT customer_id
,COUNT(DISTINCT customername) AS cnts
FROM customers
GROUP BY customer_id
HAVING cnts>1 # 1:1 대응이 아닌 값 출력
[풀이 과정]
INNER JOIN : 출력값인 고객명/총 주문 금액 집계 값은 3개 테이블 조인하여 출력할 수 있으므로 3테이블 조인
GROUP BY: EDA 를 통해 고객 ID & 고객 NAME은 1:1 대응 관계이며 NULL 값 없었으므로 고객명 기준 그룹바이
총 주문 금액 구하기 : 총 주문 금액은 quantity * price 의 sum으로 구함
총 주문 수 구하기 : 주문수는 order id 로 count 해줌
SELECT c.customername
,SUM(o.quantity * p.price)
,COUNT(o.order_id)
FROM orders o
INNER JOIN customers c on o.customer_id =c.customer_id
INNER JOIN products p on o.product_id =p.product_id
GROUP BY c.customername
[정답 쿼리]
작성한 쿼리와 동일
SELECT
c.CustomerName,
SUM(p.Price * o.Quantity) AS TotalAmount,
COUNT(o.OrderID) AS OrderCount
FROM
Customers c
JOIN
Orders o ON c.CustomerID = o.CustomerID
JOIN
Products p ON o.ProductID = p.ProductID
GROUP BY
c.CustomerName;
➡️INSIGHT
테이블 EDA를 잘 해주면 출력값에 대한 함수 선택이 빠름!
테이블 조인 후 한번에 필터링 &그룹핑이 가능하다면 선 조인 해주고 필터&집계 해주는게 빠르다.
문제2 : 각 제품 카테고리별로 가장 많이 팔린 제품명과 총 판매량 조회(카테고리/제품명/총 판매량 출력)
▶POINT. 제품별 총 판매량 집계 & 카테고리별 가장 높은 판매량을 가진 제품명 출력 & 테이블 조인
[풀이 과정]
제품별 총 판매량 구하기 : orders 테이블에서 제품id 그룹바이 하여 총 판매량 집계 해줌
카테고리 별 총 판매량이 가장 큰 제품명 : 카테고리 그룹 기준으로 총 판매량이 가장 큰 행 순번 매기기 →rank 윈도우 함수 사용 ( 가장 높은 판매량 카테고리가 동률일 경우 전부 출력 해줌, dense_rank 사용 해도 무방)
가독성을 위해 with 문 서브쿼리로 쿼리 작성
첫번째 순번 (rk=1) 인 행 출력
WITH o AS( -- 제품별 총 주문량 집계
SELECT product_id
,SUM(quantity) AS TotalSold
FROM orders
GROUP BY product_id
), rk_t AS( -- 카테고리별 최대값 넘버링+ 최대 판매량일때 중복값 전부 출력
SELECT p.category
,p.productname AS Top_Product
,o.TotalSold
,RANK() OVER (PARTITION BY p.category ORDER BY o.TotalSold DESC) AS rk
FROM o
INNER JOIN products p ON o.product_id=p.product_id
)
SELECT category
,Top_Product
,TotalSold
FROM rk_t
WHERE rk=1
[정답 쿼리 & 피드백]
카테고리 /제품명 그룹핑 후 총 판매량이 가장 큰 값을 having 절 단일행 서브쿼리로 필터링
SELECT
p.Category,
p.ProductName AS Top_Product,
SUM(o.Quantity) AS TotalSold
FROM
Products p
JOIN
Orders o ON p.ProductID = o.ProductID
GROUP BY
p.Category, p.ProductName
HAVING
SUM(o.Quantity) = (
SELECT
MAX(SumQuantity)
FROM
(SELECT
p2.Category,
SUM(o2.Quantity) AS SumQuantity
FROM
Products p2
JOIN
Orders o2 ON p2.ProductID = o2.ProductID
GROUP BY
p2.Category, p2.ProductID) AS Subquery
WHERE
Subquery.Category = p.Category
);
➡️ 피드백
with 문/ window 함수가 가독성과 효율 측면에서 좋아 (이 로직이 가장 간단하다고 생각..) 쿼리 작성할 때 이렇게만 생각하다 보니 서브쿼리 중첩이 많이 되어있는 (정답 쿼리 처럼) 긴 쿼리는 해석하기 어려움.
