미리보기

---

ERD 란

ERD (Entity Relationship Diagram)
ERD는 데이터베이스에 들어있는 테이블 간의 관계와 구조를 나타낸 그림입니다. 이 ERD를 통해서 우리는 처음보는 데이터베이스의 구조와 내용을 빠르게 파악할 수 있습니다.

• 정보 발견
• 그룹 연결
• 관계 인식

데이터모델링1

생활코딩_개념적 데이터 모델링 1

데이터모델링2

생활코딩_개념적 데이터 모델링 2

Cardinality 관계성

1:1
1:N
N:M

Optionality

반드시 있어야하는게 Mandatory +
있어도 없어도되는게 Optional O

Redash

온라인수업에서 제공받은 아이디와 비밀번호로 접속합니다.

Northwind Database는 Northwind 라는 가상의 식품회사에 대한 데이터베이스 입니다. 고객, 상품, 주문, 직원, 발주 등 총 20개의 테이블로 구성되어 있으며, 실제 기업의 데이터베이스와 유사한 구조로 되어있어 실습을 하기에 좋은 예제입니다. 주로 고객, 상품, 주문 테이블 위주로 살펴 볼 예정입니다.

Create > Query 로 새 편집기 생성합니다.
기본적으로 Orders 테이블을 확인했습니다.

1. 상품 카테고리별 갯수,평균가격대

상품(product)의 카테고리(category)별로 상품 수와 평균 가격대(list_price)를 찾는 쿼리 작성

SELECT 
    category, -- 상품 카테고리별 
    COUNT(*), -- 상품 수 
    AVG(list_price) -- 평균 가격대
FROM products
GROUP BY category;

2. JOIN연결 추출

첫시도

2006년 1분기에 고객(customer)별 주문(order)횟수, 주문상품(product)의 카테고리(category) 수, 총 주문금액(quanitity * unit_price) 를 찾는 쿼리 작성
join 이용

1. 고객별 주문횟수

-- 고객(customer)별 주문(order)횟수
SELECT 
    customer_id, 
    count(o.id) AS 고객별주문횟수
FROM orders o 
GROUP BY 1 

2. 고객별 주문상품의 카테고리 수

3. 고객별 총 주문금액

4. 2006년 1분기에 한하여

음... 복잡합니다.
어느 테이블에 어느 컬럼끼리 JOIN 하느냐 생각해봐야하는 문제이군요

탐구

고객별 주문횟수는 이미 해결했습니다.
이를 기반으로 나머지 테이블들을 어떻게 join할지 보겠습니다.

JOIN 시도

일단 필요한 테이블은 ORDER_DETAILS, ORDERS, PRODUCTS 세 개 인 것 같습니다.

모두 JOIN 하여 확인해줍니다.

SELECT * 
FROM orders o 
    INNER JOIN order_details od ON o.id = od.order_id 
    INNER JOIN products p ON p.id = od.product_id 
LIMIT 5; 

• 2006년 1분기에 일단 PASS
• 고객(customer)별 주문(order)횟수,
• 고객(customer)별 주문상품(product)의 카테고리(category) 수,
• 고객(customer)별 총 주문금액(quanitity * unit_price)

GROUP BY 에러

어떻게 하면 SELECT절과 GROUP BY를 깔끔하게 작성할 수 있을까요...

-- 고객(customer)별 주문(order)횟수, 주문상품(product)의 카테고리(category) 수, 총 주문금액(quanitity * unit_price) 
SELECT 
    o.customer_id 고객id, 
    count(o.id) 주문횟수, 
    -- p.product_name 주문상품명 , 
    -- p.category 카테고리, 
    -- sum(p.category) 카테고리수, -- 0.00 으로 모두 나옴 
    count(DISTINCT p.category) 카테고리수 ,
    -- (od.quantity * od.unit_price) 총주문금액 ERROR남 
    sum(od.quantity * od.unit_price) 총주문금액-- 총 sum 주문금액 quantity*unit_price
FROM orders o 
    INNER JOIN order_details od ON o.id = od.order_id 
    INNER JOIN products p ON p.id = od.product_id 
GROUP BY 
    o.customer_id   -- 고객별
; 

2006년 1분기 한하여

orders테이블의 order_date DATETIME
2006-01-15 00:00:00 형태로 이루어짐

2006년 1분기 = 2006년 1월 부터 2월, 3월

2006-01-00 이상
2006.04.01 미만

