Multi-Column Groups

Either Key Value Can Split

Changing either key component splits buckets.

highlighted = computed this step

Either value can split

A two-column key splits buckets when either key value differs.

either key can split\text{either key can split}

Start with one key

Grouping by region alone makes 2 buckets.

one key buckets=2\text{one key buckets}=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.

One-column contrast: 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

Add status to the key

Adding status makes 5 buckets because either key component can differ.

two key buckets=5\text{two key buckets}=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.

Two-column split: 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

Summary

More key columns can split a bucket into smaller visible buckets.

split by tuple\text{split by tuple}