Window function

The window function performs calculations across a set of table rows that are somehow related to the current row. This is similar to the form of calculation performed by aggregate functions. However, unlike regular aggregation functions, the use of window functions does not group rows into a single output row. Each row maintains its own identity.

Windows functions perform aggregation processing(ranking, etc) on acquired(SELECT) data. It can be said that it is an essential function in the recent trend of big data and OLAP.

SELECT
  Start_station_name,
  Duration,
  SUM(Duration) OVER (PARTITION BY Start_station_name ORDER BY Start_time) AS running_total
 FROM
  trip_histories
 WHERE
  Start_time < '2019-01-30'
 LIMIT 10;

SELECT
 Start_station_name,
 Duration,
 SUM(Duration) OVER (PARTITION BY Start_station_name) AS running_total,
 COUNT(Duration) OVER (PARTITION BY Start_station_name) AS running_count,
 AVG(Duration) OVER (PARTITION BY Start_station_name) AS running_avg
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

SELECT
 ROW_NUMBER() OVER (order by Start_time DESC) AS row_num,
 Start_station_name,
 Start_time,
 Duration
FROM
 trip_histories
WHERE
 Start_time < '2019-04-30'
LIMIT 10;

なるほどー、これは数こなさないとな～