[Postgre SQL] RANK| DENSE_RANK | ROW_NUMBER | ANALYTICS SQL | PARTITION BY | ORDER BY | NULLS FIRST | NULLS LAST | COALESCE

Group by는 원본 데이터 집합의 레벨을 변경시키나,

Analytics SQL은 원본 데이터 집합의 레벨을 그대로 유지하면서 적용됩니다.

또한, Window를 이용하여 Row 단의 집합 연산이 가능합니다.

 

순위 및 비율 함수의 종류는,

rank, dense_rank, row_number 등이

 

집계 함수로는, 

sum, max, min, avg, count 등이 있습니다.

 

[순위 SQL] 

- 일반적인 순위 : rank, dense_rank, row_number 

 

- 0~1 사이 정규화 순위 : cume_dist (누적) , percent_rank

 

- 분위 : ntile 

아래 예시 테이블을 만들어 놨습니다.

Customer_ID	C_name	C_age	C_address	sale_amount	rank	dense_rank	row_number
3243	KANG	26	SEOUL	800	1	1	1
1293	KIM	26	BUSAN	700	2	2	2
2059	WON	12	SEOUL	400	3	3	3
849	CHOI	34	BUSAN	400	3	3	4
3392	KANG	52	DAEGOO	300	5	4	5
395	HEO	26	DAEGOO	200	6	5	6
13924	JO	21	SEOUL	100	7	6	7

 

select *
	,rank() over(order by sale_amount) as sale_rank
    ,dense_rank() over(order by sale_amount) as sale_dense_rank
    ,row_number() over(order by sale_amount) as sale_row_number
from sale.customer;

rank -> 공동 순위가 있을 경우 다음 순위는 공동 순위 개수 만큼 밀려서 정함. 

1, 2, 3, 3, 5, 6, 7

 

dense_rank -> 공동 순위가 있더라도 다음 순위는 바로 이어서 정함

1, 2, 3, 3, 4, 5, 6 

 

row_number -> 공동 순위가 있더라도 반드시 unique한 순위를 정함 

1, 2, 3, 4, 5, 6, 7

 

** 주소지 별 가장 매출이 높은 고객 정보 ( 고유 순위 ) 

 

select *
from (
select *
	,row_number () over(partition by C.address order by sale_amount desc) as sale_rank_desc
	from sale.customer
) a where sal_rank_desc = 1;

 

** 주소지 별 가장 매출이 높은 고객 TOP2 ( 고유 순위 ) 

select *
from (
select *
,row_number () over(partition by C.address order by sale_amount desc) as sale_rank_desc
from hr.emp
) a where sale_rank_desc <= 2;

 

** 주소지 별 가장 매출이 높은 고객과 가장 매출이 낮은 고객 정보 ( 고유 순위 )

select * 
, case when sale_rank_desc = 1 then 'top'
when sale_rank = 1 then 'bottom'
else 'middle' end as gubun
from(
select *
,row_number () over (partition by C.address order by sale.amount desc) as sale_rank_desc
,row_number () over (partition by C.address order by sale.amount) as sale_rank
from sale.customer
)  a where sale_rank_desc = 1 
or sale_rank = 1;

 

** Table에 NULL 값 있을 때 처리 방법

 

-> nulls first가 default

-> nulls last 가 업무적으로는 더 많이 활용되니 가져다 쓰면 됩니다.

select *
	, rank() over (order by sale_amount desc nulls last ) as sale_amount_rank
	, row_number() over (order by sale_amount desc nulls last) as sale_amount_rnum
from sale.customer;

-- null을 전처리할 때는 coalesce 활용

select *
	, rank() over (order by COALESCE(sale_amount, 0) desc ) as sale_amount_rank
	, row_number() over (order by COALESCE(sale_amount, 0) desc) as sale_amount_row_rank
from sale.customer;