sql - 如何按非唯一值对以下行进行分组

Question

我有这样的数据:

table1
_____________
id way time
1  1   00:01
2  1   00:02
3  2   00:03
4  2   00:04
5  2   00:05
6  3   00:06
7  3   00:07
8  1   00:08
9  1   00:09

我想知道我在哪条路上的时间间隔:

desired output
_________________
id  way from   to    
1   1   00:01  00:02
3   2   00:03  00:05
6   3   00:06  00:07
8   1   00:08  00:09

我尝试使用窗口函数:

SELECT DISTINCT
  first_value(id) OVER w AS id, 
  first_value(way) OVER w as way,
  first_value(time) OVER w as from,
  last_value(time) OVER w as to
FROM table1
WINDOW w AS (
  PARTITION BY way ORDER BY ID
  range between unbounded preceding and unbounded following);

我得到的是:

ID  way from   to    
 1   1  00:01  00:09
 3   2  00:03  00:05
 6   3  00:06  00:07

这是不正确的，因为在第 1 条路上我不是从 00:01 到 00:09。是否有可能按照顺序进行分区，意味着只对以下属性进行分组，这些属性是否相等？

Answer 1

如果您的情况像示例值所建议的那样简单，@Giorgos' answer服务很好。

但是，通常情况并非如此。如果 id 列是 serial，则不能依赖于 time 较早的行也具有较小 id 的假设。
此外，time 值(或您可能拥有的 timestamp)很容易重复，您需要使排序顺序明确。

假设两者都可能发生，并且您想要每个时间片中具有最早 time 的行中的 id(实际上，最小 id 最早时间，可能有关系)，这个查询将正确处理这种情况:

SELECT *
FROM  (
   SELECT DISTINCT ON (way, grp)
          id, way, time AS time_from
        , max(time) OVER (PARTITION BY way, grp) AS time_to
   FROM (
      SELECT *
           , row_number() OVER (ORDER BY time, id)  -- id as tie breaker
           - row_number() OVER (PARTITION BY way ORDER BY time, id) AS grp
      FROM   table1
      ) t
   ORDER  BY way, grp, time, id
   ) sub
ORDER  BY time_from, id;

• ORDER BY time, id 是明确的。假设时间不是唯一的，添加(假定唯一的)id 以避免任意结果 - 这可能会以偷偷摸摸的方式在查询之间改变。

• max(time) OVER (PARTITION BY way, grp):没有ORDER BY，窗口框架跨越PARTITION的所有行，所以我们得到每个时间片的绝对最大值。

• 外部查询层只需要在结果中生成所需的排序顺序，因为我们在子查询 sub 中绑定(bind)到不同的 ORDER BY通过使用 DISTINCT ON。详情:

  • Select first row in each GROUP BY group?

SQL Fiddle演示用例。

如果您希望优化性能，在这种情况下 plpgsql 函数可能会更快。密切相关的答案:

• Group by repeating attribute

另外:不要使用基本类型名称 time 作为标识符(也是 reserved word in standard SQL )。