Window functions

Resources:

How Do I Simulate Window Functions Using Arrays on older versions of clickhouse?

  1. Group with groupArray.
  2. Calculate the needed metrics.
  3. Ungroup back using arrayJoin.

NTILE

SELECT intDiv((num - 1) - (cnt % 3), 3) AS ntile
FROM
(
    SELECT
        row_number() OVER (ORDER BY number ASC) AS num,
        count() OVER () AS cnt
    FROM numbers(11)
)

┌─ntile─┐
│     0 │
│     0 │
│     0 │
│     0 │
│     0 │
│     1 │
│     1 │
│     1 │
│     2 │
│     2 │
│     2 │
└───────┘
Last modified 2024.03.11: Updated links to various external resources (64e8374)