SQL
Evaluation
Windowing works by breaking a relation up into independent *partitions*,
*ordering* those partitions,
and then computing a new column for each row as a function of the nearby values.
Some window functions depend only on the partition boundary and the ordering,
but a few (including all the aggregates) also use a *frame*.
Frames are specified as a number of rows on either side (*preceding* or *following*) of the *current row*.
The distance can either be specified as a number of *rows* or a *range* of values
using the partition's ordering value and a distance.  
The full syntax is shown in the diagram at the top of the page,
and this diagram visually illustrates computation environment:  
<img src="/images/blog/windowing/framing.svg" alt="The Window Computation Environment" title="Figure 1: The Window Computation Environment" style="max-width:90%;width:90%;height:auto"/>  
#### Partition and Ordering {#docs:sql:functions:window_functions::partition-and-ordering}  
Partitioning breaks the relation up into independent, unrelated pieces.
Partitioning is optional, and if none is specified then the entire relation is treated as a single partition.
Window functions cannot access values outside of the partition containing the row they are being evaluated at.  
Ordering is also optional, but without it the results are not well-defined.
Each partition is ordered using the same ordering clause.  
Here is a table of power generation data, available as a CSV file ([`power-plant-generation-history.csv`](https://duckdb.org/data/power-plant-generation-history.csv)). To load the data, run:  
```sql
CREATE TABLE "Generation History" AS
FROM 'power-plant-generation-history.csv';
```  
After partitioning by plant and ordering by date, it will have this layout:  
<div class="narrow_table"></div>  
| Plant | Date | MWh |
|:---|:---|---:|
| Boston | 2019-01-02 | 564337 |
| Boston | 2019-01-03 | 507405 |
| Boston | 2019-01-04 | 528523 |
| Boston | 2019-01-05 | 469538 |
| Boston | 2019-01-06 | 474163 |
| Boston | 2019-01-07 | 507213 |
| Boston | 2019-01-08 | 613040 |
| Boston | 2019-01-09 | 582588 |
| Boston | 2019-01-10 | 499506 |
| Boston | 2019-01-11 | 482014 |
| Boston | 2019-01-12 | 486134 |
| Boston | 2019-01-13 | 531518 |
| Worcester | 2019-01-02 | 118860 |
| Worcester | 2019-01-03 | 101977 |
| Worcester | 2019-01-04 | 106054 |
| Worcester | 2019-01-05 | 92182 |
| Worcester | 2019-01-06 | 94492 |
| Worcester | 2019-01-07 | 99932 |
| Worcester | 2019-01-08 | 118854 |
| Worcester | 2019-01-09 | 113506 |
| Worcester | 2019-01-10 | 96644 |
| Worcester | 2019-01-11 | 93806 |
| Worcester | 2019-01-12 | 98963 |
| Worcester | 2019-01-13 | 107170 |  
In what follows,
we shall use this table (or small sections of it) to illustrate various pieces of window function evaluation.  
The simplest window function is `row_number()`.
This function just computes the 1-based row number within the partition using the query:  
```sql
SELECT
"Plant",
"Date",
row_number() OVER (PARTITION BY "Plant" ORDER BY "Date") AS "Row"
FROM "Generation History"
ORDER BY 1, 2;
```  
The result will be the following:  
<div class="narrow_table"></div>  
| Plant | Date | Row |
|:---|:---|---:|
| Boston | 2019-01-02 | 1 |
| Boston | 2019-01-03 | 2 |
| Boston | 2019-01-04 | 3 |
| ... | ... | ... |
| Worcester | 2019-01-02 | 1 |
| Worcester | 2019-01-03 | 2 |
| Worcester | 2019-01-04 | 3 |
| ... | ... | ... |  
Note that even though the function is computed with an `ORDER BY` clause,
the result does not have to be sorted,
so the `SELECT` also needs to be explicitly sorted if that is desired.  
#### Framing {#docs:sql:functions:window_functions::framing}  
Framing specifies a set of rows relative to each row where the function is evaluated.
The distance from the current row is given as an expression either `PRECEDING` or `FOLLOWING` the current row.
This distance can either be specified as an integral number of `ROWS`
or as a `RANGE` delta expression from the value of the ordering expression.
For a `RANGE` specification, there must  be only one ordering expression,
and it has to support addition and subtraction (i.e., numbers or `INTERVAL`s).
The default values for frames are from `UNBOUNDED PRECEDING` to `CURRENT ROW`.
It is invalid for a frame to start after it ends.
Using the [`EXCLUDE` clause](#::exclude-clause), rows around the current row can be excluded from the frame.