MySQL 특정 날짜 Select 하는 방법 - between 모를땐 구글링합니다!

WHERE o.order_date BETWEEN '2006-01-01 00:00:00' and '2006-03-31 23:59:59'

다시한번 외웁니다.
select > from > where > group by > having > order by

풀이

-- 2006년 1분기의  고객(customer)별 주문(order)횟수, 주문상품(product)의 카테고리(category) 수, 총 주문금액(quanitity * unit_price) 
SELECT 
    o.customer_id 고객id, 
    count(o.id) 주문횟수, 
    -- p.product_name 주문상품명 , 
    -- p.category 카테고리, 
    -- sum(p.category) 카테고리수, -- 0.00 으로 모두 나옴 
    count(DISTINCT p.category) 카테고리수 ,
    -- (od.quantity * od.unit_price) 총주문금액 ERROR남 
    sum(od.quantity * od.unit_price) 총주문금액-- 총 sum 주문금액 quantity*unit_price
FROM orders o 
    INNER JOIN order_details od ON o.id = od.order_id 
    INNER JOIN products p ON p.id = od.product_id 
WHERE o.order_date BETWEEN '2006-01-01 00:00:00' and '2006-03-31 23:59:59'
GROUP BY o.customer_id   -- 고객별
; 

3. Sub-query로 추출

서브쿼리에 대해 다시 복습

2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요. (join을 사용하지 않고 쿼리를 작성하세요.)

• 기간 : 2006년 3월
  • orders.order_date가 2006년 3월에 한하여
• status_name 확인 위치 : orders_status 테이블의 id 컬럼
• 조인 : orders.status_id = orders_status.id
  

일단 확인

SELECT * 
FROM orders o INNER JOIN orders_status os
    ON o.status_id = os.id
; 

하지만 JOIN이 아닌 Sub-query 이용

2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리

• order 주문 조건 : 2006년 3월 where
  • where절이 orders_status테이블이 아닌 orders테이블의 status_id컬럼이라서 여기에 sub-query
• 테이블 : orders_status from
• 찾는컬럼 : status_name select

mySQL_where절 서브쿼리 구글링

SELECT status_name
FROM orders_status -- 여기까진 알겠다. 
WEHRE 
	-- 서브쿼리 : 2006년 3월에 주문된 건에 한하여 
    -- SELECT order_date
    -- FROM orders 
    -- WHERE 2006년 3월에 한하여 

참고로 orders 테이블의 order_date 형태는 YEAR-MM-DD HH:MM:SS 로 이루어져있다.

Mysql에서는 날짜 형태는 모다?
YEAR(컬럼명) = 2006
MONTH(컬럼명) = 03

SELECT DISTINCT status_name , 
    count(status_name)
FROM orders o INNER JOIN orders_status os
    ON o.status_id = os.id
WHERE YEAR(o.order_date) = 2006 AND MONTH(o.order_date)=03
GROUP BY status_name
; 

-- 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요. 
-- (join을 사용하지 않고 쿼리를 작성하세요.) (힌트: orders_status 사용, sub-query)
SELECT 
    os.status_name,  -- 주문상태 
    count(os.status_name)
FROM orders_status os -- 의 테이블 
WHERE os.id IN (           
    -- 2006년 3월에 주문된 건에 한하여 
    SELECT o.status_id
    FROM orders o
    WHERE YEAR(o.order_date) = 2006 AND MONTH(o.order_date) = 3
    )
GROUP BY os.status_name
; 

JOIN과 SUB-QUERY 차이

왜 JOIN, SUB-QUERY 두가지 방식으로 쿼리 작성한 결과가 다를까?

• JOIN
  orders 테이블과 orders_status 테이블을 결합하고,
  주문일이 2006년 3월인 경우에 대해서만 필터링합니다.
  그 후, 주문 상태(status_name)를 기준으로 그룹화하여 각 상태별 주문의 개수를 계산합니다.

• SUB-QUERY
  orders_status 테이블에서 주문 상태(status_name)별로 그룹화한 후에,
  해당 상태가 2006년 3월에 주문된 경우에 대해서만 필터링하고
  그룹화된 주문 상태별 개수를 세는 것입니다.

• 차이점
  JOIN 은 주문과 주문 상태를 연결한 후 필터링하는 것이고,
  SUB-QUERY는 먼저 주문 상태를 선택하고 그 중에서 필요한 것만 필터링하는 것입니다.