Fun with ClickHouse® Window Functions

Presenters: Robert Hodges and Vitaliy Zakaznikov 

In this tutorial webinar, Altinity CEO Robert Hodges and QA Manager Vitaliy Aksionov introduce ClickHouse window functions, a long-awaited SQL feature that became available in 21.3 and was still experimental at the time of recording. Aimed at users new to window functions, it combines conceptual explanation with runnable SQL on the public airline on-time dataset. Robert frames the problem: standard GROUP BY can’t answer questions like “rank days by cancellations within a week” or “compute a seven-day rolling average” without pulling data into a separate application. Window functions solve this by extending the OLAP cube model to let a query look at adjacent cells, not just the current one—demonstrated with a rolling average using the OVER (ORDER BY … ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) syntax.

Vitaliy takes the deep technical section, explaining the three things needed to predict window output: the partition (which rows are in the window), the ORDER BY (what “current row peers” means in RANGE frames), and the frame clause (ROWS vs RANGE and the five boundary types). He covers the subtle peer concept—in a RANGE frame without ORDER BY, all rows are peers, so every row gets the same result—and the difference between specifying only a frame start versus an explicit BETWEEN clause.

Robert returns with practical examples: cumulative monthly cancellations, weekly ranking with rank(), multiple simultaneous windows in one query, and named window definitions via the WINDOW clause. He also shows the array-based alternative, illustrating why it works for simple cases but becomes impractical with multiple windows. The session closes with Vitaliy on current limitations: RANGE frame support restricted to integer and date types, no interval support in date range frames, no EXCLUDE clause, and lagInFrame/leadInFrame as the ClickHouse-specific replacements for standard lag/lead.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:09 – Introduction: Robert Hodges and Vitaliy Aksionov
• 1:38 – Speaker bios: Robert Hodges (Altinity CEO) and Vitaliy Aksionov (QA Manager, TestFlows author)
• 2:24 – Altinity overview: enterprise ClickHouse provider, Altinity.Cloud
• 3:02 – ClickHouse overview: SQL data warehouse, MySQL + Vertica heritage
• 6:11 – Altinity.Cloud public playground: demo/demo credentials, live examples
• 7:24 – What window functions are: extending SQL beyond GROUP BY
• 8:03 – Starting point: simple flight cancellation query on the airline on-time dataset
• 8:52 – The OLAP cube mental model: dimensions vs. metrics, what it can’t do
• 10:51 – Questions SQL alone cannot answer: ranking, cumulative sums, rolling averages
• 12:01 – Enabling window functions: allow_experimental_window_functions = 1
• 13:00 – First window function example: seven-day rolling average with OVER clause
• 14:07 – How window functions extend the cube model: looking at adjacent cells
• 15:48 – Handoff to Vitaliy Aksionov for detailed syntax walkthrough
• 16:07 – Visualizing a window: partition, frame, sliding current row
• 18:04 – Why behavior is not obvious: sum() OVER () example with numbers(5)
• 19:36 – Three things that explain the result: empty OVER, no ORDER BY, default frame
• 20:41 – Window-native functions: row_number, rank, dense_rank, first_value, last_value, lagInFrame, leadInFrame
• 21:32 – The OVER clause: empty, inline window specification, named window reference
• 22:38 – Window specification components: PARTITION BY, ORDER BY, frame clause
• 23:11 – ROWS frame vs RANGE frame: row-number-based vs value-based boundaries
• 24:19 – Current row peers: rows in the same ORDER BY sort bucket in a RANGE frame
• 26:01 – Frame extent: start-only (implicit CURRENT ROW end) vs explicit BETWEEN
• 27:22 – Five frame boundary values: CURRENT ROW, UNBOUNDED PRECEDING/FOLLOWING, expression PRECEDING/FOLLOWING
• 28:41 – Syntax summary: ROWS and RANGE frames are standard SQL, consistent with MySQL and PostgreSQL
• 29:04 – Handoff back to Robert Hodges for practical query examples
• 29:30 – Window function execution model: parallel scan → sequential aggregation → sequential window compute
• 31:02 – Distributed tables: window functions computed on the initiating node only
• 32:16 – Practical example 1: cumulative monthly cancellations with SUM() OVER (PARTITION BY month ORDER BY date)
• 33:29 – Practical example 2: weekly rank using rank() OVER (PARTITION BY week ORDER BY daily_cancels DESC)
• 35:07 – Practical example 3: multiple simultaneous windows — overall rank and rank by carrier
• 36:00 – Hawaiian Airlines insight: ~12 flights per day, highest rank in January
• 36:51 – Practical example 4: named window definitions using the WINDOW clause
• 38:56 – Alternative approach: using arrays (groupArray, arrayEnumerate, arrayJoin) instead of window functions
• 39:51 – Why arrays become impractical with multiple windows
• 40:16 – When arrays are still a valid approach: simple queries with few columns
• 40:23 – Roadmap and current limitations (Vitaliy)
• 40:55 – Limitation 1: RANGE frames only support integer and date/datetime types
• 41:21 – Limitation 2: no interval support in date RANGE frames
• 42:05 – Limitation 3: no EXCLUDE clause; no group frames
• 42:11 – Limitation 4: lagInFrame/leadInFrame vs standard lag/lead — frame boundary behavior
• 43:05 – Limitation 5: window functions cannot be used inside expressions
• 43:17 – Limitation 6: RANGE frames not supported with named windows
• 43:22 – Overall status: 93% of requirements satisfied; results validated against PostgreSQL
• 43:49 – Resources: ClickHouse docs, Altinity blog article, SRS specification, Altinity KB, Tinybird blog
• 46:07 – Special thanks: Alexander Kuzminkov (implementer), Alexey Milovidov, Altinity QA team
• 46:59 – Q&A begins
• 47:24 – Q&A: difference between lagInFrame and standard lag/lead
• 50:05 – Q&A: do window functions slow down queries or use more RAM?
• 51:40 – Q&A: lag/lead vs arrays for performance
• 53:01 – Q&A: does lagInFrame honor partitions and reset on each partition?

