按浮动日期范围分组

Question

我使用的是PostgreSQL 9.2。
我有一个包含设备停止服务时间的表格。

+----------+----------+---------------------+
| event_id |  device  |         time        |
+----------+----------+---------------------+
|        1 | Switch4  | 2013-09-01 00:01:00 |
|        2 | Switch1  | 2013-09-01 00:02:30 |
|        3 | Switch10 | 2013-09-01 00:02:40 |
|        4 | Switch51 | 2013-09-01 03:05:00 |
|        5 | Switch49 | 2013-09-02 13:00:00 |
|        6 | Switch28 | 2013-09-02 13:01:00 |
|        7 | Switch9  | 2013-09-02 13:02:00 |
+----------+----------+---------------------+

我希望将行按照+/-3分钟的时间差分组，如下所示：

+----------+----------+---------------------+--------+
| event_id |  device  |         time        |  group |
+----------+----------+---------------------+--------+
|        1 | Switch4  | 2013-09-01 00:01:00 |      1 |
|        2 | Switch1  | 2013-09-01 00:02:30 |      1 |
|        3 | Switch10 | 2013-09-01 00:02:40 |      1 |
|        4 | Switch51 | 2013-09-01 03:05:00 |      2 |
|        5 | Switch49 | 2013-09-02 13:00:00 |      3 |
|        6 | Switch28 | 2013-09-02 13:01:00 |      3 |
|        7 | Switch9  | 2013-09-02 13:02:00 |      3 |
+----------+----------+---------------------+--------+

我尝试使用窗口函数来实现，但是在子句中出现了问题。

[ RANGE | ROWS ] BETWEEN frame_start AND frame_end， 其中frame_start和frame_end可以是以下之一： UNBOUNDED PRECEDING、value PRECEDING、CURRENT ROW、value FOLLOWING、UNBOUNDED FOLLOWING。 value必须是一个整数表达式，不包含任何变量、聚合函数或窗口函数。

考虑到这一点，我无法指示时间间隔。现在我怀疑窗口函数是否能够解决我的问题。你能帮我吗？

- Sergey Bulavkin

---

为了澄清问题，如果在上面的例子中有一个时间为“00:03:40”的行，它会有第2组号码，并且其他组会向后移动1个吗？我试图理解您所说的+/-3分钟差异是什么意思。 - Roman Pekar

Answer 1

SQL Fiddle

select
    event_id, device, ts,
    floor(extract(epoch from ts) / 180) as group
from t
order by ts

使用窗口函数可以将组号设置为从1开始的序列，但这可能会带来不小的成本，我不确定是否有必要。就是这样。

select
    event_id, device, ts,
    dense_rank() over(order by "group") as group
from (
    select
        event_id, device, ts,
        floor(extract(epoch from ts) / 180) as group
    from t
) s
order by ts

time 是一个保留关键字。请选用另一个列名。

- Clodoaldo Neto

---

简单明了。我建议稍作改进。 - Erwin Brandstetter

Answer 2

{{链接1:SQLFiddle}}

with u as (
select 
   *,
   extract(epoch from ts - lag(ts) over(order by ts))/ 60 > 180 or lag(ts) over(order by ts) is null as test
from
   t
   )

   select *, sum(test::int) over(order by ts) from u

- Tomas Greif

Answer 3

这只是对@Clodoaldo基本正确答案的轻微改进。

获取连续组号：

SELECT event_id, device, ts
     , dense_rank() OVER (ORDER BY trunc(extract(epoch from ts) / 180)) AS grp
FROM   tbl
ORDER  BY ts;

• 使用ts代替(部分)保留字time是一个好建议。所以也不要使用保留字group，而是使用grp。

• 可以在没有子查询的情况下获得连续的数字。

• 使用trunc()代替floor()。两者都很好，trunc() 稍微快一些。

- Erwin Brandstetter

Answer 4

http://www.depesz.com/2010/09/12/how-to-group-messages-into-chats/

应该使用窗口化技术。这是来自教科书的一个例子。

with
  xinterval( val ) as ( select 2 ),
  data( id, t ) as 
  (
    values  

      ( 1000, 1 ),
      ( 1001, 2 ),
      ( 1002, 3 ),

      ( 1000, 7 ),
      ( 1003, 8 )

  ),  
  x( id, t, tx ) as
  (
    select id, t,
      case (t - lag(t) over (order by t)) > xinterval.val
        when true then t when null then t
      end
    from data natural join xinterval
  ),
  xx( id, t, t2 ) as
  (
    select id, t, max(tx) over (order by t) from x
  )
select id, t, text( min(t) over w ) || '-' || text( max(t) over w ) as xperiod
from xx
window w as ( partition by t2 )
order by t

- misha

---

如果某天链接失效，可以添加某些链接的部分。 - surfmuggle

Answer 5

创建函数

CREATE OR REPLACE FUNCTION public.date_round (
  base_date timestamp,
  round_interval interval
)
RETURNS TIMESTAMP WITHOUT TIME ZONE AS
$body$
DECLARE
   res TIMESTAMP;
BEGIN   
    res := TIMESTAMP 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2)
                / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second';            
    IF (base_date > res ) THEN
        res := res + $2;
    END IF;
    RETURN res;
END;
$body$
LANGUAGE 'plpgsql'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

并按此函数结果分组

SELECT t.* FROM (SELECT p.oper_date, date_round(p.oper_date, '5 minutes') as grp FROM test p) t GROUP BY t.grp

这很简单 :)

- khuawei