OVER clause and Window Functions

The OVER clause defines the window for a window function.

Purpose

Window functions are used to compute an aggregate or cumulative value, based on a group of objects. The objects are not grouped into a single output object — each object remains separate in the query output.

All window functions must have a window definition. This divides the query result set into one or more partitions, and specifies the order of objects in those partitions. Within each partition, a movable window frame is defined for every input object. The window frame determines the objects to be used by the window function.

Table of query result set with numbered callouts
Figure 1. Window partitions and the window frame

➀ The query result set.
➁ Window partitions — partitioned by name, ordered by time.
➂ The current object.
➃ The window frame — between unbounded preceding and current object.

The OVER clause specifies the window definition. Some window functions take additional window options, which are specified by further clauses before the OVER clause.

N1QL has a dedicated set of window functions. In Couchbase Server 6.5 Enterprise Edition and later, aggregate functions can also be used as window functions, when they are used with an OVER clause.

Prerequisites

For you to select data from keyspace or expression, you must have the query_select privilege on that keyspace. For more details about user roles, see Authorization.

Syntax

This page gives the generic syntax of window functions, window options, and the window definition. See the Aggregate Functions page or Window Functions page for details of the syntax of individual functions.

Window Function

window-function ::= window-function-type '(' window-function-arguments ')' [ window-function-options ] OVER '(' window-definition ')'
window-function-type '(' window-function-arguments ')' window-function-options? 'OVER' '(' window-definition ')'

Window Function Type

window-function-type ::= aggregate-functions | window-functions

Aggregate Functions

See the Aggregate Functions page for a list of aggregate functions.

Window Functions

See the Window Functions page for a list of window functions.

Window Function Arguments

window-function-arguments ::= [ [ aggregate-quantifier ] expr [ ',' expr [ ',' expr ] ] ]
window function arguments

See the Aggregate Functions page or Window Functions page for details of the arguments for individual functions.

Window Function Options

window-function-options ::= [ nthval-from ] [ nulls-treatment ]
window function options

Window function options cannot be used with aggregate functions.

Window function options can only be used with some window functions, as described below.

Nth Val From

nthval-from ::= FROM ( FIRST | LAST )
nthval from

The nth val from clause determines whether the computation begins at the first or last object in the window.

This clause can only be used with the NTH_VALUE() function.

This clause is optional. If omitted, the default setting is FROM FIRST.

Nulls Treatment

nulls-treatment::= ( RESPECT | IGNORE ) NULLS
nulls treatment

The nulls treatment clause determines whether NULL values are included in the computation, or ignored. MISSING values are treated the same way as NULL values.

This clause can only be used with the FIRST_VALUE(), LAST_VALUE(), NTH_VALUE(), LAG(), and LEAD() functions.

This clause is optional. If omitted, the default setting is RESPECT NULLS.

Window Definition

window-definition ::= [ window-partition-clause ] [ window-order-clause [ window-frame-clause [ window-frame-exclusion ] ] ]
window clause

The window definition specifies the partitioning, ordering, and framing for window functions.

Window Partition Clause

window-partition-clause ::= PARTITION BY expr [ ',' expr ]*
window partition clause

The window partition clause groups the query results into partitions using one or more expressions.

This clause may be used with any window function, or any aggregate function used as a window function.

This clause is optional. If omitted, all the query results are grouped into single partition.

Window Order Clause

window-order-clause ::= ORDER BY ordering-term [ ',' ordering-term ]*
window order clause

The window order clause determines how objects are ordered within each partition. The window function works on objects in the order specified by this clause.

This clause may be used with any window function, or any aggregate function used as a window function.

This clause is optional for some functions, and required for others. See the Aggregate Functions page or Window Functions page for details of the syntax of individual functions.

If this clause is omitted, all objects are considered peers, i.e. their order is tied. When objects in the window partition are tied, each window function behaves differently.

  • The ROW_NUMBER() function returns a distinct number for each object. If objects are tied, the results may be unpredictable.

  • The RANK(), DENSE_RANK(), PERCENT_RANK(), and CUME_DIST() functions return the same result for each object.

  • For other functions, if the window frame is defined by ROWS, the results may be unpredictable. If the window frame is defined by RANGE or GROUPS, the results are same for each object.

This clause may have multiple ordering terms. To reduce the number of ties, add additional ordering terms.

This clause does not guarantee the overall order of the query results. To guarantee the order of the final results, use the query ORDER BY clause.

Ordering Term

ordering-term ::= expr [ ASC | DESC ] [ NULLS ( FIRST | LAST ) ]
ordering term

The ordering term specifies an ordering expression, collation, and nulls ordering.

This clause has the same syntax and semantics as the ordering term for queries. See the ORDER BY clause page for details.

Window Frame Clause

window-frame-clause ::= ( ROWS | RANGE | GROUPS ) window-frame-extent
( 'ROWS' | 'RANGE' | 'GROUPS' ) window-frame-extent

The window frame clause defines the window frame.