Webinar Transcript

[0:09] — Introduction and Housekeeping

Robert: Welcome, everyone. This is our webinar from Altinity called “Fun with ClickHouse Window Functions.” My name is Robert Hodges and I’ll be presenting today with my colleague Vitaliy Aksionov. We both work for Altinity.

A couple of housekeeping things. This webinar is being recorded; we’ll post a link afterwards so you can view it again at your leisure or share it with friends. The slides are already available and I’ll repost the link in the chat. We also have Q&A: there should be plenty of time to answer questions. Post them into the Q&A box in the Zoom menu and we’ll answer them in passing if relevant, or queue them up at the end.

[1:38] — Speaker Introductions

Robert: I’m CEO of Altinity, but I’ve been working on databases since 1983. ClickHouse is database number 20. It’s definitely in my top two — the other being Sybase, which I loved when I worked there. With me today is Vitaliy Aksionov, who is the QA Manager of Altinity. He has over 13 years of experience testing hardware and software. He wrote the TestFlows open-source testing framework, which we use for all our functional testing of ClickHouse as well as other products. There are many TestFlows tests in the ClickHouse GitHub repository.

[2:24] — About Altinity

Robert: At Altinity we are an enterprise provider for ClickHouse. We do everything from training to 24×7 support to engineering features into ClickHouse itself, as well as other products. We run ClickHouse in the cloud: Altinity.Cloud lets you run it in Amazon. Relevant for this talk, we’re a major committer and community sponsor for ClickHouse, particularly in the US and European markets.

[3:02] — ClickHouse Overview

Robert: Just as a level set, let me briefly introduce ClickHouse in case someone on this call is new to it.

