Informal overview of window function invocation using the OVER clause
A good sense of the general functionality of window functions is given by examples that use row_number(), nth_value(), last_value(), lag(), and lead().
Aggregate functions can be invoked with the OVER clause. Examples are given using avg() and sum().
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
window_definition: thePARTITION BYclause, the windowORDER BYclause, and theframe_clause - 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 regularGROUP BYclause.
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
The 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.
Because the OVER clause doesn't specify a PARTITION BY clause, the so-called window that row_number() operates on coincides with all of the rows in table "t1".
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 window_definition.
\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 first_value(), last_value(), nth_value(), lag(), or 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)
Each of first_value(), last_value(), and 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;
This example uses last_value() because the data set has different values for "k" and "v" for the last row in each window. This is the result:
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 lag() and lead(). There are many other cases where lag() and/or lead()are needed and where avg() is of no use. The present use case was chosen here because it shows very clearly what lag() and lead() do and, especially, because it allows the demonstration of invoking an aggregate function with an OVER clause.
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, lag() and lead(), return NULL—or, it you prefer, a default value that you supply using an optional third parameter. See the dedicated section on lag() and 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 avg() uses:
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 OVER clause.
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 lag()/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 frame_clause:
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