스파르타 코딩클럽(부트캠프) SQL 입문 5장

조회한 데이터에 값이 없을 경우

 

없는 값을 제외해준다.

select restaurant_name,
           avg(rating) average_of_rating,
           avg(if(rating<>'Not given', rating, null)) average_of_rating2
from food_orders
group by1

출처 : 스파르타 코딩클럽

select restaurant_name,

           avg(rating) avg_rating,

           avg(if(rating<>'Not given', rating, null)) avg_rating2

from food_orders

group by 1

 

1. rating이 Not given이 아니라면 rating,
2. Not given이 맞다면 null(데이터가 없다)이라 표시가 되며
3. 그룹으로는 avg_rating2로 표시된다.
4. group by로 restaurant_name의 데이터로만 출력된다.

 

---

a와 b의 데이터를 출력할 때 b의 customer_id는 null을 출력하지않는다.(제외)

select a.order_id,

a.customer_id,

a.restaurant_name,

a.price,

b.name,

b.age,

b.gender

from food_orders a left join customers b on a.customer_id=b.customer_id

where b.customer_id is not null

 
 

---

다른 값을 대신 사용하기

• 데이터 분석 시 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도한다.
• 다른값으로 변경하고 싶을 때는 조건문을 이용한다.

if(rating >= 1, rating, 대체값)을 작성하여 대체값을 출력할 수 있다.
출력된 값이 null이라면  coalesce(age, 대체값)를 작성하여 대체값을 출력할 수 있다.

select a.order_id,

a.customer_id,

a.restaurant_name,

a.price,

b.name,

b.age,

coalesce(b.age, 20) "null 제거",

b.gender

from food_orders a left join customers b on a.customer_id=b.customer_id

where b.age is null

---

상식적이지 않은 데이터가 출력될 경우

SELECT name, age,

case when age<15 then 15

         when age>=80 then 80

else age end re_age

from customers c

 

1. age가 15세 미만이라면 15세로 출력하고
2. 80세 이상이라면 80으로 출력한다.
3. 그게 아니라면 age로 변하고 re_age에 표시한다.

 

---

Pivot table이란

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

 

pivot table의 기본 구조

출처 : 스파르타 코딩클럽

---

pivot table의 예시 

집계 기준 : 일자, 시간

출처 : 스파르타 코딩클럽

---

음식점별, 시간별 주문건수 Pivot Table뷰 만들기

15~20시 사이, 20시 주문건수는 내림차순으로 정렬한다.

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

 

1. 15와 20사이의 b의 시간을 출력할 때 2번째 글자까지만 출력한다.
2.  cnt_order가 15라면 15시에 주문한 cnt_order값을 출력하고 나머지는 0 
3.  cnt_order가 16라면 16시에 주문한cnt_order값을 출력하고 나머지는 0 
4.  cnt_order가 17라면 17시에 주문한cnt_order값을 출력하고 나머지는 0 
5.  cnt_order가 18라면 18시에 주문한cnt_order값을 출력하고 나머지는 0 
6.  cnt_order가 19라면 19시에 주문한 cnt_order값을 출력하고 나머지는 0 
7.  cnt_order가 20이면 20시에 주문한 cnt_order값을 출력하고 나머지는 0 
8. 마지막으로 7번쨰 줄의 값은 desc를 이용하여 내림차순으로 정렬한다.

---

성별, 연령별 주문건수 Pivot Table뷰 만들기

나이는 10~59세 사이, 연령순으로 내림차순

SELECT age,

              MAX(if(gender='male', cnt_order, 0)) "male",

              MAX(if(gender='female', cnt_order, 0)) "female"

FROM

(

SELECT 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,

                       COUNT(1) cnt_order

from food_orders f inner join customers c on f.customer_id = c.customer_id

where age BETWEEN 10 and 59

group by 1, 2

)a

group by 1

order by 1 DESC

 

1. 10에서 59 사이의 customer와 food_order랑 겹치는 customer_id 를 통하여 gender와 age값을 출력한다.
2. case when문을 통해 숫자들 사이의 값은 마지막 숫자로 출력을 하고 이 값을 통틀어 cnt_order라 한다.
3. 나이를 기본적으로 출력하는데 성별이 남성이라면 gender에 값을 더하고 여성이라면 female에 값을 더한다.
4. 마지막으로 나이는 내림차순으로 정렬한다.

---

window함수란

단위 단위를 묶어서 좀 더 쉽게 연산할 수 있게 도와주는 함수

 

Window Function의 기본구조

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

window_function 

기능명을 사용해준다.(sum, avg와 같이 기능명이다)

