SELECT
col1,
AVG(col2 + col3)
OVER(PARTITION BY cat_col1, cat_col2 ORDER BY col2 DESC ROWS BETWEEN <start> AND <finish> ) AS partitioned_avg
FROM table;
-- <start> : n PRECEDING, UNBOUNDED PRECEDING, CURRENT ROW (n is an integer)
-- <finish> : n FOLLOWING, UNBOUNDED FOLLOWING, CURRENT ROW (n is an integer)
-- PARTITION : resets for every new categorical value in the column
-- ROWS BETWEEN <start> AND <finish> : Window size
-- Some Other window functions : ROW_NUMBER(), DENSE_RANK(), NTILE(n), LEAD(col_name), LAG(col_name), SUM(), MIN(), MAX() etcSELECT duration_seconds,
SUM(duration_seconds) OVER (ORDER BY start_time) AS running_total
FROM tutorial.dc_bikeshare_q1_2012// The website linked gives extensive info on window functionsA window function performs a calculation across a set of table rows that are somehow
related to the current row.Window function can also be used to simplIfy complex queries
by allowing you to perform calculation in a single step that would have otherwise
require multiple queries or subquires.
Ranking function – RANK(), DENSE_RANK(), ROW_NUMBER()
Analytic functions – LAG(),LEAD()
Offset function – FIRST_VALUE() , LAST_VALUE()
-- Here's an example of using a SQL window function
SELECT employee_id, first_name, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;