Database Reference
In-Depth Information
Example 7-23. The basic window
SELECT
tract_id
,
val
,
AVG
(
val
)
OVER
()
as
val_avg
FROM
census
.
facts
WHERE
fact_type_id
=
86
;
tract_id | val | val_avg
------------+-----------+-----------------------
25001010100 | 2942.000 | 4430.0602165087956698
25001010206 | 2750.000 | 4430.0602165087956698
25001010208 | 2003.000 | 4430.0602165087956698
25001010304 | 2421.000 | 4430.0602165087956698
:
The
OVER
sets the boundary of the window. In this example, because the parentheses
contain no constraint, the window covers all the rows in our
WHERE
. So the average is
average across all rows with
fact_type_id = 86
. The clause also morphed our con‐
ventional
AVG
aggregate function into a window aggregate function. For each row, Post‐
greSQL submits all the rows in the window to the
AVG
aggregation and outputs the value
as part of the row. Because our window has multiple rows, the result of the aggregation
is repeated. Notice that with window functions, we were able to perform an aggregation
without
GROUP BY
. Furthermore, we were able to rejoin the aggregated result back with
the other variables without using a formal join.
You can use all SQL aggregate functions as window functions. In addition, you'll find
PARTITION BY
You can run a window function over rows containing particular values instead of using
the whole table. This requries the addition of a
PARTITION BY
clause, which instructs
PostgreSQL to take the aggregate over the indicated rows. In
Example 7-24
, we repeat
what we did in
Example 7-23
but partition our window by county code, which is always
the first five characters of the
tract_id
column.
Example 7-24. Partition our window by county code
SELECT
tract_id
,
val
,
AVG
(
val
)
OVER
(
PARTITION
BY
left
(
tract_id
,
5
))
As
val_avg_coun
ty
FROM
census
.
facts
WHERE
fact_type_id
=
2
ORDER
BY
tract_id
;
tract_id | val | val_avg_county
-------------+----------+-----------------------
25001010100 | 1765.000 | 1709.9107142857142857
25001010206 | 1366.000 | 1709.9107142857142857
25001010208 | 984.000 | 1709.9107142857142857
:
25003900100 | 1920.000 | 1438.2307692307692308
25003900200 | 1968.000 | 1438.2307692307692308