[DA] 30일 데이터 분석가 챌린지 2일차 (Analytic SQL - 1)

Analytic SQL 개요

<Analytic SQL 함수>

유형	함수
순위/비율 함수	Rank, dense_rank, row_number, percent_rank, cume_dist, ntile
집계(Aggregate) 함수	sum, max, min, avg, count
Lead/Lag	Lead, Lag
First_value/Last_value	First_value, Last_value
Incerse Percentile	Percentile_cont, Percentile_disc

<Analytic SQL Syntax>

<Analytic function> (인자1, ...)
OVER (
      [ Partition 절 ] -- 그룹화 컬럼명
      [ Sorting 절 ]   -- 정렬 컬럼명(Window 이동 방향 기준 컬럼명)
      [ Window 절 ]    -- window 범위(row, range)
)

• 원본 데이터의 레벨을 그대로 유지하면서, 그룹핑 레벨에서 자유롭게 Window의 이동과 크기를 조절하면서 Analytic 을 수행. 
• 자유로운 window 설정에 따른 analytic 구사가 가능하므로 SQL의 Analytic 함수를 window 함수로도 지칭

syntax 출처:&nbsp; https://www.kaggle.com/alexisbcook/analytic-functions

<순위 Analytic SQL>

• 전체 데이터/특정 그룹핑 내에서 특정 기준으로 순위를 매기는 함수
• Rank, dense_rank, row_numbe는 공동 순위를 정하는 로직이 조금씩 다르다.

순위 함수	Description
rank	공동 순위가 있을 경우 다음 순위는 공동 순위 개수만큼 밀려서 정함 1, 2, 2, 4 또는 1, 2, 2, 2, 5
dense_rank	공동 순위가 있더라도 다음 순위는 바로 이어서 정함. 1, 2, 2, 3 또는 1, 2, 2, 2, 3
row_number	공동 순위가 있더라도 반드시 unique한 순위를 정함 1, 2, 3, 4, 5 (공동 순위일 때 순위를 정하는 방법은 RDBMS 마다 상이함)

• 기준에 따라서 order by (컬럼명) 뒤어 desc를 써주어야 한다.
• 만약 특정 그룹별로 순위를 매기고 싶다면, partition by를 사용해야 한다.
• null 값에 대한 처리를 유의해야 한다.

select *
	, rank() over(order by sal desc) as rank 
	, dense_rank() over(order by sal desc) as dense_rank
	, row_number() over (order by sal desc) as row_number 
from hr.emp a;

순위 컬럼에서 where 절을 통해 조건을 두고 싶다면, from 절 안에서 서브 쿼리를 만들어야 한다.

-- 부서별 가장 급여가 높은 직원 정보:  공동 순위는 없으며 반드시 고유 순위를 정함.  
select *
from 
(
select e.ename, e.job
	, row_number() over (partition by e.deptno order by e.sal) as rank
from hr.emp e
) a where rank = 1

최대값과 최소값에 대한 데이터를 추출해달라고 할 때, desc로 첫번째, asc로 첫번째 rank를 추출하면 훨씬 간단하게 추출이 가능하다.

-- 부서별 가장 급여가 높은 직원과 가장 급여가 낮은 직원 정보. 공동 순위는 없으며 반드시 고유 순위를 정함
select a.*
	, case when sal_rn_desc=1 then 'top'
	       when sal_rn_asc=1 then 'bottom'
	       else 'middle' end as gubun
from (
	select a.*
		, row_number() over (partition by deptno order by sal desc) as sal_rn_desc
		, row_number() over (partition by deptno order by sal asc) as sal_rn_asc
	from hr.emp a
) a where sal_rn_desc = 1 or sal_rn_asc=1;

-- 부서별 가장 급여가 높은 직원과 가장 급여가 낮은 직원 정보 그리고 두 직원값의 급여차이도 함께 추출. 공동 순위는 없으며 반드시 고유 순위를 정함
with
temp_01 as (
	select a.*
		, case when sal_rn_desc=1 then 'top'
		       when sal_rn_asc=1 then 'bottom'
		       else 'middle' end as gubun
	from (
		select a.*
			, row_number() over (partition by deptno order by sal desc) as sal_rn_desc
			, row_number() over (partition by deptno order by sal asc) as sal_rn_asc
		from hr.emp a
	) a where sal_rn_desc = 1 or sal_rn_asc=1
),
temp_02 as (
	select deptno
		, max(sal) as max_sal, min(sal) as min_sal
	from temp_01 group by deptno
)
select a.*, b.max_sal - b.min_sal as diff_sal 
from temp_01 a 
	join temp_02 b on a.deptno = b.deptno
