[AI School-TIL] SQL(BigQuery) 4일차_23.01.26

 

[오늘 진도]

조건문

• case / end

-조건을 시작하고, 닫는 것을 먼저 해놓는 습관을 들이는 게 좋다.

• when then / else

select 
  order_id,
  user_id,
  status,
  case
    when status = 'Shipped' then '배송됨'
    when status = 'Complete' then '완료됨'
    when status = 'Returned' then '반품됨'
    when status = 'Cancelled' then '취소됨'
    when status = 'Processing' then '처리중'
    else '기타' 
  end as status_text
from `thelook_ecommerce.orders`
order by order_id

# cost 20달러 이하 : 저비용
# 20달러 초과 50달러 이하 : 중비용
# 50달러 이상 : 고비용
# 없음
select
  id,
  cost,
  case
    when cost <= 20 then '저비용'
    when cost <= 50 then '중비용'
    when cost > 50 then '고비용'
    else '없음'
  end as cost_level
from `thelook_ecommerce.products`

 

cf) count 함수는 null이 아닌 값만 집계한다.

count(null) 은 0으로 결과가 나옴

#연도별 남,녀 가입자수
select
  extract(year from created_at) as year,
  count(case when gender = 'F' then gender end) as female,
  count(case when gender = 'M' then gender end) as male,
  count(id) as total
from `thelook_ecommerce.users`
group by year;

#분기별 회원가입자수
select 
  id,
  created_at,
  EXTRACT(year FROM created_at) as year,
  EXTRACT(quarter FROM created_at) as quarter
from `thelook_ecommerce.users`
order by id

select 
  EXTRACT(YEAR FROM created_at) as year,
  count(CASE WHEN EXTRACT(QUARTER FROM created_at) = 1 THEN id END) as Q1,
  count(CASE WHEN EXTRACT(QUARTER FROM created_at) = 2 THEN id END) as Q2,
  count(CASE WHEN EXTRACT(QUARTER FROM created_at) = 3 THEN id END) as Q3,
  count(CASE WHEN EXTRACT(QUARTER FROM created_at) = 4 THEN id END) as Q4,
  count(id) as TOTAL
from `thelook_ecommerce.users`
group by year
order by year;

#월별 가입자수
select 
  EXTRACT(YEAR FROM created_at) as year,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 1 THEN id END) as M1,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 2 THEN id END) as M2,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 3 THEN id END) as M3,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 4 THEN id END) as M4,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 5 THEN id END) as M5,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 6 THEN id END) as M6,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 7 THEN id END) as M7,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 8 THEN id END) as M8,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 9 THEN id END) as M9,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 10 THEN id END) as M10,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 11 THEN id END) as M11,
  count(CASE WHEN EXTRACT(MONTH FROM created_at) = 12 THEN id END) as M12,
  count(id) as TOTAL
from `thelook_ecommerce.users`
group by year
order by year
#for문으로 반복시키면 좋을텐데...

 

• if(조건문, 참일 때 출력값, 거짓일 때 출력값)

#예시
select if(true, '참', '거짓');
select if(false, '참', '거짓');
select if(true, 'TEXT1', null);
select if(false, 'TEXT1', null);
select if(1=1, '참', '거짓');
select if(1=2, '참', '거짓');

#비용
select
  id,
  name,
  cost,
  if(cost <= 50, '저비용', '고비용') as cost_level
from `thelook_ecommerce.products`
order by id

#주문횟수가 4회 이상이면 VIP, 아니면 NORMAL

 

#연습문제 7
### SQL 연습문제 7-1
#주문정보(orders) 테이블에서 order_id, gender, gender_label(gender의 값에 따른 성별을 한글로 표시)을 해주세요.
#- 필드명 : gender_label
#    - gender가 F 이면 '여성'
#    - gender가 M 이면 '남성'
#- 결과로 표시할 필드
#    - order_id
#    - gender
#    - gender_label
#- 정렬순서 : order_id 오름차순

select 
  order_id,
  gender,
  CASE
    WHEN gender = 'F' THEN '여성'
    WHEN gender = 'M' THEN '남성'
    ELSE '없음'
  END as gender_label
from `thelook_ecommerce.orders`
order by order_id

### SQL 연습문제 7-2
#회원(users) 테이블에서 다음 정보를 조회하세요.
#1) 조회 항목
#- 유저아이디 - id,
#- 가입연도 - year
#- 가입월 - month
#- 가입일 - day
#- 이용경로(traffic_source) 한글 텍스트 -  traffic_source_label
    - Search → 검색엔진
    - Organic → 검색결과
    - Email → 이메일
    - Display → 디스플레이 광고
    - Facebook → 페이스북
