Window Frames, crosstab & Advanced Analytics
A rolling average that silently absorbs an extra day across a data gap. A pivot table for the board. An average computed 46x faster from 1% of the data. Three requests, three real tools.
ROWS BETWEEN n PRECEDING AND CURRENT ROW counts a fixed number of physical rows, regardless of what values those rows actually hold. For a daily rolling average, that is a subtle trap: if one day's data is missing entirely — no row at all, not a zero — a "7-row" window silently reaches back an extra calendar day to find its 7th row, quietly computing an 8-calendar-day average while still being labeled a 7-day one. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW fixes this by defining the frame in terms of the actual ORDER BY values — real calendar dates — rather than row positions, so a missing day simply shrinks the window to whatever days genuinely exist within the last 6, instead of silently reaching further back. GROUPS BETWEEN extends the same idea one step further, framing by how many distinct peer groups (rows sharing an identical ORDER BY value) precede the current one, rather than by rows or by a range of values.\n\nTwo more tools close out this block's tour of solving analytics problems inside the database: crosstab() genuinely pivots rows into columns for report-style output, and TABLESAMPLE SYSTEM trades perfect accuracy for real, measured speed on massive tables — both honest tradeoffs, both measured directly rather than assumed.
ROWS BETWEEN vs RANGE BETWEEN, Across a Real Gap
With January 10th deliberately missing from the data:
SELECT sale_date, revenue,
sum(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rows_7,
sum(revenue) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS range_7
FROM sql_sales ORDER BY sale_date;
sale_date | revenue | rows_7 | range_7
------------+---------+--------+---------
2025-01-09 | 556 | 3745 | 3745
2025-01-11 | 570 | 3801 | 3280
For January 11th, rows_7 reaches back to include January 5th through 9th plus the 11th — 7 physical rows, but 7 calendar days apart from a gap, silently including one extra day of real data than a "7-day" label implies. range_7 only includes rows whose actual date falls within the last 6 calendar days of the 11th — January 5th through 9th and the 11th, but correctly recognizing only 6 of those days actually have data, summing exactly what a genuine 7-calendar-day window should.
GROUPS BETWEEN: Framing by Peer Groups
SELECT id, grp, val, sum(val) OVER (ORDER BY grp GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_sum
FROM sql_grp_demo ORDER BY id;
id | grp | val | groups_sum
----+-----+-----+------------
1 | 1 | 10 | 30
4 | 3 | 40 | 180
5 | 3 | 50 | 180
6 | 3 | 60 | 180
Rows 4, 5, and 6 all share grp = 3 and are treated as a single peer group: their frame is "this whole peer group plus the one preceding peer group" (grp 2's single row, value 30), giving 40+50+60+30 = 180 for all three, identically — framing by group membership, not by row count or value range.
crosstab(): Rows Into Columns
SELECT * FROM crosstab(
'SELECT region, quarter, revenue FROM sql_region_sales ORDER BY 1,2',
'SELECT DISTINCT quarter FROM sql_region_sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);
region | q1 | q2 | q3 | q4
--------+------+------+------+------
east | 1000 | 1100 | 1200 | 1300
west | 900 | 950 | 1000 | 1050
A genuine pivot: one row per region, one column per quarter — the exact matrix shape a spreadsheet-style report needs, built entirely in SQL.
TABLESAMPLE SYSTEM: Speed and Its Real Cost
EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample; -- 1985.255 ms, full scan
EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample TABLESAMPLE SYSTEM(1); -- 43.243 ms, ~1% sampled
About 46 times faster on 500,000 rows. The tradeoff, measured directly:
SELECT round(avg(val),3) FROM sql_bigsample; -- true_avg: 500.595
SELECT round(avg(val),3) FROM sql_bigsample TABLESAMPLE SYSTEM(1); -- sampled_avg: 499.910
Within roughly 0.14% of the true average — a real, honest error margin in exchange for a dramatic speedup, appropriate for a dashboard estimate, inappropriate for a number that has to be exactly correct (a financial total, a billing calculation).
RANGE BETWEEN INTERVAL ... PRECEDING
A window frame defined by actual ORDER BY values within a given range, rather than by counting physical rows. For a date-ordered window, RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes every row whose date genuinely falls within that 6-day span — correctly including fewer rows when data is missing, unlike ROWS BETWEEN, which always counts exactly the requested number of physical rows regardless of what dates they actually represent.
TABLESAMPLE SYSTEM
A table sampling method that selects entire storage pages at random (approximately the requested percentage of them) rather than sampling individual rows, making it very fast since it can skip most of the table entirely — at the cost of a specific kind of imprecision: if rows within a page are correlated (all inserted together, sharing some property), SYSTEM sampling can be less representative than TABLESAMPLE BERNOULLI, which samples individual rows at the cost of still reading every page.
📅 See RANGE BETWEEN Correctly Handle a Missing Day
Compute a rolling total two ways — by row count and by calendar range — across a deliberately missing day, and compare the two results right where they diverge.
psql -U postgres -d beer_db -c "SELECT sale_date, revenue, sum(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rows_7, sum(revenue) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS range_7 FROM sql_sales ORDER BY sale_date;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT sale_date, revenue, sum(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rows_7, sum(revenue) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS range_7 FROM sql_sales ORDER BY sale_date;" SET sale_date | revenue | rows_7 | range_7 ------------+---------+--------+--------- 2025-01-01 | 500 | 500 | 500 2025-01-02 | 507 | 1007 | 1007 2025-01-03 | 514 | 1521 | 1521 2025-01-04 | 521 | 2042 | 2042 2025-01-05 | 528 | 2570 | 2570 2025-01-06 | 535 | 3105 | 3105 2025-01-07 | 542 | 3647 | 3647 2025-01-08 | 549 | 3696 | 3696 2025-01-09 | 556 | 3745 | 3745 2025-01-11 | 570 | 3801 | 3280 2025-01-12 | 577 | 3857 | 3329 2025-01-13 | 584 | 3913 | 3378 2025-01-14 | 591 | 3969 | 3427 2025-01-15 | 598 | 4025 | 3476 2025-01-16 | 605 | 4081 | 3525 2025-01-17 | 612 | 4137 | 4137 2025-01-18 | 619 | 4186 | 4186 2025-01-19 | 626 | 4235 | 4235 2025-01-20 | 633 | 4284 | 4284 (19 rows)
👥 Frame by Peer Groups With GROUPS BETWEEN
Sum a value across the current peer group plus the one preceding peer group, where several rows share the same ORDER BY value.
psql -U postgres -d beer_db -c "SELECT id, grp, val, sum(val) OVER (ORDER BY grp GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_sum FROM sql_grp_demo ORDER BY id;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT id, grp, val, sum(val) OVER (ORDER BY grp GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS groups_sum FROM sql_grp_demo ORDER BY id;" SET id | grp | val | groups_sum ----+-----+-----+------------ 1 | 1 | 10 | 30 2 | 1 | 20 | 30 3 | 2 | 30 | 60 4 | 3 | 40 | 180 5 | 3 | 50 | 180 6 | 3 | 60 | 180 7 | 4 | 70 | 220 (7 rows)
📈 Pivot Rows Into a Region-by-Quarter Matrix
Use crosstab() to reshape region/quarter/revenue rows into one row per region with one column per quarter.
psql -U postgres -d beer_db -c "SELECT * FROM crosstab('SELECT region, quarter, revenue FROM sql_region_sales ORDER BY 1,2', 'SELECT DISTINCT quarter FROM sql_region_sales ORDER BY 1') AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);"student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM crosstab('SELECT region, quarter, revenue FROM sql_region_sales ORDER BY 1,2', 'SELECT DISTINCT quarter FROM sql_region_sales ORDER BY 1') AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);" SET region | q1 | q2 | q3 | q4 --------+------+------+------+------ east | 1000 | 1100 | 1200 | 1300 west | 900 | 950 | 1000 | 1050 (2 rows)
⚡ Measure TABLESAMPLE SYSTEM Against a Full Scan
Compute an average on a 500,000-row table the normal way, then approximate it by sampling roughly 1% of pages, and compare real execution time.
psql -U postgres -d beer_db -c "CREATE TABLE sql_bigsample (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_bigsample SELECT g, random()*1000 FROM generate_series(1,500000) g;" -c "ANALYZE sql_bigsample;"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample;"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample TABLESAMPLE SYSTEM(1);"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_bigsample (id int PRIMARY KEY, val numeric);" -c "INSERT INTO sql_bigsample SELECT g, random()*1000 FROM generate_series(1,500000) g;" -c "ANALYZE sql_bigsample;" SET CREATE TABLE INSERT 0 500000 ANALYZE student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample;" SET QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=7398.70..7398.71 rows=1 width=32) (actual rows=1.00 loops=1) Buffers: shared hit=2144 read=578 -> Gather (cost=7398.47..7398.68 rows=2 width=32) (actual rows=2.00 loops=1) Workers Planned: 1 Workers Launched: 1 Buffers: shared hit=2144 read=578 -> Partial Aggregate (cost=6398.47..6398.48 rows=1 width=32) (actual rows=1.00 loops=2) Buffers: shared hit=2144 read=578 -> Parallel Seq Scan on sql_bigsample (cost=0.00..5663.18 rows=294118 width=11) (actual rows=250000.00 loops=2) Buffers: shared hit=2144 read=578 Planning: Buffers: shared hit=62 read=7 Planning Time: 5.812 ms Execution Time: 1985.255 ms (14 rows) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT avg(val) FROM sql_bigsample TABLESAMPLE SYSTEM(1);" SET QUERY PLAN ---------------------------------------------------------------------------------------------------------- Aggregate (cost=170.50..170.51 rows=1 width=32) (actual rows=1.00 loops=1) Buffers: shared hit=22 read=6 -> Sample Scan on sql_bigsample (cost=0.00..158.00 rows=5000 width=11) (actual rows=5142.00 loops=1) Sampling: system ('1'::real) Buffers: shared hit=22 read=6 Planning: Buffers: shared hit=57 Planning Time: 4.632 ms Execution Time: 43.243 ms (9 rows)
📏 Measure the Real Error Margin Behind That Speedup
Compute the true average and the sampled average side by side, to see exactly how much accuracy was traded for that speed.
psql -U postgres -d beer_db -c "SELECT round(avg(val),3) AS true_avg FROM sql_bigsample;"psql -U postgres -d beer_db -c "SELECT round(avg(val),3) AS sampled_avg FROM sql_bigsample TABLESAMPLE SYSTEM(1);"student@lab:~$ psql -U postgres -d beer_db -c "SELECT round(avg(val),3) AS true_avg FROM sql_bigsample;" SET true_avg ---------- 500.595 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT round(avg(val),3) AS sampled_avg FROM sql_bigsample TABLESAMPLE SYSTEM(1);" SET sampled_avg ------------- 499.910 (1 row)
Lab 3.3.7 complete — Block 3.3 complete. Advanced window frames and analytics, measured honestly:\n\n\n RANGE vs ROWS across a missing day : ✅ genuinely diverge — 3801 vs 3280\n GROUPS BETWEEN, peer-based framing : ✅ tied rows share one group, proven\n crosstab() region x quarter pivot : ✅ 8 rows -> 2 rows, 4 columns each\n TABLESAMPLE SYSTEM speed + accuracy : ✅ ~46x faster, ~0.14% error, both real\n
Enable JavaScript to run the live terminal and track your progress.