Window Functions
Window Functions are one of the most powerful concepts in SQL databases. They allow users to compare one row to another without doing any JOINs.
Running Total
The following query calculates the cumulative seconds spent riding a certain bike in the DC bikeshare ride history.
There are multiple keywords used in this query. Note that GROUP BY is not one of them, even though the query includes a sum. OVER specifies the query as a window function. ORDER BY functions in the same way it typically does: it provides an ordering for the rows, which directly impacts the values of the running_ride_duration column that is calculated.
SELECT start_time,
duration_seconds,
SUM(duration_seconds) OVER (ORDER BY start_time) AS running_ride_duration
FROM tutorial.dc_bikeshare_q1_2012
WHERE bike_number = 'W01412'| start_time | duration_seconds | running_ride_duration |
|---|---|---|
| 2012-01-01 00:04:00 | 475 | 475 |
| 2012-01-02 18:45:00 | 563 | 1038 |
| 2012-01-02 20:15:00 | 410 | 1448 |
| 2012-01-02 20:45:00 | 261 | 1709 |
| 2012-01-03 07:09:00 | 348 | 2057 |
PARTITION_BY Function
The PARTITION BY function enables users to specify time periods (or any other grouping of data) over which to aggregate. As an example, the following query partitions the running_ride_duration by day. As shown in the resulting rows, the running_ride_duration is reset to zero for each new partition.
SELECT DATE_TRUNC('day', start_time) AS day,
duration_seconds,
SUM(duration_seconds) OVER (PARTITION BY DATE_TRUNC('day', start_time) ORDER BY start_time) AS daily_running_ride_duration
FROM tutorial.dc_bikeshare_q1_2012
WHERE bike_number = 'W01412'| day | duration_seconds | daily_running_ride_duration |
|---|---|---|
| 2012-01-01 00:00:00 | 475 | 475 |
| 2012-01-02 00:00:00 | 563 | 563 |
| 2012-01-02 00:00:00 | 410 | 973 |
| 2012-01-02 00:00:00 | 261 | 1234 |
| 2012-01-03 00:00:00 | 348 | 348 |
Windows
The order and partition define what is called the “window.” A “window” is an ordered subset of data over which all these calculations are made.
If the ORDER BY is omitted from the query, as in the following example, then the result is the daily_ride_duration without the “running” component.
SELECT DATE_TRUNC('day', start_time) AS day,
duration_seconds,
SUM(duration_seconds) OVER (PARTITION BY DATE_TRUNC('day', start_time)) AS daily_running_ride_duration
FROM tutorial.dc_bikeshare_q1_2012
WHERE bike_number = 'W01412'| day | duration_seconds | daily_running_ride_duration |
|---|---|---|
| 2012-01-01 00:00:00 | 475 | 475 |
| 2012-01-02 00:00:00 | 563 | 1234 |
| 2012-01-02 00:00:00 | 410 | 1234 |
| 2012-01-02 00:00:00 | 261 | 1234 |
| 2012-01-03 00:00:00 | 348 | 1332 |
Window Aliases
It is possible to define an alias for partitions that will be reused multiple times. These are defined in the WINDOW clause, which usually is placed between the WHERE and GROUP BY clauses. The following query produces the same results as shown under the “PARTITION_BY Function” heading.
SELECT DATE_TRUNC('day', start_time) AS day,
duration_seconds,
SUM(duration_seconds) OVER main_window AS daily_running_ride_duration
FROM tutorial.dc_bikeshare_q1_2012
WHERE bike_number = 'W01412'
WINDOW main_window AS (PARTITION BY DATE_TRUNC('day', start_time) ORDER BY start_time)ROW_NUMBER and RANK
ROW_NUMBER displays the number of a given row within a window you define. With each new partition, the ROW_NUMBER is reset to zero.
RANK is similar, except for items with the same value in the ORDER BY clause, for which the same RANK will be assigned.