ClickHouse is a really great SQL data warehouse. It’s the first open-source SQL data warehouse that can compete with the big proprietary offerings. Think of it as a marriage of the best parts of MySQL and the best parts of a proprietary data warehouse like Vertica, Redshift, or Snowflake. From MySQL it understands SQL, runs everywhere (basically anywhere Linux runs), and is open source under Apache 2.0. From the data warehouse side it has a shared-nothing architecture, columnar storage with very high compression (you can reduce stored data by a factor of up to a thousand before pre-aggregation), very efficient parallel and vectorized execution using SIMD instructions, and linear scaling from a laptop to clusters containing hundreds of nodes running tens of petabytes. And then the final thing everybody notices: it’s super fast.

[6:11] — Altinity.Cloud Public Playground

Robert: In these talks we’re increasingly making every example executable. You can log into the Altinity.Cloud public endpoint right now to follow along. The web UI login is available at the link shown, user is demo, password is demo. If you have a ClickHouse client you can also connect using the connection string shown below. I’ll republish these links in chat.

[7:24] — What Window Functions Are and Why They Matter

Robert: Let me now dig into what window functions actually are. If you’re a ClickHouse user, you probably haven’t used them very much because prior to version 21.3 they didn’t exist. That was earlier this year when they first became available. Development was led by Alexander Kuzminkov and has been ongoing for a long time, and we’re continuing to test and work on them.

To understand window functions, it’s very helpful to start with SQL without them and understand what it can and cannot do. Here’s a simple query we’ll be working with throughout this webinar: it counts the number of flights per day and the number of canceled flights per day for January 2019 on the US airline on-time dataset.

SELECT

    FlightDate,

    count() AS total_flights,

    sum(Cancelled) AS cancelled_flights

FROM ontime

WHERE FlightDate >= ‘2019-01-01’ AND FlightDate < ‘2019-02-01’

GROUP BY FlightDate

ORDER BY FlightDate

This is a typical ClickHouse query: GROUP BY, ORDER BY, and some aggregates. Very standard.

[8:52] — The OLAP Cube Mental Model

Robert: Let me dig into the model behind this query. When you build a GROUP BY query like that, you can think of it as representing something similar to what used to be called an OLAP cube. Some of the columns you’re selecting are dimensions — things that describe the record, like flight date, or whether a flight was canceled. Other columns are metrics or measurements, like the count of records per day or the sum of the cancellations flag.

What this query does is compute the value at the cell that is the intersection of a specific flight date and the cancellations metric. All other dimensions are ignored. The result is a row for each cell with those specific values.

This is a great model and helps you understand a lot of what goes on in SQL, particularly if you’re using BI tools like Superset or Tableau which essentially have this mental model embedded in how they operate.

[10:51] — What SQL Alone Cannot Do

Robert: There is a problem, though. Because we have dimensions and then measures for cells, there are certain types of questions that are difficult or impossible to answer with a simple SQL query.

For example: what if we want to not only take those measurements but also rank particular days of the month by the number of canceled flights? What day of the month is number one in cancellations? Another thing we might want is a cumulative sum of canceled flights up to each point in the month. A third very common operation for time series is a trailing average, such as a seven-day rolling average of cancellations.

You can’t do any of these in ordinary SQL. You’d basically have to pull the data out and put it in an application, say in pandas, and do additional processing there. Window functions are the SQL feature that allows you to do this directly.

[12:01] — Enabling Window Functions

Robert: In ClickHouse, window functions are still considered experimental. If you’re a ClickHouse user you know that we sometimes add a switch that needs to be turned on because a feature isn’t completely stable yet and is only for people ready to use it. You can enable window functions in a session:

SET allow_experimental_window_functions = 1;

Or you can turn it on inside user profiles, which is how we set it up on the Altinity.Cloud demo server. Anyone with that profile can then use window functions.

[13:00] — First Window Function Example: Seven-Day Rolling Average

Robert: Once you turn them on, you can add new syntax to your queries. To compute a rolling seven-day average of canceled flights:

SELECT

    FlightDate,

    sum(Cancelled) AS daily_cancelled,

    avg(daily_cancelled) OVER (

        ORDER BY FlightDate

        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

    ) AS rolling_avg_7day

FROM ontime

