OVER clause and Window Functions
- enterprise edition
- Couchbase Server 6.5
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.

➀ 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-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 ] ] ]

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 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 )

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

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 ] ] ]

The window definition specifies the partitioning, ordering, and framing for window functions.
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 ]*

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 byRANGE
orGROUPS
, 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 ) ]

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

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 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 |
The If you want to use |
Window Frame Extent

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
— ifvalexpr
is too high, some objects may generate an empty window frame. -
( ROWS | RANGE | GROUPS ) BETWEEN valexpr PRECEDING AND valexpr PRECEDING
— if the secondvalexpr
is greater than or equal to the firstvalexpr
, all result sets will generate an empty window frame. -
( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND valexpr FOLLOWING
— if the firstvalexpr
is greater than or equal to the secondvalexpr
, all result sets will generate an empty window frame. -
( ROWS | RANGE | GROUPS ) BETWEEN valexpr FOLLOWING AND UNBOUNDED FOLLOWING
— ifvalexpr
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 )

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.