Das globale Netzwerk für historische Klimatologie hat ungültige oder fehlerhafte Daten in seiner Sammlung von Wettermessungen angezeigt. Nach dem Entfernen dieser Elemente gibt es eine Reihe von Daten, die nicht mehr zusammenhängend datierte Abschnitte enthalten. Die Daten ähneln:
%Vor%Obwohl es möglich ist, fehlende Daten zu extrapolieren (z. B. durch Mittelung aus anderen Jahren), um zusammenhängende Bereiche bereitzustellen, möchte ich zur Vereinfachung des Systems die nicht zusammenhängenden Segmente basierend darauf kennzeichnen, ob es einen zusammenhängenden Bereich von Daten zum Ausfüllen gibt Monat:
%Vor%Einige Messungen wurden im Jahr 1843 vorgenommen.
Wie würden Sie für alle Wetterstationen alle Tage in Monaten markieren, an denen ein oder mehrere Tage fehlen?
Der Code zum Auswählen der Daten ähnelt:
%Vor%Erstellen Sie eine Tabelle mit zusammenhängenden Daten und vergleichen Sie diese mit den Daten der gemessenen Daten.
Das Problem kann mit dem SQL in diesem Abschnitt neu erstellt werden.
Die Tabelle wird wie folgt erstellt:
%Vor% Das folgende SQL fügt Daten in eine Tabelle ein ( id [int], n ame [varchar], d ate [Datum], valid [boolean]):
Die Werte 'A' bis 'F' repräsentieren die Namen von Wetterstationen, die an einem bestimmten Tag gemessen haben.
Löschen Sie einige Zeilen wie folgt:
%Vor% Im Folgenden wird das Flag valid nicht auf false für alle Tage in einem Monat umgeschaltet, in dem der Monat einen oder mehrere Tage nicht enthält:
Das folgende SQL erzeugt eine leere Ergebnismenge:
%Vor%Die folgende SQL generiert alle möglichen Kombinationen von Stationsnamen und Daten:
%Vor%In den realen Daten gibt es jedoch mehrere hundert Stationen und die Daten gehen bis in die Mitte des 19. Jahrhunderts zurück, so dass das kartesische aller Daten für alle Stationen zu groß ist. Ein solcher Ansatz könnte funktionieren, wenn man genug Zeit hat ... Es muss einen schnelleren Weg geben.
PostgreSQL hat Fensterfunktionen.
Auswählen bestimmter Änderungen mithilfe von Fensterfunktionen in postgres
Danke!
Die Funktion generate_series() von PostgreSQL kann eine Ansicht erstellen, die eine fortlaufende Liste von Daten enthält:
Der Ausdruck select max(date) - min(date) from test könnte um eins stehen.
Eine Möglichkeit, ungültige Monate zu identifizieren, besteht darin, zwei Ansichten zu erstellen. Der erste zählt die Anzahl der täglichen Messwerte, die jede Station in jedem Monat produzieren sollte. (Beachten Sie, dass climate.calendar in climate_calendar übersetzt wird.) Die zweite gibt die tatsächlichen täglichen Messwerte zurück, die jede Station pro Monat produziert.
Diese Ansicht gibt die tatsächliche Anzahl an Tagen in einem Monat pro Station zurück. (Zum Beispiel hat der Februar immer 28 oder 29 Tage.)
%Vor%Die Gesamtzahl der zurückgegebenen Tage ist geringer als die Anzahl der Tage. (Zum Beispiel wird Januar immer 31 Tage oder weniger haben.)
%Vor% Löschen Sie die ORDER BY -Klauseln in der Produktion (sie sind hilfreich in der Entwicklung).
Verknüpfen Sie die beiden Ansichten, um die Stationen und Monate, die markiert werden müssen, in einer neuen Ansicht zu identifizieren:
%Vor% Die Spalte num_days_missing ist nicht notwendig, aber nützlich.
Dies sind die Zeilen, die aktualisiert werden müssen:
%Vor% Um sie zu aktualisieren, ist die id -Schlüssel praktisch.
Hier ist eine Möglichkeit, wie Sie es tun könnten, vorausgesetzt, Sie haben ein BOOLEAN-Feld namens is_contiguous. Bei Bedarf ändern:
%Vor%Bearbeiten:
Ich glaube, ich habe Ihre Anforderung missverstanden. Ich dachte, du wolltest einzelne Daten markieren, die nicht zusammenhängend waren. Offensichtlich möchten Sie jedoch die Daten eines ganzen Monats als nicht zusammenhängend markieren, wenn eine bestimmte Anzahl von Tagen fehlt.
Bearbeiten 2:
Hier ist eine modifizierte Version meiner ursprünglichen (inkorrekten) Abfrage, die die verschiedenen Monate auswählt, an denen Tage fehlen:
%Vor%Tags und Links sql postgresql date-range gaps-and-islands contiguous