Interpolation mit SQL

[Archimos]

Hey Leute,
ich habe eine sehr komplizierte Frage. Ich habe auch leider wenig Ahnung von Microsoft SQL Mangement Studio, weshalb wenn ihr eine Lösung habt es mir bitte ausführlich erklären müsst (danke schon mal dafür im Vorraus!)
Es geht um Winddaten um ca. 4Millionen. Davon ist in einer Spalte der Zeitstempel nach dem Muster:2014-06-05 06:00:14.000 (Datum Uhrzeit) angeben. In der anderen spalte die Windgeschwindigkeit zu dem Zeitpunkt.
Jetzt möchte ich, da ich nicht für jeden Zeitpunkt einen Messwerte habe, dazwischen Interpoliert wird. Und zwar so, dass ich zu jeder Minute einen Windwert habe. Die Interpolation kann einfach (linear) erfolgen.
Habt ihr Ideen? Ich hab das auch schon mit Excel versucht, aber bislang keinen Weg gefunden!
Gruß

 

[Hony%]

Hi Archimos.

Zuerst benötigst du dazu ein geeignetes Zeitraster.

Allerdings wären es nützlich wenn du uns noch ein paar Informationen dazu gibst, wie du genau interpolieren willst. Linear zwischen 06:02:43 und 06:03:01 würde per Interpolation 1,408… für 06:03:00 ergeben.

Gruß
Hony

 

[akretschmer]

Hey Leute,
ich habe eine sehr komplizierte Frage. Ich habe auch leider wenig Ahnung von Microsoft SQL Mangement Studio, weshalb wenn ihr eine Lösung habt es mir bitte ausführlich erklären müsst (danke schon mal dafür im Vorraus!)
Es geht um Winddaten um ca. 4Millionen. Davon ist in einer Spalte der Zeitstempel nach dem Muster:2014-06-05 06:00:14.000 (Datum Uhrzeit) angeben. In der anderen spalte die Windgeschwindigkeit zu dem Zeitpunkt.
Jetzt möchte ich, da ich nicht für jeden Zeitpunkt einen Messwerte habe, dazwischen Interpoliert wird. Und zwar so, dass ich zu jeder Minute einen Windwert habe. Die Interpolation kann einfach (linear) erfolgen.

Via lag() den Wert zu vorher holen, Zeitdifferenz berechnen und damit die Werte interpolieren. Hab grad wenig Zeit das als SQL auszuformulieren, außerdem verwende ich eine andere DB. Aber das sollte nicht allzu schwer sein...

 

[akretschmer]

Via lag() den Wert zu vorher holen, Zeitdifferenz berechnen und damit die Werte interpolieren. Hab grad wenig Zeit das als SQL auszuformulieren, außerdem verwende ich eine andere DB. Aber das sollte nicht allzu schwer sein...

Davon abgesehen wäre es vielleicht sinnvoller, über ein Fenster einen Durchschnitt zu berechnen.

 

[Archimos]

Hallo Hony%,
okay ich erstelle dann zuerst mal ein Zeitraster....
könnte man es so machen das ich für jede Minute einen Wert habe? sprich alle Werte die in einer Minute sind Summieren und den Mittelwert bilden? Und wenn keine Werte zu einer bestimmten Minute existieren wird Interpoliert? geht das?
gruß

 

[ukulele]

Das geht Minuten basiert, aber über wieviele Tage willst du das anwenden?

Zählen für das Interpolieren immer nur der nächst niedrigere und der nächst höhere Wert oder muss der Durchschnitt aus mehr als 2 Werten gebildet werden?

Grade für den Einsatz von lag() wäre die MSSQL Server Version wichtig zu wissen, das geht nämlich erst ab SQL 2012.

 

[Archimos]

Hallo ukulele,
ich benutze SQL 2012.
Theoretisch muss zuerst der Mittelwert aus allen Minuten Werten gebildet werden die gleich sind. Im zweiten Schritt soll dann interpoliert werden wenn Minutenwerte fehlen.
gruß

 

[Lukas987]

GROUP BY (DATEPART(MINUTE,[Datum])%1) glaub ich, musst du mal ausprobieren.

 

[Lukas987]

DATEPART(mi, [Datum]) kann es auch sein, bin mir nicht sicher. Kann leider gerade nicht nachschauen...

 

[Archimos]

was machen die Befehle? Könntest du mir das vielleicht erklären?

 

[ukulele]

Das hier könnte zunächst mal deine Messwerte in Minutenblöcken ausgeben:

-- CTE Tabelle um ein Zeitraster zu erzeugen
WITH raster(zeit_von,zeit_bis) AS (
SELECT    cast('00:00' AS TIME),
        cast('00:00:59.9999999' AS TIME)
UNION ALL
SELECT    dateadd(mi,1,zeit_von),
        dateadd(mi,1,zeit_bis)
FROM    raster
WHERE    zeit_von <= cast('23:58' AS TIME)
)
-- Ende CTE Tabelle
SELECT    cast(t.zeitstempel AS DATE) AS datum,
        r.zeit_von,
        r.zeit_bis,
        avg(t.messwert) AS anzahl
FROM    raster r
LEFT JOIN tabelle t --deine Tabelle hier
ON        cast(t.zeitstempel AS TIME) BETWEEN r.zeit_von AND r.zeit_bis
WHERE    cast(t.zeitstempel AS DATE) = cast(getdate() AS DATE)
GROUP BY cast(t.zeitstempel AS DATE),r.zeit_von,r.zeit_bis
OPTION (MAXRECURSION 2000)

Das würde ich mir eventuell in Form einer View anlegen und im Anschluss erst in vereinfachter Form fehlende Werte zu errechnen.

 

[Archimos]

okay ich werde es mal ausprobieren. Was bedeutet in einer View anlegen? Wie würdest du dann die fehlenden Werte errechnen?

 

[ukulele]

Was bedeutet in einer View anlegen?

Du kannst Views (Sichten) anlegen die dann einen Select nach Außen wie eine Tabelle darstellen auf die man bequem mit SELECT * FROM sicht zugreifen kann. Im Prinzip eine Abstraktionsschicht um den Code zu vereinfachen.

Wäre ganz gut wenn der Code (siehe letzter Post) auch lauffähig wäre. Bitte mal testen.

okay ich werde es mal ausprobieren. Was bedeutet in einer View anlegen? Wie würdest du dann die fehlenden Werte errechnen?

Ich würde dann die View zweimal mit sich selbst Joinen um die beiden nächstgelegenen Werte zu finden. Allerdings sehe ich noch ein Problem mit den bestehenden Werten (hinter avg(t.messwert) AS sollte natürlich nicht "anzahl" sondern "wert" oder etwas ähnliches als Alias stehen). avg() ermittelt ja zu jedem Zeitinterval einen Durchschnitt, leider werden NULL-Werte so eleminiert. Jetzt kann ich im Nachhinein gar nicht feststellen wann der Messwert NULL oder wann er 0 ist. Ich werd mir das nachher nochmal anschauen.

 

[Archimos]

hey,
ich kann das erst wieder am Mittwoch überprüfen! aber danke für den Lösungsvorschlag
Ich meld mich wieder am Mittwoch ob es geklappt oder nicht.

 

[Archimos]

Hallo Ukulele,
ich hab es getestet und mir wurde ein Fehler angezeigt. Kannst du damit was anfangen?
Parameter:
Tabelle= object_id169
Zeitstempel= TIM
Messwerte= VAL