按组计算时间戳中的间隔

Question

我有一个带有我的Id的表，"等级；发送的订单消息以及messagesend_time。

ID      Rank          message_send_time      
1         1          2022-01-01 00:33:04              
1         2          2022-01-01 00:34:04            
2         1          2022-01-01 00:30:04        
2         2          2022-01-01 00:32:04            
2         3          2022-01-01 00:33:04            

我想计算我的组Id和消息的排名之间的分钟间隔，我如何在SQL中计算？

ID      Rank          message_send_time       Interval_time_minutes
1         1          2022-01-01 00:33:04              
1         2          2022-01-01 00:34:04            1
2         1          2022-01-01 00:30:04        
2         2          2022-01-01 00:32:04            2
2         3          2022-01-01 00:33:04            1

Answer 1

您可以尝试将lag窗口函数与TIMESTAMPDIFF一起使用

查询#1

select
id, 
`Rank`,
message_send_time,
TIMESTAMPDIFF(MINUTE,lag(message_send_time,1) over (partition by id order by `Rank`),message_send_time) Interval_time_minutes
from T;

message_send_time<1><1>

id	排名	Interval_time_minutes
1	1	2022-01-01 00:33:04
1	2	2022-01-01 00:34:04
2	1	2022-01-01 00:30:04
2	2	2022-01-01 00:32:04		2
2	3	2022-01-01 00:33:04

我们可以使用函数datediff()和lag()

create table messages(
ID int,
message_send_time timestamp);
insert into messages values
(1,' 2022-01-01 00:33:04'),
(1,' 2022-01-01 00:34:04'),
(2,' 2022-01-01 00:30:04'),
(2,' 2022-01-01 00:32:04'), 
(2,' 2022-01-01 00:33:04');

select
id, 
rank() over(partition by id order by message_send_time) "Rank",
message_send_time,
timediff(
message_send_time,
lag(message_send_time,1) over (partition by id order by message_send_time) 
) as "Interval"
from messages;

id|Rank|message_send_time|Interval-：|---：|：------------------|：-------1|1|2022-01-01 00:33:04|空1|2|2022-01:01 00:34:04|00:01:002|1|2022-01-01 00:30:04|空2|2|2022-01:01 00:32:04|00:02:002|3|2022-01-01 00:33:04|00:01:00

db<gt；小提琴这里

select
id 
, `Rank`
, message_send_time,
TIMESTAMPDIFF(
MINUTE
, LAG(message_send_time,1) over (
partition by id order by `Rank`
)
, message_send_time
) Interval_time_minutes
from T;