[SQL] OVER함수 및 PARTITION BY 사용법

* 본 내용은 <데이터 분석을 위한 SQL 레시피> 서적을 참고하여 작성했습니다.

review라는 테이블이 다음과 같이 존재할 때

user_id	product_id	score
U001	A001	4.0
U001	A002	5.0
U001	A003	5.0
U002	A001	3.0
U002	A002	3.0
U002	A003	4.0
U003	A001	5.0
U003	A002	4.0
U003	A003	4.0

used_id별 평균 점수를 계산하고싶을때가 있다. 따라서 다음과 같은 명령어를 실행시키면 된다

SELECT
    user_id,
    product_id,
    score,
    AVG(score) OVER() AS avg_score,
    AVG(score) OVER(PARTITION BY user_id) AS _avg_score,
    score - AVG(score) OVER(PARTITION BY user_id) AS user_avg_score_diff
FROM
    review;

OVER함수와 PARTITION BY 명령어를 이용하여 유저별 평균 점수를 구할수가 있다.

5번째 라인의 OVER()함수는 있어도 그만 없어도 그만

6번째 라인에서 PARTITION BY user_id라는 명령어를 사용하여 유저별로 나눠서 평균 점수를 구한다

결과 테이블은 다음과 같다

user_id	product_id	score	avg_score	user_avg_score	user_avg_score_dIff
U001	A001	4	4.11	4.67	-0.67
U001	A002	5	4.11	4.67	0.33
U001	A003	5	4.11	4.67	0.33
U002	A001	3	4.11	3.33	-0.33
U002	A002	3	4.11	3.33	-0.33
U002	A003	4	4.11	3.33	0.67
U003	A001	5	4.11	4.33	0.67
U003	A002	4	4.11	4.33	-0.33
U003	A003	4	4.11	4.33	-0.33