如何将时间戳分组为孤岛（基于任意间隔）？ [英] How to group timestamps into islands (based on arbitrary gap)?

问题描述

将此日期列表视为 timestamptz ：

我用颜色将日期手动分组：每个组与下一组至少间隔2分钟。

I grouped the dates by hand using colors: every group is separated from the next by a gap of at least 2 minutes.

我试图通过查看用户何时执行动作（数据是他们完成句子学习的时间）来衡量给定用户学习了多少内容，例如：在黄色方块上，我认为使用者一次坐着学习，从14:24到14:27，或连续大约3分钟。

I'm trying to measure how much a given user studied, by looking at when they performed an action (the data is when they finished studying a sentence.) e.g.: on the yellow block, I'd consider the user studied in one sitting, from 14:24 till 14:27, or roughly 3 minutes in a row.

我了解如何通过遍历所有日期并寻找两行之间的间隙来使用编程语言将这些日期分组。

I see how I could group these dates with a programming language by going through all of the dates and looking for the gap between two rows.

我的问题是：如何用Postgres用这种方式对日期进行分组？

My question is: how would go about grouping dates in this way with Postgres?

（在Google或SO上寻找空白会带来太多无关紧要的结果；我想我在这里想做的事情就是词汇。）

(Looking for 'gaps' on Google or SO brings too many irrelevant results; I think I'm missing the vocabulary for what I'm trying to do here.)

推荐答案

此会做到这一点：

SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp
FROM  (
   SELECT done
       , (lag(done) OVER (ORDER BY done) <= done - interval '2 min') AS step
   FROM   tbl
   ) sub
ORDER  BY done;

子查询 sub 记录步骤为 true ，如果上一行至少相距2分钟-按时间戳列完成

The subquery sub records step as true if the previous row is at least 2 min away - sorted by the timestamp column done itself in this case.

外部查询会添加滚动步数，实际上是组编号（ grp ）-将汇总的 FILTER 子句与另一个窗口函数组合在一起。

The outer query adds a rolling count of steps, effectively the group number (grp) - combining the aggregate FILTER clause with another window function.

db<>小提琴这里

相关

• 查询以查找所有间隔超过一定间隔的时间戳记


• 当组归属取决于上一行时，如何在PostgreSQL中标记组？


• 选择最长的连续序列


• 分组或窗口

• Query to find all timestamps more than a certain interval apart
• How to label groups in postgresql when group belonging depends on the preceding line?
• Select longest continuous sequence
• Grouping or Window

关于总计 FILTER 子句：

• 如何简化此游戏统计信息查询？


• 有条件的超前/滞后函数PostgreSQL吗？

• How can I simplify this game statistics query?
• Conditional lead/lag function PostgreSQL?

这篇关于如何将时间戳分组为孤岛（基于任意间隔）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！