WHERE FlightDate >= ‘2019-01-01’ AND FlightDate < ‘2019-02-01’

GROUP BY FlightDate

ORDER BY FlightDate

The key addition is the avg(…) OVER (…) clause. The OVER defines a window and the expression inside says: look at the current row and the six preceding rows. Because the data is time-ordered, this gives you seven days, and we take the average across them.

This allows us to layer additional processing on top of the aggregation. We’re not just looking at the cell we’re processing for this row; we’re looking at cells before it as well. That’s what window functions do: they extend the OLAP model so you can consider things that relate to other cells in the cube. The rolling average is computed correctly across month boundaries and any other time boundaries in the dataset.

[15:48] — Handoff to Vitaliy Aksionov

Robert: At this point I’m going to turn it over to Vitaliy, who’s going to dig into the detailed syntax of window functions and show how they work in detail.

[16:07] — Visualizing the Window and Frame

Vitaliy: Hi everyone. For those of you with experience in MySQL or PostgreSQL, some of this information will be a refresher, but for most ClickHouse users who have never used window functions, we hope this will be very useful.

To understand window functions it really helps to visualize what’s going on. Think about it from the name: there’s something to do with “windows” and something to do with “functions.” A window can look at all the rows in your results or just a chunk of them. You can partition your results into windows.

Within a window, there’s a current row and a frame. The frame can be just the current row, or it can include a number of rows before the current row, some rows after, or both. That frame slides through the window as the current row changes. You apply a function over the frame for each position of the current row. That function result becomes another output column.

[18:04] — Why Behavior Is Not Obvious: The sum() OVER () Example

Vitaliy: The behavior of window functions is not always obvious. Let’s look at a very simple example to illustrate. We query numbers(5) and get the values 1 through 5. Then we apply sum(number) OVER () with an empty OVER clause.

SELECT number, sum(number) OVER () AS window_sum

FROM numbers(5)

Running this gives us 15 for every row. Why 15 every time? Three things explain this:

First, the empty OVER clause means there is only one window and it includes all rows in the result set. Second, there is no ORDER BY clause defined for the window, so all rows are considered peers of the current row. Third, we didn’t specify a frame, so the default frame applies. The default is a RANGE frame with extent BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Because there’s no ORDER BY, all rows are peers of the current row, which means all rows are in the frame for every row. So for each row we sum all five numbers, giving 15 every time.

Understanding these three things — partition, ORDER BY, and frame — is essential to predicting window function output.

[20:41] — Window-Native Functions

Vitaliy: The function part of a window function can be any aggregate function: min, max, sum, avg, and so on. There are also special window-native functions that only work with windows: row_number, first_value, last_value, rank, dense_rank, and in ClickHouse specifically lagInFrame and leadInFrame. We’ll come back to the distinction between these and the standard lag/lead functions at the end.

[21:32] — The OVER Clause: Three Forms

Vitaliy: For the window functions OVER clause, there are three basic scenarios. The OVER clause can be empty — one window containing all rows. It can contain an inline window specification that defines one or more partitions directly. Or it can reference a named window defined elsewhere in the query with the WINDOW keyword. Named windows are a shortcut: if you use the same window definition for multiple OVER clauses you define it once and reference it by name rather than repeating the full specification.

[22:38] — Window Specification: Three Components

Vitaliy: A window specification has three parts:

PARTITION BY — defines the window partition. Rows with the same partition key value form one window.

ORDER BY — orders rows within the window partition. This also defines what “current row peers” means, which is critical for RANGE frames.

The frame clause — specifies which rows within the partition are part of the active frame for each current row. ClickHouse currently supports two frame types: ROWS and RANGE.

[23:11] — ROWS Frame vs RANGE Frame

Vitaliy: The difference between ROWS and RANGE is important. A ROWS frame defines its extent in terms of physical row positions relative to the current row. For example, ROWS 1 PRECEDING means the one row physically preceding the current row.