#2) 정렬순서 : id 오름차순
select
  id,
  extract(year from created_at) as year,
  extract(month from created_at) as month,
  extract(day from created_at) as day,
  case
    when traffic_source = 'Search' then '검색엔진'
    when traffic_source = 'Organic' then '검색결과'
    when traffic_source = 'Email' then '이메일'
    when traffic_source = 'Display' then '디스플레이 광고'
    when traffic_source = 'Facebook' then '페이스북'
  end as traffic_source_label
from `thelook_ecommerce.users`
order by id
# dictionary로 key, value 지정해서 가져오면 좋을텐데...

 

### SQL 연습문제 7-3
#회원(users) 테이블에서 가입연도별 이용경로(traffic_source)별 가입자수를 조회하세요.
#조회 항목
#- year
#- Search
#- Organic
#- Email
#- Display
#- Facebook
#- Total
#정렬순서 : year 오름차순

select 
  extract(year from created_at) as `year`,
  count(case when traffic_source = 'Search' then id end) as `Search`,
  count(case when traffic_source = 'Organic' then id end) as `Organic`,
  count(case when traffic_source = 'Email' then id end) as `Email`,
  count(case when traffic_source = 'Display' then id end) as `Display`,
  count(case when traffic_source = 'Facebook' then id end) as `Facebook`,
  count(id) as `Total`
from `thelook_ecommerce.users`
group by year
order by year

### SQL 연습문제 7-4
# 주문정보(orders) 테이블에서 주문을 3번 이상 구매한 사람의 등급을 ‘Gold’, 
# 4번 이상 구매한 사람의 등급은 ‘VIP’, 그 외에는 'Silver'라고 등급을 지정해 줍니다.

select user_id,
  count(order_id) as order_count,
  case
    when count(order_id) >= 4 then 'VIP'
    when count(order_id) >= 3 then 'Gold'
    else 'Silver'
  end as Grade
from `thelook_ecommerce.orders`
group by user_id
order by user_id

### SQL 연습문제 7-5
#상품정보(products) 테이블에서 상품의 id, 상품명(name), 사이즈(size)라는 컬럼을 하나 만들도록 하겠습니다.
#상품명 맨 끝에 ‘XS’, ‘S’, ‘M’, ‘L’, ‘XL’, ‘XXL’ 라고 적혀져 있습니다. 
#사이즈 컬럼에 맞는 값을 저장하고 조건에 부합하지 않는 경우에는 NULL 값을 넣어줍니다.
#정렬순서 : size 내림차순
select
  id,
  name,
  case
    when name like '%XXL' then 'XXL'
    when name like '%XL' then 'XL'
    when name like '%XS' then 'XS'
    when name like '%L' then 'L'
    when name like '%M' then 'M'
    when name like '%S' then 'S'
    else NULL
  end as size
from `thelook_ecommerce.products`
order by size desc
#조건 순서 주의하기

### SQL 연습문제 7-6
#각 연도의 분기별 매출 합계
#order_items 테이블에서 각 연도의 분기별 매출합계을 표시하세요.
#order_items의 status가 Complete 인 항목만 포함합니다.
#표시항목
#- year
#- quarter
#- sum_sale_price - 소수점 2자리 반올림
#정렬 순서 : year 오름차순, quarter 오름차순

select 
  extract(year from created_at) as year,
  extract(quarter from created_at) as quarter,
  round(sum(sale_price), 2) as sum_sale_price
from `thelook_ecommerce.order_items`
where status = 'Complete'
group by year, quarter
order by year, quarter

### SQL 연습문제 7-7
#order_items 테이블에서 각 연도의 분기별 매출을 가로로 펼쳐서 표시하세요.
#order_items의 status가 Complete 인 항목만 포함합니다.
#각 쿼터별 매출합계는 소수점 2자리까지 반올림하여 표시합니다.
#표시 항목
#- YEAR
#- Q1
#- Q2
#- Q3
#- Q4
#- TOTAL
#정렬순서 :YEAR

select 
  extract(year from created_at) as YEAR,
  round(sum(case when extract(quarter from created_at) = 1 then sale_price end)) as Q1,
  round(sum(case when extract(quarter from created_at) = 2 then sale_price end)) as Q2,
  round(sum(case when extract(quarter from created_at) = 3 then sale_price end)) as Q3,
  round(sum(case when extract(quarter from created_at) = 4 then sale_price end)) as Q4,
  round(sum(sale_price), 2) as TOTAL
from `thelook_ecommerce.order_items`
where status = 'Complete'
group by YEAR
order by YEAR

 

#프로그래머스 문제

