SQL Review(4th JOIN)

1. JOIN

• 기준을 가지고 데이터를 합침

출처 : 위니브

1) Inner Join

• 두 테이블 모두에서 일치하는 값이 있는 행 반환

select table1.id, table2.id
from table1
[inner] join table2 
	on table2.id=table1.id

 

• weniv_order 테이블과 weniv_user 테이블의 조인 후 조회

select 
	orders.order_id, 
	users.name, 
	orders.created_at
from `weniv.weniv_order` as orders
inner join `weniv.weniv_user` as users
on orders.user_id = users.id

 

• weniv_order 테이블과 weniv_user 테이블의 조인 후 조회

select o.*,
  u.name as user_name,
	p.name as product_name,
	p.cost as product_cost
from `weniv.weniv_order` o
join `weniv.weniv_user` as u on o.user_id = u.id
join `weniv.weniv_product` as p on o.product_id = p.id

 

 

2) Left Join

select 
  t1.order_id,
  t1.created_at,
  t2.name,
  t2.city,
  t1.product_id,
  t3.name as product_name
from `weniv.weniv_order` t1
left join `weniv.weniv_user` t2 on t1.user_id = t2.id
left join `weniv.weniv_product` t3 on t1.product_id = t3.id

 

 

 

select 
  t1.order_id,
  t1.created_at,
  t2.name,
  t2.city,
  t1.product_id,
  t3.name as product_name
from `weniv.weniv_order` t1
left join `weniv.weniv_user` t2 on t1.user_id = t2.id
left join `weniv.weniv_product` t3 on t1.product_id = t3.id
where t2.city = 'Jeju'
order by t3.name

 

SELECT 
  t2.distribution_center_id,
  extract(year from t1.created_at) as year,
  count(t1.id) as order_item_count,
  sum(t1.sale_price) as sum_sale_price
FROM `thelook_ecommerce.order_items` t1
left join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
left join `thelook_ecommerce.users` t3 on t1.user_id = t3.id
left join `thelook_ecommerce.distribution_centers` t4 on t2.distribution_center_id = t4.id
group by t2.distribution_center_id, year
order by distribution_center_id, year

 

 

 

데이터 마트를 위한 비정규화 테이블 만들기

주문데이터(트랜잭션)에 회원데이터(마스터)와 상품데이터(마스터)를 결합시키기

select t1.order_id,
t1.user_id,
t2.name as user_name,
t2.age as user_age,
t2.city as user_city,
t2.postal_code as user_posta_code,
t1.num_of_item as sum_buy_item,
t3.name as buy_product_name,
t3.cost as buy_product_cost
from `weniv.weniv_order` t1
left join `weniv.weniv_user` t2 on t1.user_id = t2.id
left join `weniv.weniv_product` t3 on t1.product_id = t3.id

 

 

+ 자체 연습문제 

SQL 연습문제 8-1

-- 회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 모든 주문내역에 회원정보를 표시하세요.

-- - 조회 항목 : 주문ID(order_id), 주문한 상품 수량(num_of_item), 회원 이름(first_name, last_name), 주소(street_address), 우편번호(postal_code), 도시(city), 국가(country)
select o.order_id,
o.num_of_item,
concat(u.first_name, ' ', u.last_name) as user_name
u.street_address,
u.postal_code,
u.city,
u.country
from `thelook_ecommerce.orders` o
left join `thelook_ecommerce.users` u on o.user_id = u.id

 

SQL 연습문제 8-2

-- 회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 상품을 주문한 회원의 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)인 정보를 조회하시오. 

-- - 조회 항목 : 회원 이름(first_name, last_name), 주소(street_address), 우편번호(postal_code), 도시(city), 국가(country), 주문한 상품 수량(num_of_item)
-- - 조건 : 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)
select
concat(u.first_name, ' ', u.last_name) as user_name,
u.street_address,
u.postal_code,
u.city,
u.country,
o.num_of_item
from `thelook_ecommerce.users` u
join `thelook_ecommerce.orders` o on u.id = o.user_id
where u.country = 'United States' and o.status = 'Processing'

 

 

SQL 연습문제 8-3

-- 회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 국가별 총 상품 주문주(total_order_count)을 조회하시오.

