[Sql 분석] 유용한 함수

EXCEPT / REPLACE

---

• Execpt ('제외할 컬럼')
• Replace ('대체할 값' AS '컬럼명)

-- Execpt
select * except(shipping_limit_date,freight_value)
from `olist.olist_order_items`
limit 100;

-- Replace
select * replace(price * 10000 as price) -- price 컬럼을 가져올 때 10000을 곱한 값을 가져와
from `olist.olist_order_items`
limit 100;

CAST/ SAFE_CAST

---

괄호안의 값을 지정한 데이터 타입으로 변경해줌

• Cast ('값' as '데이터타입')
• Safe_cast ('값' as '데이터타입')

-- Cast 
select
cast('123' as int64), --데이터타입 변경
safe_cast('123' as int64) --위와 동일

--Safe_cast
select
cast('abc' as int64) --에러발생
safe_cast('abc' as int64) --null 값으로 대체

Safe의 활용

---

safe를 사용하면 오류가 나는 값을 모두 null 값으로 출력

• Safe_add(X,Y)
• Safe_subtract(X,Y)
• Safe_multiply(X,Y)
• Safe_divide(X,Y) -가장 많이 사용
  연산자 / 를 사용하여 나눌 때 0으로 나누면 생기는 오류를 해결할 수 있기 때문
  • ifnull() 함수를 이용해 null 값을 0 으로 바꿔줄 수도 있음

-- DIVIDE & SAFE_DIVIDE

SELECT
	DIVIDE(5 , 0), --오류 발생
  SAFE_DIVIDE(5 , 0), -- 오류값 null값으로 출력
  IFNULL(SAFE_DIVIDE(5 , 0), 0)
;

ROW_NUMBER / RANK / DENSE_RANK

---

• ROW_NUMBER : 공동순위 X
• RANK : 공동순위 O, 공동순위의 수만큼 밀려서 다음 순위 책정
• DENSE_RANK : 공동순위 O, 연속적 순위

점수	ROW_NUMBER	RANK	DENSE_RANK
100	1	1	1
95	2	2	2
95	3	2	2
80	4	4	3

• OVER (PARTITION BY 구분 기준 ORDER BY 순위를 매기는 조건)
• ORDER BY는 무조건 쓰여야함
• PATITION BY는 순위 구분하고 싶은 기준이 또 있을 때 사용

SELECT 
  name, 
  class, 
  score,
  
  -- ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
  -- RANK() OVER (ORDER BY score DESC) as row_rank,
  -- DENSE_RANK() OVER (ORDER BY score DESC) as row_dense_rank,

  
  ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) as row_num,
  RANK() OVER (PARTITION BY class ORDER BY score DESC) as row_rank,
  DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) as row_dense_rank,
  
FROM tb
-- ORDER BY row_num
ORDER BY class
;

CASE

---

• CASE
  WHEN 조건1 THEN 조건1 해당시 결과
  WHEN 조건2 THEN 조건2 해당시 결과
  ELSE 나머지 결과
  END