🐘 PostgreSQL - Window Functions
Window functions operate on the result set of your query while keeping the row context. Perform calculations across multiple rows that are somehow related to the current row.
CREATE TABLE members (
name text,
joined_at timestamp,
siblings text[]
);
INSERT INTO members VALUES
('Abel', '2016-01-01', '{"Kain"}'),
('Juhani', '1870-01-14', '{"Tuomas", "Aapo", "Simeoni", "Timo", "Lauri", "Eero"}'),
('John', '1988-04-11', '{"Oliver"}'),
('Mary', '1988-04-09', '{"Sara", "Nina"}'),
('Annabel', '1988-04-13', NULL);
-- common subquery
SELECT
m. name,
(SELECT count(*) FROM members) AS member_count
FROM members m;
-- window function with the same result
SELECT name, count(*) OVER () AS member_count
FROM members;
Window function are like aggregate functions but don't cause rows to become grouped into a single output row.
SELECT
*,
rank() OVER (ORDER BY length(name)) AS shortest_name_rank
FROM members;
Window functions are applied after the WHERE clause, HAVING clause and aggregation. If you need to filter results of a window function, the window function must be done in a subquery because it is executed after WHERE.
SELECT *
FROM (
SELECT
name,
array_length(siblings, 1) total,
rank() OVER (ORDER BY array_length(siblings, 1) DESC NULLS LAST)
AS sibling_count_rank
FROM members
GROUP BY name, siblings
) AS sibling_ranked_members
WHERE sibling_count_rank = 1;
You can use a subset of the query with PARTITION BY.
-- Number of members that joined on the same month as the row member.
SELECT
name,
joined_at,
count(*) OVER (PARTITION BY date_trunc('month', joined_at))
AS join_count_in_joined_month
FROM members
ORDER BY joined_at;
You can change the order of results with ORDER BY.
-- Joinee numbering all time.
SELECT
name,
joined_at,
count(*) OVER (ORDER BY joined_at) AS joinee_number
FROM members
ORDER BY joined_at;
You can use both PARTITION BY and ORDER BY.
-- Joinee numbering per month.
SELECT
name,
joined_at,
count(*)
OVER (PARTITION BY date_trunc('month', joined_at) ORDER BY joined_at)
AS joinee_number_in_month
FROM members
ORDER BY joined_at;
You can write window functions into WINDOW clause. This is useful when you use a single window function in multiple places or when you are using multiple window functions.
SELECT
name,
joined_at,
count(*) OVER join_month AS join_count_in_month,
max(array_length(siblings, 1)) OVER join_month AS joinee_max_siblings_in_month
FROM members
WINDOW join_month AS (PARTITION BY date_trunc('month', joined_at))
ORDER BY joined_at;