Using Window Frame Clauses
1. Understanding Frame Boundaries
| Mode | Unit |
|---|
| ROWS | Physical rows |
| RANGE | Logical (by ORDER BY value) |
| GROUPS | Peer groups PG 11+ |
2. Using UNBOUNDED PRECEDING
SELECT day, sum(revenue) OVER (ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_revenue;
3. Using UNBOUNDED FOLLOWING
SELECT day, sum(revenue) OVER (ORDER BY day
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily_revenue;
4. Using CURRENT ROW
| Mode | CURRENT ROW Means |
|---|
| ROWS | This row only |
| RANGE | All rows with same ORDER BY value |
| GROUPS | All rows in same peer group |
5. Using n PRECEDING
SELECT day, avg(revenue) OVER (ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue;
6. Using n FOLLOWING
SELECT day, avg(revenue) OVER (ORDER BY day
ROWS BETWEEN 0 PRECEDING AND 6 FOLLOWING) AS forward_avg
FROM daily_revenue;
7. Using ROWS BETWEEN
... OVER (ORDER BY t ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
8. Using RANGE BETWEEN
SELECT ts, sum(amt) OVER (
ORDER BY ts
RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
) AS amount_last_hour
FROM tx;
| RANGE Requires | Detail |
|---|
| Single ORDER BY column | Numeric or date/time |
| Offset type | Compatible with ORDER BY column |
9. Using GROUPS BETWEEN
SELECT name, score,
avg(score) OVER (ORDER BY score
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS neighbor_avg
FROM players;
10. Using EXCLUDE Clause
| Option | Removes |
|---|
| EXCLUDE CURRENT ROW | The row itself |
| EXCLUDE GROUP | Entire peer group |
| EXCLUDE TIES | Peers but keep current row |
| EXCLUDE NO OTHERS | Default |
11. Using CUME_DIST and PERCENT_RANK Functions
SELECT name, score,
cume_dist() OVER (ORDER BY score) AS cdist,
percent_rank() OVER (ORDER BY score) AS prank
FROM players;
| Function | Formula |
|---|
| cume_dist() | (rows ≤ current) / total |
| percent_rank() | (rank - 1) / (total - 1) |