我有一个有数百万条记录的表，其中每10分钟精确分组

**t1; dr：**对于不耐烦的人，请看答案中的最后一个查询，这是真实的解决方案，其他的都是一步一步的如何到达那里。另外，所有的查询+模式都可以在SQLFiddle上找到，对于那些想玩的人来说。

在我看来，解决这个问题的最好方法是将每个时间戳截断为10分钟的开始，例如，让我们尝试进行以下转换（original -> 10 minutes truncated）：

13:10 -> 13:10
13:15 -> 13:10
13:18 -> 13:10
13:20 -> 13:20
...

字符串
如果有人想尝试以下查询，可以将模式创建为：

CREATE TABLE your_table(tscol timestamptz);
INSERT INTO your_table VALUES
('2011/01/03 19:18:00.300'),
('2011/01/03 19:18:00.503'),
('2011/01/03 19:20:26.335'),
('2011/01/03 19:29:54.289'),
('2011/01/04 14:43:43.067'),
('2011/01/04 14:50:10.727'),
('2011/01/04 14:52:26.827'),
('2011/01/04 14:57:55.608'),
('2011/01/04 14:57:55.718'),
('2011/01/04 14:59:13.603'),
('2011/01/04 15:00:34.260'),
('2011/01/04 15:02:55.687'),
('2011/01/04 15:07:15.378');

型
因此，为了做到这一点，我们需要理解date_trunc和date_part函数（后者可以由标准EXTRACT调用）和interval data type。让我们一步一步地构建解决方案，最终的想法是有这样的东西（现在是伪代码）：

SELECT truncate_the_time_by_10_minutes(tscol) AS trunc10, count(*)
FROM your_table
GROUP BY trunc10
ORDER BY trunc10;

型
现在，如果问题是“按分钟聚合”，那么我们可以简单地将时间戳截断为分钟，这意味着将秒和微秒归零，这正是date_trunc('minute', ...)所做的，所以：

SELECT date_trunc('minute', tscol) AS trunc_minute, count(*)
FROM your_table
GROUP BY trunc_minute
ORDER BY trunc_minute;

型
工作，但这不是你想要的，date_trun的下一个功能是'hour'，这已经失去了我们需要的信息，所以我们需要在'minute'和'hour'之间的东西。让我们通过一些例子来看看上面的查询是如何工作的：

SELECT tscol, date_trunc('minute', tscol) AS trunc_minute
FROM your_table
ORDER BY tscol;

型
它返回：

tscol            |      trunc_minute      
----------------------------+------------------------
 2011-01-03 19:18:00.3-02   | 2011-01-03 19:18:00-02
 2011-01-03 19:18:00.503-02 | 2011-01-03 19:18:00-02
 2011-01-03 19:20:26.335-02 | 2011-01-03 19:20:00-02
 2011-01-03 19:29:54.289-02 | 2011-01-03 19:29:00-02
...

型
如果你看到2011-01-03 19:18:00-02，现在我们只需要减去8分钟，我们可以：
1.EXTRACT(MINUTE FROM tscol)将返回18
1.因为我们想截短10分钟，让我们取18 and 10的模，所以**18 % 10给予我们8**
1.现在，我们有8分钟，我们要减去，但作为一个整数，从timestamp[tz]减去，我们需要interval，因为整数代表分钟，我们可以这样做：8 * interval '1 minute'，这将给予我们**00:08:00**
在最后一个查询中得到上面的3个步骤，我们有（我将显示每一列以更好地理解）：

SELECT
    tscol,
    date_trunc('minute', tscol) AS trunc_minute,
    CAST(EXTRACT(MINUTE FROM tscol) AS integer) % 10 AS min_to_subtract,
    (CAST(EXTRACT(MINUTE FROM tscol) AS integer) % 10) * interval '1 minute' AS interval_to_subtract,
    date_trunc('minute', tscol) - (CAST(EXTRACT(MINUTE FROM tscol) AS integer) % 10) * interval '1 minute' AS solution
FROM your_table
ORDER BY tscol;

型
它返回：

