A Guide to ClickHouse® Window Functions

Introduction

Analyzing data often requires comparing rows, calculating rankings, finding running totals, or computing averages without changing the number of rows returned. Traditionally, these tasks required complex joins or subqueries.

ClickHouse® Window Functions simplify these operations by allowing calculations across a group of related rows while keeping every row in the result set. This makes analytical queries easier to write, read, and maintain.

In this guide, you'll learn what window functions are, why they're useful, and how to use the most common window functions in ClickHouse® with simple examples.

What Are Window Functions?

A window function performs calculations over a set of rows called a window. Unlike GROUP BY, which combines multiple rows into a single row, window functions return a value for every row.

For example, suppose you have employee salaries:

Using GROUP BY, you can calculate the average salary for HR, but you'll get only one row. With a window function, every employee remains in the result while also showing the department's average salary:

This is the main advantage of window functions.

Basic Syntax

SELECT
    column_name,
    window_function() OVER (
        PARTITION BY column # optional
        ORDER BY column # optional
    )
FROM table_name;

The OVER() clause defines how the window is created.

Sample Table

We'll use the following table throughout the examples:

CREATE TABLE employee_sales (
    Employee    String,
    Department  String,
    SaleDate    Date,
    Sales       UInt32
) ENGINE = MergeTree()
ORDER BY SaleDate;

Sample data:

1. Ranking Functions

ROW_NUMBER()

Assigns a unique number to every row:

SELECT
    Employee,
    Sales,
    ROW_NUMBER() OVER (ORDER BY Sales DESC) AS Rows
FROM employee_sales;

Output:

Use cases: Ranking reports, pagination, Top-N queries.

RANK()

Assigns the same rank to equal values, with gaps in the sequence:

SELECT
    Employee,
    Sales,
    RANK() OVER (ORDER BY Sales DESC) AS Rank
FROM employee_sales;

Output:

Notice that Rank 3 is skipped because two employees share Rank 2.

DENSE_RANK()

Similar to RANK() but without gaps:

SELECT
    Employee,
    Sales,
    DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRank
FROM employee_sales;

Output:

NTILE()

Divides rows into a specified number of equal-sized groups:

SELECT
    Employee,
    Sales,
    NTILE(2) OVER (ORDER BY Sales DESC) AS SalesGroup
FROM employee_sales;

Output:

Use cases: Customer segmentation, sales quartiles, performance grading.

2. Aggregations (Running and Cumulative)

Standard aggregate functions (SUM, AVG, MIN, MAX, COUNT) can operate as window functions when paired with OVER().

SUM() OVER() — Running Total

SELECT
    SaleDate,
    Sales,
    SUM(Sales) OVER (ORDER BY SaleDate) AS RunningTotal
FROM employee_sales;

Output:

Perfect for cumulative sales reports.

AVG() OVER() — Department Average

SELECT
    Employee,
    Department,
    Sales,
    AVG(Sales) OVER (PARTITION BY Department) AS DepartmentAverage
FROM employee_sales;

Output:

MOVING AVERAGES

SELECT
    SaleDate,
    Sales,
    AVG(Sales) OVER (
        ORDER BY SaleDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAverage
FROM employee_sales;

Output:

This calculates the average of the current row and the previous two rows.

Window Frames

Window frames restrict the rows used for the calculation to a specific boundary around the current row.

3. Value and Navigation Functions

LAG() - Previous Row Value

Returns the value from the previous row:

SELECT
    SaleDate,
    Sales,
    LAG(Sales) OVER (ORDER BY SaleDate) AS PreviousSales
FROM employee_sales;

Output:

Useful for comparing today's value with yesterday's.

LEAD() - Next Row Value

Returns the value from the next row:

SELECT
    SaleDate,
    Sales,
    LEAD(Sales) OVER (ORDER BY SaleDate) AS NextSales
FROM employee_sales;

Output:

Useful for forecasting and sequential analysis.

FIRST_VALUE() and LAST_VALUE()

Returns the first or last value in the window:

SELECT
    Employee,
    Sales,
    FIRST_VALUE(Sales) OVER (
        ORDER BY Sales DESC
    ) AS HighestSale,
    LAST_VALUE(Sales) OVER (
        ORDER BY Sales
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LastSale
FROM employee_sales;

Output:

Note: FIRST_VALUE() picks the first value after sorting by Sales DESC. LAST_VALUE() picks the last value over the entire window using UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING.

Window Functions vs GROUP BY

Performance Tips

• Sort data using an efficient ORDER BY key.
• Use PARTITION BY only when necessary.
• Filter data before applying window functions whenever possible.
• Avoid very large window frames if a smaller frame meets your needs.
• Use appropriate table engines such as MergeTree for analytical workloads.

Final Thoughts

ClickHouse® Window Functions provide a powerful way to perform advanced analytical calculations while preserving individual rows. Whether you're ranking records, calculating running totals, comparing values across rows, or building moving averages, window functions make SQL queries cleaner and more efficient.

By understanding the OVER() clause and commonly used functions like ROW_NUMBER(), RANK(), SUM(), AVG(), LAG(), and LEAD(), you can solve many real-world analytical problems with simpler, more readable SQL. Mastering these functions is an essential step toward writing efficient analytical queries in ClickHouse® and unlocking deeper insights from your data.

References

• ClickHouse® Documentation — Window Functions
• ClickHouse® Documentation — Aggregate Functions