This clause can be used with all aggregate functions and some window functions — see the descriptions of individual functions for more details.

This clause is allowed only when the window order clause is present.

This clause is optional.

  • If this clause is omitted and there is no window order clause, the window frame is the entire partition.

  • If this clause is omitted but there is a window order clause, the window frame becomes all objects in the partition preceding the current object and its peers — the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The window frame can be defined in the following ways:

ROWS

Counts the exact number of objects within the frame. If window ordering doesn’t result in unique ordering, the function may produce unpredictable results. You can add a unique expression or more window ordering expressions to produce unique ordering.

RANGE

Looks for a value offset within the frame. The function produces deterministic results.

GROUPS

Counts all groups of tied rows within the frame. The function produces deterministic results.

If this clause uses RANGE with either valexpr PRECEDING or valexpr FOLLOWING, the window order clause must have only a single ordering term. The ordering term expression must evaluate to a number.

If the ordering term expression does not evaluate to a number, the window frame will be empty, which means the window function will return its default value: in most cases this is NULL, except for COUNT() or COUNTN(), whose default value is 0.

This restriction does not apply when the window frame uses ROWS or GROUPS.

The RANGE window frame is commonly used to define a window frames based on date or time. In JSON, dates and times are represented as a string in ISO-8601 standard.

If you want to use RANGE with either valexpr PRECEDING or valexpr FOLLOWING, and you want to use an ordering expression based on date or time, use the appropriate date or time function to convert the date or time into milliseconds, then use the resulting number in the ordering expression.

Window Frame Extent

window-frame-extent ::= ( UNBOUNDED PRECEDING | CURRENT ROW | valexpr FOLLOWING ) | BETWEEN ( UNBOUNDED PRECEDING | CURRENT ROW | valexpr ( PRECEDING | FOLLOWING ) ) AND ( UNBOUNDED FOLLOWING | CURRENT ROW | valexpr ( PRECEDING | FOLLOWING ) )
('UNBOUNDED' 'PRECEDING' | 'CURRENT' 'ROW' | valexpr 'FOLLOWING' | 'BETWEEN' ( 'UNBOUNDED' 'PRECEDING' | 'CURRENT' 'ROW' | valexpr ( 'PRECEDING' | 'FOLLOWING' ) ) 'AND' ( 'UNBOUNDED' 'FOLLOWING' | 'CURRENT' 'ROW' | valexpr ( 'PRECEDING' | 'FOLLOWING' ) ) )

The window frame extent clause specifies the start point and end point of the window frame. The expression before AND is the start point and expression after AND is the end point. If BETWEEN is omitted, you can only specify the start point; the end point becomes CURRENT ROW.

The window frame end point can’t be before the start point. If this clause violates this restriction explicitly, an error will result. If it violates this restriction implicitly, the window frame will be empty, which means the window function will return its default value: in most cases this is NULL, except for COUNT() or COUNTN(), whose default value is 0.

Window frame extents that result in an explicit violation are:

  • ( ROWS | RANGE | GROUPS ) BETWEEN CURRENT ROW AND valexpr PRECEDING

  • ( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND valexpr PRECEDING

  • ( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND CURRENT ROW

Window frame extents that result in an implicit violation are:

  • ( ROWS | RANGE | GROUPS ) BETWEEN UNBOUNDED PRECEDING AND valexpr PRECEDING — if valexpr is too high, some objects may generate an empty window frame.

  • ( ROWS | RANGE | GROUPS ) BETWEEN valexpr PRECEDING AND valexpr PRECEDING — if the second valexpr is greater than or equal to the first valexpr, all result sets will generate an empty window frame.

  • ( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND valexpr FOLLOWING — if the first valexpr is greater than or equal to the second valexpr, all result sets will generate an empty window frame.

  • ( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND UNBOUNDED FOLLOWING — if valexpr is too high, some objects may generate an empty window frame.

  • If the window frame exclusion clause is present, any window frame specification may result in empty window frame.

The valexpr must be a positive constant or an expression that evaluates as a positive number. For ROWS or GROUPS, the valexpr must be an integer.

Window Frame Exclusion

window-frame-exclusion ::= EXCLUDE ( CURRENT ROW | GROUP | TIES | NO OTHERS )
window frame exclusion

The window frame exclusion clause enables you to exclude specified objects from the window frame.

This clause can be used with all aggregate functions and some window functions — see the descriptions of individual functions for more details.

This clause is allowed only when the window frame clause is present.

This clause is optional. If this clause is omitted, the default is no exclusion — the same as EXCLUDE NO OTHERS.

EXCLUDE CURRENT ROW

If the current object is still part of the window frame, it is removed from the window frame.

EXCLUDE GROUP

The current object and any peers of the current object are removed from the window frame.

EXCLUDE TIES

Any peers of the current object, but not the current object itself, are removed from the window frame.

EXCLUDE NO OTHERS

No additional objects are removed from the window frame.

If the current object is already removed from the window frame, then it remains removed from the window frame.

Examples

See the Window Functions page for examples.