21 Sep 19 · npack · #Bigquery ·   Bookmark   ×

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

npack

posted on 21 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied