Order by 함수와 OVER, 윈도우 함수

OVER함수는 서브쿼리를 쉽게 사용하게 해준다.

OVER절은 FROM,WHERE,GROUP BY, HAVING 절 이후에 계산되므로 쿼리 짤 때 주의

GROUP BY를 OVER안에서 사용하고 싶을 때는 -> PARITION BY

예시1: SUM(SALARY) OVER (PARITION BY TEAM)하면 TEAM별 연봉총합 나옴

 

예시 2 SELECT EMPLOYEE_ID, JOB_ID,
SUM(SALARY) OVER() 전체총합
SUM(SALARY) OVER(PARTITION BY JOB_ID ORDER BY JOB_ID) 직무별 총합
FROM EMPLOYEE_ID  

 

*출처: https://jhnyang.tistory.com/469

https://www.oracletutorial.com/oracle-aggregate-functions/

[SQL] 오라클 over절에 대해 알아보자 - 개념 및 사용법 정리

 

[SQL] 오라클 over절에 대해 알아보자 - 개념 및 사용법 정리

 

 

ORDER BY score DESC 이면 내림차 순 정렬

ROW_NUMBER 유일한 순서 번호 붙임

RANK, DENSE RANK 같은 순위 있을 때 같은 번호

RANK는 같은 순위 다음거 (55 '6' 빼고 바로 7) 빼고 붙이고

DENSE RANK는 같은순위 다음번호도 넣음 (55 6 7)

 

데이터셋

DROP TABLE IF EXISTS popular_products;
CREATE TABLE popular_products (
    product_id varchar(255)
  , category   varchar(255)
  , score      numeric
);

INSERT INTO popular_products
VALUES
    ('A001', 'action', 94)
  , ('A002', 'action', 81)
  , ('A003', 'action', 78)
  , ('A004', 'action', 64)
  , ('D001', 'drama' , 90)
  , ('D002', 'drama' , 82)
  , ('D003', 'drama' , 78)
  , ('D004', 'drama' , 58)
;

#over함수, order by와 순위매기는 함수들 사용해 내부 순서 다루기

over함수는 서브쿼리문의 길이를 줄여준다.

 

 

SELECT product_id, score 
, ROW_NUMBER() OVER(ORDER BY score DESC) AS ROW 
, RANK() over(ORDER BY score DESC) AS RANK 
, DENSE_RANK() OVER(ORDER BY score DESC) AS DENSE_RANK 

FROM popular_products 
ORDER BY rank

 

 

 

#윈도프레임 함수 - ROWS BETWEEN start AND end

curretn row 현재, n preceding n행 앞, n following , unbounded predeing 이전행 전부, unbounded following 다음행 전부

# 순위 상위로부터 누계 점수 계산하는 cum_score, 앞뒤행 기준으로 평균점수내는 local_avg, score순위 제일 높은 prodcutid 추출하는 first_value와 낮은 것 추출하는 last_value

 

SELECT product_id, score
,ROW_NUMBER() OVER(ORDER BY score desc) AS ROW 
,sum(score) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_score
,avg(score) over(ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS local_avg
,FIRST_VALUE(product_id) OVER (ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_value
,LAST_VALUE(product_id) OVER(ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_VALUE
FROM popular_products 
ORDER BY ROW;'