雪花云数据平台-基于时间戳和状态变化计算每个用户的处理时间

Question

我有一个表

user_id | status  | timestamp
null    | null    | 2022-05-05 01:01:00
null    | null    | 2022-05-05 01:02:00
7461    | null    | 2022-05-05 01:03:00
null    | open    | 2022-05-05 01:04:00
null    | null    | 2022-05-05 01:06:00
null    | on hold | 2022-05-05 01:09:00
8474    | null    | 2022-05-05 01:10:00
null    | null    | 2022-05-05 01:11:00
null    | solved  | 2022-05-05 01:12:00
2638    | null    | 2022-05-05 01:13:00
null    | null    | 2022-05-05 01:16:00
5721    | null    | 2022-05-05 01:17:00

我需要找到每个用户的处理时间，即从分配user_id和第一次状态更改的时间差。另外，如果在分配下一个用户之前没有状态变化，那么我需要找到从分配第一个user_id到分配下一个user_id的时间差。

我要找的输出是

user_id | timestamp
7461    | 00:01:00
8474    | 00:02:00
2638    | 00:04:00

我已经做了几个小时了，但我似乎不能得到正确的结果。我知道CTE会起作用，但我不知道如何正确地设置条件。如有任何帮助，不胜感激。

这是我尝试过的，它在MySQL中工作。但是我不能在这种情况下使用MySQL。

SELECT t1.user_id,
MIN(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, t1.timestamp, t2.timestamp))) AS timestamp
FROM tab t1
INNER JOIN tab t2
ON t1.timestamp < t2.timestamp
AND t1.user_id IS NOT NULL
AND t2.status IS NOT NULL 
GROUP BY t1.user_id

Answer 1

使用APPLY算子查找下一个timestamp并查找datediff()。将所需结果转换为time数据类型

select t.user_id, 
convert(time(0), dateadd(second, datediff(second, t.timestamp, n.timestamp), 0))
from   tab t
cross apply
(
select timestamp = min(x.timestamp)
from   tab x
where  x.timestamp > t.timestamp
and    (
x.status  <> t.status
or   (x.status is null and t.status is not null)
or   (x.status is not null and t.status is null)
or   x.user_id <> t.user_id
)
) n
where  t.user_id   is not null
and    n.timestamp is not null

注意:以上查询是针对SQL Server