[Maria DB] 분석 함수(Analytic function)

분석함수

분석 함수란 행 그룹을 기반하여 집계된 값들을 계산합니다.

분석 함수는 행과 행간의 분석을 수용하기 위해 윈도우 함수를 이용하여 그룹화 시킵니다.

기존의 집계 함수( sum(), avg(), count() )와는 다르게 결과셋을 변경하지 않으면서 분석 함수내 사용할 데이터를 효과적으로 분할 할 수 있습니다.

* 분석 함수는 SELECT 절에서만 사용할 수 있습니다.

 

대표적 활용사례

• 분기별 매출액 ( over() & partition by)
• 이달의 TOP10 우수사원 ( rank() & over() & order by )
• 주별 매출액의 이동평균값 ( over() & rows preceding and following )

 

분석함수 구조예시

SELECT quarter(payment_date) quarter,
	monthname(payment_date) month_nm,
    sum(amount) monthly_sales,
    max(sum(amount))
    	over (partition by quarter(paymet_date)) max_qrtr_sales
FROM payment
WHERE year(payment_date) = 2005
GROUP BY quarter(payment_date), monthname(payment_date);

 

분기, 월, 월 매출액, 해당 분기 중 max(amount)을 나타내는 쿼리입니다.

max(sum(amount)) : 윈도우 함수

over (partition by quart(payment_date)) max_qrtr_sales : 윈도우 함수는 group by 가 아닌 partition by로 집합을 구분합니다.

 

* 또한 over () 내에 order by 를 통해 순위를 할당 할 수도 있습니다.

** over() 내에 아무것도 없다면 전체 범위!

 

 

순위 함수

 

순위 함수의 종류

row_number : 동점일 경우 순위가 임의로 지정된 각 행의 고유번호를 반환합니다.

rank : 동점일 경우 순위에 차이가 있는 동일한 순위를 반환합니다.

dense_rank : 동점일 경우 순위에 차이가 없는 동일한 순위를 반환합니다. 

예시

customer_id	num_point	row_number	rank	dense_rank
3	100	1	1	1
2	90	2	2	2
4	85	3	3	3
1	85	4	3	3
5	70	5	5	4

 

 

 

리포팅 함수

분석 함수는 순위 뿐 아니라 sum, min, max, avg등 의 집계 함수를 사용하지만 GROUP BY 절이 아닌 over 절과 쌍을 이룹니다.

또한 CASE 표현식을 이용하여 준 순위 함수의 역할도 만들어 낼 수 있습니다.

 

 

누적합계 쿼리예시

SELECT yearweek(payment_date) payment_week,
	sum(amount) week_total,
    sum(sum(amount))
    	over (order by yearweek(payment_date)
        	rows unbounded preceding) rolling_sum
FROM payment
GROUP BY yearweek(payment_date)
ORDER BY 1;

 

연도 주차, 주별 매출액, 주별 매출 누적합계를 나타내는 쿼리입니다.

order by yearweek(payment_date) : 주별 매출액을 순서로 나타내며 해당 값이 누적 합계로 나타내기 위해

rows unbounded preceding 을 사용합니다.

* rows unbounded preceding : 이전의 행의 범위에 제한이 없습니다.

 

 

만약 sum(sum(amount)) 가 avg(sum(amount)) 로 바뀌고,

rows unbounded preceding 이 rows between 1 preceding and 1 following 으로 바뀐다면?

 

세번째 열은 3주 롤링 평균(지난주, 이번주, 다음주)을 구하는 열로 나타나게 됩니다!

 

 

lag()함수와 lead()함수

한 행의 값을 다른행과 비교할 때 사용하는 함수 입니다.

 

함수 예시

SELECT yearweek(payment_date) payment_week,
	sum(amount) week_total,
    lag(sum(amount), 1)
    	over (order by yearweek(payment_date)) prev_wek_tot,
    lead(sum(amount), 1)
    	over (order by yearweek(payment_date)) next_wek_tot
FROM payment
GROUP BY yearweek(payment_date)
ORDER BY 1;

 

payment_week	week_total	prev_wk_tot	next_wk_tot
202301	10	NULL	15
202302	15	10	13
202303	13	15	11
202305	11	13	7
202307	7	11	NULL