Signature and purpose of each window function

The two tables at the end classify the eleven built-in window functions into two groups according to their general common characteristics.

Note: The navigation bar lists these window functions in four functional groups. The members in each group bear a strong family resemblance to each other. The first two groups list functions from the first table below. And the second two groups list functions from the second table.

Aggregate function variants

A few of these also have an aggregate function variant. This can be seen with the \df meta-command. For example, df lag shows this:

 Result data type |          Argument data types           |  Type
------------------+----------------------------------------+--------
 bigint           |                                        | window
 bigint           | VARIADIC "any" ORDER BY VARIADIC "any" | agg

This property is marked by "Y" in the column "agg?" in the following tables; a blank in this column means that the entry has only a window function variant.

Frame_clause sensitivity

The results of all of the window functions depend upon what the window ORDER BY clause and the optional PARTITION BY clause say. Though you don't get an error if you omit the window ORDER BY clause, its omission brings unpredictable and therefore meaningless results.

The results of a few of the window functions are sensitive to what the frame_clause says. This is marked by "Y" in the column "frame?" in the following tables; a blank in this column means that the entry is not sensitive to what the frame_clause says.

Frame_clause-insensitive window functions

All of the window functions listed in the first table, and lag() and lead() from the second table, are insensitive to whatever the frame_clause might say. You can easily show this by trying a few variants like, for example, this:

-- Counter example. Don't use this for window functions
-- that aren't frame_clause-sensitive.
range between 1 preceding and 1 following exclude current row

It says "consider only the row immediately before and the row immediately after the current row". You'll see that including this, or any other variant, in the window_definition brings the identical result to what including only the window ORDER BY clause brings for each of the window functions that aren't sensitive to the frame_clause.

Yugabyte recommends that you never include a frame_clause in the window_definition that you use when you invoke a window function that isn't sensitive to the frame_clause.

Frame_clause-sensitive window functions

The names of the other window functions that the second table lists, first_value(), nth_value() and last_value(), tell you that the output of each makes obvious sense when the scope within which the specified row is found is the entire window. The results of these three functions certainly are sensitive to the frame_clause. This is the default for the frame_clause:

 -- This specification is used if you omit the frame_clause.
 -- You probably don't want the results that this specifies.
 between unbounded preceding and current row

See the section Window function invocation—SQL syntax and semantics. But the default does not specify the entire window. To do this, use this variant:

-- You must specify this explicitly unless you are sure
-- that you want a different specification.
range between unbounded preceding and unbounded following

Yugabyte recommends, therefore, that you include frame_clause in the window_definition that you use when you invoke a window function that is sensitive to the frame_clause, unless you have one of the very rare use cases where the output that you want is produced by a different frame_clause.

Note: The frame_clause's many variants are useful when an aggregate function is invoked using the OVER clause. The section Using the aggregate function avg() to compute a moving average shows an example.

Window functions that return an "int" or "double precision" value as a "classifier" of the rank of the row within its window

The only information, in the input row set, that the functions in this group depend upon is the emergent order as determined by the window ORDER BY clause. The actual order depends in the usual way on what this clause says. With the one exception of ntile(), these functions have no formal parameters. The ntile() function has a single, mandatory int formal parameter. It specifies the number of subsets into which the input row set should be classified. This means that it reflects only the invoker's intention and does not reflect the shape of the input.

Window functions that return columns of another row within the window

The functions in this group depend, in the same way as those in the first group do, upon the emergent order as determined by the window ORDER BY clause. Each has, at least, a single, mandatory, anyelement formal parameter that specifies which value to fetch from the designated other row in the window. If you want to fetch values from more than one column, you must combine them into a scalar value. The most obvious way to do this is to list the columns in the constructor for a user-defined "row" type. The nth_value() function has a mandatory second int formal parameter that specifies the value of "N" to give "Nth" its meaning. The other functions in this group have just one formal parameter.