[240919] SQL 사전강의 (~5강)

과제에서 필수 사용되는 문법 요약

• [KDC] 엑셀보다 쉽고 빠른 SQL - 5주차

  • 02. 조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

    • 1) 데이터가 잘못 기입된 예시

      1. 테이블에 잘못된 값이 입력된 경우,

         

      2. NULL 값으로 조회될 경우, (Join 했을 때와 같이)

         

    • 2) [방법1] 없는 값을 제외해주기

      • Mysql : 사용할 수 없는 값일 때 해당 값을 연산에서 제외→ 0으로 간주

      • Ex.) 음식점별 평균 rating 구할 때

        Select restaurant_name,
        			 avg(rating) avg1
        			 avg(if(rating<>'Not given', rating, null)) avg2
        from food_orders
        group by 1

        

        • avg1 : Not given → 0으로 치환하여 평균 계산
        • avg2 : Not given 레코드 제외 후 평균 계산

      • Ex.) Join 시, NULL 값 제외하는 방법

        • left join 시 NULL 값이 존재할 경우,

          Select *
          from food_orders fo
          left join customer c on fo.customer_id = c.customer_id
          where c.customer_id is not null;

          ⇒ 위와 같이 NULL 제거 시, inner join 과 동일하게 결과 조회 가능
          

    • 3) [방법2] 다른 값을 대신 사용하기

      • Null 값을 제거하는 게 아닌, 다른 값으로 대체할 경우
      • coalesce(컬럼, 대체값) 사용

        Select *,
        			 coalesce(c.age, 20) as "NULL 제거"
        from food_orders fo
        left join customer c on fo.customer_id = c.customer_id;

  • 03. 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

    • 1) 상식적이지 않은 데이터의 예시

      • 데이터가 비어있는 경우가 아닌, 상식적이지 않은 경우도 있습니다.
      • 케이스1 - 주문 고객의 나이
        • 주문 고객의 나이가 2세, 91세 등인 경우
      • 케이스2 - 결제 일자
        • 결제일자가 1970년대인 경우

    • 2) [방법] 조건문으로 값의 범위를 지정하기

      • 조건문으로 상식적인 범위 내의 값으로 지정
      • Ex.) 나이

        select *,
        			 case when age < 15 then 15
        						when age > 80 then 80
        						else age end "범위 조정 age"
        from customers ;

  • 04. [실습] SQL 로 Pivot Table 만들어보기

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

       1. 음식점별, 시간별 주문건수 집계하기
          • 정답

            select a.restaurant_name,
                   substring(b.time, 1, 2) hh,
                   count(1) cnt_order
            from food_orders a inner join payments b on a.order_id=b.order_id
            where substring(b.time, 1, 2) between 15 and 20
            group by 1, 2

       2. Pivot view 구조 만들기
          • [코드스니펫] Pivot Table 예시

            select restaurant_name,
                   max(if(hh='15', cnt_order, 0)) "15",
                   max(if(hh='16', cnt_order, 0)) "16",
                   max(if(hh='17', cnt_order, 0)) "17",
                   max(if(hh='18', cnt_order, 0)) "18",
                   max(if(hh='19', cnt_order, 0)) "19",
                   max(if(hh='20', cnt_order, 0)) "20"
            from 
            (
            select a.restaurant_name,
                   substring(b.time, 1, 2) hh,
                   count(1) cnt_order
            from food_orders a inner join payments b on a.order_id=b.order_id
            where substring(b.time, 1, 2) between 15 and 20
            group by 1, 2
            ) a
            group by 1
            order by 7 desc

       

    2. [실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

       • 행 - age / 열 - gender / 값 - Count(*)

         

         select age_range,
                max(if(gender='male', cnt_orders, 0)) male,
                max(if(gender='female', cnt_orders, 0)) female
         from 
         (
         select b.gender,
                case when age between 10 and 19 then 10
                     when age between 20 and 29 then 20
                     when age between 30 and 39 then 30
                     when age between 40 and 49 then 40
                     when age between 50 and 59 then 50 end age_range,
                count(1) cnt_orders
         from food_orders fo inner join customers c on fo.customer_id=c.customer_id
         where c.age between 10 and 59
         group by 1, 2
         ) a
         group by 1
         order by 1

       • 행 - gender/ 열 - age/ 값 - Count(*)

         

         SELECT gender,
         	   max(if(age_range = 10, cnt_orders, 0)) "10대",
         	   max(if(age_range = 20, cnt_orders, 0)) "20대",
         	   max(if(age_range = 30, cnt_orders, 0)) "30대",
         	   max(if(age_range = 40, cnt_orders, 0)) "40대",
         	   max(if(age_range = 50, cnt_orders, 0)) "50대"
         FROM 
         (SELECT c.gender,
         	   case when c.age between 10 and 19 then 10
         	   		when c.age BETWEEN 20 and 29 then 20
         	   		when c.age BETWEEN 30 and 39 then 30
         	   		when c.age BETWEEN 40 and 49 then 40
         	   		when c.age BETWEEN 50 and 59 then 50
         	   		end as age_range,
         	   count(*) as cnt_orders
         from food_orders fo 
         inner join customers c on fo.customer_id = c.customer_id 
         where c.age BETWEEN 10 and 59
         group by 1,2
         ) a
         group by 1
         order by 1 desc;

  • 05. 업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)

    • 1) Window Function 의 사례와 기본 구조
      • Window Function 은 각 행의 관계를 정의하기 위한 함수 ⇒ 그룹 내의 연산을 쉽게 만듦
      • 기본 구조

        window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

        • window_function : 기능명 (ex. sum, avg)
        • argument : 함수에 따라 작성 or 생략
        • partition by : 그룹을 나누기 위한 기준 (group by 절과 유사)
        • order by : window function 을 적용할 때 정렬할 컬럼 기준
      • 예시
        • 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶은데요, 가능할까요?
        • 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶은데 가능할까요?
        • 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 수 있을까요?
    • 2) [실습1] N 번째까지의 대상을 조회하고 싶을 때, Rank
      • [실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

        select *
        from
        (select *,
        			 rank() over (partition by cuisine_type order by cnt_orders desc) ranking
        from
        (select cuisine_type,
        			 restaurant_name,
        			 count(*) cnt_orders
        from food_orders
        group by 1,2
        ) a
        ) b
        where ranking <= 3

        • partition by cuisine_type
          • cuisine_type별 나눠라
        • order by cnt_orders desc
          • cnt_orders 가 많은 순서대로 정렬해라
        • rank() over (partition by cuisine_type order by cnt_orders desc)
          • cuisine_type 별로 나누고, cnt_orders 많은 순서대로 정렬한 다음 순위를 매겨라
        • where ranking ≤ 3
          • 각 cuisine_type별 ranking 3위까지만 조회해라
    • 3) [실습2] 전체에서 차지하는 비율, 누적합을 구할 때, Sum
      • [실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

        SELECT *,
        	   sum(cnt_orders) over (partition by cuisine_type) sum_cuisine,
        	   sum(cnt_orders) over (partition by cuisine_type order by cnt_orders, restaurant_name asc) cum_cuisine
        FROM 
        (select cuisine_type,
        			 restaurant_name,
        			 count(*) cnt_orders
        from food_orders
        group by 1,2
        ) a
        order by cuisine_type, cnt_orders, cum_cuisine;

        • sum(cnt_orders) over (partition by cuisine_type)
          • cuisine_type별로 나누고 정렬 순서는 따로 없으니까 cnt_orders를 모두 더해라
        • sum(cnt_orders) over (partition by cuisine_type order by cnt_orders, restaurant_name asc)
          • cuisine_type별로 나누고 cnt_orders, restaurant_name 순서대로 정렬한 다음 행마다 cnt_orders를 모두 더해라
• 6. 날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)

  • 1) [실습1] 날짜 데이터의 여러 포맷

    1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

       select date(date) date_type,
              date
       from payments

       

    2. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

       1. 년
          1. Y : 4자리
          2. y : 2자리
       2. 월
          1. M : 영문월
          2. m : 2자리
       3. 일
          1. d : 2자리
          2. e : 1자리
       4. 요일 : w
          1. 일요일 : 0
          2. 토요일 : 6

       

       select date(date) date_type,
              date_format(date(date), '%Y') "년",
              date_format(date(date), '%m') "월",
              date_format(date(date), '%d') "일",
              date_format(date(date), '%w') "요일"
       from payments

  • 2) [실습2]

    1. 년도, 월을 포함하여 데이터 가공하기

       select date_format(date(date), '%Y') y,
              date_format(date(date), '%m') m,
              order_id
       from food_orders a inner join payments b on a.order_id=b.order_id

    2. 년도, 월별 주문건수 구하기

       select date_format(date(date), '%Y') y,
              date_format(date(date), '%m') m,
              count(1) order_count
       from food_orders a inner join payments b on a.order_id=b.order_id
       group by 1, 2

    3. 3월 조건으로 지정하고, 년도별로 정렬하기

       

         select date_format(date(date), '%Y') y,
                date_format(date(date), '%m') m,
                count(1) order_count
         from food_orders a inner join payments b on a.order_id=b.order_id
         where date_format(date(date), '%m')='03'
         group by 1, 2
         order by 1
         