-- - 조회 항목 : 국가명(country), 국가별 총 상품 주문주(total_order_count)
-- - 정렬 : 국가별 총 상품 주문주(total_order_count)이 많은 순으로 정렬
select
u.country,
sum(o.num_of_item) as total_order_count
from `thelook_ecommerce.users` u
left join `thelook_ecommerce.orders` o
 on o.user_id = u.id  
group by u.country
order by total_order_count desc

 

 

 

-- SQL 연습문제 8-4
-- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 필요한 정보를 조회하시오. 

-- - 조회 항목 : 가격(cost), 카테고리(category), 상품명(name), 브랜드(brand), 판매가격(retail_price), 주문상태(status), 주문일시(created_at), 배송일시(shipped_at), 배송완료일시(delivered_at)
-- - 조건 : 카테고리(category)가 'Outerwear & Coats'
-- - 정렬 : 주문일시(created_at) 기준으로 최신순으로 정렬
select 
p.cost,
p.category,
p.name,
p.brand,
p.retail_price,
o.status,
o.created_at,
o.shipped_at,
o.delivered_at
from `thelook_ecommerce.order_items` as o 
left join `thelook_ecommerce.products` as p on o.product_id = p.id
where p.category = 'Outerwear & Coats'
order by o.created_at desc

 

 

SQL 연습문제 8-5

-- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 2022년 한해 주문한 상품들의 정보를 조회하시오. 

-- - 조회 항목 : 주문일(order_date), 상품명(name), 주문상태(status), 원가(cost), 판매가격(retail_price), 카테고리(category),  브랜드(brand)
-- - 주문일은 `2022-01-01` 와 같은 `%Y-%m-%d` 포맷으로 표시해주세요. 
-- FORMAT_DATE 함수 이용합니다.
-- order_items의 created_at을 데이터를 사용하여 조회하면 됩니다.
-- - 조회 조건
--     - 주문일시 : 2022-01-01 ~ 2022-12-31
-- - 정렬 조건 : 주문일시(created_at) 오름차순
select 
FORMAT_DATE('%Y-%m-%d', o.created_at) as order_date,
p.name,
o.status,
p.cost,
p.retail_price,
p.category,
p.brand
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where string(created_at) like '2022%' # format_date('%Y-%m-%d',t1.created_at) between '2022-01-01' and '2022-12-31'
order by created_at asc

 

 

SQL 연습문제 8-6
-- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문한 상품의 브랜드 별 평균할인가격(brand_avg_sale_price)을 조회하시오. 

-- - 조회 항목 : 브랜드(brand), 브랜드 별 평균할인가격(brand_avg_sale_price)
-- - 정렬 : 브랜드명으로 정렬
select
p.brand,
avg(p.retail_price) as brand_avg_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by p.brand
order by p.brand

 

SQL 연습문제 8-7

-- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여

-- 여성파트 주문완료상품의 날짜별 상품별 매출합계과 평균매출을 구하세요.

-- 단 주문건수가 2개이상인 데이터만 표시하세요.

-- 조회항목

-- - 주문일 (order_date)
--     - 표시 형식 : 2022-01-01
-- - 상품명 (product_name)
-- - 주문건수 (order_count)
-- - 매출합계 (sum_sale_price) - 소수점 2자리까지 표시, 반올림
-- - 평균매출(avg_sale_price) - 소수점 2자리까지 표시, 반올림

-- 필터 조건

-- - 상품의 department가 Women 입니다.
-- - 주문의 상태가 `Complete` 인 주문만 포함합니다.
-- - 주문건수가 2개 이상 데이터만 표시

-- 정렬조건

-- - 주문일 오름차순
-- - 상품명 오름차순

select
format_date('%Y-%m-%d', date(o.created_at)) as order_date,
p.name as product_name,
count(o.order_id) as order_count,
round(sum(o.sale_price),2) as sum_sale_price,
round(avg(o.sale_price),2) as avg_sale_price
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
where p.department ='Women' and
o.status ='Complete'
group by order_date,
product_name
having order_count >= 2
order by order_date, product_name

order_items 테이블에서 id를 가져와야하는지 order_id를 가져와야하는지?

주문건수는 count로 진행한다count는 주문이 들어간 행 자체를 계산하기 때문에 어떤 값을 불러와도 값은 동일!그래도 order_item_count이므로 order_items에서

 

 

 

SQL 연습문제 8-8

-- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 주문id 당 매출 합계 정보를 구하세요.

-- group by order_id를 이용하세요.

