PostgreSQL - GROUP BY 基于每行 10 分钟

Question

我有一个难以解决的问题，我想你可以帮忙。我有一个包含数百万条记录的表，其中每 10 分钟根据注册表值精确分组，例如:

Record "01 | 2011/01/03 19:18:00.300" the time it needs to count the records is 19:18:00.300 to 19:28:00.299. With this process it will group records 01,02,03.

Record "04 | 2011/01/03 19:29:54.289" the time it needs to count the records is 19:29:54.289 to 19:39:54.288. With this process it will group records only the record 04.

Record "05 | 2011/01/04 14:43:43.067", the time he needs to count the records is 14:43:43.067 to 14:43:53.066. With this process it will group records 05,06,07.

Record "08 | 2011/01/04 14:57:55.608;" the time it needs to count the records is 14:57:55.608 to 15:07:55.607. With this process it will group records 08,09,10,11,12,13,14,15.

输入数据:

ID   TS
01   2011/01/03 19:18:00.300
02   2011/01/03 19:18:00.503
03   2011/01/03 19:20:26.335
04   2011/01/03 19:29:54.289
05   2011/01/04 14:43:43.067
06   2011/01/04 14:50:10.727
07   2011/01/04 14:52:26.827
08   2011/01/04 14:57:55.608
09   2011/01/04 14:57:55.718
10   2011/01/04 14:59:13.603
11   2011/01/04 15:00:34.260
12   2011/01/04 15:02:55.687
13   2011/01/04 15:04:51.917
14   2011/01/04 15:06:24.760
15   2011/01/04 15:07:15.378

输出数据:

ID  TS   Count
01   2011/01/03 19:18:00.300    3
02   2011/01/03 19:29:54.289    1
03   2011/01/04 14:43:43.067    3
04   2011/01/04 14:57:55.608    8

有没有人能解决这个问题？已经，感谢您的关注。

Answer 1

I have a table with millions of records in which precise group every 10 minutes

tl;dr: 不耐烦的请看答案最后一个问题，才是真正的解决办法，其他的都是一步一步来的。另外，all queries + schemas are available at SQLFiddle , 对于那些想玩的人。

在我看来，此类问题的最佳解决方案是将每个时间戳截断到其 10 分钟的开始，例如，让我们尝试进行以下转换(原始 -> 截断 10 分钟):

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 functions (the latter can be invoked by the standard 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
2. 因为我们想要截断 10 分钟，所以我们对 18 和 10 取模，所以 18 % 10 给我们 < strong>8
3. 现在，我们有要减去的 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)

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