윈도우(Window) 함수 구조

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

집계 함수: SUM(), AVG(), MAX(), MIN()

구조

SELECT 컬럼 명,
집계 함수(컬럼) OVER ([PARTITION BY 절] [ORDER BY 절] [WINDOWING 절]) 
FROM 테이블 명

• 종류
  

예시

• Table web
  

SQL query 및 결과

SUM, AVG, MAX, MIN with PARTITION BY YYMM

SELECT *,
  SUM(Visits) OVER (PARTITION BY YYMM) AS SUM,
  AVG(Visits) OVER (PARTITION BY YYMM) AS AVG,
  MAX(Visits) OVER (PARTITION BY YYMM) AS MAX,
  MIN(Visits) OVER (PARTITION BY YYMM) AS MIN
FROM web

with PARTITION BY YYMMDD

SELECT *,
  SUM(Visits) OVER (PARTITION BY YYMMDD) AS SUM,
  AVG(Visits) OVER (PARTITION BY YYMMDD) AS AVG,
  MAX(Visits) OVER (PARTITION BY YYMMDD) AS MAX,
  MIN(Visits) OVER (PARTITION BY YYMMDD) AS MIN
FROM web

행 순서 집계 함수: FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()

구조

SELECT 컬럼 명,
행 순서 함수(기준 컬럼) OVER ([PARTITION BY 절] [ORDER BY 절] [WINDOWING 절]) 
FROM 테이블 명

• 종류
  

⭐ WINDOWING 절

• 행의 범위 기준을 의미
• ROWS BETWEEN A AND B 형태
  

예시

• Table item
  

SQL query 및 결과

FIRST_VALUE, LAST_VALUE

SELECT *,
  FIRST_VALUE(price) OVER (
    ORDER BY price DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FV,
  LAST_VALUE(price) OVER (
    ORDER BY price DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LV
FROM item

with PARTITION BY

SELECT *,
  FIRST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FV,
  LAST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price DESC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LV
FROM item

with CURRENT ROW

SELECT *,
  FIRST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price DESC 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FV,
  LAST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price DESC 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LV
FROM item

LAG, LEAD

SELECT *,
  LAG(price, 1) OVER (
    ORDER BY price DESC) LAG,
  LEAD(price, 1) OVER (
    ORDER BY price DESC) LEAD,
FROM item
ORDER BY price DESC

with n=2

SELECT *,
  LAG(price, 2) OVER (
    ORDER BY price DESC) LAG,
  LEAD(price, 2) OVER (
    ORDER BY price DESC) LEAD,
FROM my-first-project-466601.modulabs.item
ORDER BY price DESC