Multi-Column Groups

Group by One Column

One key column forms broad buckets.

highlighted = computed this step

One group key

Grouping by region makes one bucket per visible region value.

one key column\text{one key column}

Read one-column buckets

The compiler uses 1 key column and forms 2 buckets.

groups=2\text{groups}=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 one column: 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

Same region, same bucket

Rows with the same region share a bucket even when status differs.

same region\text{same region}

Summary

One-column GROUP BY looks only at one key value.

one key value\text{one key value}