ClickHouse中runningDifference()函数在指定场景下无法正常工作求助

问题原因 #

runningDifference函数的计算依赖数据的输入顺序，你当前的查询中，外部的ORDER BY是在函数计算完成后才执行的，导致函数计算时数据并未按endtime排序，最终差值全部为0。

解决方案 #

方案1：子查询提前排序

将排序逻辑放到子查询中，确保runningDifference计算时数据已经是有序的：

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
);

方案2：使用窗口函数指定排序

直接在runningDifference后通过OVER (ORDER BY endtime)强制基于排序后的序列计算差值：

select 
    endtime, 
    runningDifference(endtime) OVER (ORDER BY 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;

预期输出 #

执行任意一种方案后，都会得到正确的差值结果：

1708473600  0
1708473645  45
1708473648  3

内容的提问来源于stack exchange，提问作者dundi rajesh