Different Windowing Functions

Tags:

May 12, 2016
SELECT
  num,
  AVG(num) OVER (ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) as moving_avg,
  AVG(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as moving_avg_middled,
  LAG(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as lag_mid,
  LEAD(num) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as lead_mid,
  MAX(num) OVER (ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more,
  MAX(num) OVER (ORDER BY num DESC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more_desc,
  MIN(num) OVER (ORDER BY num ASC ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING ) as max_here_3_more_asc,
FROM 
  ( SELECT 2 AS num),
  ( SELECT 4 AS num),
  ( SELECT 3 AS num),
  ( SELECT 1 AS num),