Over

To execute a window function, an OVER() clause is required. An OVER() clause can contain information about the partitions a window function should be applied on, the order in which a window function will operate over a given partition, as well as the window frames over which aggregation or certain window functions should be computed on.

PARTITION BY

The PARTITION BY option groups the rows of the query into partitions, which the window function processes separately. Without PARTITION BY, the query is treated as having only one partititon.

/*
In this example, we end up with three partitions.
One partition includes all rows with 1 as the number, one
includes only the row with 3 as the number value, and one
with only 4 as the number value.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, ROW_NUMBER() OVER(PARTITION BY number) AS row_number
  FROM numbers

+-----------+------------+ | number | row_number | |-----------+------------| | 1 | 1 | | 1 | 2 | | 3 | 1 | | 4 | 1 | +-----------+------------+

ORDER BY

The ORDER BY option determines the order in which the rows of a partition are processed. It works similarly to a query-level ORDER BY clause. Without ORDER BY, rows are processed in an unspecified order. Note that ORDER BY inside of a window function may not affect the row ordering in the output columns. The only thing ORDER BY guarantees is the order in which the window function is applied across a partition.

/*
In this example, we are applying the sum function
in descending order relative to the number column.
Note that both rows with number value 1 are treated
as peers, and are therefore aggregated together.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, SUM(number) OVER(ORDER BY number DESC) AS sum
  FROM numbers

+-----------+-----+ | number | sum | |-----------+-----| | 4 | 4 | | 3 | 7 | | 1 | 9 | | 1 | 9 | +-----------+-----+

RANGE/ROWS BETWEEN

The window frame specification for the current partition. This is either RANGE or ROWS. This defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, this sets the frame to be all rows from the partition start up to the current row. Without ORDER BY, all rows of the partition are included in the window frame. Restrictions are that frame_start cannot be UNBOUNDED FOLLOWING, frame_end cannot be UNBOUNDED PRECEDING, and the frame_end choice cannot appear earlier in the above list than the frame_start choice — for example RANGE BETWEEN CURRENT ROW AND 1 PRECEDING is not allowed.

/*
Here, our window frame is rows between 1 preceding and 1 following.
The COUNT(*) function operates over an independent window frame for
each row it processes.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, COUNT(*) OVER(ORDER BY number ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS count
  FROM numbers

+-----------+-------+ | number | count | |-----------+-------| | 1 | 2 | | 1 | 3 | | 3 | 3 | | 4 | 2 | +-----------+-------+

/*
Here, our window frame is everything in the range
between the current row and all rows after. Note
that both rows with number value 1 are treated as
peer rows, and recieve the same value for count.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, COUNT(*) OVER(ORDER BY number RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS count
  FROM numbers

+-----------+-------+ | number | count | |-----------+-------| | 1 | 4 | | 1 | 4 | | 3 | 2 | | 4 | 1 | +-----------+-------+

/*
Here, our window frame is between 1 preceding
and implicitly the current row. Note that the
rows with 1 as the number value are not treated
as peers.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, COUNT(*) OVER(ORDER BY number ROWS 1 PRECEDING) AS count
  FROM numbers

+-----------+-------+ | number | count | |-----------+-------| | 1 | 1 | | 1 | 2 | | 3 | 2 | | 4 | 2 | +-----------+-------+

/*
Here, our window frame is only peer rows to the current row.
*/
WITH numbers AS (
      SELECT 1 AS number
      UNION ALL SELECT 1
      UNION ALL SELECT 3
      UNION ALL SELECT 4
  )
  SELECT number, COUNT(*) OVER(ORDER BY number RANGE CURRENT ROW) AS count
  FROM numbers

+-----------+-------+ | number | count | |-----------+-------| | 1 | 2 | | 1 | 2 | | 3 | 1 | | 4 | 1 | +-----------+-------+

Aggregate Functions

All aggregate functions can also be used as window functions. When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a "running sum" behavior. To obtain aggregation over a row's entire partition, omit the ORDER BY clause entirely or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to capture the entire partition.