[TIL] SQL_9일차(null,coalesce,Pivot view)

이전 시간에는 JOIN에 대해 배웠는데요. 이번 시간에는 아래와 같은 내용에 대해 공부합니다.

☕

✍🏻배운내용🌞

1. 조회한 데이터에 아무런 값도 없을 때 대처방법 

2. 조회한 데이터가 상식적이지 않은 값을 가지고 있을 때 대처방법

3. SQL 로 Pivot Table 만들어보기

---

1. 조회한 데이터에 아무런 값도 없을 때 대처방법

‣ 데이터가 없을 때의 연산 결과가 변화한 경우

‣ JOIN 을 했을 때 값이 없는 경우

 

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

‣  Mysql 에서는 사용할 수 없는 값일 때 해당 값을 0으로 간주합니다. (연산에서 제외, 존재하지 않는 값)

null 은 '데이터가 없다'라는 의미입니다.

1) 제외하고 싶은 데이터가 있을 때 '어떤 조건에서는 제외 시켜줘'라는 명령 :

→ if(rating<>'Not given', rating, null) ⇒  rating 이 Not given 이 아니라면 rating 은 null이다.

다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)

select restaurant_name, avg(rating) avg_rating, avg(if(rating<>'Not given', rating, null)) avg_rating2 from food_orders group by 1 # restaurant_name 범주별로 계산

 

 

‣  avg_rating 의 값보다 avg_rating2의 값이 더 큰 이유 : null이 있는 값은 연산에 포함하지 않아서 나눌 때에도 포함 시키지 않았기 때문입니다.

‣  명확하게 연산을 지정해주기 위해 null 문법을 이용합니다.

 

2) null 값이 안나오게 하는 방법 : where ~ is not null

→ null 제거를 했을 때 (join 시에는 inner join 과 동일함)

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

 

 

[방법2] 사용할 수 없는 값 대신 다른 값을 대체해서 사용하는 방법

‣ 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 합니다.

 

‣  다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있습니다.

1) 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)

 

2) null 값일 때 : coalesce(컬럼, 대체값)

→ null 을 다른 값으로 대체할 때 coalesce문을 씁니다.

→ customer 테이블에 없는 데이터 중에 age 만 20으로 채워진 것을 확인하실 수 있습니다.

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

select a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, coalesce(b.age, 25) "null 제거", b.gender, coalesce(b.gender, '중성') "null 제거2" from food_orders a left join customers b on a.customer_id=b.customer_id where b.age is null

 

2. 조회한 데이터가 상식적이지 않은 값을 가지고 있을 때 대처방법

‣ 상식적이지 않은 데이터의 예시 : 이상치, 오류값

→ 주문한 고객나 2세일 경우, 결제 일자가 1970년대인 경우

 

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

select customer_id, name, email, gender, age from customers

 

범위를 지정해준 결과, 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체합니다.

select customer_id, name, email, gendor, age, case when age<15 then 15 when age>80 then 80 else age end "범위를 지정해준 age" from customers

 

3. SQL 로 Pivot Table 만들어보기

1) Pivot table 이란? 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미합니다.

2) 왜 SQL 로 Pivot Table 만드는가?

 데이터를 뽑아서 엑셀로 가공하지 않고, 바로 Pivot table 을 만들면 과정이 줄어들어 업무를 빠르게 할 수 있습니다. (엑셀에서도 Pivot Table를 많이 사용합니다)

 

[ Pivot table 의 기본 구조 ]

1. 베이스 데이터 만들기

2. Pivot view 구조 만들기 : 집계 기준 컬럼, 구분 컬럼 max(), max()...

 

[실습] 

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

(15~20시(*앞 두 글짜만) 사이 → substr(p.time, 1, 2) hh, 20시 주문건수 기준 내림차순 → order by 7 desc)

1)  베이스 데이터 만들기 : 성별, 연령별 주문건수 집계하기

🚨 음식점별 시간 데이터가 모두 있어야해서 inner join합니다.

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

2) Pivot view 구조 만들기

🚨 max,min,sum,avg와 같은 연산 함수가 있을 때에는 group by 절을 꼭 적어줘야합니다.

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 f.restaurant_name, substr(p.time, 1, 2) hh, count(1) cnt_order from food_orders f inner join payments p on f.order_id = p.order_id where substr(p.time, 1, 2) between 15 and 20 group by 1, 2 ) a group by 1 order by 7 desc

 

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

(나이는 10~59세 사이 몇 대로, 연령 순으로 내림차순 → order by age)

1)  성별, 연령별 주문건수 집계하기

🚨 성별, 연령별 데이터가 모두 있어야해서 inner join합니다.

select c.gender, case when age between 10 and 19 then '10th' when age between 20 and 29 then '20th' when age between 30 and 39 then '30th' when age between 40 and 49 then '40th' when age between 50 and 59 then '50th' end age, count(1) from food_orders f inner join customers c on f.customer_id = c.customer_id where c.age between 10 and 59 group by 1, 2

 

2) Pivot view 구조 만들기

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

 

 

---

💡[ Tip ] SQL생각 흐름 🧠 

1. 기본구조를 적기 : select from where

2. 어떤 데이터를 가져올 테이블을 적기 : from 테이블

 → 만약 조건이 잘 파악이 안 되면 (전체) 테이블만 우선 실행하여 결과를 보고 조건 찾기

3. 어떤 컬럼을 이용할 것인지 선택 : select 컬럼

4. 어떤 조건을 지정할 지 적기 : where 조건, group by / order by

5. 어떤 함수(수식) 을 이용해야 하는가 → 갯수 구하는 수식

 

[🔎복습 ]

💡replace(바꿀 컬럼, 현재 값, 바꿀 값)

💡 substr(조회 할 컬럼, 시작 위치, 글자 수)

💡 concat if (붙이고 싶은 값1, 값2, 값3, .....)

💡 if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

💡 case when 조건1 then 값(수식)1

             when 조건2 then 값(수식)2

             else 값(수식)3

             end

---

ADsP 시험 정보가 궁금하신 분들은 여기를 클릭 💨

이전 내용이 궁금하다면 여기를 클릭 💨

replace, substr, concat, if/case에 대해 더 알고싶다면 여기를 클릭🔥

 

🌸 오늘 하루도 즐거운 하루 되세요🌄