[엑셀보다 쉽고 빠른 SQL] - 5주차 (PIVOT, WINDOW)

특정 값 처리하기

 

1.  NULL : 특정 값을 결측값으로 만들기. 특정 값을 제외하기 위해서 결측값으로 바꿈. 다양한 구문에서 사용 가능

컬럼에 문자가 포함된 숫자 데이터의 경우, 문자가 0으로 계산됨

SELECT AVG(IF(변수명 <> 'Not',변수명, null)
FROM 테이블명

 

2. IS NOT NULL : 결측값 제외하기

SELECT 변수명
FROM 테이블명
WHERE 변수명 IS NOT NULL

 

3. COALESCE : 다른 값으로 대체하기

IF 구문을 사용해서 다른 값으로 대체하는 것도 가능함

#COALESCE 함수 사용
SELECT coalesce(변수명, 결측값을 대체할 값)
FROM 테이블명

#IF 구문 이용
SELECT IF(변수명 IS NULL, 대체할 값, NULL이 아닌 경우의 값)

 

상식적이지 않은 값 처리하기

 

1. 상식적이지 않은 값 처리하기

너무 낮은 값과 높은 값을 조건에 적고 이를 대체할 새로운 값으로 지정함

SELECT 변수명,
	   CASE WHEN 조건1 THEN 새로운 값 1
       		WHEN 조건2 THEN 새로운 값 2
            ELSE 새로운 값 3 END
FROM 테이블명

 

피벗 테이블(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, substr(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 substr(p.time, 1, 2) between 15 and 20
GROUP BY 1, 2
)
GROUP BY 1
ORDER BY 1, 2

 

WINDOW 함수

 

[Window Function]

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

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

• window_function : 기능 명 작성 (sum, avg ....)
• argument : 함수에 따라 작성하거나 생략
• partition by : 그룹을 나누기 위한 기준 컬럼
• order by : 정렬 기준이 되는 컬럼

1. Ranking : 특정 기준으로 순위를 매겨주는 함수

#rank(빈칸이어도 됨) over(partition by 구분해줄 변수명 기재 order by 순서의 기준이 되는 변수명)

SELECT 변수명1, 변수명2, rank() over (partition by 변수명 3 order by 변수명 4) 새로운 변수명 1
FROM 테이블명
GROUP BY 1, 2

 

2. 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

 

날짜 형식 지정하기

 

1. DATE : 문자 형식의 날짜 값을 날짜 형식으로 변경해주는 함수

SELECT 변수명, date(변수명) 
FROM 테이블명

 

2. DATE_FORMAT : 날짜 형식으로 기재된 값을 이용해 연도, 달, 일, 요일 정보를 불러올 때 사용함

SELECT date_format(date(변수명), '%Y') "년"   #연도 불러오기
       date_format(date(변수명), '%m') "월"   #월 불러오기
       date_format(date(변수명), '%d') "일"   #일 불러오기
       date_format(date(변수명), '%w') "요일" #요일 불러오기
FROM 테이블명