SQL 5주차

[엑셀보다 쉽고 빠른 SQL] 5주차 정리

SQL 4주차 에 이어서 SQL 5주차 마무리까지 정리한 글이다.
이번 주차는 저번 주차에서 배웠던 서브쿼리, 조인을 활용해서 엑셀에서 자주 사용하던 Pivot Table을 SQL에서 만드는 법과 Window Function과 날짜 포맷 사용하는 법을 배웠다.

---

1. 원하지 않은 값 제거

어떤 데이터를 조회하기 위해 연산이 필요할 때 원치않은 값이 포함되어 결과가 이상하게 나올때가 있다.

예를 들어,

• 데이터에 이상한 범주의 값이 들어가 있거나 (나이대, 날짜 등)
• 데이터가 없어서 연산이 이상하게 들어가는 경우 (null)

이런 예외 상황을 고려해서 조회할 때 특정 데이터의 값을 미리 배제하는 것이 중요하다.

해결 방법

• if 문을 통해 예외적인 데이터 배제하기
  • if((조회할 데이터)=(없는데이터), null, 조회할 데이터)
• coalesce() 함수를 통해 값 변경하여 배제하기
  • coalesce((조회할데이터), (변경할데이터))
• case 문을 통해 범주에 강제로 포함하기
  • 이상한 값이 들어가 있는 경우를 모두 포함해서 특정 값으로 강제 변경하여 포함하는 경우이다.

---

2. Pivot Table 만들기

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

15~20시의 음식점별 시간별 주문건수를 보고싶다고 했을 때,

	15시	16시	17시	18시	19시	20시
음식점 A	13	7	6	9	3	9
음식점 B	4	2	7	3	3	4
음식점 C	3	2	8	14	15	4

이런 형태로 볼 수 있는 것이 Pivot Table이다.

앞서 배웠던 서브쿼리를 사용하여 1차적으로 15~20시 이내에 있는 음식점별 시간별 주문건수 데이터들을 조회한다.

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

이제 음식점의 시간대별로 주문건수가 조회될 것이다.

그 다음은 서브쿼리로 사용하여 레스토랑 이름에 맞춰 시간대별로 누적된 수를 출력해주면 된다.

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

가장 중요한건 컬럼으로 사용할 데이터인 cnt_order 를 뽑는 것같다.
이후 cnt_order인 주문건수를 Pivot Table로 만들면 된다.

---

3. Window Function

Window Function은 SUM, AVG, RANK 등 여러 함수가 있으며 SQL에서 기본적으로 제공되는 sum과 avg와는 다르게 사용된다.

partition by를 통해 어떤 구역(그룹)을 나누어 준 뒤, 연산을 수행하기 때문에 서브쿼리가 존재하고 여러번의 연산이 필요한 경우 사용할 수 있다.

Window Function 기본 구조
window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

• sum이나 avg 같은 경우 이름이 겹치기 때문에 뒤에 over라는 것을 붙여 윈도우기능임을 알릴 수 있다.

RANK() 실습
Q. 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회

1. 음식 타입별로 주문 건수를 조회하는 쿼리가 필요
2. 음식 타입별 주문 건수 랭킹을 매기는 쿼리가 필요
3. 랭킹 3등까지만 조회하는 쿼리가 필요

3파트로 나눌 수 있다.

순서대로 쿼리를 작성하면
1.

select restaurant_name, cuisine_type, count(1) order_count
FROM food_orders
group by 1, 2

SELECT restaurant_name, cuisine_type, order_count,
	rank() over (partition by cuisine_type order by order_count desc) c_rank
from (1번쿼리)
=> rank() 윈도우 함수 사용, 음식 타입별로 랭킹을 조회하기 때문에 partition by 뒤에 cuisine_type이 온다.
   또한, 1등부터 정렬해야하므로 order by ~ desc 를 사용.

select restaurant_name, cuisine_type, order_count, c_rank
from (2번쿼리)
where c_rank<=3

(결과)

