Rows between unbounded preceding and unbounded following in BigQuery - Window Function Syntax

Window Function:

window_frame_clause defines the window frame, around the current row within a partition, over which the analytic function is evaluated. window_frame_clause allows both physical window frames (defined by ROWS) and logical window frames (defined by RANGE)

select
SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) as ColumnAlias
from Employee;

Other scenarios / examples:

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

MAX(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) as ColumnAlias

AVG(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) as ColumnAlias

nVector

posted on 21 Sep 19