윈도우(Window) 함수 구조

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

순위함수: RANK(), DENSE_RANK(), ROW_NUMBER()

구조

SELECT 컬럼 명,
RANK() OVER ([PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절])
FROM 테이블 명;

• 종류
  

예시

• Table item
  

SQL query 및 결과

RANK, DENSE_RANK, ROW_NUMBER

SELECT *, 
  RANK() OVER (ORDER BY category, price DESC) AS RK,
  DENSE_RANK() OVER (ORDER BY category, price DESC) AS DRK,
  ROW_NUMBER() OVER (ORDER BY category, price DESC) AS RN
FROM item
ORDER BY category, price DESC

with ORDER BY

SELECT *, 
  RANK() OVER (ORDER BY category, price DESC) AS RK,
  DENSE_RANK() OVER (ORDER BY category, price DESC) AS DRK,
  ROW_NUMBER() OVER (ORDER BY category, price DESC) AS RN
FROM item
ORDER BY category, price DESC

with PARTITION BY

SELECT *, 
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS RK,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS DRK,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS RN
FROM item
ORDER BY category, price DESC