The tables queried this blog post are the publicly-available “tutorial.dc_bikeshare_q1_2012,” table that is available at modeanalytics.com.
Window Functions are one of the most powerful concepts in SQL databases. They allow users to compare one row to another without doing any
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.
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.
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.
ORDER BY is omitted from the query, as in the following example, then the result is the daily_ride_duration without the “running” component.
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
GROUP BY clauses. The following query produces the same results as shown under the “PARTITION_BY Function” heading.