sql - 从最小值开始分组为 5 秒的间隔

Question

我试图找出一个 postgresql 代码，以从每个最小时间戳开始的时间间隔(例如 5 秒)内将数据表分组。

CREATE TABLE foo AS SELECT
  timestamp::timestamp with time zone,
  name::text
FROM ( VALUES
  ('2010-11-16 10:32:22', 'John'),
  ('2010-11-16 10:32:23', 'John'),
  ('2010-11-16 10:32:25', 'John'),
  ('2010-11-16 10:32:27', 'John'),
  ('2010-11-16 10:32:27', 'John'),
  ('2010-11-16 10:32:29', 'John'),
  ('2010-11-16 10:37:45', 'John'),
  ('2010-11-16 10:37:45', 'John'),
  ('2010-11-16 10:37:46', 'John'),
  ('2010-11-16 10:38:08', 'John')
) AS t(timestamp, name);

来自测试数据，

timestamp            name  
-------------------  ----  
2010-11-16 10:32:22  John  
2010-11-16 10:32:23  John 
2010-11-16 10:32:25  John  
2010-11-16 10:32:27  John  
2010-11-16 10:32:27  John 
2010-11-16 10:32:29  John  
2010-11-16 10:37:45  John  
2010-11-16 10:37:45  John  
2010-11-16 10:37:46  John  
2010-11-16 10:38:08  John  

期望的结果应该是这样的:

timestamp            name  
-------------------  ----
2010-11-16 10:32:22  John  
2010-11-16 10:32:27  John  
2010-11-16 10:37:45  John 
2010-11-16 10:38:08  John  

注意:间隔基于时间戳的第一次出现，而不是讨论的一般间隔 here

Answer 1

这就是你想要的。

首先我们计算一个groupid。

1. timestamp-min(timestamp) OVER ():这是一个计算间隔的窗口函数——当前时间和最小时间戳之间的持续时间。
2. extract(epoch from INTERVAL):然后我们以秒为单位提取该间隔。
3. floor( SECONDS/5):除以 5 秒，向下取整

这是那个查询，

SELECT floor(extract(epoch from (timestamp-min(timestamp) OVER ()))/5) AS groupid
  , *
FROM foo

然后我们将其包装为一个子查询，并从按时间戳降序排列的每个组中选择一个不同的行。

WITH t AS (
  SELECT 
    floor(extract(epoch from timestamp-min(timestamp) OVER ()) /5) AS groupid, *
  FROM foo
)
SELECT DISTINCT ON (groupid) timestamp, name
FROM t
ORDER BY groupid, timestamp;

请注意，我们不会在任何地方执行 GROUP BY。这是因为您要返回整行。没有必要。

警告

正如@ypercube(TM) 指出的那样，

• 此解决方案完全按照您的要求“注意:时间间隔基于时间戳的第一次出现，而不是此处讨论的一般时间间隔”

例如，如果您将 10:37:45 更改为 10:37:41，您将看到 10:37:41 与 10:37:45 在不同的组中。