Window functions
Link | blog.tinybird.co/2021/03/16/c… |
---|---|
Date | Mar 26, 2021 |
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…
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 │
└───────┘
Last modified 2021.11.08: Update window-functions.md (a527d5f)