[AI School-TIL] SQL(BigQuery) 5일차_23.01.27

Q. JOIN vs UNION ?

-join은 테이블 간의 결합

-union은 조회 결과를 위아래(세로방향)으로 합치는 것 (필드는 확장되지 않음)

 

[오늘 진도]

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');

 

• 연습문제

### SQL 연습문제 8-6
#주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 
#주문한 상품의 브랜드 별 평균판매가격(brand_avg_sale_price)을 조회하시오.
#- 조회 항목
#    - 브랜드(brand)
#    - 브랜드 별 평균판매가격(brand_avg_sale_price) :소수점 2자리까지 표시, 반올림 처리
#- 정렬 : 브랜드명 오름차순 정렬
select 
  t2.brand,
  round(avg(t1.sale_price), 2) as brand_avg_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
group by brand
order by 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', t1.created_at) as order_date,
  t2.name as product_name,
  count(t1.id) as order_count,
  round(sum(t1.sale_price), 2) as sum_sale_price,
  round(avg(t1.sale_price), 2) as avg_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where t2.department = 'Women'
and t1.status = 'Complete'
group by order_date, product_name
having order_count >= 2
order by order_date, product_name

 

'''### SQL 연습문제 8-8

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

group by order_id를 이용하세요.

- 조회 항목 :
    - 주문 id(order_id)
    - 비용 합계(sum_cost)
    - 판매가격 합계(sum_retail_price)
    - 총 이익(sum_profit) : 이익 = 판매가격 - 비용'''
select t1.order_id,
  round(sum(t2.cost), 2) as sum_cost,
  round(sum(t2.retail_price), 2) as sum_retail_price,
  round(sum(t2.retail_price - t2.cost), 2) as sum_profit
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 
  on t1.product_id = t2.id
group by order_id
order by 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 event_date,
  count(case when event_type = 'home' then 1 end) as home,
  count(case when event_type = 'department' then 1 end) as department,
  count(case when event_type = 'product' then 1 end) as product,
  count(case when event_type = 'cart' then 1 end) as cart,
  count(case when event_type = 'purchase' then 1 end) as purchase,
  count(case when event_type = 'cancel' then 1 end) as cancel
from `thelook_ecommerce.events`
group by event_date
order by event_date

 

• 프로그래머스 문제

### 프로그래머스 문제 8-1 (level 1)
#과일로 만든 아이스크림 고르기
SELECT t1.FLAVOR
FROM FIRST_HALF t1
JOIN ICECREAM_INFO t2 on t1.FLAVOR = t2.FLAVOR
WHERE t1.TOTAL_ORDER > 3000
AND t2.INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC

### 프로그래머스 문제 8-2 (level 2)
#상품 별 오프라인 매출 구하기
SELECT
    t2.PRODUCT_CODE,
    SUM(t2.PRICE * t1.SALES_AMOUNT) AS SALES
FROM OFFLINE_SALE t1
JOIN PRODUCT t2 on t1.PRODUCT_ID = t2.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

