Window functions

blog.tinybird.co/2021/03/16/c…

An exploration on what’s possible to do with the most recent experimental feature on ClickHouse - window functions, and an overview of other interesting feat…

Windows Functions Blog Link

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 │
└───────┘