title: Clickhouse running diff with grouping
body: <h2>General Task</h2>
  
  <p>A table consists of three columns (<em>time</em>, <em>key</em>, <em>value</em>). The task is to calculate a running difference for each key.
  So, from input</p>
  
  <pre><code>---------------
  | time | key | value |
  ---------------
  | 1    | A   | 4     |
  | 2    | B   | 1     |
  | 3    | A   | 6     |
  | 4    | A   | 7     |
  | 5    | B   | 3     |
  | 6    | B   | 7     |
  </code></pre>
  
  <p>it is desired to get</p>
  
  <pre><code>----------------------
  | key | value | delta |
  ----------------------
  | A   | 4     |  0    |
  | B   | 1     |  0    |
  | A   | 6     |  2    |
  | A   | 7     |  1    |
  | B   | 3     |  2    |
  | B   | 7     |  4    |
  </code></pre>
  
  <h2>Approaches</h2>
  
  <ol>
  <li><p><code>runningDifference</code> function. Works, if the key is fixed. So we can</p>
  
  <pre><code>select *, runningDifference(value) from
      (SELECT key, value from table where key = 'A' order by time)
  </code></pre>
  
  <p>Note that subquery <em>is</em> necessary here. This solution suffers when you want to get this for different <em>key</em>s</p></li>
  <li><p><code>groupArray</code>.</p>
  
  <pre><code>select key, groupArray(value) from
      (SELECT key, value from table order by time)
      group by key
  </code></pre>
  
  <p>So, now we get a key and an array of elements with this key. Good.</p>
  
  <p>But <strong>how to calculate a sliding difference</strong>? If we could do that, then <code>ARRAY JOIN</code> would lead us to a result.</p>
  
  <p>Or we can even <code>zip</code> the array with itself and then apply lambda (we have <code>arrayMap</code> for that) but... we don't have any <code>zip</code> alternative.</p></li>
  </ol>
  
  <p>Any ideas?
  Thanks in advance.</p>