• HW. 5주차 숙제 해설

  💡음식 타입별, 연령별 주문건수 pivot view 만들기

  SELECT cuisine_type,
  	   MAX(if(age = 10,cnt_orders,0)) '10대',
  	   MAX(if(age = 20,cnt_orders,0)) '20대',
  	   MAX(if(age = 30,cnt_orders,0)) '30대',
  	   MAX(if(age = 40,cnt_orders,0)) '40대',
  	   MAX(if(age = 50,cnt_orders,0)) '50대',
  	   MAX(if(age = 60,cnt_orders,0)) '60대 이상'
  FROM 
  (SELECT fo.cuisine_type,
  	    case when c.age < 20 then 10
  	   		 when c.age between 20 and 29 then 20
  	   		 when c.age between 30 and 39 then 30
  	   		 when c.age between 40 and 49 then 40
  	   		 when c.age between 50 and 59 then 50
  	   		 else 60 end age,
  	    COUNT(*) as cnt_orders 
  from food_orders fo 
  inner join customers c on fo.customer_id = c.customer_id
  group by 1,2
  ) a
  group by 1;

  • 내가 쓴 코드

    select cuisine_type,
    	   MAX(IF(age_range = 10, tot, 0)) '10대',
    	   MAX(if(age_range = 20, tot, 0)) '20대',
    	   MAX(if(age_range = 30,tot,0)) '30대',
    	   MAX(if(age_range = 40,tot,0)) '40대',
    	   MAX(if(age_range = 50,tot,0)) '50대',
    	   MAX(if(age_range = 60,tot,0)) '60대 이상'	   
    from
    (select cuisine_type,
    	   case when age < 20 then 10
    	   		 when age between 20 and 29 then 20
    	   		 when age between 30 and 39 then 30
    	   		 when age between 40 and 49 then 40
    	   		 when age between 50 and 59 then 50
    	   else 60 end age_range,
    	   SUM(cnt_orders) as tot
    from
    (select fo.cuisine_type,
    	   c.age,
    	   count(*) as cnt_orders
    from food_orders fo 
    inner join customers c on fo.customer_id = c.customer_id 
    group by 1,2
    order by 1 desc,2
    ) a
    group by 1,2
    ) b
    group by 1;