order by a.deptno, a.sal desc;

with을 통해서 쿼리를 만들고, 그 다음에 join에 대한 연산을 진행하는 것이 훨씬 간결하다!

여러 개의 쿼리를 만드는 것에 거부감을 느끼거나 어려워하지 말자

<NULL 처리하기>

rank() over(
	<partition 절>
    order by column [nulls first/last] 
)

• Nulls first는 Null을 최우선 순위로, Nulls last는 Null을 가장 마지막 순위로 설정. 
• NULL을 0으로 처리해도 되는 경우에는 COALESCE로 NULL 값을 0으로 바꾸거나, NULL을 맨 처음이나 마지막 rank로 분류할 수 있다.

집계(Aggregate) Analytic과 Window 상세

<Aggregate Analytic SQL 개요>

<Analytic function> (인자1, ...)
OVER (
      [ Partition 절 ] -- 그룹화 컬럼명
      [ Sorting 절 ]   -- 정렬 컬럼명(Window 이동 방향 기준 컬럼명)
      [ Window 절 ]    -- window 범위(row, range)
)

<Sum Analytic SQL>

order by를 사용하면 window 절을 생략해도 디폴트 값으로 사용된다. 

누적합을 계산하는 연산

select order_id, line_prod_seq, product_id, amount
, sum(amount) over (partition by order_id) as total_sum_by_ord 
, sum(amount) over (partition by order_id order by line_prod_seq) as cum_sum_by_ord_01 -- 누적 합
, sum(amount) over (partition by order_id order by line_prod_seq rows between unbounded preceding and current row) as cum_sum_by_ord_02
, sum(amount) over ( ) as total_sum
from nw.order_items;

• 집계(aggregate) 계열 analytic 함수는 order by 절이 있을 경우 window 절은 기본적으로 range unbounded preceding and current row 이다.
• 만약 order by 절이 없다면 window는 해당 partition의 모든 row를 대상으로 결과값을 도출한다.
• 만약 partition 절도 없다면 window는 전체 데이터의 row를 대상으로 결과값을 도출한다.

<Max/Min Analytic SQL>

order_id 별 상품 최대 구매금액과 order_id별 상품 누적(순차적 비교) 최대 금액을 추출하는 쿼리

select order_id, line_prod_seq, product_id, amount
           , max(amount) over (partition by order_id) as max_by_ord 
           , max(amount) over (partition by order_id order by line_prod_seq) as cum_max_by_ord
from nw.order_items;

max(amount) over (partition by order_id) as max_by_ord

→ order_id가 동일한 상품 중에서 최대 구매 금액으로 컬럼이 채워진다.

max(amount) over (partition by order_id order by line_prod_seq) as cum_max_by_ord

→ order_id가 동일한 상품 중에서 순차적으로 탐색하면서 최대값이 업데이트된 형태로 컬럼이 채워진다.

<avg( ) Analytic SQL>

 order_id 별 상품 평균 구매금액, order_id별 상품 누적(순차적 비교) 평균 구매금액

select order_id, line_prod_seq, product_id, amount
     , avg(amount) over (partition by order_id) as avg_by_ord 
     , avg(amount) over (partition by order_id order by line_prod_seq) as cum_avg_by_ord
from nw.order_items;

avg(amount) over (partition by order_id) as avg_by_ord 

→ order_id가 동일한 상품들의 평균값으로 컬럼이 채워진다.
avg(amount) over (partition by order_id order by line_prod_seq) as cum_avg_by_ord

→ order_id가 동일한 상품을 순차적으로 탐색하면서 평균값이 업데이트되어 컬럼이 채워진다.

<count( ) Analytic SQL 활용>

 order_id 별 건수, order_id별 누적 건수

