[SQL] 배운것들 총집합-엑셀보다 쉽고 빠른 SQL 완강 후기

🥑 오늘 배운 것

 

- 테이블에 잘못 된 값(사용할 수 없는 데이터)이 들어있거나, 값이 없을 때

 

1. 없는 값을 제외해준다. (**Mysql에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해준다(0으로 간주))

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

 

avg_rating(0으로 간주해서 0,1,2,3,4,5를 6으로 나눈 것)

avg_rating2(사용할 수 없는 값을 제거해서 1,2,3,4,5를 5로 나눈것)

 

if(조건,조건 충족할 때 값, 아닐때 값)

3 레이팅이 not given 이 아닐 때의 값만 사용하고 싶음(숫자일때), 레이팅 그대로 사용, null(제외시킬 것)

 

경우에 따라서 where 컬럼 is not null을 사용할 수도 있다. (null값을 제외하고 불러오기)

 

 

2. 다른 값을 대신 사용한다.

데이터 분석 시 평균값, 중앙값 등의 대표값을 이용하여 대체해주기도 한다.

다른 값으로 변경하고 싶을 때 이용할 수 있는 문법 : if 조건문, coalsece(null값일 때)

coalsece(컬럼, 대체값) as 별명설정

*coalsece (더 큰 덩어리로) 합치다

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

null 값은 20으로 대체함.

 

 

 

-SQL로 Pivot Table 만들기

 

Q. 왜 엑셀을 사용하지 않고 SQL을 사용하는지?

A. 프로세스를 줄일 수 있다!

 

 

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

 

왕초보의 Tip  쿼리문을 한줄 이상 띄워서 작성하면 서로 다른 쿼리문으로 인식해서 커서를 둔 쿼리만을 실행해 준다.

 

 

 

-피벗 테이블을 만들기 위한 단계

1. 베이스 데이터 만들기
2. 베이스 데이터를 이용하여 피벗뷰를 만들기

원하는 피벗뷰를 만들어주기 위해서는 최댓값 max 함수를 써야한다. (깔끔하게 만들어주기 위해서, 그냥 외우라고 하셨는데 왜 그래야하는지 생각해봐야겠다.)

 

 

- window function, RANK, SUM

 

윈도우 함수 : ㅁㅁㅁ 처럼 창문별로 묶인 형상이라고 떠올리면 쉽다. 각 행의 관계를 정의하기 위한 함수로 단위(그룹) 안에서 연산을 쉽게 할 수 있도록 도와준다.

예) 한식 음식점별 순위 매기기, 일식 음식점별 순위 매기기(덩어리마다)

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

window_function : 기능명 (sum, rank 등)

argument : 함수에 따라 작성하거나 생략한다. 생략하더라도 괄호는 유지해주어야 한다.

partiton by : 그룹을 나누는 기준

 

 

- 누적합과 순위 구하기

 

RANK : 순위 구할 때(n번째까지의 대상을 조회하고 싶을 때)

RANK (  ) over (구분해줄 파티션 있으면 partition by 컬럼, order by 컬럼)

 

 

누적합을 구할 때

 

각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

1. 전체 주문건에서의 음식타입별 합계 구하기.
2. 주문건이 낮은 순으로 정렬 했을 때 누적 합 구하기
3. 베이스 데이터(음식점별 주문건수) 만들기

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, restaurant_name) cum_cuisine
from
(
select cuisine_type, 
	restaurant_name, 
	count(1) cnt_order
from food_orders
group by 1, 2
) a
order by cuisine_type , cnt_order, cum_cuisine

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

음식타입별 합계 구하기

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

주문건수의 합계 over(음식타입별 누적합)

 

 

 

- 날짜 포맷(포맷함수)

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

문자포맷으로 되어있는 날짜를 날짜 포맷으로 변경하고, 그 값을 데이터 포맷 함수를 통해 년만 추출한다.

 

년 : Y(4자리), y(2자리)

월 : M, m

일 : d, e

요일 : w

 

*요일의 경우 0은 일요일 1은 월요일이다.

** 연월, 월일 등을 붙여서 쓸수도 있다  ‘%y%m’

 

 

🥑 적용

---

가면 갈수록 쿼리도 복잡해지고, 전에 배운 것들을 활용해야하는 실습 문제들이 많아졌다. SQL의 첫 발자국을 뗀 나로서는 쉽지 않은 일주일이었다. 짧은 강의 시간에도 불구하고 직접 쿼리를 작성해보는 실습까지 하고, 배운 것을 복습하고 의문이 생기는 부분을 체크하다 보면 2~3시간은 훌쩍 지나있었다. 앞으로 더 기초를 탄탄히 해서 문제 푸는 시간을 줄이고 싶은 바램이다!