✔️ 조회한 데이터에 아무 값이 없을 때 처리하는 방법
- 사용할 수 없는 데이터가 들어있거나, 같이 없는 경우에 처리하는 방법 (데이터를 사용할 때 매우 흔한 경우)
[방법1] 없는 값을 제외해주기
- Mysql에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해줌 -> 0 으로 간주
- 즉, 평균 rating을 구하는 쿼리를 아래와 같이 작성했을 때 실제 연산에 사용되는 데이터는 다음과 같다.
➡️ 명확한 연산을 지정해주기 위해 null 문법을 이용
- not null : customer_id 테이블에 데이터가 없는 애들 제외 (null이 아닌 애들만 부름)
[방법2] 다른 값을 대신 사용하기
- 사용할 수 없는 값 대신 다른 값을 대체해서 사용하는 방법
- 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체해주기도 함
- 다른 값으로 변경하고 싶을 때, 다음 두개의 문법을 이용할 수 있음.
1. 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
2. null값일 때 : coalesce(age, 대체값)
- null을 다른 값으로 대체한 쿼리문을 실행하면 다음과 같음.
customer 테이블에 없는 데이터 중에 age만 20으로 채워진 것을 확인할 수 있음.
- is null : null인 것들만 뽑아줘
✔️ 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?
1) 상식적이지 않은 데이터의 예시
- 케이스1 : 주문 고객의 나이
보통 음식을 주문한 고객은 20세 이상인 성인인 경우가 많음. 하지만 데이터를 보면 2세와 같이 상식적이지 않은 값들을 확인
- 케이스2 : 결제 일자
결제의 경우, 비교적 최근인 일자가 있어야 상식적일 것이다. 하지만, 데이터를 보면 1970년대와 같이 상식적이지 않은 값들을 확인
[방법] 조건문으로 값의 범위를 지정하기
가장 큰 값, 가장 작은 값 (상식적인 수준 안에서) 범위를 지정해줌.
✔️ SQL로 Pivot Table 만들어보기
- Pivot table 이란 : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것
[실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
➡️ Pivot view 구조 만들기
[실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기(나이는 10~59세 사이, 연령순으로 내림차순)
➡️ Pivot view 구조 만들기
✔️ 윈도우 함수(Window Function)
- 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줌.
ex) 한식 식당 중에서 주문건수가 많은 순으로 매길 때
2건 이상의 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 때
- 기본 구조
- window_function : 기능 명을 사용해줌.(sum, avg와 같이 기능명이 있음)
- argument : 함수에 따라 작성하거나 생략함.
- partition by : 그룹을 나누기 위한 기준. group by절과 유사하다고 생각하면 됨.
- order by : window function을 적용할 때 정렬 할 컬럼 기준을 적어줌.
[실습] N번째까지의 대상을 조회하고 싶을때, Rank
- 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기
+ Rank함수 적용
- rank() over 는 한쌍, 항상 함수+over가 같이 나옴.
- over 뒤에는 두가지를 적어야됨.(partition by : ~별로 ranking을 구할꺼야 과 order by : ranking 순서를 정할때 사용)
➡️ 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기
[실습] 전체에서 차지하는 비율, 누적합을 구할 때, SUM
- 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적합 구하기
+ SUM으로 동일한 cnt_order값을 가진 여러 행이 있을 경우, SQL엔진은 이 값을 한꺼번에 더함. cnt_order의 순서를 결정할 명확한 기준이 없으므로 문제 발생
-> order by 절에 cnt_order 외에 추가적인 열에 순서를 부여할 수 있는 restaurant_name 을 포함시켜야 함.
-> 동일한 cnt_order 값을 가진 행들이 명확하게 순서가 정해져 누적합이 정상적으로 처리
-> 누적합을 순서대로 표기하기 위해 order by 에 cum_cuisine을 추가해줌.
✔️ 날짜 포맷과 조건까지 SQL로 한번에 끝내기
- 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가짐.
- 년, 월, 일, 시, 분,초 등의 값을 모두 갖고 있으며 목적에 따라 '월', '주', '일' 등으로 포맷을 변경할 수 있음.
[실습] 날짜 데이터의 여러 포맷
1. yyyy-mm-dd 형식의 컬럼을 daty type으로 변경
➡️ date type -> date_format 을 이용하여 년, 월, 일, 주 로 조회
a. 년 : Y(4자리), y(2자리)
b. 월 : M, m
c. 일 : d, e
d. 요일 : w
[실습] 년도별 3월의 주문건수 구하기
[실습문제] 음식 타입별, 연령별 주문건수 Pivot View 만들기
조건 : 연령은 10~59세 사이
- 결과물 예시
💡 학습하면서 느꼈던 문제점&에러
오늘은 SQL을 활용해 결측값(null)과 이상값을 처리하는 방법을 학습했다. null 값을 제외할지 대체할지 판단하는 과정이 어려웠지만, 여러 쿼리를 직접 비교하며 상황에 따라 기준을 세울 수 있었다. 또한 비정상적인 데이터는 조건문으로 범위를 지정해 필터링했다. Pivot Table을 만들 때는 어떤 기준으로 데이터를 나눌지 정하는 것이 헷갈렸지만, “무엇을 비교할 것인가”를 먼저 정의하며 해결했다.
Window Function에서는 PARTITION BY와 ORDER BY 설정이 특히 어려웠고, 동일 값 처리 문제를 정렬 기준을 추가해 해결했다. 날짜 데이터는 다양한 포맷으로 활용할 수 있어 분석 범위를 넓혀준다는 점이 인상적이었다. 전반적으로 SQL 문법보다 데이터를 해석하는 기준이 더 중요하다는 것을 느꼈다.