Uruchamianie złożonego zapytania dla każdej daty w zakresie

Mam tabelę zamówień

   Column   |            Type             |                      Modifiers                      
------------+-----------------------------+-----------------------------------------------------
 id         | integer                     | not null default nextval('orders_id_seq'::regclass)
 client_id  | integer                     | not null
 start_date | date                        | not null
 end_date   | date                        | 
 order_type | character varying           | not null

Dane nie nakładają się na stałe zleceń dla id_użytkownika, a czasami zlecenie tymczasowe, które zastępuje stałe zlecenie w jego dacie_początkowej, gdy mają pasujący identyfikator_danych klienta. Istnieją ograniczenia na poziomie aplikacji, które uniemożliwiają nakładanie się zamówień tego samego typu.

 id | client_id | start_date |  end_date  | order_type 
----+-----------+------------+------------+------------
 17 |        11 | 2014-02-05 |            | standing
 18 |        15 | 2014-07-16 | 2015-07-19 | standing
 19 |        16 | 2015-04-01 |            | standing
 20 |        16 | 2015-07-18 | 2015-07-18 | temporary

Na przykład na 2015-07-18kliencie 16 ma zamówienie nr 20, ponieważ jest to zamówienie aktywne, ponieważ zastępuje zlecenie stałe nr 19. Z pewnym zamieszaniem znalazłem skuteczny sposób sprawdzania aktywnych identyfikatorów zamówień na randkę.

    SELECT id from (
      SELECT
        id,
        first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
      FROM orders
      WHERE start_date <= ? and (end_date is null OR end_date >= ?)
    ) active_orders
    WHERE id = active_order_id

Jeśli zapytasz o to 2015-07-18jako symbole zastępcze, otrzymasz

 id 
----
 17
 18
 20

Plan zapytań dla tego zapytania w porównaniu do niektórych innych moich pomysłów (takich jak zapytania częściowe liczące liczbę tymczasowych zamówień dla klienta na randkę) jest dość niewielki i jestem z niego bardzo zadowolony. (projekt stołu, nie jestem zachwycony)

Teraz muszę znaleźć wszystkie aktywne zamówienia dla zakresu dat połączone z datami, w których są aktywne. Na przykład z zakresem dat 2015-07-18do 2015-07-19chciałbym następujący wynik.

active_date | id 
------------+----
 2015-07-18 | 17
 2015-07-18 | 18
 2015-07-18 | 20
 2015-07-19 | 17
 2015-07-19 | 18
 2015-07-19 | 19

Zamówienie 20 zastępuje zamówienie 19 włączone, 2015-07-18ale nie włączone 2015-07-19.

Zauważyłem, generate_series()że mogę generować zakres dat, ale nie mam pojęcia, jak się z tym połączyć, aby uzyskać tabelę dat i identyfikatorów zamówienia. Moje przeczucie jest połączeniem krzyżowym, ale nie mogę wymyślić, jak to zrobić w takich okolicznościach.

Dzięki

AKTUALIZACJA Dodano skrzypce sql .

Chciałbym użyć select distinct onzamiast funkcji okna, a następnie po prostu dołączyć do dni.

select 
    distinct on (date, client_id) date, 
    id 
from orders
inner join generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') date
  on start_date <= date and (end_date is null or date <= end_date)
order by date, client_id, order_type desc

http://sqlfiddle.com/#!15/5a420/16/0

Mogę rozwinąć więcej, jeśli coś nie jest jasne.

Napisz funkcję, która przyjmuje pojedynczą datę jako parametr i zwraca listę dat + id, które mają zamówienie.

Następnie użyj polecenia generator_series zgodnie z sugestią i wywołaj funkcję w zakresie dat.

Jest to powszechna strategia w przypadku złożonych warunków w języku SQL.

Poniżej zamieściłem trochę kodu, ale powyższa odpowiedź SQL jest znacznie prostsza.

Oto funkcja:

create or replace function o( date) returns setof INT AS '
SELECT id from (
 SELECT
  id,
  first_value(id) OVER (PARTITION BY client_id ORDER BY order_type DESC) active_order_id
 FROM orders
 WHERE start_date <= $1 and (end_date is null OR end_date >= $1)
) active_orders
WHERE id = active_order_id;
' LANGUAGE sql ;

I jak to nazwać:

select distinct d, o(d::date) 
from generate_series('2015-07-18'::date, '2015-07-19'::date, '1 day') as d;

SQLFiddle