윈도우함수 와 집계함수

 

 

윈도우 함수

순위, 집계 등 행과 행 사이의 관계를 정의하는 함수로 OVER 구문을 필수로 한다.

 

종류

1. 순위 함수: RANK() / DENSE_RANK() / ROW_NUMBER()
2. 집계 함수 : SUM() / MAX() / MIN() /  AVG() / COUNT()  
3. 행 순서 함수 : FIRST_VALUE() / LAST_VALUE() / LAG() / LEAD()  
4. 비율 함수 : CUME_DIST() / PERCENT_RANK() / NTILE() / RATIO_TO_REPORT()  

 

문법

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블명;

 

 

 

 

 

 

1. 순위함수

• RANK() :
  • 같은 값이 있다면 같은 순위로 표기, 모든 로우를 기준으로 해당 행의 순위 표기
• DENSE_RANK() :
  • 순위를 기준으로 계산 중복 순위가 있다면 다음 순위는 해당 순위 +1 임
• ROW_NUMBER() :
  • 같은 값이더라도 정렬된 순서대로 순위를 부

 

 

테이블 데이터

 

순위 함수 쿼리

SELECT 
    MEMBER_ID 
    ,SQUAT 
    ,BENCH_PRESS
    ,DEADLIFT 
    ,(SQUAT+BENCH_PRESS+DEADLIFT) AS WEIGHT_SUM 
    ,RANK() OVER (ORDER BY WEIGHT_SUM DESC)AS RANK	//순위 함수
FROM GYM_MEMBER;

 

쿼리 결과

 

 

집계 함수는 사용에 익숙함으로 패스~

 

 

 

2. 행 순서 함수 

• FIRST_VALUE() :
  • 첫 값
• LAST_VALUE() :
  • 마지막 값
• LAG() :
  • 이전 값
• LEAD() :
  • 다음값

 

 

현재 테이블 데이터

 

행 순서 함수 쿼리

SELECT 
    ID 
    , GROUP_NUM ,TIME_RECORD 
    , LAG(TIME_RECORD, 1) OVER(PARTITION BY GROUP_NUM ORDER BY TIME_RECORD ASC) AS LAG
	, LEAD(TIME_RECORD, 1) OVER(PARTITION BY GROUP_NUM ORDER BY TIME_RECORD ASC) AS LEAD 
    , FIRST_VALUE(TIME_RECORD) 
    	OVER(PARTITION BY GROUP_NUM ORDER BY TIME_RECORD ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FIRST_VALUE
    , LAST_VALUE(TIME_RECORD) 
    	OVER(PARTITION BY GROUP_NUM ORDER BY TIME_RECORD ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LAST_VALUE
FROM PARTICIPANT
ORDER BY GROUP_NUM ASC, TIME_RECORD ASC;

 

 

쿼리 결과

 

 

3. 비율 함수

 

쿼리 예시)

 

- 각 비용의 전체 비용 중 비율 

SELECT 
    ID
    , USE_CODE 
    , EXPENSE 
    , ROUND((EXPENSE / SUM(EXPENSE) OVER())*100 , 4)
FROM BUDGET_USE
ORDER BY ID ASC;

 

- 총점 기준으로 3분할 

SELECT 
    ID 
    , MATH 
    , PHYSICS 
    , CHEMISTRY 
    , (MATH + PHYSICS + CHEMISTRY) AS SCORE_SUM
    , NTILE(3) OVER(ORDER BY SCORE_SUM DESC) AS NTILE
FROM STUDENT
ORDER BY ID ASC;