argument

함수에 따라 작성하거나 생략한다.

partition by

그룹을 나누기 위한 기준이며 group by 절과 유사하다.

order by

window function을 적용할 때 정렬할 컬럼 기준을 적어준다.
 

---

N번째 까지의 대상을 조회하고 싶을 때 (Rank)
Rank는 특정 기준으로 순위를 매겨주는 기능
Rank() over는 세트

 

음식 타입별로 주문건수가 가장 많은 상점 3개씩 조회하기.

SELECT cuisine_type,

               restaurant_name,

               cnt_order,

                ranking

from

(

SELECT cuisine_type,

               restaurant_name,

                cnt_order,

rank() over (partition by cuisine_type order by cnt_order desc) ranking

from

(

SELECT restaurant_name, cuisine_type,

               count(1) cnt_order

from food_orders f

group by 1, 2

) a

) b

where ranking<=3

1. 음식점 별, 음식 타입별 따로 묶어 숫자(count)를 해주고 cnt_order 이름값을준다.
2. cuisine_type은 partition by로 인해 묶어서 그룹별표시하고
3. cnt_order는 음식점마다의 음식타입을 갯수를 묶어서 많은갯수순으로 내림차순(order by) 으로 정렬하고
4. ranking은 3등이내로 표시하는데 많은순이 1이고 다음이 2 다음이 3등이다.

---

전체에서 차지하는 비율, 누적합을 구할 때 (sum)

select cuisine_type,

           restaurant_name,

           cnt_order,

            sum(cnt_order) over(partition by cuisine_type) sum_cuisine,

            sum(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine

from

(

SELECT restaurant_name,

               cuisine_type,

               count(1) cnt_order

from food_orders

group by 1, 2

)a

order by cuisine_type, cnt_order

1. 레스토랑 이름별 음식타입별마다 따로 묶어서 숫자(count)로 표시하고 cnt_order의 이름을준다.
2. 음식점 이름과 음식 타입별은 따로 묶어 한묶음으로 표시한다.
3. 서브쿼리로 묶어서 sum()over()에 각각 더해서 표시한다.
4. 음식타입별 partition by는 음식 타입별로 묶어서 cnt_order 값을 더을 sum_cuisine에 표시하고
5. 레스토랑 네임의 partition by는 cnt_order의 음식 타입별로 묶은것을 그룹으로 묶어서
6. 내림차순으로 정렬 cnt_order는  표시할때  cum_cuisine에 표시한다. 

---

날짜 데이터란

문자타입, 숫자 타입과 같이 날짜 데이터도 특정한 타입을 갖고있다.
년, 월, 일 ,시 ,분 , 초 등의 값을 모두 가지며 목적에 따라 월, 주, 일 등으로 변경도 가능하다.

 

왼쪽은 문자형 오른쪽은 데이터형이다.

출처 : 스파르타 코딩클럽

---

SELECT date,

date(date) change_date

 

from payments

1. date를 출력하고 date함수안의 date를 출력하고 이름은 change_date를 준다.

 

---

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

 

1. date의 데이터를 date type으로 표시한다
2.  date의 데이터를 가져와 변환시키는 함수 date_format을 사용하여 연, 월, 일, 요일 별로 나누어 표시를한다.
3. 요일에 0은 일요일, 1은 월, 2는 화, 3은 수,4는 목, 5는 금, 6은 토이다.

---

년도와 월을 포함하여 date의 데이터 가공하기

select date_format(date(date), '%Y') "년",

           date_format(date(date), '%m') "월",

           date_format(date(date), '%Y%m') "년월",

            count(1) "주문건수"

from food_orders f inner join payments p on f.order_id = p.order_id

WHERE date_format(date(date), '%m')='03'

group by 1, 2, 3

order by 1

 

1. food_orders와 payments에 아이디 값을 주어 공통자료(inner join)을 출력할 수 있게 order_id를 사용해준다.
2. date의 데이터를 출력하고 date_format을 사용하여 문자값이 아니기에 데이터의 이름을 변경하여 출력해준다.
3. 숫자로 출력할 수 있게 count를 쓰고 이름은 주문건수 라 칭한다.
4. where절을 주어 문자형으로 바뀐 date를 '03'월만 맞춰 출력할 수 있다.
5. group by는 각 년, 월, 년월 따로 묶어서 열에 맞춰 출력한다.
6. 주문건수는 각 섹션에 공통값을 출력해준다.
7. order by절을 이용해 select의 1번째 값"년"의 데이터를  내림차순으로 정렬한다.