Generierung von PostgreSQL-Datumssequenzen und generate_series

Bei einem der Projekte mussten wir Ende des Monats einen Bericht über Finanztransaktionen für den Zeitraum mit einer Gruppe von Zwischensummen erstellen.

Die Aufgabe ist im Allgemeinen einfach: Bestimmen Sie die erforderlichen Zeiträume innerhalb eines großen Intervalls, binden Sie jede Operation an einen geeigneten Zeitraum, gruppieren Sie sie und addieren Sie die Summe.

Um Perioden innerhalb des Intervalls zu generieren, habe ich gewöhnlich die Funktion generate_series verwendet, mit der ich häufig numerische Sequenzen generiere. Ich überprüfte die Dokumentation über die Möglichkeit, eine Folge von Daten zu generieren, betrachtete sie als Beispiel, schrieb eine Abfrage und war verwirrt.

select gs::date from generate_series('2018-01-31', '2018-05-31', interval '1 month') as gs; 

Das Ergebnis war ebenso unerwartet wie logisch. Die Funktion generate_series hat ehrlich iterativ eine Folge von Daten generiert, die auf dem Prinzip basiert, dem vorherigen Wert nacheinander eine Verschiebung hinzuzufügen. Gleichzeitig wurde bei jedem Schritt die Richtigkeit und Bearbeitung des Empfangsdatums überprüft. Der 31. Februar findet nicht statt, daher wurde das Datum auf den 28. Februar geändert und durch die weitere Hinzufügung des Monats wurde die gesamte Sequenz auf den 28. Februar reduziert.

UPD Erklärungen nach Fragen in den Kommentaren. Im Allgemeinen ist die anfängliche Aufgabe umfassender: Daten an beliebigen Tagen im Monat zu gruppieren. Gruppieren Sie beispielsweise nach dem 20. Tag eines jeden Monats, nach dem 15. Tag, aber es gibt keine Probleme mit solchen Daten beim Generieren. Der Mechanismus, nach dem wir suchen, sollte ebenso gut eine Folge von 10 Zahlen pro Monat und 21 Zahlen erstellen und die Enden der Monate korrekt berechnen.

Ich frage mich, wie sich die Additionsoperation mit mehreren Monaten gleichzeitig verhalten wird. Was passiert, wenn wir das Intervall nicht iterativ, sondern "in großen Mengen" hinzufügen?

 select '2018-01-31'::date +interval '1 mons' 28.02.2018 select '2018-01-31'::date +interval '2 mons' 31.03.2018 

In diesem Fall erfolgt die Hinzufügung ehrlich.
Wie können mit diesem Ansatz die erforderlichen Daten generiert werden?

Wenn die Anzahl der Monate bekannt ist, ist es sehr einfach:

 select '2018-01-31'::date +make_interval(0, i) as gs from generate_series(0, 4, 1) as i 

Was ist, wenn nur das Start- und Enddatum bekannt sind?
Dieses Problem kann ganz einfach gelöst werden, indem eine gespeicherte Funktion und ein einfacher Zyklus darin geschrieben werden. Wir sind jedoch an einer Implementierungsoption interessiert, wenn keine Möglichkeit oder kein Wunsch besteht, die Datenbankstruktur mit unnötigen Objekten zu verstopfen.
Versuchen wir, die Aufgabe auf die vorherige zu reduzieren.

Der folgende Code ist in gewissem Maße ein Steckbrett und gibt nicht vor, elegant zu sein. Wir schreiben die ersten Abfrageoptionen im Unternehmen, wobei der Schwerpunkt auf der Flexibilität und Austauschbarkeit von Blöcken liegt

 /*  -  ,         ,      */ with dates as ( select '2018-01-31'::date as dt1, '2018-05-31'::date as dt2 ), /*      ""  */ g_age as ( select age( (select dt2 from dates), (select dt1 from dates)) ), /*       (*12 + )   +1       */ months as ( select (extract(year from (select * from g_age))*12 + extract(month from (select * from g_age))+1)::integer ), /*  ,           -   ,       */ seq as( select ((select dt1 from dates) + make_interval(0, gs)) as gs from generate_series ( 0, (select * from months), 1 ) as gs where ((select dt1 from dates) + make_interval(0, gs)) <= (select dt2 from dates) ) /*         */ select * from seq 

Die Lösung erwies sich als ziemlich umständlich, funktionierte jedoch und es ist recht einfach, sie über den with-Mechanismus in andere Anforderungen zu integrieren.
Wir haben den Bericht umgesetzt, aber die Idee, dass diese Anfrage nicht nur umständlich ist, sondern auch nur schrittweise über ganze Monate hinweg verwendet werden kann, hat keine Ruhe gegeben.

Option 2
Nach einer Weile wurde mir klar, dass die sequentielle Datumsgenerierung im Wesentlichen eine rekursive Prozedur ist. Nur nicht in seiner reinen Form, da in unserem Fall die Berechnung des nächsten Datums aus dem vorherigen zum ursprünglichen Problem führt. Aber bei jedem Schritt können wir das Intervall erhöhen, das zu Beginn unserer Periode hinzugefügt wird:

 /*    -,     timestamp */ with recursive dates as ( select '2018-01-31'::timestamp as dt1, '2018-05-31'::timestamp as dt2, interval '1 month' as interval ), /*           ,          ,   .  ,        */ pr AS( select 1 as i, (select dt1 from dates) as dt union select i+1 as i, ( (select dt1 from dates) + ( select interval from dates)*i)::timestamp as dt from pr where ( ((select dt1 from dates) + (select interval from dates)*i)::timestamp) <=(select dt2 from dates) ) select dt as gs from pr; 

Diese Abfrage funktioniert korrekt mit allen Eingabezeiträumen und -intervallen.