A RANGE frame defines its extent in terms of the value of the ORDER BY column relative to the current row’s value. RANGE 1 PRECEDING means all rows where the ORDER BY column value is greater than or equal to the current row’s value minus 1.

RANGE frames involve the concept of current row peers.

[24:19] — Current Row Peers

Vitaliy: Current row peers are rows that fall into the same ORDER BY sort bucket as the current row, and this concept applies only to RANGE frames. Let me show two examples.

Without an ORDER BY clause, all rows are peers of the current row. The sum is the same for every row because the entire window is always in the frame.

With an ORDER BY clause, only rows with the same ORDER BY value are peers. So if you have two rows with value 2, those two rows are peers of each other. For the row with value 2, the peers (value 2, twice) plus all rows with smaller values that fall into the frame are included. The result differs per row.

Knowing what current row peers are is essential when you use RANGE frames. Whatever rows fall in the same sort bucket are considered peers of that current row.

[26:01] — Frame Extent: Start-Only vs BETWEEN

Vitaliy: You can define the frame extent in two ways: specifying only the start, or using an explicit BETWEEN clause.

If you only specify the start, the end is implicit: it is the current row. So ROWS 1 PRECEDING is equivalent to ROWS BETWEEN 1 PRECEDING AND CURRENT ROW. ClickHouse will actually convert your query to the explicit BETWEEN form when it executes it.

If you use BETWEEN explicitly, ClickHouse uses exactly what you specified. So ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING gives you precisely that range.

[27:22] — Five Frame Boundary Values

Vitaliy: For ROWS and RANGE frame starts and ends, there are five values:

CURRENT ROW — the same in both frame types: it refers to the current row.

UNBOUNDED PRECEDING — all rows before the current row are in the frame.

UNBOUNDED FOLLOWING — everything from the current row forward is included.

expression PRECEDING — for ROWS, this many rows before the current row; for RANGE, rows where the ORDER BY value is at least expression less than the current row’s value.

expression FOLLOWING — the mirror of the above, looking ahead.

The syntax is standard SQL, consistent with MySQL and PostgreSQL, which is very convenient.

[29:04] — Window Function Execution Model

Robert: Understanding the plumbing of how window functions execute inside ClickHouse matters because it has performance implications.

Normally, when ClickHouse processes a query, it achieves high speed partly through parallelizing the scan. For a standard GROUP BY query, ClickHouse breaks the query up and farms pieces to all available CPU cores. They race through storage reading columns, doing partial aggregation: if you’re summing, each core sums its piece. Then those partial results flow back to a sequential step where the final aggregation and sort are completed.

The two stages are: (1) parallel scan with partial aggregation, and (2) sequential merge plus sort.

Window functions depend on the values produced by this first sequential step. You cannot compute window functions until that sequential aggregation is completely done. The window function step itself is also processed sequentially. This means that if you’re doing something in a window function that would benefit from parallelization, that parallelization won’t happen in ClickHouse.

[31:02] — Distributed Tables and Window Functions

Robert: There’s a further consequence for distributed tables. A distributed table engine is an umbrella over a bunch of local tables. Sub-queries are farmed out to each local table and results are passed back to the initiating node for final aggregation, sort, and window function computation.

Your window function is always computed on that initiating node. If you have an enormous amount of data passed back, or if the window functions are very complex, you may face memory pressure or simply longer query times because you don’t get the benefits of distribution for that step.

We’ll be doing performance testing on window functions as they stabilize and will publish more concrete guidance. For cumulative aggregations in ClickHouse and related patterns, the Altinity Knowledge Base has additional examples.

[32:16] — Practical Example 1: Cumulative Monthly Cancellations

Robert: Let’s look at real examples using the airline on-time dataset. First, cumulative monthly cancellations: for each day of the month, show the running total of canceled flights.

