Informal overview of function invocation using the OVER clause
Aggregate functions can be invoked with the
OVER clause. Examples are given using
These examples are sufficient to give a general sense of the following notions:
- how window functions are invoked, and their general semantics
- the three clauses of the
PARTITION BYclause, the window
ORDER BYclause, and the
- how an aggregate function gains different useful functionality when it's invoked using an
OVERclause rather than (as is probably more common) in conjunction with the regular
If you haven't yet installed the tables that the code examples use, then go to the section The data sets used by the code examples.
Using row_number() in the simplest way
row_number() window function is the simplest among the set of eleven such functions that YSQL supports. Briefly, this function assigns an ordinal number, starting at 1, to the rows within the specified window according to the specified ordering rule. Here is the most basic example.
select k, row_number() over(order by k desc) as r from t1 order by k asc;
The syntax and semantics of the
ORDER BY clause, within the parentheses of the
OVER clause, are identical to what you're used to when an
ORDER BY clause is used after the
FROM clause in a subquery. The
DESC keyword is used in this example to emphasize this point. It says that the values returned by
row_number() are to be assigned in the order corresponding to sorting the values of "k" in descending order—and it specifies nothing else. Here is the result:
k | r ----+---- 1 | 25 2 | 24 3 | 23 4 | 22 5 | 21 ... 21 | 5 22 | 4 23 | 3 24 | 2 25 | 1
The output lines for values of "r" between 6 and 20 were manually removed to reduce the clutter.
The next example emphasizes the point that a window function is often used in a subquery which, like any other subquery, is used to define a
WITH clause view to allow further logic to be applied—in this case, a
WHERE cause restriction on the values returned by
row_number() (and, of course, a final query-level
ORDER BY rule).
with v as ( select k, row_number() over(order by k desc) as r from t1) select k, r from v where (r between 1 and 5) or (r between 21 and 25) order by r asc;
This is the result:
k | r ----+---- 25 | 1 24 | 2 23 | 3 22 | 4 21 | 5 5 | 21 4 | 22 3 | 23 2 | 24 1 | 25
Showing the importance of the window ORDER BY clause
Here is a counter example. Notice that the
window_definition doesn't specify a window
ORDER BY clause.
with a as ( select -- The use of the bare OVER() here brings meaningless results. row_number() over () as r, class, k from t1) select r, class, k, case k=r when true then 'true' else '' end as chk from a order by r;
To see the most dramatic effect of the unpredictability of the result set, save the code from table t1 into a file called, say, "unpredictable.sql". Then copy the SQL statement, above, at the end of this file and invoke it time and again in
ysqlsh. Here is a typical result:
r | class | k | chk ----+-------+----+------ 1 | 5 | 23 | 2 | 5 | 25 | 3 | 2 | 9 | 4 | 1 | 4 | true 5 | 3 | 11 | 6 | 1 | 1 | 7 | 3 | 13 | 8 | 4 | 16 | 9 | 1 | 2 | 10 | 2 | 7 | 11 | 1 | 3 | 12 | 4 | 18 | 13 | 3 | 15 | 14 | 5 | 21 | 15 | 3 | 14 | 16 | 3 | 12 | 17 | 4 | 17 | true 18 | 1 | 5 | 19 | 2 | 10 | 20 | 4 | 20 | true 21 | 5 | 24 | 22 | 5 | 22 | true 23 | 2 | 6 | 24 | 2 | 8 | 25 | 4 | 19 |
Sometimes, you'll see that, by chance, not a single output row is marked "true". Sometimes, you'll see that a few are so marked.
Using row_number() with "PARTITION BY"
This example adds a
PARTITION BY clause to the window
ORDER BY clause in the
window_definition . It selects and orders by "v" rather than "k" because this has
NULLs and demonstrates the within-window effect of
NULLS FIRST. The
window_definition is moved to a dedicated
WINDOW clause that names it so that the
OVER clause can simply reference the definition that it needs. This might seem only to add verbosity in this example. But using a dedicated
WINDOW clause reduces verbosity when invocations of several different window functions in the same subquery use the same
\pset null '??' with a as ( select class, v, row_number() over w as r from t1 window w as (partition by class order by v desc nulls first)) select class, v, r from a where class in (2, 4) order by class, r;
This is the result:
class | v | r -------+----+--- 2 | ?? | 1 2 | 9 | 2 2 | 8 | 3 2 | 7 | 4 2 | 6 | 5 4 | ?? | 1 4 | 19 | 2 4 | 18 | 3 4 | 17 | 4 4 | 16 | 5
Using nth_value() and last_value() to return the whole row
If you want the output value for any of
lead() to include more than one column, then you must list them in a "row" type constructor. This example uses
nth_value(). This accesses the Nth row within the ordered set that each window defines. It picks out the third row. The restriction "class in (3, 5)" cuts down the result set to make it easier to read.
drop type if exists rt cascade; create type rt as (class int, k int, v int); select class, nth_value((class, k, v)::rt, 3) over w as nv from t1 where class in (3, 5) window w as ( partition by class order by k range between unbounded preceding and unbounded following ) order by class;
It produces this result:
class | nv -------+----------- 3 | (3,13,13) 3 | (3,13,13) 3 | (3,13,13) 3 | (3,13,13) 3 | (3,13,13) 5 | (5,23,23) 5 | (5,23,23) 5 | (5,23,23) 5 | (5,23,23) 5 | (5,23,23)
nth_value(), as their names suggest, produces the same output for each row of a window. It would be natural, therefore, to use the query above in a
WITH clause whose final
SELECT picks out the individual columns from the record and adds a
GROUP BY clause, thus:
drop type if exists rt cascade; create type rt as (class int, k int, v int); \pset null '??' with a as ( select last_value((class, k, v)::rt) over w as lv from t1 window w as ( partition by class order by k range between unbounded preceding and unbounded following)) select (lv).class, (lv).k, (lv).v from a group by class, k, v order by class;
class | k | v -------+----+---- 1 | 5 | ?? 2 | 10 | ?? 3 | 15 | ?? 4 | 20 | ?? 5 | 25 | ??
Using lag() and lead() to compute a moving average
The aim is to compute the moving average for each day within the window, where this is feasible, over the last-but one day, the last day, the current day, the next day, and the next-but-one day.
Notice that the following section uses the aggregate function
avg() to produce the same result, and it shows the advantages of that approach over using the window functions
lead(). There are many other cases where
lead()are needed and where
avg() is of no use. The present use case was chosen here because it shows very clearly what
lead() do and, especially, because it allows the demonstration of invoking an aggregate function with an
The query is specifically written to meet the exact requirements. It would need to be manually re-written to base the moving average on a bigger, or smaller, range of days. Notice that the same
window_definition , "w", is used as the argument for each of the four uses of the
OVER clause. This is where using a separate
WINDOW clause delivers its intended benefit.
The statement of requirement implies that the computation is not feasible for the first two and the last two days in the window. Under these circumstances,
NULL—or, it you prefer, a default value that you supply using an optional third parameter. See the dedicated section on
lead() for details.
with v as ( select day, lag (price::numeric, 2) over w as lag_2, lag (price::numeric, 1) over w as lag_1, price::numeric, lead(price::numeric, 1) over w as lead_1, lead(price::numeric, 2) over w as lead_2 from t3 window w as (order by day)) select to_char(day, 'Dy DD-Mon') as "Day", ((lag_2 + lag_1 + price + lead_1 + lead_2)/5.0)::money as moving_avg from v where (lag_2 is not null) and (lead_2 is not null) order by day;
This is the result:
Day | moving_avg ------------+------------ Wed 17-Sep | $18.98 Thu 18-Sep | $19.13 Fri 19-Sep | $19.27 Mon 22-Sep | $19.64 Tue 23-Sep | $19.99 Wed 24-Sep | $20.10 Thu 25-Sep | $19.90 Fri 26-Sep | $19.62 Mon 29-Sep | $19.60 Tue 30-Sep | $19.41 Wed 01-Oct | $19.18 Thu 02-Oct | $19.08 Fri 03-Oct | $18.78 Mon 06-Oct | $18.19 Tue 07-Oct | $17.53 Wed 08-Oct | $16.97 Thu 09-Oct | $17.08 Fri 10-Oct | $17.26 Mon 13-Oct | $17.08 Tue 14-Oct | $17.23 Wed 15-Oct | $17.30
Using the aggregate function avg() to compute a moving average
This solution takes advantage of this
window_definition to determine the rows that
order by day groups between $1 preceding and $1 following
Here, the statement is first prepared and then executed to emphasize the fact that a single formulation of the statement text works for any arbitrary range of days around the current row. The section Window function invocation—SQL syntax and semantics explains the full power of expression brought by the
Notice that this approach uses the value returned by
row_number(), using an
OVER clause that does no more than order the rows, to exclude the meaningless first N and last N averages, where N is the same parameterized value that "groups between N preceding and N following" uses. These rows, if not excluded, would simply show the averages over the rows that allow access. You probably don't want to see those answers.
prepare stmt(int) as with v as ( select day, avg(price::numeric) over w1 as a, row_number() over w2 as r from t3 window w1 as (order by day groups between $1 preceding and $1 following), w2 as (order by day)) select to_char(day, 'Dy DD-Mon') as "Day", a::money as moving_avg from v where r between ($1 + 1) and (select (count(*) - $1) from v) order by day; execute stmt(2);
The result is identical to that produced by the
lead() approach. Try repeating the
EXECUTE statement with a few different actual arguments. The bigger it gets, the fewer result rows you see, and the closer the values of the moving average get to each other.
Using the aggregate function
sum() with the OVER clause
This example shows a different spelling of the
range between unbounded preceding and current row
so that the average includes, for each row, the row itself and only the rows that precede it in the sort order.
with v as ( select class, k, sum(k) over w as s from t1 window w as ( partition by class order by k range between unbounded preceding and current row)) select class, k, s from v where class in (2, 4) order by class, k;
This is the result:
class | k | s -------+----+---- 2 | 6 | 6 2 | 7 | 13 2 | 8 | 21 2 | 9 | 30 2 | 10 | 40 4 | 16 | 16 4 | 17 | 33 4 | 18 | 51 4 | 19 | 70 4 | 20 | 90