#프로그래머스 문제 7-1 (level 2)
#중성화 여부 파악하기
SELECT 
    ANIMAL_ID,
    NAME,
    CASE
        WHEN SEX_UPON_INTAKE LIKE '%Neutered%' THEN 'O'
        WHEN SEX_UPON_INTAKE LIKE '%Spayed%' THEN 'O'
        ELSE 'X'
    END as '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

### 프로그래머스 문제 7-2 (level 3)
#조건별로 분류하여 주문상태 출력하기
SELECT 
 ORDER_ID,
 PRODUCT_ID,
 DATE_FORMAT(OUT_DATE, '%Y-%m-%d') AS OUT_DATE,
 CASE
    WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
    WHEN OUT_DATE > '2022-05-01'THEN '출고대기'
    ELSE '출고미정'
 END AS '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_ID

 

JOIN

예를 들어,

상품명이나 유저 정보처럼 변경될 수 있는 경우, 보통으로 중복을 방지하기 위해서

테이블을 나눠서 이용함.

고유값을 key값으로 해서 PK라고 부름.

보통 inner join, left join을 많이 씀.

cross join은 잘 안 씀 (레코드가 너무 많으면 cross join된 건 아닌지 검토해보기)

• inner join : A와 B에 둘 다 있는 것을 보여줌
• left join : A(left)에 있는 것 다 보여주고, B를 채움 (없으면 null로 들어감)
• right join 
• full outer join 

select a.order_id,
  a.product_id,
  b.name
from `weniv.weniv_order` a
join `weniv.weniv_product` b
on a.product_id = b.id 

#naming은 a,b로 하기도 하고
#t1, t2로 하기도 하고 : 추후에 5~6개씩 join하는 경우도 있어서 숫자붙여서 구분
#모음빼고 자음으로만 축약하기도 하고

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

# 2. left join
select *
from `weniv.weniv_order` t1
left join `weniv.weniv_user`t2 on t1.user_id = t2.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

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
#필터, 정렬은 t1, t2, t3에 있는 필드 모두 사용 가능

# 3. right join

# 4. full outer join

# 5. cross 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
where t2.city ='Jeju'
order by t3.name

select
  t1.order_id,
  t1.user_id,
  t1.created_at,
  t1.num_of_item,
  t1.product_id,
  t2.name as user_name,
  t2.city as user_city,
  t2.postal_code as user_postal_code,
  t3.name as product_name,
  t3.cost as 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
#빅쿼리 테이블로 별도 저장 해놓기

 

• 하드 삭제, 소프트 삭제

-주문정보 ID 10 -> 회원정보 ID 10 (하드 삭제 : 실제 모든 데이터 지우는 거)

-회원정보 USE_YN = 'N', DEL_YN = 'N' (소프트 삭제)

 

#연습문제 8
### SQL 연습문제 8-1
#회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 모든 주문내역에 회원정보를 표시하세요.
#조회 항목 : 주문ID(order_id), 주문한 상품 수량(num_of_item), 회원 이름(first_name, last_name), 
#주소(street_address), 우편번호(postal_code), 도시(city), 국가(country)

select 
  t1.order_id,
  t1.num_of_item,
  concat(t2.first_name, ' ', t2.last_name) as user_name,
  t2.street_address,
  t2.postal_code,
  t2.city,
  t2.country
from `thelook_ecommerce.orders` t1
left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id

### SQL 연습문제 8-2
#회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 상품을 주문한 회원의 국가가 ‘United States’이면서
#주문 상태가 처리중(Processing)인 정보를 조회하시오. 
#조회 항목
#    - 주문ID(order_id)
#    - 회원 이름(first_name, last_name)
#    - 주소(street_address)
#    - 우편번호(postal_code)
#    - 도시(city)
#    - 국가(country)
#    - 주문한 상품 수량(num_of_item)
#- 조건 : 국가가 ‘United States’이면서 주문 상태가 처리중(Processing)

select 
  t1.order_id,
  t2.first_name || ' ' ||t2.last_name as user_name,
  t2.street_address,
  t2.postal_code,
  t2.city,
  t2.country,
  t1.num_of_item
from `thelook_ecommerce.orders` t1
join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
where t2.country = 'United States'
and t1.status = 'Processing'

### SQL 연습문제 8-3
#회원(users) 테이블과 주문정보(orders) 테이블을 이용하여 국가별 총 상품 주문주(total_order_count)을 조회.
#- 조회 항목 : 국가명(country), 국가별 총 상품 주문주(total_order_count)
#- 정렬 : 국가별 총 상품 주문주(total_order_count)이 많은 순으로 정렬
select 
  t2.country,
  count(order_id) as total_order_count
from `thelook_ecommerce.orders` t1
left join `thelook_ecommerce.users` t2 on t1.user_id = t2.id
group by country
order by total_order_count desc