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

问题描述

将此日期列表视为timestamptz:

我使用颜色手动对日期进行分组:每组与下一组之间至少间隔 2 分钟.

我试图通过查看用户执行操作的时间(数据是他们完成学习句子的时间)来衡量给定用户的学习量.例如:在黄色块上，我认为用户已学习从 14 点 24 分到 14 点 27 分一次，或者大约连续 3 分钟.

我知道如何通过查看所有日期并查找两行之间的间隔，使用编程语言对这些日期进行分组.

我的问题是:如何使用 Postgres 以这种方式对日期进行分组?

(在 Google 或 SO 上寻找差距"会带来太多不相关的结果；我想我错过了我在这里尝试做的词汇.)

解决方案

这样就可以了:

SELECT done, count(*) FILTER (WHERE step) OVER (ORDER BY done) AS grp从  (选择完成, (lag(done) OVER (ORDER BY done) <= done - interval '2 min') AS step来自 tbl) 子ORDER BY done;

如果前一行距离至少 2 分钟，则子查询 sub 将 step 记录为 true - 按时间戳列 排序在这种情况下完成本身.

外部查询添加了滚动步数，实际上是组号 (grp) - 将聚合 FILTER 子句与另一个窗口函数结合起来.

db<>小提琴这里p>

相关:

• 查询以查找相隔超过某个时间间隔的所有时间戳
• 当组归属取决于前一行时，如何在postgresql中标记组?
• 选择最长的连续序列
• 分组或窗口

关于聚合FILTER子句:

• 如何简化此游戏统计查询?
• 条件超前/滞后函数PostgreSQL?

Consider this list of dates as timestamptz:

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

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.

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

(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.)

解决方案

This would do it:

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;

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.

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

db<>fiddle here

Related:

• 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

About the aggregate FILTER clause:

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

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