일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
- 회귀분석
- 데이터분석
- text mining
- 스택/큐
- SparkSQL
- sparkml
- python
- hackerrank
- 터키 여행
- postgresql
- Deep Learning
- 딥러닝
- sql
- pyspark
- Machine Learning
- 머신러닝
- 텍스트마이닝
- 카파도키아
- 기초통계학
- 프로그래머스
- mysql
- 통계분석
- 튀르키예 여행
- 파이썬
- nlp
- 인과추론
- ADP실기
- 시계열분석
- 튀르키예
- spark
- Today
- Total
YSY의 데이터분석 블로그
[PostgreSQL] Window Function(윈도우 함수), 순위함수 본문
윈도우 함수란
- 윈도우 함수는 테이블에서 행집합을 대상으로 `함수입니다.
- 행집합은 한 행에 대해서 어떠한 방식으로 관계된 행들이며, 집합 단위로 계산한다는 점에서 집계 함수와 비슷합니다.
- 하지만 집계 함수는 해당 되는 행집합에 대해서 하나의 로우로 그 결과물을 보여주지만, 윈도우 함수는 각 행마다 처리결과를 출력합니다. 따라서 윈도우 함수는 집계결과뿐만 아니라 각행의 특징도 볼 수 있습니다.
윈도우 함수의 구조
- 집약함수로 윈도함수를 사용하려면 집약함수 뒤에 OVER를 붙이고 윈도함수를 지정합니다.
- 만약 OVER구문에 매개변수를 지정하지 않으면 테이블 전체에 집약함수가 적용됩니다.
- 매개변수에 PARTITION BY (컬럼이름) 을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약함수가 적용됩니다.
Select Window_Function(인수)
OVER(PARTITION BY 컬럼이름 ORDER BY 윈도우프레임)
From 테이블;
ORDER BY
- ORDER BY는 SELECT문에서 가지고 온 데이터를 특정 칼럼을 기준으로 테이블을 정렬하는 함수입니다.
- 기본값은 오름차순(ASC)(생략가능)이며 DESC를 입력하면 내림차순으로 정렬 할 수 있습니다.
순위함수
- ROW_NUMBER() : 유일한 순위를 붙이는 함수
- RANK() : 같은 순위를 허용해서 순위를 붙이는 함수(동순위 밑 순위는 동순위 개수만큼 증가)
- DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 뛰고 순위를 붙임
- LAG(대상칼럼, 행값) : 현재 행보다 앞에 있는 행의 값 출력
- LEAD(대상칼럼, 행값) : 현재 행보다 뒤에 있는 행의 값 출력
- FIRST_VALUE(대상칼럼) : 첫번째 레코드 추출
- LAST_VALUE(대상칼럼) : 마지막 레코드 추출
select
product_id
,score
,ROW_NUMBER() over(order by score DESC) as row_num
,RANK() over(order by score DESC) as ranks
,DENSE_RANK() over(order by score DESC) as dense_ranks
,LAG(product_id) over(order by score DESC) as lag1
,LAG(product_id, 2) over(order by score DESC) as lag2
,LEAD(product_id) over(order by score DESC) as lead1
,LEAD(product_id, 2) over(order by score DESC) as lead2
from review
order by row_num;
윈도프레임 지정별 상품 ID 집약
- array_agg 함수 활용
select product_id
, row_number() over(order by score DESC) as row
, array_agg(product_id)
over (order by score desc rows between unbounded preceding and unbounded following) as whole_agg
, array_agg(product_id)
over (order by score desc rows between unbounded preceding and current row) as cum_agg
, array_agg(product_id)
over (order by score desc rows between 1 preceding and 1 following) as local_agg
from popular_products
where category='action'
order by row
;
PARTITION BY와 ORDER BY조합
select
category
,score
,ROW_NUMBER() over(PARTITION BY category order by score DESC) as row_num
,RANK() over(PARTITION BY category order by score DESC) as ranks
,DENSE_RANK() over(PARTITION BY category order by score DESC) as dense_ranks
from popular_products
order by row_num;
윈도우 프레임
- ORDER BY구문과 집약함수를 조합할 수가 있는데 이때 사용되는 것이 윈도우프레임 지정입니다.
- 아래가 기본적인 프레임 구문이며 start와 end 사이를 바꾸어가며 프레임지정을 다르게 할 수 있습니다.
OVER(ORDER BY 칼럼 ROWS BETWEEN start AND end)
- CURRENT ROW: 현재의 행
- n PRECEDING : n행 앞
- n FOLLOWING : n행 뒤
- UNBOUNDED PRECEDING : 이전행 전부
- UNBOUNDED FOLLOWING : 이후 행 전부
select
product_id
,ROW_NUMBER() over(order by score DESC) as row_num
-- 누계점수 계산
,sum(score) over(order by score DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
as sum_score
-- 앞뒤로 평균계싼
,avg(score) over(order by score DESC
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
as avg_score
-- 순위가 가장 높은 id 추출
,FIRST_VALUE(product_id) over(order by score DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
as first
-- 순위가 가장 낮은 id 추출
,LAST_VALUE(product_id) over(order by score DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
as last
from review
order by row_num;
카테고리 별 최상위 상품 추출
select distinct category
,first_value(product_id)
over(PARTITION BY category order by score DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as product_id
from popular_products;
'SQL & DB > PostgreSQL' 카테고리의 다른 글
[PostgreSQL] 테이블 세로 결합(UNION) (0) | 2021.01.07 |
---|---|
[PostgreSQL] 데이터 행과 열 변환 (0) | 2021.01.07 |
[PostgreSQL] 집약함수와 Grouping(COUNT, SUM, AVG, MAX, MIN, GROUP BY) (0) | 2021.01.07 |
[PostgreSQL] 계산 관련 함수(ABS, POWER , SQRT, POINT) (0) | 2021.01.07 |
[PostgreSQL] 값 비교(CASE, SIGN, GREATEST, LEAST), 평균값 구하기 (0) | 2021.01.07 |