Mam tabelę (w PostgreSQL 9.4), która wygląda następująco:

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');

Teraz chcę obliczyć dla podanych dat i dla każdego rodzaju, ile wierszy z dates_rangeskażdej daty przypada. Zera można ewentualnie pominąć.

Pożądany rezultat:

+-------+------------+----+
|  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 |
+-------+------------+----+

Wymyśliłem dwa rozwiązania, jedno z LEFT JOINiGROUP 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

i jeden z LATERAL, który jest nieco szybszy:

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

Zastanawiam się, czy jest lepszy sposób na napisanie tego zapytania? A jak uwzględnić pary dat z liczbą 0?

W rzeczywistości istnieje kilka różnych rodzajów, okres do pięciu lat (1800 dat) i ~ 30 000 wierszy w dates_rangestabeli (ale może znacznie wzrosnąć).

Brak indeksów. Mówiąc dokładniej, w moim przypadku jest to wynikiem podzapytania, ale chciałem ograniczyć pytanie do jednego problemu, więc jest bardziej ogólne.

Następujące zapytanie działa również, jeśli „brak zer” jest poprawny:

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;

ale nie jest to szybsze niż lateralwersja z małym zestawem danych. Może jednak skalować się lepiej, ponieważ nie jest wymagane łączenie, ale powyższa wersja agreguje wszystkie wiersze, więc może ponownie stracić.

Poniższe zapytanie próbuje uniknąć niepotrzebnej pracy poprzez usunięcie serii, które i tak się nie nakładają:

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;

- i muszę użyć overlapsoperatora! Zauważ, że musisz dodać interval '1 day'po prawej, ponieważ nakładający się operator uważa, że ​​przedziały czasowe są otwarte po prawej stronie (co jest dość logiczne, ponieważ data jest często uważana za znacznik czasu z komponentem czasu północy).

A jak uwzględnić pary dat z liczbą 0?

Zbuduj siatkę wszystkich kombinacji, a następnie LATERAL dołącz do stołu, tak jak to:

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;

Powinien też być tak szybki, jak to możliwe.

Na początku miałem LEFT JOIN LATERAL ... on true, ale w podzapytaniu jest agregat c, więc zawsze otrzymujemy wiersz i możemy go również użyć CROSS JOIN. Bez różnicy w wydajności.

Jeśli masz tabelę zawierającą wszystkie odpowiednie rodzaje , użyj jej zamiast generowania listy z podzapytaniem k.

Przesyłanie do integerjest opcjonalne. Jeszcze raz bigint.

Pomogą w tym indeksy, zwłaszcza indeks wielokolumnowy (kind, start_date, end_date). Ponieważ budujesz na podzapytaniu, może to być lub nie być możliwe do osiągnięcia.

Używanie funkcji zwracających zestaw, takich jak generate_series()na SELECTliście, na ogół nie jest zalecane w wersjach Postgres wcześniejszych niż 10 (chyba że wiesz dokładnie, co robisz). Widzieć:

• Jakie jest oczekiwane zachowanie wielu funkcji zwracających zestaw w klauzuli select?

Jeśli masz wiele kombinacji z kilkoma wierszami lub bez, ten równoważny formularz może być szybszy:

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;

Używanie daterangetypu

PostgreSQL ma daterange. Korzystanie z niego jest dość proste. Zaczynając od przykładowych danych, przechodzimy do użycia typu z tabeli.

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)

Chcę obliczyć dla podanych dat i dla każdego rodzaju, w ilu wierszach z dat_rangów przypada każda data.

Teraz, aby wykonać zapytanie, odwracamy procedurę i generujemy serie dat, ale oto haczyk samo zapytanie może użyć @>operatora zawierającego ( ), aby sprawdzić, czy daty mieszczą się w zakresie, za pomocą indeksu.

Uwaga, której używamy timestamp without time zone(aby zatrzymać zagrożenia 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;

To jest wyszczególnione nakładki na dzień w indeksie.

Dodatkową korzyścią jest to, że dzięki typowi zmiany możesz zatrzymać wstawianie zakresów, które pokrywają się z innymi za pomocąEXCLUDE CONSTRAINT