[231207] SQL: 데이터 가공, pivot table 생성, window function, date 포맷

* 수강 강의명: 엑셀보다 쉽고 빠른 SQL - 5주차 

 [요약] 

1. 데이터 가공팁

① null 값 

 - null 값 포함 여부에 따라 평균값이나 개수 등이 바뀌므로 원하는 결과값에 따라 가공이 필요할 수 있음

 - null 제외하는 법 

 └  where 절에 [is not null] 을 적거나 join 시에는 inner join을 활용

- null 대체값으로 변경하는 법

└ coalesce(칼럼명, 대체값) * ex. coalesce(age, 20)  / age 칼럼에 null 값은 모두 '20'으로 넣어줘

 

cf) 
COUNT(*) : NULL 값을 포함한 모든 행의 개수를 카운트
COUNT(컬럼명) : NULL 값을 제외한 행의 개수를 카운트

② 비상식적인 데이터 
- if 나 case when 절을 통해 값의 범위를 지정 (ex. 주문고객 나이가 2세일 경우, 15세 미만 나이는 모두 15세로 표기)

 

 

2. 피벗 테이블(Pivot Table) 생성법 

- 피벗 테이블 : 2개 집계 기준(항목)을 행과 열로 나눠 보기 쉽게 배열하는 표를 의미

 1) 우선 뽑고 싶은 데이터 테이블 쿼리를 작성한 후, from 절에 서브쿼리로 넣기

 2) 1차 가공된 테이블을 바탕으로, SELECT절에 조건절( if 나 case when)을 활용하여 피벗 테이블 첫 행 값을 생성하기 

  └  단, 조건절만 적으면 하기와 같이 NULL 영역이 생성되어, 피벗 테이블 형식으로 보이지 않음 

출처: https://schatz37.tistory.com/8

 3) 조건절 앞에는 반드시 집계함수 넣어주기 (SUM, AVG, MIN, MAX 중 아무거나) 

* 연령별-성별 주문수 예시

 

 

3. 윈도우 함수(Window Function)
- 윈도우 함수 : 행과 행 간의 관계를 정의하기 위해 만든 함수로, 분석 함수나 순위 함수로 불림
- 기본 구조 : Window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)
└   window_function : SUM, AVG, RANK 등 기능에 따른 함수명
└   argument(인수) : 함수에 따라 작성하거나 생략 가능
└  partition by 절: group by 절과 유사한 기능으로, 윈도우 함수가 적용될 그룹 기준
└   order by 절 : 어떤 항목의 순서에 따라 윈도우 함수를 적용할지 지정 

 ① RANK: 특정 기준으로 순위를 매겨주는 함수

└  예시: rank() over (partition by cuisine_type order by order_count desc)

└ RANK 방식에 따라 RANK / DENSE_RANK / ROW_NUMBER 가 있음

 

② SUM: 누적 합계나 그룹별 합계를 구하는 함수

└  그룹별 합계:  sum(order_count) over (partition by cuisine_type) 
└  누적 합계: sum(order_count) over (partition by cuisine_type order by order_count, restaurant_name)

 

 

4. 날짜 포맷 변경

- DATE: 날짜 타입으로 변경해주는 함수. 날짜도 문자, 숫자와 같이 특정 타입이 있음

└  기본 구조: DATE(칼럼명)

- DATE_FORMAT : 데이터 타입으로 된 날짜를 년, 월, 일, 주 등 원하는 값으로 변환해주는 함수  

└  기본 구조: date_format(date(date), '%m')

- 데이터 포맷 기호 종류 

구분	기호	내용	예시
년	%Y	4자리 년도	2023
	%y	2자리 년도	23
월	%M	영문 월	June
	%b	짧은 영문 월	Jun
	%m	두 자리 숫자 월	06
	%c	한 자리 숫자 월	6
일	%d	두 자리 일	08
	%e	한 자리 일	8
요일	%W	긴 영문 요일	Friday
	%w	숫자 요일 (일요일부터 0~6로 표기)	5
	%a	짧은 영문 요일	Fri
시간	%I	12시 시간	01
	%H	24시 시간	13
	%i	분	12
	%S	초	4
	%r	hh:mm:ss AM. PM	04:00:04 PM
	%T	hh:mm:ss	16:00:04

 

[정리]

1) 어떤 문제가 있었나 

괄호, 쉼표 등 작은 실수로 쿼리 오류가 종종 발생함

 

2) 내가 시도해본 건 무엇인가

각 구간 별로 잘못된 내용을 뜯어보거나 아예 쿼리를 처음부터 새로 작성

 

3) 어떻게 해결했나

쿼리 작성 할 때 나만의 흐름을 만들고자 고민함(ex. select 칼럼명 기입 시 줄바꿈을 통해 쉼표 누락 여부를 한 눈에 파악 등)

 

4) 무엇을 새롭게 알았나

- MAX 값의 칼럼 값을 구하는 SQL 코드카타 20번 문제에서, 최근 배운 서브쿼리를 활용했으나 내림차순 정렬하여 LIMIT를 사용하면 훨씬 짧고 깔끔한 쿼리 작성이 가능했음

- 수업 교재에 포함된 함수나 기능 외에도 다양한 함수가 있어 지속적으로 서칭하면서 암기 필요