[SQL] 데이터 캠프 수강 (5) - PostgreSQL Summary Stats and Window Functions

Window Functions

• 행 집합에서 작업을 수행하므로 현재 작동중인 행과 관련이 있다.
• GROUP BY 집계함수와 비슷하지만, 모든 행이 출력에 나타난다는 점이 다르다.
• OVER() 을 활용해 Window Function임을 나타낸다. 아래는 내부에서 활용할 수 있는 명령어들이다.
  
  • ORDER BY : 순서 정렬을 해준다. *OVER 내부 쿼리의 ORDER BY가 외부 쿼리의 ORDER BY보다 먼저 실행된다.
  • PARTITION BY : 열의 값을 기반으로 테이블을 분할한다.
    • 2개 이상의 열을 활용할 때에는 ',' 로 구분지어준다.
  • ROWS/RANGE PRECIDING/FOLLOWING/UNBOUNDED : 
• ROW_NUMBER() : 인덱스를 생성하여 행의 번호를 입력해준다.

---

Fetching

• LAG/LEAD(column, n) : 현재 행 이전/이후의 n개의 행을 반환해준다. (상대적 개념)
• FIRST_VALUE/LAST_VALUE(column) : 테이블 또는 파티션의 첫 번째/마지막 값을 반환한다. (절대적 개념)
  • LAST_VALUE를 사용할 때 주의할 점은 현재 행을 기준으로 마지막 값을 반환하기 때문에, RANGE로 모든 값을 지정해주어야 한다.

Ranking

• ROW_NUMBER() : 동일한 값이더라도 고유한 순위를 부여한다.
  • ex) 순위 : 1,2,3,4,5, ...
• RANK() : 동일한 값에 동일한 순위를 부여하고, 동일한 값을 가진 수만큼 다음 순위를 건너띈다.
  • ex) 순위 : 1,2,2,4,5, ...
• DENSE_RANK() : 동일한 값에 동일한 순위를 부여하고, 동일한 값을 가진 수만큼 다음 순위를 건너띄지 않는다.
  • ex) 순위 : 1,2,2,3,4, ...
• 2개 이상의 그룹을 나누어 순위를 매길때 PARTITION BY를 사용하자.
  • GROUP BY를 사용한다면, 전체 데이터에 대한 순위가 매겨지기 때문에 원하는 결과를 못 얻을 수 있다.

Paging

데이터를 동일한 크기로 나누는 것을 의미한다. 4분위수 등을 알고 싶을 때 사용하자.

• NTILE(n) : 데이터를 n개의 동일한 크기로 분할하는 함수이다.

---

Aggregate Window Functions

• SUM() : 윈도우는 현재 행을 기준으로 모든 행을 참조하므로 누적 합계를 나타낸다.
• MAX(), MIN() : 각 행을 참조하며 최대/최소 값이 변경될 수 있으며, 최종적으로 최대/최소 값을 반환한다.

Frames

• ROWS BETWEEN [START] AND [FINISH]
  • n PRECIDING : 현재 행 이전의 n개의 값
  • CURRENT ROW : 현재 행
  • n FOLLOWING : 현재 행 이후의 n개의 값
  • *총 프레임 수 : n1-n2+1
• Moving Average : 마지막 n 기간의 평균
  • 계절성을 제거하는데 유용하다.
  • 추세를 나타내는 지표를 확인할 때 사용된다.
• Moving Total : 마지막 n 개의 합계
  • 최근 성과를 나타내는데 사용된다.
• RANGE BETWEEN 과 ROWS BETWEEN의 차이점
  • RANGE : ORDER BY 하위 절에 존재하는 중복된 값을 단일 값으로 처리한다.
    • 중복 값을 단일 행으로, 합계한 다음 각 중복 행에 대한 합계를 표시한다.
  • ROWS :  ORDER BY 하위 절에 존재하는 중복된 값을 단일 값으로 처리하지 않는다.

---

Pivoting

• CROSS TAB
  • CROSS TAB을 사용하기 이전에 CREATE EXTENSION 문을 사용해야 한다.
    • CREATE EXTENSION : 확장의 추가 기능을 사용하게 한다. (패키지 느낌)

• $$, $$ 사이에 소스 쿼리를 배치한 후, ()안에 새 피벗 테이블의 열 이름과 유형을 작성한다.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM CROSSTAB($$
	query ~~
$$)	AS ct (인덱스에 사용할 col, DATA TYPE,
		   피벗 테이블에 입력할 col1 값, DATA TYPE,
           피벗 테이블에 입력할 col2 값, DATA TYPE)

* col :: INTEGER <- column의 유형을 결정해준다.

 

ROLLUP and CUBE

• ROLL UP : Group By 집계를 위한 함수이다.
  • 계층적 집계 함수이며, 열의 순서를 다르게 지정해주면 다른 결과 값을 출력한다.
  • 계층적 데이터가 존재하는 경우 사용한다.
• CUBE : Group By 집계를 위한 함수이다.
  • 계층적이지 않으며, 가능한 모든 그룹의 수준에 대한 집계를 생성한다.
  • 가능한 모든 그룹의 수준의 집계를 원할 때 사용한다.
• COALESCE : NULL 값이 포함된 리스트에서 값을 차례대로 읽어, NULL 값이 아닌 첫번째 값을 반환한다.

COALESCE(col, value) AS ~~ -- Country 열에 존재하는 값이 NULL이라면, value를 반환해라

• STRING_AGG(column, separator) : 해당 column의 모든 값을 가져와서 separator를 사용하여 연결한다.
  • ex) STRING_AGG(country, ', ') = KOR, USA, ....