SELECT

    FlightDate,

    sum(Cancelled) AS daily_cancelled,

    sum(daily_cancelled) OVER (

        PARTITION BY toStartOfMonth(FlightDate)

        ORDER BY FlightDate

        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS cumulative_cancelled

FROM ontime

WHERE FlightDate >= ‘2019-01-01’ AND FlightDate < ‘2019-02-01’

GROUP BY FlightDate

ORDER BY FlightDate

We partition by month, order by date within each partition, and sum with a window from the beginning of the partition to the current row. You can see in the results that the cumulative totals correctly add up across the month.

[33:29] — Practical Example 2: Ranking Cancellations by Week

Robert: Another example: rank the days of each week by number of cancellations. Which day had the most cancellations? We use the window-native rank() function:

SELECT

    FlightDate,

    sum(Cancelled) AS daily_cancelled,

    rank() OVER (

        PARTITION BY toStartOfWeek(FlightDate)

        ORDER BY sum(Cancelled) DESC

    ) AS weekly_rank

FROM ontime

WHERE FlightDate >= ‘2019-01-01’ AND FlightDate < ‘2019-02-01’

GROUP BY FlightDate

ORDER BY FlightDate

toStartOfWeek normalizes each date to the start of its week. Within each week partition the rows are ordered by descending daily cancellations, and rank() assigns rankings. This is a pretty useful computation that would be very hard to produce in standard SQL without window functions.

[35:07] — Practical Example 3: Multiple Simultaneous Windows

Robert: One of the places where window functions really shine in ClickHouse is having multiple windows operating in a single query. You can have window functions looking at the data in different orders with different partitions simultaneously, which is very hard to achieve any other way.

Here we get both an overall rank across all carriers and a rank within each carrier, in a single pass:

SELECT

    UniqueCarrier,

    count() AS flights,

    rank() OVER (ORDER BY count() DESC) AS overall_rank,

    rank() OVER (PARTITION BY UniqueCarrier ORDER BY count() DESC) AS carrier_rank

FROM ontime

WHERE FlightDate >= ‘2019-01-01’ AND FlightDate < ‘2019-02-01’

GROUP BY UniqueCarrier

ORDER BY overall_rank

An interesting finding from January: Hawaiian Airlines ranks number one in daily flights, doing about 12 flights per day for their aircraft. You can then see both their overall rank and their rank within their own carrier.

[36:51] — Practical Example 4: Named Window Definitions

Robert: You can also reuse window definitions using the WINDOW clause, which comes after the GROUP BY. It’s similar to a Common Table Expression: you define the window once and reference it by name in multiple OVER clauses.

SELECT

    FlightDate,

    avg(daily_cancelled) OVER seven_day AS rolling_avg,

    max(daily_cancelled) OVER seven_day AS rolling_max

FROM (…)

WINDOW seven_day AS (ORDER BY FlightDate ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

I called my window seven_day and refer to it in both OVER clauses. This keeps the window specification in one place and makes the query easier to read. My names tend to be descriptive so queries are still long, but at least the window definition isn’t repeated.

[38:56] — Arrays as an Alternative to Window Functions

Robert: Are window functions the only way to do these things? Definitely not. ClickHouse has had arrays for a long time and many experienced users use them for calculations that window functions can now handle.

Here’s what the weekly-rank-by-cancellations example looks like using arrays:

SELECT

    week,

    arrayJoin(arraySort((x, y) -> y, arrayEnumerate(grouped_dates), grouped_cancelled)) AS rank,

    arrayJoin(arraySort((x, y) -> y, grouped_dates, grouped_cancelled)) AS flight_date

FROM (

    SELECT

        toStartOfWeek(FlightDate) AS week,

        groupArray(FlightDate) AS grouped_dates,

        groupArray(sum_cancelled) AS grouped_cancelled

    FROM (

        SELECT FlightDate, sum(Cancelled) AS sum_cancelled

        FROM ontime

        GROUP BY FlightDate

    )

    GROUP BY week

)

The trick with arrays is using groupArray to collect all values within a group into an array, then arrayEnumerate to get indices, sorting those indices by the cancellation values in reverse order, and finally arrayJoin to unroll the arrays back into tabular rows.

If you followed that, you’re pretty good at ClickHouse arrays. But this is exactly why people want window functions instead of array workarounds: the array approach works but is considerably more complex. More importantly, if you had multiple windows defined in a single query, you’d need multiple array groupings and multiple unrolling steps. I think that would be very difficult or impossible in many cases.

For simple queries with small numbers of columns, arrays can still be a valid approach. But for anything involving multiple windows, window functions are the right tool.

[40:23] — Limitations and Roadmap

Vitaliy: We at Altinity performed the initial testing for window functions and found a number of things not yet supported compared to PostgreSQL or MySQL.

Limitation 1: RANGE frame type restrictions. RANGE frames currently only support unsigned integers, signed integers, dates, and datetimes as the ORDER BY column type. Nullable variants like Nullable(Int32) will give an error. So check your column types before using RANGE frames.

Limitation 2: No interval support in date RANGE frames. Even though dates and datetimes are supported in RANGE frames, working with them is not convenient because there’s no support for SQL intervals. You cannot say “30 days before this date.” There is a workaround — specify the value explicitly in days as an integer — but computing date arithmetic that accounts for varying month and year lengths is tricky without interval support.

Limitation 3: No EXCLUDE clause; no group frames. These are present in PostgreSQL but not yet in ClickHouse.

Limitation 4: lagInFrame and leadInFrame vs standard lag/lead. In MySQL and PostgreSQL, lag and lead do not respect frame boundaries — they look at the full partition window. In ClickHouse, the implementations are called lagInFrame and leadInFrame to emphasize that they do respect frame boundaries. If you want the standard lag/lead behavior, there is a workaround documented on the ClickHouse lag and lead KB page.

Limitation 5: No use of window functions inside expressions. You currently cannot embed a window function call inside a larger expression.

Limitation 6: RANGE frames not supported with named windows.

Overall, our testing showed that the implementation satisfies 93% of the requirements we defined. We compared results against PostgreSQL to validate correctness, and the results are correct for all supported cases. Window functions are maturing fast and will likely move out of experimental status soon. For a comprehensive overview of the feature, see the ClickHouse window functions: current state of the art blog article on the Altinity blog.

[43:49] — Resources

Vitaliy: If you’d like more information:

The ClickHouse documentation has a concise section covering supported syntax and known limitations. The Altinity window functions blog article covers syntax, examples, limitations, and workarounds in detail. We also publish our Software Requirements Specification (SRS) documents internally, which specify the exact expected behavior and are used to drive our test development. The Altinity Knowledge Base has many window function examples including cumulative sums. Tinybird also published a blog article with additional window function examples and use cases. And if you’re coming from MySQL or PostgreSQL, their window function documentation is quite good as a reference for the standard syntax.

[46:07] — Special Thanks

Robert: A huge community effort made this happen. Alexander Kuzminkov is the implementer of window functions and is still actively working on them. Unfortunately he couldn’t join today. Alexey Milovidov, the ClickHouse lead committer, provided helpful hints on internal plumbing. And the Altinity QA team, led by Vitaliy who is on this call, did a really great job of testing this feature.

Thank you all for attending. If you’d like to contact us, come to altinity.com or send email to info@altinity.com. Altinity.Cloud is where all the sample queries in this talk can be run. And we’re hiring.

[46:59] — Q&A

Robert: Questions are coming in. Let me read them out.

Q: Can you elaborate on the difference between lagInFrame and the standard lag function?

Vitaliy: The difference is in the frame boundary behavior. Standard lag and lead in PostgreSQL and MySQL do not have a frame concept — they look at the full partition window, all rows within the window regardless of the frame definition. lagInFrame and leadInFrame in ClickHouse respect the frame boundaries. lagInFrame will only consider rows within the defined frame. That’s why the name emphasizes “InFrame.”

Q: Since window functions are computed sequentially after the usual stages, does that mean they will slow down my queries a lot or use a lot more RAM?

Robert: The answer, like a lot of things in ClickHouse, is: it depends. If you have complex window functions with multiple windows, or a very large result set of aggregates, yes, it will slow things down and yes, it will use more resources. My understanding is that memory usage tends to be roughly linear with the result set size. So be careful about how many results you’re passing through window functions. It’s a powerful feature but there’s a cost to computing these things.

Q: How does lagInFrame compare to arrays for performance?

Vitaliy: When we talk about performance, the best answer is always to try it out and measure it. We did mostly functional testing of window functions to validate correctness. Performance testing is the next step. You can expect a blog article from us about that in the near future. Also worth noting: ClickHouse has a neighbor() function that can handle some lag/lead use cases, so that’s another option to benchmark.

Q: Will lagInFrame honor the partition and reset on each partition?

Vitaliy: Yes, that’s correct. lagInFrame honors the partition and resets at each partition boundary.

Robert: Thank you all for these excellent questions. Feel free to contact us directly, or join us on Telegram where our support folks who have worked extensively with window functions are available. I’d also like to thank Vitaliy for all the testing and work he did to validate and document window functions so thoroughly. Thanks everyone, have a great day.

Vitaliy: Thanks everyone, thank you Robert.

FAQ

What are ClickHouse window functions and when were they introduced?

Window functions are a SQL feature that allow a query to compute values that look at rows beyond the current row in the result set, such as ranking, cumulative sums, and rolling averages. In ClickHouse they were first introduced in version 21.3 (early 2021) and were still marked experimental at the time of this webinar. They must be enabled with SET allow_experimental_window_functions = 1 unless a user profile has them enabled by default.

What is the difference between a ROWS frame and a RANGE frame?

A ROWS frame defines its boundaries in terms of physical row positions: ROWS 3 PRECEDING means the three rows immediately before the current row regardless of their values. A RANGE frame defines boundaries in terms of the value of the ORDER BY column: RANGE 3 PRECEDING means all rows where the ORDER BY column value is within 3 units of the current row’s value. RANGE frames involve the concept of current row peers: all rows with the same ORDER BY value as the current row are considered peers and are always included together in the frame.

What are current row peers and why do they matter?

In a RANGE frame, current row peers are all rows that fall into the same ORDER BY sort bucket as the current row — that is, rows with the same ORDER BY value. If there is no ORDER BY clause in the window definition, all rows in the window are peers. This means the frame always includes every row, and aggregate functions like SUM will return the same value for every output row. Understanding this is essential to predicting RANGE frame results.

Why is the empty OVER() clause sum different from what you might expect?

With sum(x) OVER (), the OVER clause is empty, meaning there is one window containing all rows. There is no ORDER BY, so all rows are peers of the current row. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Because all rows are peers, every row’s frame includes all rows in the window, so the sum is the total across all rows — the same value for every output row.

What are the main current limitations of ClickHouse window functions?

At the time of this webinar: RANGE frames only support integer and date/datetime ORDER BY column types (no Nullable variants, no floats, no strings); no interval support in date RANGE frames; no EXCLUDE clause; no group frames; no support for standard lag/lead (replaced by lagInFrame/leadInFrame which respect frame boundaries); window functions cannot be used inside expressions; and RANGE frames are not supported with named windows. Overall 93% of requirements as defined by Altinity testing were satisfied.

How do ClickHouse lagInFrame and leadInFrame differ from standard SQL lag and lead?

Standard lag and lead in PostgreSQL and MySQL do not respect frame boundaries — they always look at the entire partition window. ClickHouse’s lagInFrame and leadInFrame do respect the frame boundaries you define. If you want behavior equivalent to standard lag/lead, there is a workaround documented in the Altinity Knowledge Base.

What is the performance impact of window functions in ClickHouse?

Window functions are computed sequentially after the parallel scan and sequential aggregation stages are complete. Unlike regular aggregation which benefits from parallel CPU processing, window function computation happens on a single thread. For distributed tables, window functions are computed on the initiating node only, meaning all data from shards must be gathered to that node before the window computation can begin. Memory usage scales roughly linearly with the result set size. For large result sets or complex multi-window queries, this can have meaningful performance and memory impact.

© 2021 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.