如何獲得分組時間戳的最小值和最大值

我很想知道如何創建一個視圖，該視圖以 10 分鐘最接近 10 分鐘的間隔對時間戳進行分組，並包含每個時間戳的最小和最大時間戳。

所以一個看起來像這樣的表：

| Hero         | timestamp           |

| Batman       | 2016-12-08 12:00:00 |
| Batman       | 2016-12-08 12:07:00 |
| Batman       | 2016-12-08 13:00:00 |
| Batman       | 2016-12-08 14:00:00 |
| Wonder Woman | 2016-12-08 10:15:00 |
| Wonder Woman | 2016-12-08 10:18:00 |
| Wonder Woman | 2016-12-08 10:25:00 |
| Wonder Woman | 2016-12-08 10:30:00 |

會導致這樣的視圖

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00 |

或這個：

| Hero         | start_time          | end_time            |

| Batman       | 2016-12-08 13:00:00 | NULL                |
| Batman       | 2016-12-08 14:00:00 | NULL                |
| Batman       | 2016-12-08 12:00:00 | 2016-12-08 12:07:00 |
| Wonder Woman | 2016-12-08 10:15:00 | 2016-12-08 10:30:00|

任何一種解決方案都可以。

10 分鐘間隔的柵格

我建議按“小時”和 10 分鐘間隔的組合進行分組：

SELECT hero
    , min(timestamp) AS start_time
    , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM   tbl
GROUP  BY hero
    , date_trunc('hour', timestamp)
    , EXTRACT(MINUTE FROM timestamp)::int / 10
ORDER  BY 1, 2;  -- optional

請參閱手冊中的日期/時間功能和運算符一章。

EXTRACT(minute FROM timestamp)提取時間的分鐘部分 表達式。轉換為整數 ( ::int) 後，整數除法 ( / 10) 有效地舍入到 10 分鐘間隔 ( 0- 5)。

該CASE表達式僅添加一個end_timeif 多個行落在同一 10 分鐘間隔內。

我建議不要使用“時間戳”作為標識符。它是標準 SQL 中的保留字和 Postgres 中的基本數據類型。

由 10 分鐘或更長時間的間隔定義的組

如果“組”由同一英雄的行之間的 10 分鐘或更長時間的間隔定義：

SELECT hero
    , count(*) AS ct  -- optional
    , min(timestamp) AS start_time
    , CASE WHEN count(*) > 1 THEN max(timestamp) END AS end_time
FROM  (
  SELECT hero, timestamp, count(step OR NULL) OVER (ORDER BY hero, timestamp) AS grp
  FROM  (
     SELECT *
          , lag(timestamp) OVER (PARTITION BY hero ORDER BY timestamp)
          < timestamp - interval '10 min' AS step
     FROM   tbl
     ) sub1
  ) sub2
GROUP  BY hero, grp;

詳細解釋：

• 選擇最長的連續序列

db<>fiddle here

舊 sqlfiddle

引用自：https://dba.stackexchange.com/questions/157595