SQL

[내일배움캠프] SQL - NULL 값 / 결측치 처리, 피벗 테이블, RANK, DATE TYPE

응애 PM 2025. 3. 17. 15:22

이번에는 다양한 SQL의 기능들을 알아보았다.

 

- NULL값 처리

 

코딩을 접해본 사람이라면 NULL이란 값들을 많이 봐왔을텐데, 

 

0과는 다른 아예 빈, 값이 없는 것을 NULL이라고 한다.

 

이러한 NULL 값은 테이블 간 연산 과정에서 발생할 수도 있으며, NULL 값을 대체하거나 처리하는 과정이 필요해지기도 한다.

 

만약 NULL 값을 제외하고 싶다면 전에 배웠던 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

 

의 코드와 같이 WHERE 조건절에 IS NOT NULL 조건을 붙여서 NULL값을 제외하고 조회하는 방법을 사용할 수도 있따.

 

 

다른 방법도 존재하는데, NULL 값을 대체하는 것이다.

 

인공지능 교과목을 공부하면서 NULL값을 0 / 평균값 / 중앙값 / 최빈값 등으로 대체하는 작업을 했던 적이 있어서

 

나름 익숙하게? 편하게? 이해할 수 있었다.

 

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

 

단어가 좀 어려운데, 코알라 + ESC 로 외웠다 ㅋㄷㅋ

 

COALESCE(NULL 대체할 컬럼명, 대체값)과 같이 사용하면 된다.

 

- 결측치 처리

예를 들어서, age 컬럼의 값 중 1000이 있다고 생각해보자.

 

아무리 무병장수 불로불사에 관한 소설이나 만화들이 많다지만 현실적으로 1000살까지 살긴 어렵다ㅋㅋ..

 

아니면 또 다른 예시를 들어서, height(키) 컬럼의 값들 중 1이 있다면?

 

움파룸파족보다도 작은 키를 가진 사람일것..이 아니라, 잘못된 값이 들어가 있는게 명백하다~

 

이러한 결측치들을 사전에 방지하기 위해 CASE 절을 활용해서 범위를 지정해 결측값들을 처리할 수 있따

 

SELECT customer_id, name, email, gender, age,
       CASE WHEN age<15 then 15
            WHEN age>80 then 80
            ELSE age END "범위를 지정해준 age"
FROM customers

 

결측치를 15, 80으로 대체한 모습.

- 피벗 테이블

엑셀을 다뤄본 사람들은 이미 많이 접해봤을 피벗 테이블이다.

 

피벗 테이블이란 뭘까?

 

피벗('PIVOT') 의 단어 뜻 자체는 '중심점'이란 뜻이다. 어원에서 유추해서 알 수 있는 내용은

 

피벗테이블은 한 컬럼을 '중심점'으로 잡고 자신이 원하는 데이터만 갖고 원하는 행과 열에 데이터를 배치하는 기능을 가진 테이블을 말하는 것이다.

 

피벗테이블의 구조.

 

근데 왜 굳이 SQL에서 피벗테이블을?

 

=> SQL로 업무를 효율적으로 하기 위해 데이터를 뽑아서 엑셀로 가공하지 않고, 바로 피벗 테이블로 만드는 작업을 수행하려고!

 

- 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순) 

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
order by 7 desc

 

15 ~ 20시 사이로 잘 구분되어 정렬되어있는 모습이다.

 

주요하게 볼 것은 max(if(hh='15', cnt_order, 0)) "15" 와 같이 IF문에 MAX()로 감싼 것인데, 

 

왜 MAX()로 감싼 걸까? 주문 건수를 합치는 거니까 SUM()을 이용하면 되지 않을까? 라고 생각해서 챗지피티한테 물어봤따..

 

 

지피티의 해설. 깔끔하다.

 

사실 SUM을 썼던 내 방식도 괜찮았지만,

 

혹시나 중복된 데이터가 있어 값에 오류가 발생할 문제를 MAX()로 보장해준다는 것이었다!

 

cnt_order (주문건수) 는 1대1 대응으로 특정 시간대 - 식당에 하나의 주문건수만 있기 때문에

 

MAX()로 써주는게 더 정확하다.

- RANK 함수

만약 컬럼들의 값을 1, 2, 3... 순위를 매겨서 컬럼값으로 넣고 싶다면?

 

이때 사용할 수 있는 것이 RANK 함수이다.

 

엑셀에서도 사용했던 기억이 나는데, SQL에서는 어떻게 활용할 수 있는지 예제를 통해 알아보자..

 

- 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

select cuisine_type,
       restaurant_name,
       order_count,
       rn "순위"
from
(
select cuisine_type,
       restaurant_name,
       rank() over (partition by cuisine_type order by order_count desc) rn,
       order_count
from
(
select cuisine_type, restaurant_name, count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1, 4

 

1. 일단 음식 타입별로 주문건수를 정렬

2. RANK() OVER 함수를 통해 음식 타입별 주문 수에 따른 RANK 내림차순 정렬

3. WHERE 절을 통해 순위가 3위까지만 조회하도록 설정

 

의 순서로 코드를 짰다.

 

RANK 함수의 사용법은 'RANK() OVER (PARTITION BY '그룹열' ORDER BY '정렬기준열' ASC/DESC)' 와 같이 사용한다.

 

RANK() OVER은 고정, 뒤에 따라오는 PARTITION BY ~ ORDER BY ~ 를 잘 설정해줘야 한다.

 

결과 화면.

 

- DATE TYPE

날짜 데이터를 그냥 '2001-08-02' 같이 타입명시 없이 작성하면 (참고로 저 날짜는 내 생일이다 ㅋㅋ) 

 

와 같이 'A-Z', 즉 문자열 타입으로 기본 설정되어 있는 것을 알 수 있다.

 

날짜 데이터를 DATE_FORMAT을 이용해서 사용하려면 DATE TYPE으로 변경해서 이용해야 한다.

 

ex) 

select date_format(date(date), '%Y') y,
       date_format(date(date), '%m') m,
       order_id
from food_orders a inner join payments b on a.order_id=b.order_id

 

C언어에서 사용하는 것처럼 %Y, %m을 이용해서 년, 월 데이터로 가공하고 이를 활용할 수 있다.

 

select date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(data), 'Y%m') "년월",
       count(1) "주문건수"
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

 

위와 같이 년, 월 데이터를 추출하고 이를 다방면에서 활용할 수 있게 된다.

 

년, 월 뿐만 아니라 일, 시, 분, 초 등 다양하게 추출할 수 있으니 참고!

 

- 예제 풀이

 

- 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성 

SELECT *
FROM team_projects
WHERE aws_cost >= 40000;

 

 

- 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성

SELECT *
FROM team_projects
WHERE start_date IN
(
SELECT start_date,
FROM team_projects
WHERE DATE_FORMAT(DATE(start_date), '%Y')='2022'
);

 

 

- 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요! 

SELECT *
FROM team_projects
WHERE CURDATE() BETWEEN start_date AND end_date;

 

 

- 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성 

SELECT DATEDIFF(end_date, start_date) "지속 기간"
FROM team_projects;

 

 

 

강의에 나오지 않는 내용들이 좀 있어서 구글링을 참고했다 😅