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
ROW , RANK , LEAD , and others listed in Window Functions .
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
Search WWH ::




Custom Search