[TIL 240628] Pivot Table, Window Function

오늘의 학습 내용은 다음과 같다.

---

 

1. 피벗 테이블 Pivot Table

 

피벗 테이블은 데이터 회전을 뜻한다.

즉 데이터를 행에서 열로 변환하는 프로세스를 말하며,

보고서에 적합한 정보를 제공하는 요약 테이블이라 할 수 있다.

 

 

위와 같은 프로세스를 거쳐 진행하게 된다.

1. 행을 group by로 combine 한다.

2. case, if, where 등으로 filter를 해준다.

 

 

 

2. 윈도우 함수 Window Function

 

SQL은 집계함수를 통해

열 (Column) 간의 연산은 수행할 수 있지만,

행 (Row) 간의 연산은 처리하기 어렵다.

이 때 사용하는 것이 윈도우 함수다.

 

윈도우 함수 구조

SELECT
	WINDOW_FUNCTION (ARGUMENTS)
    	OVER( [PARTITION BY 컬럼] [ORDER BY] [ROWS BETWEEN ~ AND] ) 
FROM TABLE;

 

윈도우 함수에는 Over 가 필수적으로 들어간다.

 

그 밖에도

partition by column

order by column

rows between ~ and ~

등의 옵션들이 들어간다.

 

group by와 partition by의 차이는 아래와 같다.

	group by	partition by
기능	자르기 + 집약	자르기
특징	1. group by 에 지정된 컬럼으로 데이터를 자른다. 2. 집계 함수를 이용해 집약시킨다.	1. partition by 에 지정된 컬럼으로 데이터를 자른다.
차이점	행의 수가 줄어든다	행의 수가 유지된다

 

"집약"을 해주느냐가 다르다.

group by를 사용하면 특정 컬럼 별로, "하나의 열"로 보여주는 반면,

partition by를 사용하면 하나로 통합하지 않고, "모든 열"을 다 보여준다.

그래서 두 결과는 행의 수가 다르다.

 

윈도우 함수 종류

 

1. 순위 함수

 

Rank, Dense_rank, Row_number 등이 있다.

 

rank()와 dense_rank()

select a, b, rank() over(order by b desc) as AA
from table ;

select a, b, dense_rank() over(order by b desc) as BB
from table ;

 

rank()는 공동 순위 후, 그 갯수만큼 건너뛰고 다음 순위를 부여하고

dense_rank()는 건너뛰지 않고 바로 다음 순위를 부여한다.

 

row_number()

select a, b, row_number() over(order by a, b)
from table;

 

 

2. 행 순서 함수

 

first_value() 와 last_value()

first_value(a) over(partition by b order by c desc) as AA

last_value(a) over(partition by b order by c desc) as AA

 

위의 예시는  a 컬럼을 b를 기준으로 c로 내림차순 정렬했을 때 제일 먼저 오는 값을 반환하는 것,

last_value() 는 그 반대로 제일 나중에 오는 값을 반환한다.

 

 

lag() 와 lead()

lag(a, 2, '없음') over(order by a)

lead(a, 2, '없음') over(order by a)

 

위의 예시는 a 컬럼을 a 오름차순으로 정렬했을 때 2 행 위의 값을 반환하고,

그 값이 없을 때는 "없음"을 반환한다.

lead는 그 반대다.

 

3. 비율함수

 

percent_rank() 와 cume_dist()

percent_rank() over(partition by a ORDER BY b desc)

cume_dist() over(partition by a ORDER BY b desc)

 

a 별로 가장 처음 나오는 값을 0, 가장 나중에 나오는 값을 1로 해

행의 순서를 기준으로 백분율 값을 반환한다.

 

cume_dist()는 현재 행보다 작거나 같은 데이터에 대해 누적 백분율을 반환한다.

 

Ntile()

ntile(4) over(order by a desc)

 

a 로 order 했을 때, 전채 행을 n-tile 로 나눠 값을 반환한다.

예를 들어 ntile(4) 일 경우, quartile 로 1,2,3,4 로 각 행을 나눠준다.