### 프로그래머스 문제 8-3 (level 2)
#성분으로 구분한 아이스크림 총 주문량
SELECT 
    t2.INGREDIENT_TYPE,
    SUM(t1.TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF t1
JOIN ICECREAM_INFO t2 on t1.FLAVOR = t2.FLAVOR
GROUP BY INGREDIENT_TYPE

### 프로그래머스 문제 8-4 (level 2)
#조건에 맞는 도서와 저자 리스트 출력하기
SELECT 
    t1.BOOK_ID,
    t2.AUTHOR_NAME,
    DATE_FORMAT(t1.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
    -- BigQuery용 
    -- FORMAT_DATE('%Y-%m-%d', t1.PUBLISHED_DATE) AS PUBLISHED_DATE
FROM BOOK t1
JOIN AUTHOR t2 on t1.AUTHOR_ID = t2.AUTHOR_ID
WHERE t1.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE 

### 프로그래머스 문제 8-5 (level 3)
#카테고리 별 도서 판매량 집계하기
SELECT 
    t2.CATEGORY,
    SUM(t1.SALES) AS TOTAL_SALES
FROM BOOK_SALES t1
JOIN BOOK t2 on t1.BOOK_ID = t2.BOOK_ID
WHERE DATE_FORMAT(t1.SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY CATEGORY
ORDER BY CATEGORY

### 프로그래머스 문제 8-6 (level 3)
#오랜 기간 보호한 동물(2)
SELECT 
    t1.ANIMAL_ID,
    t1.NAME
FROM ANIMAL_INS t1
JOIN ANIMAL_OUTS t2 on t1.ANIMAL_ID    = t2.ANIMAL_ID
ORDER BY t2.DATETIME - t1.DATETIME DESC
LIMIT 2

### 프로그래머스 문제 8-7 (level 3)
#오랜 기간 보호한 동물(1)
SELECT t1.NAME,
    t1.DATETIME
FROM ANIMAL_INS t1
LEFT JOIN ANIMAL_OUTS t2 on t1.ANIMAL_ID = t2.ANIMAL_ID
WHERE t2.ANIMAL_ID IS NULL
ORDER BY t1.DATETIME ASC
LIMIT 3

### 프로그래머스 문제 8-8 (level 3)
#있었는데요 없었습니다 (보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회)
SELECT 
    t1.ANIMAL_ID,
    t1.NAME
FROM ANIMAL_INS t1
JOIN ANIMAL_OUTS t2 on t1.ANIMAL_ID = t2.ANIMAL_ID
WHERE t1.DATETIME > t2.DATETIME
ORDER BY t1.DATETIME

### 프로그래머스 문제 8-9 (level 3)
#없어진 기록 찾기 (입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회)
SELECT  
    t1.ANIMAL_ID,
    t1.NAME
FROM ANIMAL_OUTS t1
LEFT JOIN ANIMAL_INS t2 on t1.ANIMAL_ID = t2.ANIMAL_ID
WHERE t2.ANIMAL_ID IS NULL
ORDER BY t1.ANIMAL_ID

 

• datediff 와 timestampdiff

 

집합

• UNION(합집합) : 레코드와 레코드끼리 합치는 것 (행과 행끼리, 열과 열끼리)

- UNION ALL (각각 다른 조건에서 조회해서 결과를 합칠 수 있다.)

select 1
UNION ALL
select 2

 

- UNION DISTINCT : 중복값 제거해서 합침

select 1
UNION DISTINCT
select 1

 

• INTERSECT(교집합)

select * from `weniv.weniv_user` as user1
INTERSECT DISTINCT
select * from `weniv.weniv_user3` as user3

 

• EXCEPT(차집합)

select * from `weniv.weniv_user` as user1
except DISTINCT
select * from `weniv.weniv_user3` as user3

 

#연습문제9
### SQL 연습문제 9-1
#위니브 상품정보1(weniv_product) 테이블과 위니브 상품정보2(weniv_product2) 테이블 간 모든 정보를 조회
#단, 중복 제거하여 조회
select * from `weniv.weniv_product`
UNION DISTINCT
select * from `weniv.weniv_product2`

### SQL 연습문제 9-2
#위니브 회원(weniv_user) 테이블에서 주문 이력(weniv_order)이 있는 사람의 id을 조회
select id from `weniv.weniv_user`
INTERSECT DISTINCT
select user_id from `weniv.weniv_order`

### SQL 연습문제 9-3
#위니브 상품정보1(weniv_product) 테이블에서 위니브 상품정보2(weniv_product2) 테이블에 있는 정보를 뺀 
#나머지의 정보를 조회
select * from `weniv.weniv_product`
EXCEPT DISTINCT
select * from `weniv.weniv_product2`

### SQL 연습문제 9-4
#상품정보(products) 테이블에서 카테고리가 ‘Clothing Sets’인 정보와 'Jumpsuits & Rompers' 인 정보를 
#각각 조회후 합쳐서 조회
select * from `thelook_ecommerce.products` 
where category = 'Clothing Sets'
union all
select * from `thelook_ecommerce.products` 
where category = 'Jumpsuits & Rompers'

### SQL 연습문제 9-5
#회원(users) 테이블에서 국가가 'South Korea' 인 회원정보에서 
#나이가 20세~40세인 회원을 뺀 나머지 회원의 정보를 조회
select * from `thelook_ecommerce.users` where country = 'South Korea'
except distinct
select * from `thelook_ecommerce.users` where age between 20 and 40

### SQL 연습문제 9-6
#주문아이템(order_items) 테이블과 상품(products)테이블을 이용하여 
#2019 ~ 2022연도의 연도별로 매출총액이 1위인 상품의 상품명과 매출합계를 표시하세요.
#각 연도의 매출총액 1위 상품의 상품명과 매출합계를 구한뒤 합쳐서 조회하세요.
#조회 항목
#- 연도(year)
#- 상품명(product_name)
#- 매출합계금액(sum_sale_price)
#정렬조건 : 연도(year)
(select 
  2019 as year,
  t2.name as product_name,
  round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where extract(year from t1.created_at) = 2019
group by product_name
order by sum_sale_price desc
limit 1)
union all
(select 
  2020 as year,
  t2.name as product_name,
  round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where extract(year from t1.created_at) = 2020
group by product_name
order by sum_sale_price desc
limit 1)
union all
(select 
  2021 as year,
  t2.name as product_name,
  round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where extract(year from t1.created_at) = 2021
group by product_name
order by sum_sale_price desc
limit 1)
union all
(select 
  2022 as year,
  t2.name as product_name,
  round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items` t1
join `thelook_ecommerce.products` t2 on t1.product_id = t2.id
where extract(year from t1.created_at) = 2022
group by product_name
order by sum_sale_price desc
limit 1)
order by year

 

• 서브쿼리(Sub Query)

-왜 필요한가 ?

: JOIN을 대신해서 사용 (쉽게 코드를 짤 수 있는 장점이 있음)

-JOIN과 비교했을 때 단점은 ?

:

#1.where절 안에서 사용
#유저의 국가가 'Brasil'인 유저의 
#주문정보(orders)를 조회하는 쿼리

select *
from `thelook_ecommerce.orders`
where user_id in (
  select id
  from `thelook_ecommerce.users`
  where country = 'Brasil'
);

#2.from절 안에서 사용 (가상의 테이블을 하나 만듦)
select t1.id,
  t1.first_name,
  t1.last_name,
  t2.order_count as order_count
from `thelook_ecommerce.users` t1
left join (
    select user_id, count(order_id) as order_count 
    from `thelook_ecommerce.orders`
    group by user_id
  ) t2 on t1.id = t2.user_id
order by t1.id
limit 10;

#3.select절에서 사용 (from절에서 가져온 거랑 연결 시킬 수 있음)
select id,
  first_name,
  last_name,
  (select count(order_id) from `thelook_ecommerce.orders` where user_id = a.id) as order_count
from `thelook_ecommerce.users` a
order by a.id
limit 10;

#주문건이 3건 이상인 유저의 id와 이름을 조회한다.
#1)주문건이 3건 이상인 유저 id 조회
#2)이 아이디로 유저정보를 조회
select id,
  a.first_name,
  a.last_name,
  b.order_count as order_count
from `thelook_ecommerce.users` a
left join (
    select user_id, count(order_id) as order_count 
    from `thelook_ecommerce.orders`
    group by user_id
  ) b on a.id = b.user_id
order by a.id
limit 10;

 

• WITH (변수처럼 가상의 테이블을 만들어서 사용하는 것)

-WITH CTE명 AS (select ~), CTE명 AS (select ~)

-장점 : 리포트 만들기 위해서 raw data들을 가져오고, 정제한 다음에 연단위, 월단위, 일단위 집계를 내고, 다시 요약 데이터를 만들음 (각 단계에 해당하는 것을 CTE로 만들어서 작성할 수 있음)

-MySQL : 8.0부터 가능 (5.7은 안됨)

# 회원수가 4000명 이상인 국가명과 국가의 회원수
WITH many_user_country AS (select country,
  count(id) as user_count
from `thelook_ecommerce.users`
group by country
having user_count >= 4000)
select * from many_user_country
order by user_count desc
limit 3