SQL 함수 정리 - Subquery / JOIN / Pivot Table / Window 함수 / 날짜 함수

 

 

---

주요 내용 정리하기

○ Subquery

  -  Query 안에 Sub로 들어간 구문으로 하나의 쿼리문으로 동시에 여러가지 연산 진행 가능

  -  Select / From 은 기본으로 작성해주고, Sub Query는 시작과 끝에 ()로 묶어서 이름 'a' 붙여주기

  [실습] 음식점의 평균 단가별 Segmentation, 그룹에 따라 수수료를 연산하라

 

  >  음식점의 평균 단가 나눠서 Segmentation까지 Subquery로 지정 

  >  Segmentation에 따라 총 3단계 수수료 산정 

 

 

  [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점 별 수수료율 산정하기 

 

  > Subquery : 총 주문수량 sum(quantity), 음식점 수 count(distinct restaurant_name)    ※ distinct 문자 갯수 세기

  > Query : Case When 활용하여 작성 

 

○ JOIN

  -  두개의 다른 테이블에 있는 데이터를 모두 합쳐서 사용

  -  엑셀의 VLOOKUP과 비슷한 함수 (두 테이블이 공통 컬럼을 기준으로 결합)

 

  -  LEFT JOIN : 데이터가 비더라도 비워놓고 불러오는 함수

  -  INNER JOIN : 반드시 모두 다 해당해야 하는 함수 (공백없음) 

 [기본구문]

  SELECT *

  FROM food_orders a INNER JOIN customers c on a.customer_id = c.customer_id

 

 

○ 조회한 데이터 값이 없다면 

•   [방법 1] 데이터 제외하기 
    SELECT avg ( if(rating <> 'Not given', rating, null) )
     → 만약 rating이 'Not given'이라고 되어 있지 않으면 rating 값 그대로, 되어 있다면 null 값을 주어라
  
  
•  [방법 2] 없는 데이터 제외하고 불러오기
   WHERE b.customer_id is no null 
    → customer id가 null이 아닌 값만을 불러와라
•  [방법 3] null 대신 다른 값으로 대체하여 사용하기
   coalesce(age, 20) 
    → age가 비어있다면 20으로 대체해줘
  
  

○ 상식적이지 않은 값을 가지고 있을 때

•   조건문을 활용하여 범위를 지정해주기
    ex) 나이가 15세보다 적다면 15세로, 80세 이상은 80으로 통일
          case when age < 15 then 15
                  when age >= 80 then 80
                  else age end 

 

○ Pivot Table 만들기 

•  ① 지문에서 먼저 구할 수 있는 Base Data 먼저 만들어서 Sub Query로 넣고
•  ② Select에 순서대로 넣기
       - 가장 왼편에 들어갈 컬럼  ex) restaurant_name
       - 상단 조건에 들어갈 것 
          max ( if(___조건____, _____, 0) )   "(상단명칭)" 

 

 

○  Window Function : 구간을 나눠서 각각 데이터를 처리하는 함수 

•  Rank, Sum
  ex) 한식 음식점 별로 랭킹을 정해줘 / 한식 음식점 별로 주문건수 더해줘

Rank : 순위매기는 함수 

>>  rank () over ( partition by ___A___ order by __B___ desc )

      랭킹을 매겨줘 / __ A___ 별로 / ___B__을 기준으로 

 

Sum : 합계 혹은 누적합계를 구하는 함수 

① 음식 종류에 따른 주문건수의 합계를 구해줘
>>  sum(cnt_order)  over (partition by cuisine_type) 

② 음식 종류에 따른 주문건수의 누적합계를 구해줘
>>  sum(cnt_order)  over (partition by cuisine_type order by)

 

 

○  날짜 함수 

•  date(date)
   문자로 적힌 날짜를 날짜 데이터로 변경하는 함수 
• 년/월/일 별로 추출 시 date_format( )
  >> date_format( date(date), '%Y' ) "년"
  >> date_format( date(date), '%m') "월) 

 

 

 

 

---

느낀 점 

내일배움캠프가 시작되는 첫 날, 긴장했는지 밤잠을 설쳤다. 사전캠프에 미리 준비를 했으면 바로 미니 프로젝트를 준비해볼 수 있었을 텐데  조금은 아쉽지만 그래도 이론을 탄탄히 쌓아놔야 나중에 무너지지 않을거라 믿는다 이번주는 그래서 강의를 최대한 빨리 완강하고, 한번 더 복습까지 했으면 한다.  그리고 유사 프로젝트 사례를 다수 찾아볼 예정이다.  내일은 데이터분석 강의를 최대한 많이 들어야겠다.