Multi-Column Groups

Honest by Construction

Expected multi-column group facts are assertions only.

highlighted = computed this step

Honest by construction

Expected multi-column group facts are assertions only.

compiler is source of truth\text{compiler is source of truth}

Render checked groups

The guarded multi-column grouping renders 5 checked groups after recomputation.

checked groups=5\text{checked groups}=5

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

Honest by construction: base rowsidregionstatusqty1eastopen22eastopen33eastclosed14westopen45westNULL56eastNULLNULL7westopen1 Group bucketskeysourceRowsrowCount(east, open)(0, 1)2(east, closed)(2)1(west, open)(3, 6)2(west, NULL)(4)1(east, NULL)(5)1 Aggregate resultskeysourceRowsaggregates(east, open)(0, 1){n:2, total_qty:5}(east, closed)(2){n:1, total_qty:1}(west, open)(3, 6){n:2, total_qty:5}(west, NULL)(4){n:1, total_qty:5}(east, NULL)(5){n:1, total_qty:NULL} Aggregate detailskeyaggregateseenaddednullSkippedresult(east, open)n202(east, open)total_qty2205(east, closed)n101(east, closed)total_qty1101(west, open)n202(west, open)total_qty2205(west, NULL)n101(west, NULL)total_qty1105(east, NULL)n101(east, NULL)total_qty101NULL Output rowsregionstatusntotal_qtyeastopen25eastclosed11westopen25westNULL15eastNULL1NULL Multi-group factsfactvalueinputRowCount7keyColumnCount2groupCount5aggregateCount2outputRowCount5nullKeyComponents2groupOrderfirst_seen_input_orderoutputCarriesKeyColumnsyes

Try to tamper

Wrong expected facts, derived sidecars, bad group columns, too many key columns, bad SUM columns, bools, and floats fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite one-column and two-column GROUP BY behavior only.

scoped composite grouping\text{scoped composite grouping}