Named windows with WINDOW clause in PostgreSQL

Introduction

Named windows let you reuse the same window definition in multiple places in your query. This saves time and keeps your query neat.

When you want to apply the same sorting and partitioning rules to several window functions.

When your query has many window functions that share the same frame or partition.

When you want to make your SQL easier to read and maintain by avoiding repeated code.

Syntax

PostgreSQL

WINDOW window_name AS (window_definition)

The WINDOW clause is placed after the FROM clause and before ORDER BY.

You can define multiple named windows separated by commas.

Examples

This defines a window named w that groups rows by department and orders them by salary descending.

PostgreSQL

WINDOW w AS (PARTITION BY department ORDER BY salary DESC)

Uses the named window w in the RANK() function to rank employees by salary within each department.

PostgreSQL

SELECT name, salary, RANK() OVER w FROM employees WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

Defines two named windows: w1 partitions by category, w2 orders by date.

PostgreSQL

WINDOW w1 AS (PARTITION BY category), w2 AS (ORDER BY date)

Sample Program

This example creates a sales table, inserts some data, and then uses a named window w to calculate total sales and rank within each region.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  region TEXT,
  amount INT
);

INSERT INTO sales (region, amount) VALUES
('North', 100),
('South', 150),
('North', 200),
('South', 120),
('East', 130);

SELECT region, amount,
  SUM(amount) OVER w AS total_region_sales,
  RANK() OVER w AS rank_in_region
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY amount DESC);

Important Notes

Named windows help avoid repeating the same partition and order clauses multiple times.

You can refer to a named window inside another window definition by using WINDOW new_name AS (existing_name [frame]).

Summary

Named windows let you define window specifications once and reuse them.

This makes queries shorter, clearer, and easier to maintain.

Use the WINDOW clause after FROM and before ORDER BY.