window function, 포맷 함수

각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어 준다.

기본 구조

WINDOW_FUNCTION(ARGUMENT) OVER( PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

WINDOW_FUNCTION : 기능 명을 사용해준다.(SUM,AVG 등등)

ARGUMENT : 함수에 따라 작성하거나 생략한다.

PARTITION BY : 그룹을 나누기 위한 기준이다. GROUP BY 절과 유사하다.

ORDER BY : WINDOW FUNCTION을 적용할 때 정렬 할 컬럼 기준을 적어준다.

 

RANK

특정 기준으로 순위를 매겨주는 기능이다.

음식 타입별, 음식점별 주문 건수 집계 후 RANK 함수 적용

3위까지 조회하고, 음식 타입별, 순위별로 정렬하는 예시 코드

SELECT 
	CUISINE_TYPE,
	RESTAURANT_NAME,
	CNT_ORDER,
	RANKING
FROM
(
SELECT
	CUISINE_TYPE,
	RESTAURANT_NAME,
	CNT_ORDER,
	RANK() OVER(PARTITION BY CUISINE_TYPE ORDER BY CNT_ORDER DESC) AS RANKING
FROM
(
SELECT 
	cuisine_type,
	restaurant_name,
	COUNT(1) CNT_ORDER 
FROM food_orders
GROUP BY 1, 2
) A 
) B
WHERE RANKING <= 3

A 서브쿼리를 보면

음식 종류, 가게명, 주문건수를 음식 종류와 가게명을 그룹으로 묶어 보여달라한 쿼리이다.

B 서브쿼리를 보면

RANK 함수가 사용 됐는데 음식 종류를 기준으로 그룹화하고 A서브 쿼리에서 만든 주문 건수 별로 내림차순 정령해 달라 라고 되어있다.

본쿼리의 WHERE 절을 보면 3등 이내만 보여달라 이다.

결과 음식종류별 가게명 주문건수 주문건수가 많은 순대로 랭킹이 매겨진 모습이다.

 

 

SUM

합계를 구하는 기능이다.

누적합이 필요하거나 카테고리별 합계컬럼과 원본 컬럼을 함께 이용할때 유용하다.

 

음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적합 구하기

SELECT
	CUISINE_TYPE,
	RESTAURANT_NAME,
	CNT_ORDER,
	SUM(CNT_ORDER) OVER(PARTITION BY CUISINE_TYPE) AS SUM_CUISINE,
	SUM(CNT_ORDER) OVER(PARTITION BY CUISINE_TYPE ORDER BY CNT_ORDER) AS 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

서브쿼리는 주문량을 음식종류 가게명을 그룹으로 묶어 보여달라

본 쿼리는 첫번째 윈도우 함수는  주문 건수를 합하는데 음식별로 합하는거라 첫 행부터 총합이 나오고

두번째 윈도우 함수는 주문 건수를 합하는데 음식 종류를 기준으로 하고(PARTITION BY CUISINE_TYPE)

주문건수 별로 오름차순 정렬하여(ORDER BY CNT_ORDER) 합하라 하여 누적합이 되어

CNT_ORDER 의 수가 같은 CUISINE_TYPE들의 합부터 오름차순으로 보인다.

누적합을 편리하게 구할 때 쓰면 좋을 것 같다.

 

SUM_CUISINE은 바로 총합이 보이지만 CUM_CUISINE은 CNT_ORDER가 1인 것의 합(9) + 2인것의 합(9 + 2 + 2 = 13) .... 이런식으로 누적합으로 나온다

 

 

포맷 함수

• DATE

날짜 데이터도 SQL에서 연산이 가능하며 날짜 타입으로 바꿔 조회 해주는 함수가 존재한다

(UPDATE 함수를 사용하는게 아니면 조회할 때만 타입 변경이다.)

select date(date) date_type,
       date
from payments

payments 테이블의 date 컬럼이 varchar 형식이었지만 date타입으로 바꿔 조회하기 위해 DATE() 함수를 이용한다.

그럼 컬럼의 타입이 DATE로 변경된다.

 

 

• DATE_FORMAT

이 함수는 DATE 타입 컬럼을 년, 월, 일, 주(요일) 로 조회가능하게 해주는 함수이다.

DATE_FORMAT(컬럼, '%Y%M%D%W')

%Y = 년도 숫자 4자리 전부 %y = 년두 숫자 뒤에 두자리

%M = 월을 영어로 나타냄 %m = 월을 숫자로 나타냄

&D = 일을 1st, 2nd 방식으로 나타냄 &d = 일을 숫자로 나타냄

&W = 영어로 요일을 나타냄 &w = 일요일부터 토요일을  0 ~ 6 으로 나타냄

 

 

년도, 월별 주문건수를 구하는데 3월에 주문한 곳만 찾기

SELECT
	DATE_FORMAT(DATE(DATE), '%Y') AS '년',
	DATE_FORMAT(DATE(DATE), '%M') AS '월',
	DATE_FORMAT(DATE(DATE), '%Y%m') AS '년월',
	COUNT(1) AS '주문건수' 
FROM food_orders FO INNER JOIN payments P ON FO.order_id = P.order_id
WHERE DATE_FORMAT(DATE(DATE), '%m') = '03' 
GROUP BY 1, 2, 3
ORDER BY 1

DATE 컬럼을 DATE() 함수로 DATE 타입으로 변경한 뒤

DATE_FORMAT 함수로 각각 년, 월, 년월을 가져와 보여주게 한다.

 

각 년도 3월마다 주문 건수가 얼마인지 나오는 모습.