Window Functions

Base Ordered Rows

Window functions add labels beside visible base rows.

highlighted = computed this step

Base ordered rows

A window function starts with the same base rows still visible.

base rows stay visible\text{base rows stay visible}

Sort once

The compiler reads 5 base rows and produces 5 window rows.

window rows=5\text{window rows}=5

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.

Base ordered rows: base rowsidregiondayamount1east152east273west144east235west26 partitionspartitionKeysourceRowsrowCount[][0, 1, 2, 3, 4]5 window rowssourceRowpartitionKeyorderKeytieBreakerKeyrowNumber0[][1, 1][]12[][1, 3][]21[][2, 2][]33[][2, 4][]44[][2, 5][]5 ambiguous tiespartitionKeydeterministicKeysourceRowsnonenonenone window factsfactvaluerowCount5partitionCount1partitionColumnsnoneorderColumnsday,idtieBreakerColumnsnonefunctionsrow_numberresultRowCount5rejectednorejectionReasonnoneambiguousTieGroups0rowOrderpartition_then_orderframenone

Read the order

The window rows table shows source row, order key, and tie-breaker key before the function value.

source plus order\text{source plus order}

Summary

The original rows are not grouped away; the window value is added beside them.

rows remain rows\text{rows remain rows}