Window Functions

ORDER BY Inside Each Partition

Each partition gets its own deterministic order.

highlighted = computed this step

ORDER BY inside each partition

Within each partition, ORDER BY chooses the visible sequence.

partition then order\text{partition then order}

Use a tie-breaker

With the explicit tie-breaker, ambiguous tie groups are 0.

ambiguous ties=0\text{ambiguous ties}=0

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.

ORDER BY inside each partition: 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 ordered rows

The row order is partition key first, then order key, then tie-breaker key.

deterministic row order\text{deterministic row order}

Summary

The compiler does not rely on hidden source order for ties.

tie-breaker is explicit\text{tie-breaker is explicit}