Как заполнить пробелы временных меток в запросе Postgres?

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path = tmp;

DROP TABLE IF EXISTS channel1m CASCADE;
CREATE TABLE channel1m (
  zts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  zitemid BIGINT,
  zvalue BIGINT
);

-- in which a row may be inserted each minute, per zitemid, as follows:

INSERT INTO channel1m(zts, zitemid, zvalue) VALUES
 ('2012-12-03 15:29:00',   100,            1)
,('2012-12-03 15:30:00',   100,            2)
,('2012-12-03 15:30:00',   101,            0)
,('2012-12-03 15:32:00',   100,            1)
,('2012-12-03 15:32:00',   101,            1)
        ;

        -- CTE to the rescue!!!
WITH cal AS (
        WITH mm AS (
                SELECT MIN(xx.zts) AS minmin, MAX(xx.zts) AS maxmax
                 FROM channel1m xx)
        SELECT generate_series(mm.minmin , mm.maxmax , '1 min'::interval) AS stamp
        FROM mm
        )
, ite AS (
        SELECT DISTINCT zitemid AS zitemid
        FROM channel1m
        )
SELECT cal.stamp
        , ite.zitemid
        , tab.zvalue
FROM cal
JOIN ite ON 1=1 -- Note: this is a cartesian product of the {time,id} -domains
LEFT JOIN channel1m tab ON tab.zts = cal.stamp AND tab.zitemid = ite.zitemid
ORDER BY stamp ASC
        ;

Выход:

NOTICE:  drop cascades to table tmp.channel1m
DROP SCHEMA
CREATE SCHEMA
SET
NOTICE:  table "channel1m" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 5
        stamp        | zitemid | zvalue 
---------------------+---------+--------
 2012-12-03 15:29:00 |     101 |       
 2012-12-03 15:29:00 |     100 |      1
 2012-12-03 15:30:00 |     100 |      2
 2012-12-03 15:30:00 |     101 |      0
 2012-12-03 15:31:00 |     100 |       
 2012-12-03 15:31:00 |     101 |       
 2012-12-03 15:32:00 |     100 |      1
 2012-12-03 15:32:00 |     101 |      1
(8 rows)

Вам понадобятся: таблица со всеми itemId и (псевдо) таблица со всеми необходимыми датами.

Вероятно, у вас есть таблица со всеми отдельными itemId. Назовем его item_table.

Псевдотаблицу с датами можно получить с помощью generate_series('start_date','end_date', interval '1 minute'). Подробности здесь.

Запрос:

SELECT gs.ts, it.itemId, ch1m.value
FROM item_table it
CROSS JOIN generate_series('start_date','end_date', interval '1 minute') gs(ts)
LEFT JOIN channel1m ch1m ON it.itemId = ch1m.itemId 
                         AND gs.ts = ch1m.ts

Замените 'start_date','end_date' нужными значениями или получите их из подзапроса.

Этот запрос:

1) Строит все пары элемент-время через CROSS JOIN

2) Получает value через LEFT JOIN

Я думаю, что наиболее читаемым способом является создание серии табличных выражений. Перекрестное соединение между минутами и идентификационными номерами предметов даст вам каждую комбинацию.

with all_minutes as (
  select ('2012-12-03 15:29'::timestamp + 
           (n || ' minute')::interval)::timestamp as ts
  from generate_series(0,10) n
),
item_ids as (
  select distinct itemid from channel1m
),
all_items_and_minutes as (
  select all_minutes.ts, item_ids.itemid from all_minutes cross join item_ids
)
select all_items_and_minutes.ts, all_items_and_minutes.itemId, channel1m.value
from all_items_and_minutes 
left join channel1m 
       on all_items_and_minutes.ts = channel1m.ts
      and all_items_and_minutes.itemid = channel1m.itemid
order by all_items_and_minutes.ts, all_items_and_minutes.itemid

Вы можете заменить литералы меток времени операторами SELECT, чтобы получить фактический диапазон, который вам нужен. Если у вас есть другая таблица, содержащая все уникальные идентификаторы элементов, вам, возможно, лучше выбирать из этой таблицы, а не выбирать отдельные значения из таблицы channel1m.

1. Используйте time_bucket или date_trunc для создания границ блока.
2. Используйте generate_series для создания пустых сегментов для вашего периода времени.
3. Используйте UNION, чтобы объединить пустой набор данных временного интервала с вашими данными.
4. Используйте DISTINCT ON, чтобы выбрать уникальные строки для каждого сегмента времени, отдавая предпочтение тем, которые содержат данные.

Пример:

WITH
  timeseries_data AS (
    SELECT
      time_bucket(interval '5 minutes', started_at) time_bucket_start,
      count(distinct v1.value) unique_row_count
    FROM
      probe_execution pe1
    CROSS JOIN LATERAL (
      SELECT value
      FROM jsonb_array_elements(pe1.result)
    ) v1
    WHERE
      pe1.probe_id = 8 AND
      pe1.result_count > 0 AND
      pe1.started_at > now() - interval '1 day' AND
      pe1.ended_at < now()
    GROUP BY time_bucket_start
    UNION
    SELECT
      gs1 time_bucket_start,
      0 unique_row_count
    FROM
      generate_series(
        time_bucket(interval '5 minutes', now() - interval '1 day'),
        time_bucket(interval '5 minutes', now()),
        interval '1 minute'
      ) as gs1
  )
SELECT DISTINCT ON (td1.time_bucket_start)
  td1.time_bucket_start,
  td1.unique_row_count
FROM timeseries_data td1
ORDER BY td1.time_bucket_start, td1.unique_row_count DESC