Window functions

Link blog.tinybird.co/2021/03/16/c…
Date Mar 26, 2021

Windows Function Slides

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 
└───────┘
Last modified 2021.11.08: Update window-functions.md (b3af31b34)