Window Function

Introduction

When using GROUP BY to aggregate duplicate data rows, relevant individual data rows are lost.
Window Function is used to overcome this limitation. It helps perform aggregation calculations on a set of related rows while preserving the original data rows without collapsing them.
Window Function executes after WHERE, GROUP BY and HAVING clauses, but before DISTINCT and ORDER BY in the main statement.

Therefore, you cannot place a Window Function directly inside the WHERE clause.
If you want to filter data based on the result of a Window Function, you must use a temporary table such as a CTE or Subquery.

How to Use

To construct a Window Function, combine a Function and a Clause as follows:

SELECT
    FUNCTION() OVER (
        PARTITION BY col1
        ORDER BY col2
        ROWS/RANGE BETWEEN <START> AND <END>
    ) AS col_name
FROM table_name;

Function: The function placed before the OVER keyword, such as SUM(), AVG(), ROW_NUMBER(), RANK(). The function itself determines what calculation is performed.
Window Clause: The entire OVER (...) expression, which defines the scope of data over which the function operates.
- PARTITION BY: Groups rows into partitions, similar to GROUP BY but without collapsing rows. If omitted, calculations apply to the entire table.
- ORDER BY: Sorts rows within each partition for computation, which is crucial for ranking or cumulative aggregation functions.
- ROWS: Counts physical rows above or below the current row, regardless of the values contained within those rows.
- RANGE: Evaluates column values specified in the ORDER BY clause. Rows with duplicate values are grouped and calculated together simultaneously.
- To specify START and END frame boundary points, PostgreSQL provides the following keywords:
  - CURRENT ROW: The active row.
  - UNBOUNDED PRECEDING: The first row of the partition.
  - UNBOUNDED FOLLOWING: The last row of the partition.
  - N PRECEDING: N rows before the current row.
  - N FOLLOWING: N rows after the current row.
  - When using PRECEDING/FOLLOWING, PostgreSQL supports almost all standard time units within the INTERVAL string, such as minute, hour, day, month, year.
  - Examples: INTERVAL '30 minutes', INTERVAL '12 hours', INTERVAL '3 days', INTERVAL '1 month', INTERVAL '1 year'.

Advantages

When working with Real-time Analytics systems on a single table (if data spans multiple tables, using JOIN is mandatory), applying Window Function effectively can replace complex Self-Join or SUBQUERY statements with the following benefits:

Clean, maintainable and optimized code
- To add calculations under different logical conditions, simply add a column using a Window Function.
- No need to rewrite query logic as required when using JOIN, GROUP BY or SUBQUERY.
Hardware resource savings (I/O and Memory)
- When using JOIN, PostgreSQL creates a temporary table in memory (or writes to disk if dataset is too large) to store intermediate results before joining. This consumes significant RAM and increases Disk I/O bottlenecks.
- Window Function computes directly on-the-fly (evaluating data directly on the streaming flow), consuming minimal system resources.
Lower algorithmic complexity and higher processing performance
- When using JOIN / SUBQUERY, regardless of the join algorithm used (Nested Loop, Hash, Merge), PostgreSQL must process across two datasets. As data grows larger alongside GROUP BY, computational cost increases significantly.
- When using ROWS / RANGE, data only needs to be sorted once (or skipped completely if an INDEX exists). Afterwards, it scans the table in a single pass from top to bottom, computing values using an intermediate state variable during iteration.

Detail

First, create the necessary Tables and Indexes as follows:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

CREATE TABLE user_activity_logs (
    log_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    action_time TIMESTAMP NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    points_earned INT DEFAULT 0
);

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_logs_customer_time ON user_activity_logs (customer_id, action_time);

Here is an example comparing performance differences between JOIN and Window Function:

-- Query 1
SELECT 
    o1.customer_id,
    o1.order_id,
    o1.order_date,
    o1.amount,
    SUM(o2.amount) AS running_total
FROM orders o1
JOIN orders o2 
    ON o1.customer_id = o2.customer_id 
   AND o2.order_date <= o1.order_date
GROUP BY 
    o1.customer_id, 
    o1.order_id, 
    o1.order_date, 
    o1.amount
ORDER BY 
    o1.customer_id, 
    o1.order_date;