select restaurant_name, cuisine_type, order_count, c_rank
from
(SELECT restaurant_name, cuisine_type, order_count,
	rank() over (partition by cuisine_type order by order_count desc) c_rank
from
(select restaurant_name, cuisine_type, count(1) order_count
FROM food_orders
group by 1, 2 )a
)b
where c_rank<=3

이렇게 서브쿼리가 필요하고 연산을 여러번 해야하는 경우 윈도우 기능을 사용하면 편하게 조회가 가능하다.

---

4. 날짜 포맷

SQL에는 문자열, 숫자 외에 날짜라는 데이터 타입이 있다.

위 사진처럼

• pay_type : 문자열 (ABC 표시)
• vat : 숫자 (123 표시)
• date(date) : 날짜 (시간 표시)

형태로 출력되는 것을 볼 수 있다.

사실 date 라고하는 컬럼으로 문자열이 저장되어 있는데 date() 함수를 사용하여 문자열로 저장된 date를 날짜 데이터형으로 바꾼것이다.

그럼 vat을 date() 함수를 사용하여 날짜형으로 바꾸면 어떻게 될까?

사진에 맨 오른쪽 컬럼 내용처럼 값이 제대로 변환되지 않은 것을 볼 수 있다.

즉, 날짜형식에 맞게 문자열이 입력되어 있어야 형변환 시, 정상적으로 변환이 된다.

또한, date_format() 함수를 사용하여 날짜 데이터를 년/월/일/요일로 분리할 수도 있다.

2024-07-04 (목) 기준,

• 년
  • date_format(date, '%Y') = 2024
  • date_format(date, '%y') = 24
• 월
  • date_format(date, '%M') = July
  • date_format(date, '%m') = 07
• 일
  • date_format(date, '%d') = 04
  • date_format(date, '%e') = 4
• 요일
  • date_format(date, '%w') = 4 [일(0)->월(1)->화(2)->수(3)->목(4)->...]

형태로 출력된다.

문자열로 저장된 date 컬럼을 날짜 데이터로 포맷시키거나 날짜별 연산이 필요한 경우 유용하게 사용할 수 있다.

---

5. 5주차 숙제

Q. 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

1. 음식 타입별, 연령 10~59세 사이의 연령별 주문건수 쿼리 필요

select cuisine_type,
	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 cus_age,
	count(1) order_count
FROM food_orders fo inner join customers c on fo.customer_id=c.customer_id
where age BETWEEN 10 and 59
group by 1, 2

2. 음식타입별 10대, 20대, 30대, 40대, 50대별 주문건수 피벗테이블 쿼리 필요

SELECT cuisine_type,
	max(if(cus_age=10, order_count, 0)) '10대',
	max(if(cus_age=20, order_count, 0)) '20대',
	max(if(cus_age=30, order_count, 0)) '30대',
	max(if(cus_age=40, order_count, 0)) '40대',
	max(if(cus_age=50, order_count, 0)) '50대'
from (서브쿼리1)
group by 1

(결과)

SELECT cuisine_type,
	max(if(cus_age=10, order_count, 0)) '10대',
	max(if(cus_age=20, order_count, 0)) '20대',
	max(if(cus_age=30, order_count, 0)) '30대',
	max(if(cus_age=40, order_count, 0)) '40대',
	max(if(cus_age=50, order_count, 0)) '50대'
from
(select cuisine_type,
	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 cus_age,
	count(1) order_count
FROM food_orders fo inner join customers c on fo.customer_id=c.customer_id
where age BETWEEN 10 and 59
group by 1, 2) a
group by 1

2번 쿼리에서 if 함수 앞에 max() 함수를 사용하는 이유는 뭘까?

바로 if문에서 열의 값을 검증할 때 모든 열을 판단하기 때문이다.

또한, 위 코드에서 if 문이 거짓일 때, 0 으로 출력하라고 했기 때문에 0으로 출력된 것이다.

즉, if문을 사용하는 경우 cus_age=10 이라고 했을때 10대만 보는 것이 아닌 10대~50대 모든 열을 평가하기 때문에 참으로 된 것 외에 모두 0으로 출력된 것이다.