How to Use Window Functions in ClickHouse

How do I use window functions in ClickHouse to create running totals, rankings, and moving averages?

Window functions in ClickHouse perform calculations across sets of query rows that are related to the current row without collapsing the result into groups.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problems do ClickHouse window functions solve?

Need running totals, rankings, or moving averages without losing individual rows? Window functions let you compute these metrics in a single query, keeping all detail intact.

Which ClickHouse window clauses are available?

ClickHouse supports OVER with optional PARTITION BY, ORDER BY, and a frame clause such as ROWS BETWEEN or RANGE BETWEEN. The frame controls which rows feed each calculation.

How do I declare a simple window?

Add OVER () after an aggregate-like function.Example: sum(total_amount) OVER () returns the grand total on every row.

How can I segment results per customer?

Place PARTITION BY customer_id inside OVER. Each customer gets its own running or ranking calculation.

How do I order rows in a partition?

Add ORDER BY to control calculation order. Commonly you use ORDER BY order_date for chronological metrics.

What frame options should I know?

Omit the frame to default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.Explicitly set ROWS BETWEEN 3 PRECEDING AND CURRENT ROW for 3-row moving windows.

Which built-in functions work as windows?

Any aggregate—sum, avg, count, min, max—plus ranking helpers such as row_number(), rank(), and dense_rank().

How to calculate a running revenue per customer?

Combine sum(total_amount), PARTITION BY customer_id, and ORDER BY order_date to get per-customer cumulative revenue.

Best practices for window functions?

Use the smallest frame

Smaller frames scan fewer rows and run faster.Prefer ROWS BETWEEN limits when the business logic allows.

Select only needed columns

Fetching unnecessary columns expands intermediate data. List just the fields you actually use.

Common pitfalls

Forgetting ORDER BY in a cumulative calc

Without ORDER BY, ClickHouse has no deterministic order, so running totals are meaningless. Always specify an ordering column.

Using RANGE on non-numeric ORDER BY

RANGE frames require numeric or date types. On strings you’ll get an error; switch to ROWS instead.

Why How to Use Window Functions in ClickHouse is important

How to Use Window Functions in ClickHouse Example Usage


SELECT
    o.id,
    o.customer_id,
    o.order_date,
    o.total_amount,
    sum(o.total_amount) OVER (
        PARTITION BY o.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total,
    row_number() OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS order_sequence
FROM Orders AS o
ORDER BY o.customer_id, o.order_date;

How to Use Window Functions in ClickHouse Syntax


SELECT
    column_list,
    agg_func(expression) OVER (
        PARTITION BY partition_expression
        ORDER BY order_expression
        [ROWS BETWEEN frame_start AND frame_end | RANGE BETWEEN frame_start AND frame_end]
    ) AS alias
FROM table_name;

Common Mistakes

Omitting ORDER BY inside OVER when calculating running metrics results in nondeterministic output. Always declare an ORDER BY to set row sequence.
Choosing RANGE frames on text columns triggers type errors. Use ROWS frames or cast the ORDER BY column to a numeric/date type.