Adding OVER designates it as a window function. The first part of the above aggregation, SUM(duration_seconds), looks a lot like any other aggregation. Let's break down the syntax and see how it works. You can see that the above query creates an aggregation ( running_total) without using GROUP BY. SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total The most practical example of this is a running total: SELECT duration_seconds, Behind the scenes, the window function is able to access more than just the current row of the query result.

But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row - the rows retain their separate identities. This is comparable to the type of calculation that can be done with an aggregate function. PostgreSQL's documentation does an excellent job of introducing the concept of Window Functions:Ī window function performs a calculation across a set of table rows that are somehow related to the current row. The start_time and end_time fields were cleaned up from their original forms to suit SQL date formatting-they are stored in this table as timestamps. Most fields are self-explanatory, except rider_type: 'Registered' indicates a monthly membership to the rideshare program, 'Casual' incidates that the rider bought a 3-day pass. The data was downloaded in February, 2014, but is limited to data collected during the first quarter of 2012. This lesson uses data from Washington DC's Capital Bikeshare Program, which publishes detailed trip-level historical data on their website.

The usual suspects: SUM, COUNT, and AVG.Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis.

Avg clean up