AVG OVER ORDER BY

[IchHH]

Hallo ich versuche gerade eine Lösung für einen Rolling Average zu erarbeiten und scheitere gerade an folgenden Problem.

Mittels

,ROUND(AVG([Wert in Hauswährung]) OVER (PARTITION BY Materialnummer, Kontonummer, Werk ORDER BY Buchungsdatum ROWS BETWEEN 365 PRECEDING AND CURRENT ROW),2) AS Rolling_AVG

erreiche ich es zwar, dass er die letzten 365 Zeilen vor der aktuellen ermittelt, nur leider eben nicht das es exakt ein Jahr ist, da ein Buchungsdatum natürlich mehrfach vorkommen kann. Dabei ist eine Aggregation keine Lösung das es bewußt unterschiedliche Belegarten geben kann die eben mehr als nur ein Buchungsdatum pro Tag zur Folge haben.

Versuche wie....

...DATEADD(year,-1,Buchungsdatum) AND Buchungsdatum..
...DATEDIFF(day,Buchungsdatum,DATEADD(year,-1,Buchungsdatum))...
...usw. haben nicht geholfen. Eine Where Bedingung innerhalb der Window Funktion läuft auch nicht... ich weiß einfach nicht weiter und hoffe auf Tips von euch.

 

[dabadepdu]

Wenn Du nicht auf die Anzahl der Zeilen aus bist, weil das ja statisch wäre, Dein Fenster aber am Datum hängt, dann versuch mal
.. RANGE BETWEEN INTERVAL '365 DAY' statt rows

 

[IchHH]

Coole Idee die leider nicht funktioniert hat. Er meldet "Incorrect Syntax near '365 DAY'"

 

[dabadepdu]

Mh, dann schau dir mal bitte die Syntax dafür bei SQL Server an. Ich nutze das nicht und hab keinen verfügbar.

 

[IchHH]

Interval scheint richtig zu sein, aber annehmen tut er es nicht.

 

[akretschmer]

postgres=# create table ichhh(d date, val int);
CREATE TABLE
postgres=# select avg(val) over (order by d range between interval '10 days' preceding and current row) from ichhh ;
 avg 
-----
(0 rows)

postgres=#

 

[IchHH]

Es tut mir leider, aber er liefert bei mir nur eine Fehlermeldung welche lautet. "Incorrect syntax near '10 days'."
Ich habe echt keine Ahnung warum es bei euch läuft.

 

[IchHH]

Offensichtlich kann das die Azure SQL nicht.

 

[dabadepdu]

Ich habe echt keine Ahnung warum es bei euch läuft.

Also ich habe nie gesagt, dass es bei mir läuft, im Gegenteil.
Was @akretschmer gezeigt hat, ist ja nicht sql server sondern postgres, wie man sehen kann.

Das Seltsame ist, dass ich vor meinem Post kurz geschaut hab, ob range und interval für SQL Server geht und hatte auch n Treffer, aber den find ich nicht mehr. Interval ist für postgres ganz normal auch an anderen Stellen, wo SQL Server z.B: mit datadif arbeitet.
Aber ist ja auch wurscht, die Lösung steht ja in dem Diagramm. Datum in Zahl konvertieren.

 

[akretschmer]

Aber ist ja auch wurscht, die Lösung steht

... könnte auch sein, zu PostgreSQL zu migrieren. Wir haben passende Tools dafür, ist täglich Brot für unsere Consultants ;-)

 

[dabadepdu]

Lösung.. könnte auch sein, zu PostgreSQL zu migrieren

Ach komm schon .. wieso? Nur weil es diese Funktionen hat und nichts kostet?
Und schließlich investiert Microsoft jetzt 3 Mrd in Deutschland, da kann man auch mal n bisschen Dankbarkeit zeigen.

Aber mal ernst.

Offensichtlich kann das die Azure SQL nicht

Man kann es in der Syntax Referenz tatsächlich nicht finden. Die "Mühe" hatte ich mir bei meinem Vorschlag tatsächlich nicht gemacht. Dazu sind solche Webseiten aber natürlich gut, macht schlauer, ist höchstwahrscheinlich am zuverlässigsten und spart Zeit.

OVER clause (Transact-SQL) - SQL Server

Transact-SQL reference for the OVER clause, which defines a user-specified set of rows within a query result set.

learn.microsoft.com

oder

OVER clause (Transact-SQL) - SQL Server

Transact-SQL reference for the OVER clause, which defines a user-specified set of rows within a query result set.

learn.microsoft.com

 

[akretschmer]

Ach komm schon .. wieso? Nur weil es diese Funktionen hat und nichts kostet?

Nein, weil ich im Sales bin und Provision bekommen möchte ;-)

 

[dabadepdu]

Nein, weil ich im Sales bin und Provision bekommen möchte ;-)

Ach Scheiße Mann! Und ich dachte immer, Du bist von dem Produkt überzeugt! ;-)

 

[akretschmer]

das sowieso ;-)

 

[IchHH]

Aber ist ja auch wurscht, die Lösung steht ja in dem Diagramm. Datum in Zahl konvertieren.

Sorry das ist aber keine Lösung, aus verschiedenen Gründen.
1. Ich kann nicht in den Between eine Variable einfügen um z.B. zu sagen zähle Buchungsdatum-365.
2. Ein Fester Wert z.B. 365 (was evtl. für ein Jahr gelten könnte), nicht klappt, da ich sehr häufig auch mehr als nur eine Buchung und einem Tag habe und diese nicht aggregieren kann.
3. Eine Umformartierung in einer Zahl akzeptiert der Code an der Stelle ebenfalls nicht, ebenso wenig wie nur eine Zahl, wenn ich mit Range arbeite