오답노트

• Pivot table View 를 만들 때, Max 함수가 필요한 이유

  • 행 - age / 열 - gender / 값 - Count(*)

    

    select age_range,
           max(if(gender='male', cnt_orders, 0)) male,
           max(if(gender='female', cnt_orders, 0)) female
    from 
    (
    select b.gender,
           case when age between 10 and 19 then 10
                when age between 20 and 29 then 20
                when age between 30 and 39 then 30
                when age between 40 and 49 then 40
                when age between 50 and 59 then 50 end age_range,
           count(1) cnt_orders
    from food_orders fo inner join customers c on fo.customer_id=c.customer_id
    where c.age between 10 and 59
    group by 1, 2
    ) a
    group by 1
    order by 1

    ⇒ 위와 같은 Pivot Table을 만든다고 가정할 때,

    Max 이전에 10대의 IF 함수값은 아래와 같이 반환된다.

    age	male	female
    10	(84, 0)	(0, 94)

    이 때, 두 값 중 하나의 값을 반환하여 Pivot table 형식처럼 나타내려면

    각 열에 max 함수를 통해 둘 중 더 큰 값을 반환함으로써,

    10대 행의 남자, 여자 컬럼값을 각각 반환할 수 있다.
    

• Window Function 해석 방법

  1. Partition by : 먼저 특정 컬럼 기준으로 크게 나눠라
  2. Order by : 특정 컬럼으로 정렬해라
  3. Window Fuction : 행마다 해당 기능 실시해라
  • [실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

    SELECT *,
    	   sum(cnt_orders) over (partition by cuisine_type) sum_cuisine,
    	   sum(cnt_orders) over (partition by cuisine_type order by cnt_orders, restaurant_name asc) cum_cuisine
    FROM 
    (select cuisine_type,
    			 restaurant_name,
    			 count(*) cnt_orders
    from food_orders
    group by 1,2
    ) a
    order by cuisine_type, cnt_orders, cum_cuisine;

    ⇒ 아래 쿼리의 경우,

    sum(cnt_orders) over (partition by cuisine_type order by cnt_orders, restaurant_name asc) cum_cuisine

    1. cuisine_type별로 크게 나눠라
    2. cnt_orders, restaurant_name 순서대로 정렬한 다음
       • restaurant_name 있어야 하는 이유
         • cnt_orders 로만 나누면 더 구체적인 기준이 없기 때문에 동일한 값들로 인식하여 행마다가 아닌, 같은 값마다의 cnt_orders를 더함. ⇒ 작은 단위의 partition by
           
         • 더 구체적인 기준 (음식점명)을 세워서 각자 다른 값으로 정렬시켜 모두 다른 행임을 인식시켜야 함.
    3. 행마다 cnt_orders를 모두 더해라 (SUM)

• Group by 를 통해 Count 된 값을 다시 Subquery로 가져와서 범주로 나누려면 Count(*) 가 아닌 Sum()으로 더해야 함.

  💡음식 타입별, 연령별 주문건수 pivot view 만들기

  SELECT cuisine_type,
  	   MAX(if(age = 10,cnt_orders,0)) '10대',
  	   MAX(if(age = 20,cnt_orders,0)) '20대',
  	   MAX(if(age = 30,cnt_orders,0)) '30대',
  	   MAX(if(age = 40,cnt_orders,0)) '40대',
  	   MAX(if(age = 50,cnt_orders,0)) '50대',
  	   MAX(if(age = 60,cnt_orders,0)) '60대 이상'
  FROM 
  (SELECT fo.cuisine_type,
  	    case when c.age < 20 then 10
  	   		 when c.age between 20 and 29 then 20
  	   		 when c.age between 30 and 39 then 30
  	   		 when c.age between 40 and 49 then 40
  	   		 when c.age between 50 and 59 then 50
  	   		 else 60 end age,
  	    COUNT(*) as cnt_orders 
  from food_orders fo 
  inner join customers c on fo.customer_id = c.customer_id
  group by 1,2
  ) a
  group by 1;

  • 내가 쓴 코드

    select cuisine_type,
    	   MAX(IF(age_range = 10, tot, 0)) '10대',
    	   MAX(if(age_range = 20, tot, 0)) '20대',
    	   MAX(if(age_range = 30,tot,0)) '30대',
    	   MAX(if(age_range = 40,tot,0)) '40대',
    	   MAX(if(age_range = 50,tot,0)) '50대',
    	   MAX(if(age_range = 60,tot,0)) '60대 이상'	   
    from
    (select cuisine_type,
    	   case when age < 20 then 10
    	   		 when age between 20 and 29 then 20
    	   		 when age between 30 and 39 then 30
    	   		 when age between 40 and 49 then 40
    	   		 when age between 50 and 59 then 50
    	   else 60 end age_range,
    	   SUM(cnt_orders) as tot
    /* 위에서 Count(*) 를 다시 쓰게 되면 raw data의 값을 나이대에 따라 Count 하는 것이 아닌
     그저 나이 데이터가 몇개인지 셀 수 밖에 없음 */
    from
    (select fo.cuisine_type,
    	   c.age,
    	   count(*) as cnt_orders
    from food_orders fo 
    inner join customers c on fo.customer_id = c.customer_id 
    group by 1,2
    order by 1 desc,2
    ) a
    group by 1,2
    ) b
    group by 1;