-- - 조회 항목 :
--     - 주문 id(order_id)
--     - 비용 합계(sum_cost)
--     - 판매가격 합계(sum_retail_price)
--     - 총 이익(sum_profit)

select o.order_id,
sum(p.cost) as sum_cost,
sum(p.retail_price) as sum_retail_price,
sum(p.retail_price - p.cost) as sum_profit
from `thelook_ecommerce.order_items` o
left join `thelook_ecommerce.products` p on o.product_id = p.id
group by o.order_id
order by o.order_id

 

SQL 연습문제 8-9

-- 사용자 이벤트(events) 테이블에서 일별 이벤트타입별 이벤트 발생 횟수를 조회해보세요.

-- 조회항목

-- - 이벤트 발생일(date)
-- - home 이벤트 횟수 (home)
-- - department 이벤트 횟수 (department)
-- - product 이벤트 횟수 (product)
-- - cart 이벤트 횟수 (cart)
-- - purchase 이벤트 횟수 (purchase)
-- - cancel 이벤트 횟수 (cancel)

-- 정렬순서

-- - 이벤트 발생인 오름차순
select 
format_date('%Y-%m-%d', created_at) as date,
count(case when event_type = 'home' then event_type end) as home, 
count(case when event_type = 'department' then event_type end) as department,
count(case when event_type = 'product' then event_type end) as product,
count(case when event_type = 'cart' then event_type end) as cart,
count(case when event_type = 'purchase' then event_type end) as purchase,
count(case when event_type = 'cancel' then event_type end) as cancel
from `thelook_ecommerce.events`
group by date
order by date

event_type 대신에 1을 넣어줘도 값은 같다 (이벤트 한 횟수라서 숫자 1을 count해도 상관없는 것 같다)

 

+ 프로그래머스  SQL문제 

 

프로그래머스 문제 8-1 (level 1) (과일로 만든 아이스크림 고르기)
https://school.programmers.co.kr/learn/courses/30/lessons/133025

SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
WHERE I.INGREDIENT_TYPE = 'fruit_based' and
F.TOTAL_ORDER >= 3000
ORDER BY TOTAL_ORDER DESC

 

 

프로그래머스 8-2 문제 (상품 별 오프라인 매출)
https://school.programmers.co.kr/learn/courses/30/lessons/131533

SELECT P.PRODUCT_CODE,
SUM(P.PRICE * S.SALES_AMOUNT) AS SALES
FROM PRODUCT P
LEFT JOIN OFFLINE_SALE S ON P.PRODUCT_ID = S.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE

 

프로그래머스 8-3 문제(성분으로 구분한 아이스크림 총 주문량)
https://school.programmers.co.kr/learn/courses/30/lessons/133026

SELECT I.INGREDIENT_TYPE,
SUM(CASE 
     WHEN I.INGREDIENT_TYPE = 'sugar_based' THEN F.TOTAL_ORDER
     WHEN I.INGREDIENT_TYPE = 'fruit_based' THEN F.TOTAL_ORDER
    END) AS TOTAL_ORDER
FROM FIRST_HALF F
LEFT JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY I.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER

 

 

프로그래머스 8-4 문제 (조건에 맞는 도서 출력하기)
https://school.programmers.co.kr/learn/courses/30/lessons/144854

SELECT
B.BOOK_ID,
A.AUTHOR_NAME,
DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B
LEFT JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC

아니..빅쿼리랑 MySQL 날짜변환형 다른거 계속 헷갈리네

mysql : DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d')

Big Query : FORMAT_DATE('%Y-%m-%d', B.PUBLISHED_DATE)

 

 

프로그래머스 문제 8-5 (카테고리 별 도서 판매량 집걔)
https://school.programmers.co.kr/learn/courses/30/lessons/144855

SELECT CATEGORY,
SUM(CASE
  WHEN CATEGORY = '경제' THEN SALES 
  WHEN CATEGORY = '인문' THEN SALES
  WHEN CATEGORY = '소설' THEN SALES
  WHEN CATEGORY = '생활' THEN SALES
  WHEN CATEGORY = '기술' THEN SALES
 END) AS TOTAL_SALES
FROM BOOK B
LEFT JOIN BOOK_SALES S ON B.BOOK_ID = S.BOOK_ID
WHERE LEFT(DATE_FORMAT(S.SALES_DATE, '%Y-%m-%d'), 7) = '2022-01' #LEFT함수는 문자열로 가져오므로 우변도 문자열로 !
GROUP BY CATEGORY
ORDER BY CATEGORY

 

