Precomputing aggregates
Computing ahead what queries keep asking: aggregate tables and materialised views, the lattice of groupings, ROLLUP, CUBE and GROUPING SETS, measures that recombine or not, refreshing.
- 30 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Aggregate table: what is it?
Show the answer
A table that stores results already summed or counted at a coarse level (sales by region and month), to avoid rereading the detailed facts.
A query asking for a total per region reads a few hundred rows instead of millions. It has to be kept up to date and the level it keeps has to be chosen.
Which groupings does this query compute?
```sql
SELECT region, mois, SUM(montant)
FROM ventes
GROUP BY ROLLUP (region, mois);`- (region, month), (region) and the grand total
- (region, month), (month) and the grand total, dropping the columns from the left
- (region, month), (region), (month) and the grand total
- (region) and (month) only
Show the answer
(region, month), (region) and the grand total
ROLLUP drops columns from the right: after (region, month) comes (region), then the total. Grouping by month alone, which fits no hierarchy, only comes with CUBE.
A lattice of … dimensions contains 2 to the power 3, that is … groupings, from the finest to the grand total.
Show the answer
A lattice of three dimensions contains 2 to the power 3, that is eight groupings, from the finest to the grand total.
Each dimension is either in the grouping or absent: two choices per dimension, hence 2 to the power n.
Materialised view: what is it?
Show the answer
A query whose result is stored physically, and which the engine can reread without recomputing it until it is refreshed.
An ordinary view is recomputed on every read; the materialised view is fast but can be stale. In PostgreSQL, REFRESH MATERIALIZED VIEW recomputes it in full.