Window Functions

GROUP BY collapses rows into one row per group. Sometimes you want the aggregate alongside every original row instead — a running total next to each transaction, a rank next to each score. That's what window functions are for.

The shape of a window function

SELECT
  user_id,
  event_time,
  amount,
  sum(amount) OVER (
      PARTITION BY user_id
      ORDER BY event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM payments;

PARTITION BY groups rows the same way GROUP BY would, but every row in the group is kept. ORDER BY inside OVER (...) defines the order the window function walks rows in within each partition — it has nothing to do with the table's ORDER BY (the primary key). The frame clause, ROWS BETWEEN ... AND ..., controls exactly which rows around the current one are included in the calculation; omitting it defaults to the whole partition for most aggregate functions used this way.

Common functions

• row_number() — a unique, gapless sequence per partition.
• rank() / dense_rank() — ranking with (rank) or without (dense_rank) gaps after ties.
• lag(col, n) / lead(col, n) — the value of a column n rows before/after the current one in the ordered partition — useful for period-over-period comparisons.
• sum, avg, min, max used with OVER (...) instead of GROUP BY.

SELECT
  user_id,
  total_spend,
  rank() OVER (ORDER BY total_spend DESC) AS spend_rank
FROM user_totals;

SAMPLE: trading accuracy for speed

Separately from window functions, but in the same spirit of "SQL features that behave differently here": the SAMPLE clause lets a query run against a deterministic fraction of a table's rows instead of all of them, for fast approximate answers on huge tables during exploration.

CREATE TABLE events
(
  user_id UInt64,
  event   String
)
ENGINE = MergeTree
ORDER BY user_id
SAMPLE BY cityHash64(user_id);

SELECT count() * 10 AS approx_count
FROM events
SAMPLE 0.1;

SAMPLE only works on a table that declares a SAMPLE BY expression (usually a hash of some column, so the sampling is deterministic and evenly distributed rather than arbitrary). SAMPLE 0.1 reads roughly 10% of the data; results for aggregates need to be scaled back up manually, as shown above — ClickHouse doesn't do that scaling for you.