An aggregate window function comes with an aggregate function and an OVER clause. It operates on a set of rows and returns a single value for each row from the underlying query. The OVER clause indicates how to partition the rows in the result set.
When you use aggregate functions with GROUP BY, a single row is returned for each unique set of values in the grouped columns. Aggregate window functions do not collapse rows. All of the rows in the result set are returned. See Examples for a detailed comparison.
All the aggregate functions supported by Databend can be used as aggregate window functions. See Aggregate Functions for supported aggregate functions.
<aggregate-function> ( <arguments> )
OVER ([PARTITION BY expression1 [, expression2] ...]
[ORDER BY expression1 [ASC | DESC]] [, expression2 [ASC | DESC]] ... )
Imagine that we manage a bookstore with two branches in Toronto and Ottawa. We create a table to store the transactions for both cities from June 21 to June 23.
-- create a table
CREATE TABLE BookSold (
id INTEGER PRIMARY KEY,
date TEXT NOT NULL,
city TEXT NOT NULL,
amount INTEGER NOT NULL
-- insert some values
INSERT INTO BookSold VALUES (1, 'June 21', 'Toronto', 685);
INSERT INTO BookSold VALUES (2, 'June 21', 'Ottawa', 403);
INSERT INTO BookSold VALUES (3, 'June 22', 'Toronto', 679);
INSERT INTO BookSold VALUES (4, 'June 22', 'Ottawa', 230);
INSERT INTO BookSold VALUES (5, 'June 23', 'Toronto', 379);
INSERT INTO BookSold VALUES (6, 'June 23', 'Ottawa', 907);
-- show the table
SELECT * FROM BookSold;
If we use the aggregate function (AVG) to calculate the average amount of books sold for each branch, the result will be grouped by date:
-- use aggrerate function with GROUP BY
SELECT date, AVG(amount) AS avg_amount_for_branch
GROUP BY date;
If we use the aggrerate window function, the result will include all the rows:
-- use aggrerate window function
SELECT date, AVG(amount) over (partition by date)
If we leave the OVER clause empty, it calculates the average of the total amount of three days.
-- use aggrerate window function without PARTITION BY in the OVER clause
SELECT date, AVG(amount) over ()