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 |