Window Functions

PARTITION BY Groups

PARTITION BY splits rows into separate groups.

highlighted = computed this step

PARTITION BY groups

PARTITION BY splits the input into separate groups before numbering starts.

partition by region\text{partition by region}

Make groups

The compiler finds 2 partitions.

partitions=2\text{partitions}=2

Window-function examples are tiny finite table transforms; SQL dialects, NULL ordering, frames beyond prefix rows, optimizer behavior, performance, and product behavior are out of scope.

PARTITION BY groups: base rowsidregiondayamount1east152east273west144east235west26 partitionspartitionKeysourceRowsrowCount[east][0, 1, 3]3[west][2, 4]2 window rowssourceRowpartitionKeyorderKeytieBreakerKeyrowNumber0[east][1][1]11[east][2][2]23[east][2][4]32[west][1][3]14[west][2][5]2 ambiguous tiespartitionKeydeterministicKeysourceRowsnonenonenone window factsfactvaluerowCount5partitionCount2partitionColumnsregionorderColumnsdaytieBreakerColumnsidfunctionsrow_numberresultRowCount5rejectednorejectionReasonnoneambiguousTieGroups0rowOrderpartition_then_orderframenone

Read each group

The partition table lists the source rows that belong to each group.

source rows per partition\text{source rows per partition}

Summary

Each partition gets its own window calculation.

separate groups\text{separate groups}