TIL #3 < join(SQL), NULL처리, Pivot table, Window function>

<SQL 4주차 강의 요약2>

 

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기(join함수)

 

- join 함수는 두 테이블이 동일하게 갖고 있는 컬럼을 기준으로 두 테이블을 합친다.

  1) left join

     > A 테이블의 모든 데이터 + A테이블과 B테이블과 동시에 갖고 있는 데이터

   2) inner join

    > 두 테이블이 동시에 갖고 있는 데이터

 

- join 함수의 기본형

   > 테이블1 a(별칭) left join 테이블2 b(별칭) on a.공통컬럼=b.공통컬럼

 

<SQL 5주차 강의 요약>

 

1. 조회한 데이터에 값이 없을 때, 해결방법

    1) 없는 값 제외하기

        > 해당하는 값이 없을 때 연산에서 제외 --> 즉, 0으로 간주

             > rating의 데이터 값이 숫자 데이터가 아닌 'Not given'이라는 문자열 데이터가 들어있을 때, if 문을 사용하여 null                    값으로 바꿔 연산에서 제외시킴.

    2) null 값 대신 대체값 넣기

       > coalesce(컬럼, 대체값) ---> 컬럼 안의 데이터 중에 null값들을 대체값으로 바꿈.

 

2. SQL로 Pivot table 만들기

- Pivot table이란? 

   > 2개 이상의 기준으로 데이터를 정렬할 때, 배열하여 보여주는 것

 

- pivot table 만들기 예제

> 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

select restaurant_name,

          max(if(timing='15', ct, 0)) "15시",

          max(if(timing='16', ct, 0)) "16시",

          max(if(timing='17', ct, 0)) "17시",

          max(if(timing='18', ct, 0)) "18시",

          max(if(timing='19', ct, 0)) "19시",

          max(if(timing='20', ct, 0)) "20시"

from

(select a.restaurant_name,

substr(time,1,2) timing,

count(1) ct

from food_orders a inner join payments b on a.order_id=b.order_id

where substr(time, 1, 2) between 15 and 20

group by a.restaurant_name

) result

group by 1

order by 7 desc

 

## 열이 되는 컬럼은 max로 감싸줘야함

 

3. 업무시간을 단축시켜주는 문법

   - Window function

       > 윈도우 펑션의 기본 구조

       > 실습은 Rank함수와 Sum함수로 진행하였지만,  더 많은 기능들이 있음.

    

    1) Rank 함수

      예시) 음식 타입별로 주문수가 가장 많은 순서대로

               > rank() over(partition by cuisine_type order by order_count desc)

rank() over ---> 기본으로 써야함. 

partition ---> 그룹 기준 컬럼

order by ---> 정렬 기준

 

    2) SUM 함수

       > 누적값을 구할 때 유용함. 위와 같음

 

4. 날짜 데이터

    YYYY-MM-DD 형식의 컬럼을 날짜 data로 변경할 수 있음.

    > date(date)

       앞의 data는 함수명이며, 뒤의 date는 컬럼명임.

 

    > date_format을 이용하여 년 월 일 단위로 조회 가능