ClickHouse Window Functions: Analytics

Tip: Running Total

SELECT date, revenue,
    sum(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;

Gotcha: PARTITION BY

ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC)

Resets numbering per category.

Tip: Moving Average

SELECT date, value,
    avg(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
FROM metrics;

Gotcha: Frame Specification

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Default frame. Explicit specification for clarity.

Tip: RANK and DENSE_RANK

SELECT name, score,
    RANK() OVER (ORDER BY score DESC) as rank
FROM players;

Gotcha: Window Functions Performance

Window functions require sorting. Large datasets may be slow.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

Window functions in ClickHouse are powerful but syntactically different from PostgreSQL. The main difference: ClickHouse supports WINDOW clause definition for reusing window specifications. I use rank() and row_number() for top-N queries, lagInFrame() for time-series comparisons, and sum() with ROWS BETWEEN for running totals. The performance of window functions in ClickHouse is excellent — they process the data in a single pass, not a subquery.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)