Ваш запрос кажется сложным. Вам нужно только сгенерировать последовательность времен, а затем использовать left join, чтобы объединить их. . . и суммировать:

select g.ts,  g.ts + interval '4 second', count(ds.id)
from (select generate_series(min(starttime), max(strttime), interval '4 second') as ts
      from data_store
     ) g left join
     data_store ds
     on ds.starttime >= g.ts and ds.starttime < g.ts + interval '4 second'
group by g.ts
order by g.ts;

Примечание. Если вы хотите, чтобы интервал начинался с точной секунды (и не имел какого-то странного количества миллисекунд 999 раз из 1000), используйте date_trunc().

РЕДАКТИРОВАТЬ:

Возможно, стоит посмотреть, быстрее ли коррелированный подзапрос:

select gs.ts,
       (select count(*)
        from data_store ds
        where ds.starttime >= g.ts and ds.starttime < g.ts + interval '4 second'
       ) as cnt
from (select generate_series(min(starttime), max(strttime), interval '4 second') as ts
      from data_store
     ) g;

Улучшение запроса в выбранном ответе.

Я только что улучшил запрос, который вы можете найти в выбранном ответе.

Окончательный запрос выглядит следующим образом:

SELECT gp.tp AS starttime_from, gp.tp + interval '4 second' AS starttime_to, count(ds.id)
FROM (SELECT generate_series(min(starttime),max(starttime), interval '4 second') as tp
      FROM data_store
      WHERE id_user_table=1 and sip='147.32.84.138'
      ORDER BY 1
     ) gp 
     LEFT JOIN data_store ds 
     ON ds.id_user_table=1 and ds.sip='147.32.84.138' 
        and ds.starttime >= gp.tp and ds.starttime < gp.tp + interval '4 second'
GROUP BY starttime_from

Я переместил ORDER BY в подзапрос. Теперь немного быстрее. Я также добавил обязательные столбцы в предложение WHERE. Наконец, я создал многоколоночный индекс для столбцов, которые я всегда использую в запросе:

CREATE INDEX my_index ON data_store (id_user_table, sip, starttime);

На данный момент запрос выполняется очень быстро. Обратите внимание: для очень коротких интервалов времени результат запроса включает много строк с нулевым количеством. Эти строки съедают место. В этом случае запрос должен включать ограничение HAVING count(ds.id) > 0, но тогда вам придется обрабатывать эти 0 на стороне клиента.

Другое решение

Это решение не такое быстрое, как предыдущее, но в приведенном ниже запросе не используется многоколоночный индекс, и оно по-прежнему быстрое.

Две важные вещи в запросе, которые вы можете найти в конце этого ответа:

• 'second' - это точность, до которой усекается входное значение. Вы также можете выбрать другую точность, например: millisecond,minute,day и т. д.

• '4 second' - это временной интервал. Временной интервал может иметь другие единицы измерения, такие как millisecond, minute, day и т. д.

Здесь вы можете найти объяснение запроса:

• Запрос generate_period генерирует интервалы, начиная с указанной даты и времени до определенной даты и времени. Вы можете указать эту конкретную дату и время вручную или с помощью своего столбца таблицы (как в моем случае). Для временного интервала в 4 секунды запрос возвращает:

            tp
  ---------------------
   2011-09-12 15:24:03
   2011-09-12 15:24:07
   2011-09-12 15:24:11
           ...
  

• Запрос data_series подсчитывает записи для определенной точности даты и времени: for 1 second time interval, for 1 day time interval и т. д. В моем случае конкретная точность равна 'second', поэтому for 1 second time interval, но результат операции выбора не включает значение 0 для даты и времени, которое не т происходит. В моем случае запрос data_series возвращает:

         starttime     |    ct
  ---------------------+-----------
   2011-09-12 15:24:03 |     2
   2011-09-12 15:24:06 |     2
   2011-09-12 15:24:09 |     3     
           ...         |    ...
  

• Наконец, последняя часть запроса суммирует столбец ct за определенные периоды времени. Запрос возвращает это:

      starttime-from   |    starttime-to     |   ct
  ---------------------+---------------------+---------
   2011-09-12 15:24:03 | 2011-09-12 15:24:07 |    4
   2011-09-12 15:24:07 | 2011-09-12 15:24:11 |    3
   2011-09-12 15:24:11 | 2011-09-12 15:24:15 |    0
           ...         |         ...         |   ...
  

Вот запрос:

WITH generate_period AS(

    SELECT generate_series(date_trunc('second',min(starttime)), 
                           date_trunc('second',max(starttime)), 
                           interval '4 second') as tp
    FROM data_store 
    WHERE id_user_table=1 --other restrictions

), data_series AS(

    SELECT date_trunc('second', starttime) AS starttime, count(*) AS ct
    FROM data_store  
    WHERE id_user_table=1 --other restrictions
    GROUP  BY 1

)

SELECT gp.tp AS starttime-from, 
       gp.tp + interval '4 second' AS starttime-to, 
       COALESCE(sum(ds.ct),0) AS ct
FROM  generate_period gp
LEFT JOIN data_series ds ON date_trunc('second',ds.starttime) >= gp.tp 
                        and date_trunc('second',ds.starttime) < gp.tp + interval '4 second'
GROUP BY 1
ORDER BY 1;