PostgreSQL Summary Stats and Window Functions
Michel Semaan
Moving averages
n
periodsn
periodsQuery
SELECT
Year, COUNT(*) AS Medals
FROM Summer_Medals
WHERE
Country = 'USA'
AND Medal = 'Gold'
AND Year >= 1980
GROUP BY Year
ORDER BY Year ASC;
Result
| Year | Medals |
|------|--------|
| 1984 | 168 |
| 1988 | 77 |
| 1992 | 89 |
| 1996 | 160 |
| 2000 | 130 |
| 2004 | 116 |
| 2008 | 125 |
| 2012 | 147 |
Query
WITH US_Medals AS (...)
SELECT
Year, Medals,
AVG(Medals) OVER
(ORDER BY Year ASC
ROWS BETWEEN
2 PRECEDING AND CURRENT ROW) AS Medals_MA
FROM US_Medals
ORDER BY Year ASC;
Result
| Year | Medals | Medals_MA |
|------|--------|-----------|
| 1984 | 168 | 168.00 |
| 1988 | 77 | 122.50 |
| 1992 | 89 | 111.33 |
| 1996 | 160 | 108.67 |
| 2000 | 130 | 126.33 |
| 2004 | 116 | 135.33 |
| 2008 | 125 | 123.67 |
| 2012 | 147 | 129.33 |
Query
WITH US_Medals AS (...)
SELECT
Year, Medals,
SUM(Medals) OVER
(ORDER BY Year ASC
ROWS BETWEEN
2 PRECEDING AND CURRENT ROW) AS Medals_MT
FROM US_Medals
ORDER BY Year ASC;
Result
| Year | Medals | Medals_MT |
|------|--------|-----------|
| 1984 | 168 | 168 |
| 1988 | 77 | 245 |
| 1992 | 89 | 334 |
| 1996 | 160 | 326 |
| 2000 | 130 | 379 |
| 2004 | 116 | 406 |
| 2008 | 125 | 371 |
| 2012 | 147 | 388 |
RANGE BETWEEN [START] AND [FINISH]
ROWS BETWEEN
RANGE
treats duplicates in OVER
's ORDER BY
subclause as a single entityTable
| Year | Medals | Rows_RT | Range_RT |
|------|--------|---------|----------|
| 1992 | 10 | 10 | 10 |
| 1996 | 50 | 60 | 110 |
| 2000 | 50 | 110 | 110 |
| 2004 | 60 | 170 | 230 |
| 2008 | 60 | 230 | 230 |
| 2012 | 70 | 300 | 300 |
ROWS BETWEEN
is almost always used over RANGE BETWEEN
PostgreSQL Summary Stats and Window Functions