멋사 AI스쿨_SQL_TIL (4)

2023.01.26

 

조건분기(CASE, IF)

 

● case

- case와 end가 한 쌍이다.

- when이 2번 이상 들어가면 첫번째 when은 if문의 역할이고 두번째부터 when 부터는 elif문의 역할을 한다.

# case
#    when 조건 then 참일 경우_실행구문
#    else 거짓일경우_실행구문
# end

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

 

 

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

SELECT 
    user_id,
    IF(COUNT(order_id) > 3, 'VIP', 'NORMAL') AS order_level,
    COUNT(order_id) as order_count
FROM `thelook_ecommerce.orders`
GROUP BY user_id
order by user_id DESC;

● 연습문제 

### SQL 연습문제
-- 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), 2) as Q1,
round(sum(case when extract(quarter from created_at)=2 then sale_price end), 2) as Q2,
round(sum(case when extract(quarter from created_at)=3 then sale_price end), 2) as Q3,
round(sum(case when extract(quarter from created_at)=4 then sale_price end), 2) as Q4,
round(sum(sale_price), 2) as Total
from `thelook_ecommerce.order_items`
where status = 'Complete'
group by year
order by year

 

 

 

JOIN

출처 : 위키피디아 - Join (SQL)

- 여러 테이블을 같이 합쳐서 조회하는 방법

- join의 종류를 표시 안 해주면 일반적으로 inner join으로 판단

INNER JOIN	교집합
FULL OUTER JOIN	합집합
LEFT JOIN	왼쪽 집합기준으로 조인
RIGHT JOIN	오른쪽 집합기준으로 조인

●  연습문제

 -- ### SQL 연습문제
  -- 주문상품(order_items) 테이블과 상품정보(products) 테이블을 이용하여 2022년 한해 주문한 상품들의 주문정보를 조회하시오.
  -- - 조회 항목 : 주문아이템ID(order_item_id), 주문ID(order_id), 주문일(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
  t1.id AS order_item_id,
  t1.order_id,
  FORMAT_DATE("%Y-%m-%d", t1.created_at) AS order_date,
  t2.name,
  t1.status,
  t2.cost,
  t2.retail_price,
  t2.category,
  t2.brand
FROM
  `thelook_ecommerce.order_items`t1
LEFT JOIN
  `thelook_ecommerce.products` t2
ON
  t1.product_id = t2.id
WHERE
  FORMAT_DATE("%Y-%m-%d", t1.created_at) BETWEEN "2022-01-01"
  AND "2022-12-31"
ORDER BY
  created_at ASC

 

● 데이터의 종류 

1) 트랜잭션 데이터 : 다양한 어플레케이션에서 일상적인 비즈니스 프로세스를 실행하거나 지원할 때 생성되는 데이터

 ex) 주문데이터

2) 마스터 테이터 : 트랙잭션에서 참고되는 각종 정보

  ex) 회원데이터, 상품데이터

3) 분석데이터 : 트랜잭션 데이터에 대한 계산 또는 분석을 통해 생성되는 데이터

  ex) 통계 데이터

 

● 비정규화

- 다 쪼개는 것을 보통 정규화

- 다 합치는 것을 보통 비정규화라고 한다. 

- 트랜젝션 데이터 + 마스터 결합시켜서 데이터마트를 만들어서 사용

 

 

❗️꿀팁  날짜포맷 비교하여 알아두기 1) BigQuery : SELECT FORMAT_DATE('%Y-%m-%d', DATE '2008-12-25') ;  2) MySQL : SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');