Блог

Главная - Вопросы по программированию - Замените временные пробелы в наборе результатов значением по умолчанию в Postgres

Замените временные пробелы в наборе результатов значением по умолчанию в Postgres

#sql #postgresql

Вопрос:

Я использую этот запрос для усреднения значения (производительности) за 3-минутные интервалы в час:

 SELECT
  date_trunc('hour', created) AS created,
  (extract(minute FROM created)::int / 3) minute_part,
  worker,
  AVG(performance)
FROM perf_stats
WHERE
  group_id = 'foo' AND 
  worker = 'bar' AND
  created > '1.1.2021 19:00:00' AND
  created < '1.1.2021 20:00:00'
GROUP BY 1, 2, worker
ORDER BY 1, 2, worker;

Это возвращает следующий результирующий набор для образца набора данных:

созданный минутная часть работник среднее
2021-01-01 19:00:00 10 бар 3

Теперь я хотел бы заполнить нули для повышения производительности, когда нет строк, удовлетворяющих запросу. В результате этого результирующего набора:

созданный минутная часть работник среднее
2021-01-01 19:00:00 0 бар 0
2021-01-01 19:00:00 1 бар 0
2021-01-01 19:00:00 2 бар 0
2021-01-01 19:00:00 3 бар 0
2021-01-01 19:00:00 4 бар 0
2021-01-01 19:00:00 5 бар 0
2021-01-01 19:00:00 6 бар 0
2021-01-01 19:00:00 7 бар 0
2021-01-01 19:00:00 8 бар 0
2021-01-01 19:00:00 9 бар 0
2021-01-01 19:00:00 10 бар 3
2021-01-01 19:00:00 11 бар 0
2021-01-01 19:00:00 12 бар 0
2021-01-01 19:00:00 13 бар 0
2021-01-01 19:00:00 14 бар 0
2021-01-01 19:00:00 15 бар 0
2021-01-01 19:00:00 16 бар 0
2021-01-01 19:00:00 17 бар 0
2021-01-01 19:00:00 18 бар 0
2021-01-01 19:00:00 19 бар 0

Стол:

 CREATE TABLE perf_stats
(
    id BIGSERIAL NOT NULL PRIMARY KEY,
    group_id TEXT NOT NULL,
    worker TEXT NOT NULL,
    performance DOUBLE PRECISION NOT NULL DEFAULT 0,
    created TIMESTAMP NOT NULL
);

CREATE INDEX IDX_PERFSTATS_CREATED on perf_stats(group_id, created);

Примерные Данные:

 INSERT INTO perf_stats (group_id, worker, performance, created) 
VALUES('foo', 'bar', 2, '1.1.2021 19:30:00');

INSERT INTO perf_stats (group_id, worker, performance, created) 
VALUES('foo', 'bar', 4, '1.1.2021 19:31:00');

Я экспериментировал, generate_series но до сих пор это не работало.

Комментарии:

1. почему только до 19-й минуты? по какой-то конкретной причине?

2. @eshirvana Есть только 20 3-минутных кусочков в час. После этого он оборачивается и снова начинается с нуля в следующий час.

3. Я вижу, как насчет среднего значения , я вижу , что среднее значение не в час, как вы вычисляете среднее значение?

Ответ №1:

Вы, кажется, хотите генерировать строки в течение всех минут. Вы можете использовать generate_series() для этого, а затем LEFT JOIN :

 SELECT date_trunc('hour', gs.ts) AS created,
       (extract(minute FROM gs.ts)::int / 3) minute_part,
       worker,
       AVG(performance)
FROM generate_series('2021-01-01 19:00:00'::timestamp, '2021-01-01 20:00:00'::timestamp, interval '3 minute') gs(ts) LEFT JOIN
     perf_stats ps
     ON ps.group_id = 'foo' AND 
        ps.worker = 'bar' AND
        ps.created >= gs.ts AND
        ps.created < gs.ts   interval '3 minute'
GROUP BY 1, 2, worker
ORDER BY 1, 2, worker;
Метки: