Mar 16, 2021

Coming soon on ClickHouse: Window functions

An exploration on what's possible to do with the most recent experimental feature on ClickHouse - window functions, and an overview of other interesting features to come.
Xoel L贸pez
Founder at TheirStack

Window functions have been a very requested feature for ClickHouse for years. They exist in other databases like Postgres and let you perform calculations across a set of table rows that are somehow related to the current row. They behave similar to regular aggregate functions but, with window functions, rows don鈥檛 get grouped into a single output row.

That enables you to write simpler queries and to do certain things with window functions that you couldn鈥檛 do otherwise. Also, you can use the same syntax that you鈥檙e already used to from other databases like Postgres or MySQL.

Lots of work has gone into adding window functions to ClickHouse in the last few months, and they鈥檙e now available as an experimental feature (docs).

In this post we鈥檒l explore a bit what you can do with them.

Window functions

Let鈥檚 load some data first. We鈥檒l use a sample of the events dataset we used for our getting started guides. Download it from here or running curl -o events_10K.csv.

Then create a table to store it like this:

And load the data onto the table you鈥檝e just create doing ch client -q 'insert into events format CSV' < events_10K.csv.

This is what a sample of the data looks like

Now we鈥檙e ready to make some queries. These are some of the things that window functions enable you to do.

Note: This is an experimental feature. To be able to use it, you鈥檒l have to run the following command in your ClickHouse client console before you use any window function:

Now we鈥檙e ready to go.

Cumulative sums, averages and other aggregate functions

Until now, the only way to do cumulative sums and averages was using groupArray, arrayJoin, groupArrayMovingSum and groupArrayMovingAvg functions, like described here.

For example, this is how you鈥檇 go about getting the daily and cumulative count of how many buy events happened since we started recording data, and the daily and cumulative revenue as well.

WINDOW clause

In the previous query we repeated twice the over (order by date asc rows unbounded preceding) expression. That鈥檚 error prone, and to avoid that we can define the window just once and reuse it as many times as we want in our query.

The syntax is the same as for Postgres (docs)

The previous query would be rewritten like this, using the window clause:

Moving counts and averages

If we wanted to calculate rolling aggregate functions for only the last (or next) N rows, that鈥檚 also possible. For example, this is how you鈥檇 calculate the 30-day moving average of revenue, and the sum of revenue for the past 30 days.


The PARTITION BY clause divides rows into multiple groups or partitions to which the window function is applied. So if you wanted to calculate the cumulative revenue per month, since the beginning of each year, you鈥檇 do it like this:

RANGE clause

It鈥檚 possible to define the window boundaries by a given value difference, instead of rows. If the column used for the window is a DateTime column, the value will be measured in seconds. This is how you鈥檇 calculate the total number of purchases and the revenue for the past hour, for every buy event:


These functions are also supported, and they behave the same way as in Postgres. This is how you鈥檇 be the top purchases of each day, ranked by the money spent:

As of the time of writing this, there seems to be some issues related to sorting when a window with a partition by expression is used. Update (2021-03-22): this is now fixed.

As a side note, a similar result could be obtained with the LIMIT BY clause.

What else?

There are many other promising features coming up. On the ClickHouse GitHub repository, there is a ticket with all the features that are in the roadmap for 2021 and their current status. Here are some additional ones we鈥檙e quite excited about:

Nested and semistructured data

Right now, ClickHouse already has a Nested data type that lets you have columns that store more than one field inside, with a single nesting level.

There鈥檚 work being done to allow an arbitrary nesting level. They鈥檇 eliminate the need to save JSON columns as strings, allow different column representations to store the same DataType, dynamic columns and much more

Separation of storage and compute

To allow, for example, to store replicas of the data on S3. Follow the progress on this PR

A PostgreSQL table engine

An experimental MySQL table engine already exists, that lets you run queries over data stored originally on a MySQL database. A PostgreSQL engine is also in the works


Projections will let you do some simple aggregations with the performance of a Materialized View, but without having to create a MV. They鈥檒l be defined at the table level. Read a much more detailed explanation with examples in this issue


Having the possibility to import and export data and metadata to S3, a local filesystem or another ClickHouse server. Allowing incremental backups, and much more. See all the details in this ticket

One more thing

Tinybird lets you define dynamic endpoints to do real-time analytics at scale on top of ClickHouse. If you鈥檇 like to use our product, sign up here

Do you like this post?

Related posts

ClickHouse Tips #12: Apply Functions to Columns with a Single Call
Sep 29, 2021
ClickHouse Tips #4: Materializing UNION ALL queries
Experimental ClickHouse: Projections
ClickHouse tips #6: Filtering data in subqueries to avoid joins
ClickHouse tips #11: Best way to get query types
Sep 22, 2021
We launched an open source ClickHouse Knowledge Base
Oct 11, 2022
ClickHouse tips #3: the transform function
ClickHouse tips #8: Generating time-series on ClickHouse
Investigating Performance Bottlenecks With SQL & Statistics
ClickHouse Meetup Madrid videos

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.