计算递归函数每次被调用所花费的时间..在SQL中

Question

我有如下的数据

id | Function_name | event | time
1     function_a     start   12:01
2     function_a     start   12:02
3     function_a     start   12:03
4     function_a     end     12:04
5     function_a     start   12:05
6     function_a     end     12:06
7     function_a     end     12:07
8     function_a     end     12:08

在上述数据中，函数_a被递归调用4次，并记录函数的开始和结束。我需要记录每个函数调用所花费的时间。上表的输出应如下所示。

Function_call_id | time_taken_for_function_call(seconds)
1                   7
2                   5
3                   1
4                   1

Answer 1

您可以枚举调用，然后进行聚合。这列举了的启动和停止

select t.*,
(next_time - time) as diff
from (select t.*, lead(time) over (partition by grp order by id) as next_time
from (select t.*,
(sum(case when event = 'start' then 1 else -1 end) over (order by id) +
(case when event = 'end' then 1 else 0 end)
) as grp
from t
) t
) t
where event = 'start';