select order_id, line_prod_seq, product_id, amount
     , count(line_prod_seq) over (partition by order_id) as cnt_by_ord 
     , count(line_prod_seq) over (partition by order_id order by line_prod_seq) as cum_cnt_by_ord
from nw.order_items;

count(line_prod_seq) over (partition by order_id) as cnt_by_ord 

→ order_id가 동일한 상품들의 전체 건수로 컬럼이 채워진다.
count(line_prod_seq) over (partition by order_id order by line_prod_seq) as cum_cnt_by_ord

→ order_id가 동일한 상품들을 순차적으로 탐색하면서 건수가 하나씩 업데이트되어 컬럼이 채워진다.

<Window 절 구문>

Window 함수는 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수이다.

windowing_clause =
     { ROWS | RANGE }
     { BETWEEN
	{ UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING }
     } 
     AND
     { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING }
     }
   | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING}
   }

구문	구문 설명
ROWS | RANGE	Window의 개별 row를 정의함.  Rows는 물리적인 row, Range는 논리적인 row 의미.  Order by 절이 없으면 해당 구문은 기술할 수 없음.  over (partition by {column_name} order by unit_price rows between unbounded preceding and current row) over (partition by {column_name} order by order_date range between interval '2' day preceding and current row)
BETWEEN ... AND	Window의 시작과 종료 지점을 기술.  Between {시작 지점} And {종료 지점}   Between이 없다면 Row|Range 다음이 시작점, (기본 설정) Current row가 종료점으로 설정.  over (partition by {column_name} order by unit_price rows between 2 preceding and current row) over (partition by {column_name} order by unit_price rows 2 preceding) -- 생략도 가능 but 권장 X
UNBOUNDED PRECEDING	Window의 시작이 Partition의 첫번째 row부터 시작함을 기술.  Window의 종료점으로는 사용될 수 없음.
UNBOUNDED FOLLOWING	Window의 종료가 Partition의 마지막 row에서 종료됨을 기술.  Window의 시작점으로는 사용될 수 없음.
CURRENT ROW	Window의 시작점 또는 종료점으로 사용될 수 있으나, 보통은 종료점으로 사용. 종료점으로 사용시 window의 종료가 현재 row에서 종료됨을 기술.   시작점으로 사용시 window의 시작이 현재 row에서 시작됨을 기술 over (partition by {column_name} order by unit_price  rows between unbounded preceding and current row) over (partition by {column_name} order by unit_price rows between current row and unbounded following)

구문	구문 설명 : 특정 지점을 window의 시작 또는 종료 지점으로 나타낼 수 있음.
ROWS + value_expr PRECEDING / value_expr FOLLOWING	over (partition by {column_name} order by unit_price rows between 2 preceding and current row) over (partition by {column_name} order by unit_price rows between 2 preceding and 1 following) over (partition by {column_name} order by unit_price rows between and 1 following and current row)
RANGE + value_expr PRECEDING / value_expr FOLLOWING	Range와 사용할 때는 논리적인 row 위치를 지정하므로 value 표현이 숫자외에도 논리적인 값을 적용되어야 함. 보통은 숫자값과 interval 값이 사용됨. 또한 order by 절의 컬럼도 numeric 또는 (대부분) date/timestamp 가 되어야 함.  over (partition by {column_name} order by order_date range between interval '2' day preceding and current row) 위 구절에서 range의 경우 테이블에 2일 전의 값이 없더라도 논리적으로 요구한 2일 전의 값들에 대한 연산을 진행해서 컬럼을 채운다. 그러나 row의 경우 2일 전이 아니라 단순히 이전의 2개의 행에 대한 연산을 수행한다.

 

<Moving Average(이동 평균)>

범위 구간을 이동하면서 구하는 평균값

-- 3일 이동 평균이라고 한다면,
avg(daily_sum) over (order by ord_date rows between 2 preceding and current row) as avg_3days

유의사항

• Aggregation 함수 자체가 NULL은 연산에 활용하지 않기 때문에! 꼭 전처리를 통해 목적에 맞게 처리하는 것이 중요!!

[Reference]

https://www.kaggle.com/code/alexisbcook/analytic-functions/tutorial

Analytic Functions

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174