如何在Presto SQL中根据心跳数据计算用户会话时间

Question

我当前正在记录用户通过心跳活动的时间。它存储在一个这样的表中：

一天中的分钟数

用户ID
1	3
1	4
1	5
1	8
1	9
2	2
2	3
2	4

您可以尝试以下操作，该操作使用滞后函数来确定活动周期(diff = 1(，然后再将它们相加

SELECT 
USERID, 
SUM(diff) as TotalMinutes
FROM (
SELECT
UserId,
(MinuteofDay - LAG(MinuteofDay,1,MinuteofDay) OVER (PARTITION BY UserId ORDER BY MinuteofDay)) as diff
FROM
my_table
) t
WHERE 
diff = 1
GROUP BY 
UserID;

userid	总分钟数
1	3
2	2