[SOLVED] PostgreSQL - GROUP BY 10 minutes based in each row

PostgreSQL - GROUP BY 10 minutes based in each row

I have a problem difficult to solve, I think you can help. I have a table with millions of records in which precise group every 10 minutes, based on the registry value, for example:

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.

Input data:

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

Output data:

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

Does anyone have a solution to this problem? Already, grateful for the attention.

Solution

This may be a bit sub-optimal, but it works. The recursive query detects the start- and stop- times of the intervals; the count(*) scalar subquery counts the number of original records within each interval.

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
        ;