프로그래머스 8-6 문제 (오랜 기간 보호한 동물 (2))
https://school.programmers.co.kr/learn/courses/30/lessons/59411

SELECT
I.ANIMAL_ID,
I.NAME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY O.DATETIME - I.DATETIME DESC
LIMIT 2;

 

 

프로그래머스 8-7 (오랜기간 보호한 동물(1))

SELECT
I.NAME,
I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY DATETIME ASC
LIMIT 3;

DATE에 MIN 하는걸 생각했는데 그냥 간단하게 내림차순으로 정렬하고 LIMIT 걸면된다 !

 

 

프로그래머스 8-8 (있었는데요 없었습니다)
https://school.programmers.co.kr/learn/courses/30/lessons/59043

SELECT 
I.ANIMAL_ID,
I.NAME
FROM ANIMAL_INS I
JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME >= O.DATETIME 
# WHERE TIMESTAMPDIFF(SECOND, AI.DATETIME, AO.DATETIME) < 0 
# datediff 로하면 같은날인데 시간이 더 빠르거나 느린경우는 포함이안 될수도 있기 때문에 초단위로
ORDER BY I.DATETIME

 

프로그래머스 8-9 (없어진 기록 찾기)

SELECT
O.ANIMAL_ID,
O.NAME
FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL # 이건 굳이 없어도됨 O.DATETIME IS NOT NULL , DATETIME 이 아니라 ANIMAL_ID
ORDER BY O.ANIMAL_ID

WHERE 조건문에서 보호소 들어온 기록 찾을 때는 그냥 ID 값이 NULL인지 보면된다.

(DATETIME이나 NAME등말고 그냥 ID로)

 

 

★

Inner Join 과 Left Join의 차이 

Inner Join : 교집합Left Join : 합집합(Outer Join의 일부)

 

★

날짜포맷 BigQuery SELECT FORMAT_DATE('%Y-%m-%d', DATE '2008-12-25') ; MySQL SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');

 

+동기님들이 좋은 질문 많이 해주셔서 이것도 추가

 

★Case 문과 IF 문 사용 시기

특별히 정해져있는건 아님. 

간단히 쓰는 경우에 IF.  ELSE IF 처럼 연달아 예외케이스를 많이 설정해야 하는 경우에 CASE

 

★

테이블 관리 잘 되어 있으면 Left Join 대신 Join 사용 가능 -> 관리가 잘되어 있다?

네 오늘 처럼 참고하는 마스터테이블인 user나 product에 해당 id의 레코드가 없는경우 inner join의 경우에는 order 테이블 레코드도 출력이 안될 수 있어요. 예) 1번 주문에 user_id가 10인데 user 테이블에 10번 유저 데이터가 아예 없는 경우.

order테이블에서 2022년 전체 매출기록을 조회하는데 탈퇴한 10번유저의 주문기록만 빠져서는 안됨.

관리가 잘 되어있다는 것은 order테이블에 기록되어있는 user_id에 해당하는 모든 id의 유저가 user테이블에도 다 레코드가 존재가는것을 이야기 한 것.(=결측값 없이 전부 매치)

 

★

Having 과 Where의 차이

 

Having

데이터를 가져오는 순서의 차이

1) from 에서 데이터를 가져온다. --> 2) where에서 조건에 맞는 데이터를 필터해서 가져온다. --> 3) group by 에서 where로부터 가져온 데이터를 가지고 그룹으로 만든다. --> 4) having 에서 group으로 만든 데이터에다가 조건을 걸어서 필터한다.

위 경우에는  SQL은 모든년도의 데이터를 가져와서 그룹핑을 한뒤에 2020년도의 그룹핑 데이터만 having에서 필터한 것

 

Where

SQL은 2020년도의 orders 데이터만 가져와서 그룹핑한 것

 

※결론

어느것이 더 좋은 것인지는 얼마나 연산이 덜 들어가냐에 따라 다름.

빅쿼리에서 실행결과 - 세부정보에 보면 슬롯시간(연산능력 소모량)이 나옵니다.

슬록시간(연산능력소모량)이 적고 읽고 쓴 레코드량이 적은것이 더 좋은 것이지만 코드 가독성도 무시할수 없음.

비슷한 시간 소모한다면 가독성 좋은 코드가 더 좋음