如何计算SQL中连续时间戳的差异

Question

所以我有一些数据看起来像这样：

USER_ID      text_field                   TIMESTAMP 
1  0000001          dragon     2021-06-04 21:23:30.612      
2  0000001          dragon     2021-06-04 21:23:55.411
3  0000001          dragon     2021-06-04 21:27:35.418
4  0000002         pitbull     2021-06-04 19:17:53.211
5  0000002          dragon     2021-06-04 19:47:53.211
6  0000002          dragon     2021-06-04 19:57:53.211
7  0000003          dragon     2021-06-05 10:00:53.211
8  0000003          dragon     2021-06-05 10:02:53.211
9  0000003           earth     2021-06-05 10:04:53.211
10 0000003          dragon     2021-06-05 10:06:53.211
11 0000003          dragon     2021-06-05 10:08:53.211

Amd我想看到的是以单词dragon为特征的消费条目的第一个和最后一个时间戳之间的差异

所以在ID0000001中，我们所有的条目都是dragon，所以在这种情况下，我想要第3行减去第1行。即所有以dragon和ID=0000001为特征的条目的最大值(时间戳(-最小值(时间戳记(。

对于ID 2，请注意，并不是所有的条目都是dragon，但我只希望dragon条目有同样的差异。

ID也有可能具有非consequativedragon条目，如ID 0000003中的条目。在这里，我需要两个块中的消费条目的区别这意味着对于ID 0000003，我需要row 8 - row 7和row 11 - row 10。

最终输出：

USER_ID  time_diff
0000001       (2021-06-04 21:27:35.418  - 2021-06-04 21:23:30.612)
0000002       (2021-06-04 19:57:53.211  - 2021-06-04 19:47:53.211)
0000003       (2021-06-05 10:02:53.211  - 2021-06-05 10:00:53.211)
0000003       (2021-06-05 10:08:53.211  - 2021-06-05 10:06:53.211)

很明显，这些只是time_diff的方程，我真的想在这一列中得到差异。

Answer 1

如果您特别关心"龙；仅限s，您可以使用每行非龙的累积和来识别每组。然后聚合：

select userid, min(timestamp), max(timestamp)
from (select t.*,
sum(case when text_field <> 'dragon' then 1 else 0 end) over (partition by user_id order by timestamp) as grp
from t
) t
where text_field = 'dragon'
group by userid, grp;

你可以随心所欲地计算差额。

Answer 2

以下是如何做到这一点：

select user_id , min(TIMESTAMP) , max(TIMESTAMP), datediff(second,max(TIMESTAMP),min(TIMESTAMP)) time_diff
from (
select *
, rank() over (partition by user_id order by TIMESTAMP)
-rank() over (partition by user_id,text_field order by TIMESTAMP) rn
from users
) t
where text_field = 'dragon'
group by user_id , rn 
order by user_id