Самый быстрый способ подсчета количества диапазонов дат охватывает каждую дату из серии

У меня есть таблица (в PostgreSQL 9.4), которая выглядит так:

CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES 
    (1, '2018-01-01', '2018-01-31'),
    (1, '2018-01-01', '2018-01-05'),
    (1, '2018-01-03', '2018-01-06'),
    (2, '2018-01-01', '2018-01-01'),
    (2, '2018-01-01', '2018-01-02'),
    (3, '2018-01-02', '2018-01-08'),
    (3, '2018-01-05', '2018-01-10');

Теперь я хочу рассчитать для заданных дат и для каждого вида, сколько строк из dates_ranges каждая дата падает. Возможно, нули могут быть опущены.

Желаемый результат:

+-------+------------+----+
|  kind | as_of_date |  n |
+-------+------------+----+
|     1 | 2018-01-01 |  2 |
|     1 | 2018-01-02 |  2 |
|     1 | 2018-01-03 |  3 |
|     2 | 2018-01-01 |  2 |
|     2 | 2018-01-02 |  1 |
|     3 | 2018-01-02 |  1 |
|     3 | 2018-01-03 |  1 |
+-------+------------+----+

Я придумал два решения: один с LEFT JOIN и GROUP BY

SELECT
kind, as_of_date, COUNT(*) n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates
LEFT JOIN
    dates_ranges ON dates.as_of_date BETWEEN start_date AND end_date
GROUP BY 1,2 ORDER BY 1,2

и один с LATERAL, который немного быстрее:

SELECT
    kind, as_of_date, n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates,
LATERAL
    (SELECT kind, COUNT(*) AS n FROM dates_ranges WHERE dates.as_of_date BETWEEN start_date AND end_date GROUP BY kind) ss
ORDER BY kind, as_of_date

Мне интересно, это лучший способ написать этот запрос? И как включить пары date-kind с 0 count?

В действительности существует несколько различных видов, период до пяти лет (1800 дат) и ~ 30 тыс. строк в dates_ranges (но может значительно возрасти).

Нет индексов. Чтобы быть точным, в моем случае это результат подзапроса, но я хотел ограничить вопрос одной проблемой, поэтому она более общая.

10 голосов | спросил BartekCh 30 Maypm18 2018, 16:29:19

3 ответа


2

Следующий запрос также работает, если «отсутствующие нули» в порядке:

select *
from (
  select
    kind,
    generate_series(start_date, end_date, interval '1 day')::date as d,
    count(*)
  from dates_ranges
  group by 1, 2
) x
where d between date '2018-01-01' and date '2018-01-03'
order by 1, 2;

, но это не быстрее, чем версия lateral с небольшим набором данных. Это может быть лучше, хотя, поскольку соединение не требуется, но приведенная выше версия объединяется по всем строкам, поэтому она может снова проиграть.

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

select
  kind,
  generate_series(greatest(start_date, date '2018-01-01'), least(end_date, date '2018-01-03'), interval '1 day')::date as d,
  count(*)
from dates_ranges
where (start_date, end_date + interval '1 day') overlaps (date '2018-01-01', date '2018-01-03' + interval '1 day')
group by 1, 2
order by 1, 2;

- и я должен использовать оператор overlaps! Обратите внимание, что вам нужно добавить interval '1 day' вправо, поскольку оператор перекрытий считает, что периоды времени должны быть открыты справа (что довольно логично, потому что дата часто считается временной меткой с компонентом времени в полночь).

ответил Colin 't Hart 30 Maypm18 2018, 17:12:25
5
  

И как включить пары date-kind с 0 count?

Создайте сетку из всех комбинаций , затем LATERAL присоединитесь к своей таблице, например:

SELECT k.kind, d.as_of_date, c.n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS  JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
CROSS  JOIN LATERAL (
   SELECT count(*)::int AS n
   FROM   dates_ranges
   WHERE  kind = k.kind
   AND    d.as_of_date BETWEEN start_date AND end_date
   ) c
ORDER  BY k.kind, d.as_of_date;

Также должен быть как можно быстрее.

У меня сначала был LEFT JOIN LATERAL ... on true, но в подзапросе c, поэтому мы always получаем строку и можем использовать CROSS JOIN как Что ж. Никакой разницы в производительности.

Если у вас есть таблица, содержащая все соответствующие виды , используйте это вместо создания списка с подзапросом k.

Приведение в integer является необязательным. Иначе вы получите bigint.

Индексы помогут, особенно многоколоночный индекс на (kind, start_date, end_date). Поскольку вы строите подзапрос, это может быть или не быть возможным.

Использование функций set-return, таких как generate_series() в SELECT обычно не рекомендуется в версиях Postgres до 10 (если вы точно не знаете, что вы делаете). См:

Если у вас много комбинаций с несколькими или никакими строками, эта эквивалентная форма может быть быстрее:

SELECT k.kind, d.as_of_date, count(dr.kind)::int AS n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
LEFT   JOIN dates_ranges dr ON dr.kind = k.kind
                           AND d.as_of_date BETWEEN dr.start_date AND dr.end_date
GROUP  BY 1, 2
ORDER  BY 1, 2;
ответил Erwin Brandstetter 30 Maypm18 2018, 18:37:42
1

Использование типа daterange

PostgreSQL имеет daterange . Использование его довольно просто. Начиная с ваших данных образца, мы переходим к использованию типа в таблице.

BEGIN;
  ALTER TABLE dates_ranges ADD COLUMN myrange daterange;
  UPDATE dates_ranges
    SET myrange = daterange(start_date, end_date, '[]');
  ALTER TABLE dates_ranges
    DROP COLUMN start_date,
    DROP COLUMN end_date;
COMMIT;

-- Now you can create GIST index on it...
CREATE INDEX ON dates_ranges USING gist (myrange);

TABLE dates_ranges;
 kind |         myrange         
------+-------------------------
    1 | [2018-01-01,2018-02-01)
    1 | [2018-01-01,2018-01-06)
    1 | [2018-01-03,2018-01-07)
    2 | [2018-01-01,2018-01-02)
    2 | [2018-01-01,2018-01-03)
    3 | [2018-01-02,2018-01-09)
    3 | [2018-01-05,2018-01-11)
(7 rows)
  

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

Теперь, чтобы запросить его, мы отменим процедуру, а создадим серию дат , но вот уловка самого запроса может использовать оператор сдерживания (@>), чтобы проверить, что даты находятся в диапазоне, с использованием индекса.

Примечание: мы используем timestamp without time zone (чтобы остановить опасности DST)

SELECT d1.kind, day::date, count(d2.kind)
FROM dates_ranges AS d1
CROSS JOIN LATERAL generate_series(
  lower(myrange)::timestamp without time zone,
  upper(myrange)::timestamp without time zone,
  '1 day'
) AS gs(day)
INNER JOIN dates_ranges AS d2
  ON d2.myrange @> day::date
GROUP BY d1.kind, day;

Который является детализацией дневных перекрытий по индексу.

В качестве бонусного бонуса с типом daterange вы можете остановить вставки диапазонов, которые перекрываются с другими, используя EXCLUDE CONSTRAINT

ответил Evan Carroll 30 Maypm18 2018, 20:55:27

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132