PostgreSQL Window Functions
A window clause consists of three optional parts, a partition clause, an order clause and a frame clause.
The partition clause is similar to a group by clause,
except that it can only contain expressions, no output columns. Like the
group by clause, it splits the output into parts which are processed
independently.
The order clause specifies how rows within each partition are ordered.
The frame clause finally determines which rows are included in the window.
A row frame counts rows:
hjp=> select a, b, c, avg(c) over w
from t
window w as (
partition by a
order by b
rows between 1 preceding and 1 following
)
limit 20;
╔═══╤════╤═══╤════════════════════╗
║ a │ b │ c │ avg ║
╟───┼────┼───┼────────────────────╢
║ 0 │ 1 │ 7 │ 4.5000000000000000 ║ ┤ │
║ 0 │ 3 │ 2 │ 4.6666666666666667 ║ │ ┤ │
║ 0 │ 7 │ 5 │ 4.0000000000000000 ║ │ ┤ │
║ 0 │ 7 │ 5 │ 5.3333333333333333 ║ │ ┤ │
║ 0 │ 8 │ 6 │ 5.3333333333333333 ║ │ ┤ │
║ 0 │ 8 │ 5 │ 4.3333333333333333 ║ │ ┤ │
║ 0 │ 9 │ 2 │ 4.3333333333333333 ║ │ ┤ │
║ 0 │ 10 │ 6 │ 4.0000000000000000 ║ │ ┤
║ 1 │ 1 │ 4 │ 6.5000000000000000 ║ ┤ │
║ 1 │ 1 │ 9 │ 6.6666666666666667 ║ │ ┤ │
║ 1 │ 3 │ 7 │ 6.6666666666666667 ║ │ ┤ │
║ 1 │ 4 │ 4 │ 5.6666666666666667 ║ │ ┤ │
║ 1 │ 5 │ 6 │ 6.0000000000000000 ║ │ ┤ │
║ 1 │ 7 │ 8 │ 7.3333333333333333 ║ │ ┤ │
║ 1 │ 8 │ 8 │ 5.6666666666666667 ║ │ ┤ │
║ 1 │ 8 │ 1 │ 5.3333333333333333 ║ │ ┤
║ 1 │ 9 │ 7 │ 4.0000000000000000 ║
║ 1 │ 10 │ 4 │ 5.5000000000000000 ║
║ 2 │ 1 │ 5 │ 7.0000000000000000 ║ ┤ │
║ 2 │ 2 │ 9 │ 6.3333333333333333 ║ │ ┤
╚═══╧════╧═══╧════════════════════╝
(20 rows)
Easy!
A group frame counts by groups of rows which are equivalent according to the row clause:
hjp=> select a, b, c, avg(c) over w
from t
window w as (
partition by a
order by b
groups between 1 preceding and 1 following
)
limit 20;
╔═══╤════╤═══╤════════════════════╗
║ a │ b │ c │ avg ║
╟───┼────┼───┼────────────────────╢
║ 0 │ 1 │ 7 │ 4.5000000000000000 ║ ┤ │
║ 0 │ 3 │ 2 │ 4.7500000000000000 ║ │ ┤ │ │
║ 0 │ 7 │ 5 │ 4.6000000000000000 ║ │ ┤ │ │ │
║ 0 │ 7 │ 5 │ 4.6000000000000000 ║ │ │ ┤ │ │
║ 0 │ 8 │ 6 │ 4.6000000000000000 ║ │ │ ┤ │ │
║ 0 │ 8 │ 5 │ 4.6000000000000000 ║ │ │ │ ┤ │
║ 0 │ 9 │ 2 │ 4.7500000000000000 ║ │ │ ┤ │
║ 0 │ 10 │ 6 │ 4.0000000000000000 ║ │ ┤
║ 1 │ 1 │ 4 │ 6.6666666666666667 ║ ┤ │ │
║ 1 │ 1 │ 9 │ 6.6666666666666667 ║ │ ┤ │
║ 1 │ 3 │ 7 │ 6.0000000000000000 ║ │ │ ┤ │
║ 1 │ 4 │ 4 │ 5.6666666666666667 ║ │ ┤ │
║ 1 │ 5 │ 6 │ 6.0000000000000000 ║ │ ┤ │
║ 1 │ 7 │ 8 │ 5.7500000000000000 ║ │ ┤ │ │
║ 1 │ 8 │ 8 │ 6.0000000000000000 ║ │ ┤ │ │
║ 1 │ 8 │ 1 │ 6.0000000000000000 ║ │ │ ┤ │
║ 1 │ 9 │ 7 │ 5.0000000000000000 ║ │ │ ┤
║ 1 │ 10 │ 4 │ 5.5000000000000000 ║ │
║ 2 │ 1 │ 5 │ 6.3333333333333333 ║ ┤ │
║ 2 │ 2 │ 9 │ 6.4000000000000000 ║ │ ┤
╚═══╧════╧═══╧════════════════════╝
(20 rows)
Here, lines 3 and 4 form a group (b = 7), and same for lines 5 and 6 (b = 8). So the value for line 4 is the average of lines 2 (preceding group), 3 and 4 (current group) and 5 and 6 (following group).
Finally, a range frame uses the value in the column (there can be only one) in the order clause instead of a count. So it can only be used with columns where a difference between values makes sense, such as numeric or timestamp columns.
hjp=> select a, b, c, avg(c) over w
from t
window w as (
partition by a
order by b
range between 1 preceding and 1 following
)
limit 20;
╔═══╤════╤═══╤════════════════════╗
║ a │ b │ c │ avg ║
╟───┼────┼───┼────────────────────╢
║ 0 │ 1 │ 7 │ 7.0000000000000000 ║ ┤
║ 0 │ 3 │ 2 │ 2.0000000000000000 ║ ┤
║ 0 │ 7 │ 5 │ 5.2500000000000000 ║ ┤ │ │ │
║ 0 │ 7 │ 5 │ 5.2500000000000000 ║ │ ┤ │ │
║ 0 │ 8 │ 6 │ 4.6000000000000000 ║ │ │ ┤ │ │
║ 0 │ 8 │ 5 │ 4.6000000000000000 ║ │ │ │ ┤ │
║ 0 │ 9 │ 2 │ 4.7500000000000000 ║ │ │ ┤ │
║ 0 │ 10 │ 6 │ 4.0000000000000000 ║ │ ┤
║ 1 │ 1 │ 4 │ 6.5000000000000000 ║ ┤ │
║ 1 │ 1 │ 9 │ 6.5000000000000000 ║ │ ┤
║ 1 │ 3 │ 7 │ 5.5000000000000000 ║ ┤ │
║ 1 │ 4 │ 4 │ 5.6666666666666667 ║ │ ┤ │
║ 1 │ 5 │ 6 │ 5.0000000000000000 ║ │ ┤
║ 1 │ 7 │ 8 │ 5.6666666666666667 ║ ┤ │ │
║ 1 │ 8 │ 8 │ 6.0000000000000000 ║ │ ┤ │ │
║ 1 │ 8 │ 1 │ 6.0000000000000000 ║ │ │ ┤ │
║ 1 │ 9 │ 7 │ 5.0000000000000000 ║ │ │ ┤ │
║ 1 │ 10 │ 4 │ 5.5000000000000000 ║ │ ┤
║ 2 │ 1 │ 5 │ 6.3333333333333333 ║ ┤ │
║ 2 │ 2 │ 9 │ 6.3333333333333333 ║ │ ┤
╚═══╧════╧═══╧════════════════════╝
(20 rows)
Line 2 has a value of 3 in the b column, so its window includes all rows with b between (3-1) and (3+1). That's only itself. But the next row has b = 7 and there are four rows with b between (7-1) and (7+1).