mysqlclickhouseclickhouse-clientclickhouse-go

runningDifference() not working in clickhouse for this serinario

In clickhouse lag/lead functions are not supporting, i used runningDifference(), for this scenario it's not working.

select endtime, runningDifference(endtime) as time_diff from (select toUnixTimestamp(toDateTime('2024-02-21 00:00:00')) endtime, 'Queue' as Event union all select toUnixTimestamp(toDateTime('2024-02-21 00:00:45')) endtime, 'AgentDial' as Event union all select toUnixTimestamp(toDateTime('2024-02-21 00:00:48')) endtime, 'CustDial' as Event) order by endtime;

OUTPUT:-

1708473600 0 1708473645 0 1708473648 0

Solution

  • according to https://clickhouse.com/docs/en/sql-reference/functions/other-functions#runningDifference

    runningDifference Only returns differences inside the currently processed data block.

    You are used UNION ALL in your sub query, it will produce 3 separate data blocks

    You can apply some trick to produce only one data block

    SELECT endtime, runningDifference(endtime) AS time_diff FROM (
  SELECT toUnixTimestamp(toDateTime(dt)) endtime, Event FROM 
  
  format(JSONEachRow,

$$
{"dt": "2024-02-21 00:00:00", "Event": "Queue"}
{"dt": "2024-02-21 00:00:45", "Event": "AgentDial"}
{"dt": "2024-02-21 00:00:48", "Event": "CustDial"}
$$
  
  )
  ORDER BY endtime
);