TIL

8/8 TIL - SQL 4,5주차 수강

nbcssw 2024. 8. 8. 21:31

SQL 4주차

Subquery

여러번의 연산을 수행해야 할 때, 조건문에 연산 결과를 사용해야 할 때 등 편리하게 쓸 수 있는 방법

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a

Subquery를 활용해서 음식 준비시간이 25분을 초과한 경우만 가져와서 초과한 시간을 조회 (초과하지 않은 경우 0)

 

JOIN

필요한 데이터가 각각 다른 테이블에 존재해서 여러 테이블에서 데이터를 불러와야 할 때 사용

두 테이블이 공통으로 가지고 있는 컬럼을 기준으로, 두 테이블을 묶음

 

LEFT JOIN, INNER JOIN

.

LEFT JOIN: 공통 컬럼을 기준으로 하나의 테이블에 값이 없더라도 모두 조회함

다음과 같이 고객 ID 5번에 전화번호 등의 값이 없어도 전부 조회함

 

INNER JOIN: 공통 컬럼을 기준으로 두 테이블 모두에 값이 있어야 조회

LEFT JOIN의 표와 다르게 모든 값을 가지고 있지 않은 5번은 제외하고 조회함

 

JOIN의 기본 구조

-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

a: 테이블1의 별명, b:테이블2의 별명

 

select f.order_id,
	   f.customer_id,
	   f.restaurant_name,
	   f.price,
	   c.name,
	   c.age,
	   c.gender
from food_orders f left join customers c on f.customer_id=c.customer_id

 

 

SQL 5주차

조회한 데이터에 아무 값이 없다면?

LEFT JOIN을 사용하면, 데이터가 없어도 조회하기 때문에 데이터에 비어있는 부분이 생길 때도 있다. 이럴 때에 평균값을 구한다거나 하면 값이 비정상적으로 나오게 될 수 있는데, 이럴 때 어떻게 해야하는지 살펴보자

1) 없는 값을 제외해주기

select f.order_id,
	   f.customer_id,
	   f.restaurant_name,
	   f.price,
	   c.name,
	   c.age,
	   c.gender
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.customer_id is not null

where을 통해서 null 값을 모두 제외

 

2) 다른 값을 대신 사용하기

select f.order_id,
       f.customer_id,
       f.restaurant_name,
       f.price,
       c.name,
       c.age,
       coalesce(c.age, 20) "null 제거",
       c.gender
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age is null

coalesce(c.age, 20): c.age가 null이면 대신 20을 넣음

 

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

음식을 주문한 고객의 나이를 조회하는데, 고객의 나이가 2살로 표시되는 비상식적인 경우가 있을 수 있다. 이럴 경우에 어떻게 해야하는지 알아보자

 

조건문으로 값의 범위를 지정하기

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

case를 통해 15살 미만은 15살로, 80살 초과는 80살로 표시되게 변경

 

Pivot table 만들기

Pivot table이란

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 f.restaurant_name,
       substring(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 substring(p.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

max를 사용하는 이유는 값을 더 확실하게 하기 위함이다(?)

 

Window Function - RANK, SUM

Window Function이란 각 행의 관계를 정의하기 위한 함수로, 그룹 내의 연산을 쉽게 만들어줌

ex)

○ 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶은데요, 가능할까요?

○ 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶은데 가능할까요?

○ 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 수 있을까요?

 

Window Function의 기본 구조

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

window_function : 기능 명 (RANK나 SUM)

argument : 함수에 따라 작성하거나 생략되는 부분

partition by : 그룹을 나누기 위한 기준

 

RANK

특정 기준으로 순위를 매겨주는 기능을 함\

select cuisine_type,
	   restaurant_name,
	   cnt_order,
	   rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(
select cuisine_type,
	   restaurant_name,
	   count(1) cnt_order
from food_orders
group by 1, 2
)a

cuisine_type 별로 주문 수로 순위를 매김

 

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

cuising_type 별 합, 누적합 구하기

 

오늘의 comment

주말이 다가올수록 집중력이 떨어지는게 느껴진다.. 오늘 내용은 특히 어려워서 힘든 날이었던 것 같다. 내일까지만 힘내서 열심히 공부해보자

'TIL' 카테고리의 다른 글

TIL 8/12 - JavaScript 문법 종합반 1주차, 2주차 초반  (0) 2024.08.12
8/9 TIL - 코드카타, JavaScript 문법 종합반 조금  (0) 2024.08.09
8/7 TIL - SQL 1~3주차 수강  (0) 2024.08.07
8/6 TIL  (0) 2024.08.06
8/5 TIL  (0) 2024.08.05