Блог

У меня есть таблица, в которой каждая запись имеет индикатор и диапазон, и я хочу знать общий разброс, охватываемый диапазонами для каждого индикатора, но не двойной подсчет, когда диапазоны перекрываются для определенного индикатора.

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

 CREATE TABLE records(id int, spread int4range);
INSERT INTO records VALUES
    (1, int4range(1, 4)),
    (1, int4range(2, 7)),
    (1, int4range(11, 15)),
    (2, int4range(3, 5)),
    (2, int4range(6, 10));
SELECT * FROM records;
  

Получение выходных данных:

  id | spread
---- ---------
  1 | [1,4)
  1 | [2,7)
  1 | [11,15)
  2 | [3,5)
  2 | [6,10)
(5 rows)
  

Теперь я хотел бы получить запрос, который выдает следующий результат:

 id | total
--- --------
 1 |  10
 2 |   6
  

Откуда взялись числа 10 и 6?Для идентификатора 1 у нас есть диапазоны, которые включают 1, 2, 3, 4, 5, 6, 11, 12, 13, и 14; всего 10 различных целых чисел. Для идентификатора 2 у нас есть диапазоны, которые включают 3, 4, 6, 7, 8, и 9; всего шесть различных целых чисел.

Если это поможет вам понять проблему, вы можете представить ее примерно так: «Если эти записи представляют собой диапазон дней и времени для встреч в моем календаре, сколько всего часов в каждом дне я забронировал хотя бы один раз?»

Версия Postgres — 9.4.8, если это имеет значение.

 select id, count(*)
from (
    select distinct id, generate_series(lower(spread), upper(spread) - 1)
    from records
) s
group by id
;
 id | count 
---- -------
  1 |    10
  2 |     6