Window functions
Resources:
- Tutorial: ClickHouse® Window Functions
- Video: Fun with ClickHouse Window Functions
- Blog: Battle of the Views: ClickHouse Window View vs. Live View
How Do I Simulate Window Functions Using Arrays on older versions of ClickHouse?
- Group with groupArray.
- Calculate the needed metrics.
- 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 │
└───────┘