Multi-Column Groups

Group by Two Columns

Two key columns form tuple buckets.

highlighted = computed this step

Two-column key

Grouping by region and status uses a key tuple.

key tuple\text{key tuple}

Read key tuples

The compiler uses 2 key columns and forms 5 key tuples.

key columns=2\text{key columns}=2

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

Group by two columns: 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

Tuple means both values

The displayed key is region plus status together, such as an east/open bucket.

region,status\text{region,status}

Summary

A composite key is one visible tuple, not two separate reports.

composite key\text{composite key}