Multi-Column Groups

COUNT and SUM per Composite Group

Aggregate facts belong to the full key tuple.

highlighted = computed this step

Facts per tuple

Each composite-key bucket gets its own COUNT and integer SUM facts.

facts per tuple\text{facts per tuple}

Read aggregate rows

The compiler computes 2 aggregate facts for each of 5 composite groups.

aggregate facts=2\text{aggregate facts}=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.

COUNT and SUM per composite group: 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

SUM stays scoped

SUM adds present integer quantity cells inside each displayed tuple bucket.

sum inside bucket\text{sum inside bucket}

Summary

Aggregate facts belong to the full key tuple.

full key owns facts\text{full key owns facts}