Multi-Column Groups

Base Rows before Grouping

Visible rows come before composite group keys.

highlighted = computed this step

Base rows first

Start with visible rows before any group key is chosen.

rows first\text{rows first}

Read the source rows

The pinned table has 7 rows.

rows=7\text{rows}=7

Multi-column grouping examples are tiny finite table transforms; SQL dialect completeness, optimizer behavior, indexing, product behavior, and performance claims are out of scope.

Base rows before grouping: base rowsidregionstatusqty1eastopen22eastopen33eastclosed14westopen45westNULL56eastNULLNULL7westopen1 Group bucketskeysourceRowsrowCount(east)(0, 1, 2, 5)4(west)(3, 4, 6)3 Aggregate resultskeysourceRowsaggregates(east)(0, 1, 2, 5){n:4, total_qty:6}(west)(3, 4, 6){n:3, total_qty:10} Aggregate detailskeyaggregateseenaddednullSkippedresult(east)n404(east)total_qty4316(west)n303(west)total_qty33010 Output rowsregionntotal_qtyeast46west310 Multi-group factsfactvalueinputRowCount7keyColumnCount1groupCount2aggregateCount2outputRowCount2nullKeyComponents0groupOrderfirst_seen_input_orderoutputCarriesKeyColumnsyes

Keep source rows visible

Later buckets point back to these row positions.

source rows visible\text{source rows visible}

Summary

Every group fact in this book is recomputed from these rows.

facts from rows\text{facts from rows}