-- Query 2
SELECT 
    customer_id,
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY 
    customer_id, 
    order_date;

These queries compute cumulative total spending (running_total) for each customer across orders, sorted by order_date.

Query 1: Uses Self-JOIN
- Requires joining orders o1 and orders o2 with condition o1.customer_id = o2.customer_id AND o2.order_date <= o1.order_date.
- Hash Join output generates large intermediate rows.
- Requires subsequent GROUP BY execution on the combined dataset.
Query 2: Uses Window Function
- Index works efficiently with ORDER BY.
- Scans table orders only once to compute SUM per customer without joining datasets.
- Execution speed is significantly faster than Self-JOIN.

Next are examples demonstrating usage with ROW/RANGE:

-- Query 1
SELECT 
    c.customer_name,
    o.order_date,
    o.amount,

    FIRST_VALUE(o.amount) OVER w_full AS first_order_amount,
    LAST_VALUE(o.amount)  OVER w_full AS latest_order_amount,

    SUM(o.amount) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_rows,

    SUM(o.amount) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_range,

    AVG(o.amount) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS moving_avg_3_orders
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WINDOW w_full AS (
    PARTITION BY o.customer_id 
    ORDER BY o.order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY o.customer_id, o.order_date;

-- Query 2
SELECT 
    c.customer_name,
    l.action_time,
    l.action_type,
    l.points_earned AS points_this_action,

    SUM(l.points_earned) OVER (
        PARTITION BY l.customer_id
        ORDER BY l.action_time
        RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
    ) AS points_in_last_5_mins,

    SUM(l.points_earned) OVER (
        PARTITION BY l.customer_id
        ORDER BY l.action_time
        RANGE BETWEEN INTERVAL '2 hours' PRECEDING AND CURRENT ROW
    ) AS points_in_last_2_hours,

    SUM(l.points_earned) OVER (
        PARTITION BY l.customer_id
        ORDER BY l.action_time
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) AS points_in_last_2_days,

    SUM(l.points_earned) OVER (
        PARTITION BY l.customer_id
        ORDER BY l.action_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS total_lifetime_points

FROM user_activity_logs l
JOIN customers c ON l.customer_id = c.customer_id
ORDER BY l.customer_id, l.action_time;

-- Query 3
WITH ranked_orders AS (
    SELECT 
        order_id,
        customer_id,
        order_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY amount DESC, order_date DESC
        ) AS rank_by_amount
    FROM orders
)
SELECT 
    c.customer_id,
    c.customer_name,
    c.email,
    ro.order_id AS highest_order_id,
    ro.amount AS highest_order_amount,
    ro.order_date AS highest_order_date
FROM ranked_orders ro
JOIN customers c ON ro.customer_id = c.customer_id
WHERE ro.rank_by_amount = 1
ORDER BY ro.amount DESC;

Query 1: Analyzes spending behavior and order value per customer
- FIRST_VALUE and LAST_VALUE extract order amounts for first and last orders respectively.
  - ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING evaluates across all rows within partition customer_id.
  - WINDOW w_full serves as a reusable alias since both functions share identical window specifications.
- SUM with ROWS calculates cumulative total based strictly on physical rows scanned up to the current row.
- RANGE calculates logical accumulation where rows with duplicate timestamps are grouped together.
- AVG with ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING computes a 3-order moving average.
Query 2: Detects spam behavior or unusual point accumulation over real-time intervals
- Uses INTERVAL to calculate windowed metrics:
  - points_in_last_5_mins: Total points in recent 5 minutes.
  - points_in_last_2_hours: Total points in recent 2 hours.
  - points_in_last_2_days: Total points in recent 2 days.
- total_lifetime_points tracks lifetime accumulation up to current row.
Query 3: Retrieves highest value order (Top 1) per customer alongside customer details
- Uses CTE to construct temporary result set ranked_orders with window function.
  - ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_date DESC) partitions by customer_id and orders by amount and order_date descending.
  - Column rank_by_amount starts at 1 and increments per customer partition.
- Joins with customers table after ranking to maximize query performance.
- WHERE ro.rank_by_amount = 1 filters top ranked order per customer efficiently.