Window functions let you compute values across a set of related rows, while keeping each row in the result. Use them for things like per group averages or totals, without collapsing rows with a GROUP BY.
SELECT
column_name1,
window_function(column_name2) OVER (PARTITION BY column_name3) AS new_column
FROM table_name;- window_function is an aggregate such as SUM, AVG, COUNT, MIN, MAX
- PARTITION BY groups rows for the window
- Omit PARTITION BY to treat the entire result as one window
Example
Add a new column to your dataset that calculates an aggregate value from another column. For example, create a column called Store_total that contains the sum of all values in the Purchase Total column.
Select *, SUM(Purchase Total) OVER() as Store_Total;Add a new column that aggregates data from another column, but groups the calculation by a categorical column.
Select *, SUM(Purchase Total) OVER(PARTITION BY Department) as Department_Total;
The current implementation supports the OVER clause with an optional PARTITION BY, enabling the use of aggregate functions within the window, including SUM, AVG, COUNT, MIN, and MAX. At present, ORDER BY within OVER is not supported, and ranking functions such as ROW_NUMBER, RANK, and DENSE_RANK are not yet available.