tscol            |      trunc_minute      | min_to_subtract | interval_to_subtract |        solution        
----------------------------+------------------------+-----------------+----------------------+------------------------
 2011-01-03 19:18:00.3-02   | 2011-01-03 19:18:00-02 |               8 | 00:08:00             | 2011-01-03 19:10:00-02
 2011-01-03 19:18:00.503-02 | 2011-01-03 19:18:00-02 |               8 | 00:08:00             | 2011-01-03 19:10:00-02
 2011-01-03 19:20:26.335-02 | 2011-01-03 19:20:00-02 |               0 | 00:00:00             | 2011-01-03 19:20:00-02
 2011-01-03 19:29:54.289-02 | 2011-01-03 19:29:00-02 |               9 | 00:09:00             | 2011-01-03 19:20:00-02
...

型
现在，最后一列是我们想要的解决方案，时间戳被截断为10分钟组，现在我们可以简单地聚合并获得我们的最终解决方案：

SELECT
    date_trunc('minute', tscol) - (CAST(EXTRACT(MINUTE FROM tscol) AS integer) % 10) * interval '1 minute' AS trunc_10_minute,
    count(*)
FROM your_table
GROUP BY trunc_10_minute
ORDER BY trunc_10_minute;

型
它返回：

trunc_10_minute     | count 
------------------------+-------
 2011-01-03 19:10:00-02 |     2
 2011-01-03 19:20:00-02 |     2
 2011-01-04 14:40:00-02 |     1
 2011-01-04 14:50:00-02 |     5
 2011-01-04 15:00:00-02 |     5
(5 rows)

型
这就是你给出的确切输出，但我相信这是你实际期望的，如果不是，那只是一个小调整的问题。

递归查询检测间隔的开始和停止时间; count（*）标量子查询计算每个间隔内的原始记录数。

WITH RECURSIVE rr AS (
        SELECT 1::integer AS num
                , MIN(tscol) AS starter
                , MIN(tscol) + '10 min'::INTERVAL AS stopper
        FROM your_table
        UNION ALL
        SELECT
                1+rr.num AS num
                , tscol AS starter
                , tscol + '10 min'::INTERVAL AS stopper
        FROM your_table yt
        JOIN rr ON yt.tscol > rr.stopper
                AND NOT EXISTS ( SELECT *
                  FROM your_table nx
                  WHERE nx.tscol > rr.stopper
                  AND nx.tscol < yt.tscol
                )
        )
SELECT num,starter,stopper
        , (SELECT COUNT(*) FROM your_table yt
                WHERE yt.tscol BETWEEN rr.starter AND rr.stopper
        ) AS cnt
FROM rr
        ;

字符串

动态界限（提问）

每个组的边界都依赖于前一个组，所以它不适合于基于集合的解，我们必须遍历表来确定边界。

WITH RECURSIVE cte AS (
   SELECT min(ts) AS ts
   FROM   tbl
   
   UNION ALL
   (
   SELECT t.ts
   FROM   cte c
   JOIN   tbl t ON t.ts > c.ts + interval '10 min'
   ORDER  BY t.ts
   LIMIT  1
   )
   )
SELECT row_number() OVER (ORDER BY c.ts) AS id  -- seems optional
     , c.ts, count(*)
FROM   cte c
JOIN   tbl t ON t.ts >= c.ts
            AND t.ts <= c.ts + interval '10 min'
GROUP  BY c.ts;

字符串
fiddle
请确保时间戳列上有一个普通索引。
基本上是模拟索引跳过扫描的另一个应用。请参阅：

• 优化GROUP BY查询以检索每个用户最新行

wildplasser提供了一个可行的解决方案，但它更简单、更快。

固定的10分钟网格

就简单多了。

-- simpler (different!) solution with fixed 10-min slots using date_bin()
SELECT date_bin('10 min', ts, '2011-1-1') AS ts, count(*)
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

型
fiddle的
不需要索引。导致单个顺序扫描，因此速度更快。
date_bin()需要Posterre 14或更新版本。请参阅：

• 在Posterre中，将时间戳缩短为5分钟的最快方法是什么？

MatheusOl为旧的Posterre版本提供了一个有效的解决方案。