[TIL#7] 엑셀보다 쉽고 빠른 SQL(5)

1. 조회한 데이터에 사용할 수 없는 데이터가 있거나 아무 값도 없는 경우

1. 없는 값을 제외해주기
   • null값으로 바꾸거나 조건을 사용하여 연산에서 제외하기
     ex)
     select restaurant_name,
     avg(rating) average_of_rating,
     avg(if(rating<>'Not given', rating, null)) average_of_rating2
     from food_orders
     group by 1
2. 잘못된 값을 다른 값으로 대체해주기
   • 평균값, 중앙값 등의 대푯값을 이용하여 대체
   • 다른 값으로 변경하고 싶은 경우에는 다음 두 가지의 문법 사용 가능
     • 다른 값이 있을 때 조건문 이용하기: if(rating>=1,rating,대체값)
     • null값일 때: coalesce(age, 대체값)

2. 조회한 데이터가 상식적이지 않은 값을 가진 경우

주문 고객의 나이가 2세거나, 결제 일자가 1970년대로 나타나는 등 상식적이지 않은 값이 존재하는 경우에는

• 조건문으로 가장 큰 값과 가장 작은 값의 범위를 지정해줄 수 있음 (상식적인 수준 안에서 범위를 지정)
  ex)
  select customer_id, name, email, gender, age,
  case when age<15 then 15
  when age>80 then 80
  else age end "범위를 지정해준 age"
  from customers

3. Pivot Table

Pivot Table: 2개 이상의 기준으로 데이터를 집계할 때 보기 쉽게 배열하여 보여주는 것

예시)

SQL을 이용하여 피벗테이블 만들기
예시)

4. Window Function - Rank, Sum

• Window Funciton: 각 행의 관계를 정의하기 위한 함수로, 그룹 내 연산을 쉽게 만들어 줌
  - ex) 한식당 중 주문건수가 많은 순으로 순위를 매기고 싶을 때
  • ex) 한식당 전체 주문건수 중 A 식당이 차지하는 비율을 알고 싶을 때
• 기본 SQL 구조로 해결하려면 복잡한 subquery문을 이용하거나 여러번의 연산을 수행해야 하지만, window function이 자체적으로 제공하는 기능을 사용하면 더욱 편리함

  기본 구조
  window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
  - window function: sum, avg 등과 같은 기능명
  - argument: 그룹을 나누기 위한 기준 (group by와 유사)
  - order by: window function 적용 시 정렬할 컬럼 기준

• Rank: 특정 기준으로 순위를 매겨주는 기능
• Sum: 합계를 구하는 기능 -> 누적합이 필요하거나, 카테고리별 합계컬럼과 원본컬럼을 함께 이용할 때 유용하게 사용 가능함

5. 포맷 함수: 날짜 포맷과 조건

날짜 데이터

• 년(Y, y), 월(M, m), 일(d, e), 시, 분, 초 등
• 목적에 따라 월, 주, 일 등으로 포맷 변경 가능
• 요일(w)