title: Clickhouse: Sliding / moving window
body: <p>I'm looking for an efficient way on how to query the <strong>n</strong> past values as array in ClickHouse for each row ordered by one column (i.e. <code>Time</code>), where the values should be retrieved as array.</p>
  <p>Window functions are still not supported in ClickHouse (see <a href="https://github.com/ClickHouse/ClickHouse/issues/1469" rel="nofollow noreferrer">#1469</a>), so I was hoping for a work-around using aggregation functions like <code>groupArray()</code>?</p>
  <p>Table:</p>
  <pre><code>Time  | Value
  12:11 | 1
  12:12 | 2
  12:13 | 3
  12:14 | 4
  12:15 | 5
  12:16 | 6
  </code></pre>
  <p>Expected result with a window of size <code>n=3</code>:</p>
  <pre><code>Time  | Value
  12:13 | [1,2,3]
  12:14 | [2,3,4]
  12:15 | [3,4,5]
  12:16 | [4,5,6]
  </code></pre>
  <p>What are the ways/functions currently used in ClickHouse to efficiently query a sliding/moving window and how can I achieve my desired result?</p>
  <p><strong>EDIT:</strong></p>
  <p>My solution based on response of @vladimir:</p>
  <pre class="lang-sql prettyprint-override"><code>select max(Time) as Time, groupArray(Value) as Values
  from (
      select
          *,
          rowNumberInAllBlocks() as row_number,
          arrayJoin(range(row_number, row_number + 3)) as window_id
      from (
          /* BEGIN emulate origin dataset */
          select toDateTime(a) as Time, rowNumberInAllBlocks()+1 as Value
          from (
              select arrayJoin([
                  '2020-01-01 12:11:00',
                  '2020-01-01 12:12:00',
                  '2020-01-01 12:13:00',
                  '2020-01-01 12:14:00',
                  '2020-01-01 12:15:00',
                  '2020-01-01 12:16:00']) a
          )
          order by Time
          /* END emulate origin dataset */
      )
      order by Time
  ) s
  group by window_id
  having length(Values) = 3
  order by Time
  </code></pre>
  <p>Note that <code>3</code> appears twice in the query and represents the window size <strong>n</strong>.</p>
  <p>Output:</p>
  <pre><code>┌────────────────Time─┬─Values──┐
  │ 2020-01-01 12:13:00 │ [1,2,3] │
  │ 2020-01-01 12:14:00 │ [2,3,4] │
  │ 2020-01-01 12:15:00 │ [3,4,5] │
  │ 2020-01-01 12:16:00 │ [4,5,6] │
  └─────────────────────┴─────────┘
  </code></pre>