Gleitender Durchschnitt in postgresql

Ich habe folgende Tabelle in meiner Postgresql-9.1 Datenbank:

select * from ro;
date       |  shop_id | amount 
-----------+----------+--------
2013-02-07 |     1001 |      3
2013-01-31 |     1001 |      2
2013-01-24 |     1001 |      1
2013-01-17 |     1001 |      5
2013-02-10 |     1001 |     10
2013-02-03 |     1001 |      4
2012-12-27 |     1001 |      6
2012-12-20 |     1001 |      8
2012-12-13 |     1001 |      4
2012-12-06 |     1001 |      3
2012-10-29 |     1001 |      3

Ich versuche ein gleitender-Mittelwert-Vergleich der Daten gegen die letzten 3 Donnerstage ohne die aktuellen Donnerstag. Hier ist meine Abfrage:

select date, shop_id, amount, extract(dow from date),
avg(amount) OVER (PARTITION BY extract(dow from date) ORDER BY date DESC
                      ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING)                          
from ro
where extract(dow from date) = 4

Dies ist das Ergebnis

date       |  shop_id | amount | date_part |        avg         
-----------+----------+--------+-----------+--------------------
2013-02-07 |     1001 |      3 |         4 | 2.0000000000000000
2013-01-31 |     1001 |      2 |         4 | 2.6666666666666667
2013-01-24 |     1001 |      1 |         4 | 4.0000000000000000
2013-01-17 |     1001 |      5 |         4 | 6.3333333333333333
2012-12-27 |     1001 |      6 |         4 | 6.0000000000000000
2012-12-20 |     1001 |      8 |         4 | 5.0000000000000000
2012-12-13 |     1001 |      4 |         4 | 3.5000000000000000
2012-12-06 |     1001 |      3 |         4 | 3.0000000000000000

Erwarte ich

date       |  shop_id | amount | date_part |        avg         
-----------+----------+--------+-----------+--------------------
2013-02-07 |     1001 |      3 |         4 | 2.6666666666666667
2013-01-31 |     1001 |      2 |         4 | 4.0000000000000000
2013-01-24 |     1001 |      1 |         4 | 6.3333333333333333
2013-01-17 |     1001 |      5 |         4 | 6.0000000000000000
2012-12-27 |     1001 |      6 |         4 | 5.0000000000000000
2012-12-20 |     1001 |      8 |         4 |
2012-12-13 |     1001 |      4 |         4 |
2012-12-06 |     1001 |      3 |         4 |

+1 gute Frage - Pg-version, sample-Daten, die erwarteten Ergebnisse. Danke! Umgerechnet auf SQLFiddle hier: sqlfiddle.com/#!1/18891/1
BTW, "Datum" eine schreckliche Spalte name, da es der name eines Datentyps. Vermeiden Sie es. Wenn Sie es verwenden müssen, immer zu qualifizieren mit der alias-Tabelle und doppelklicken Sie nennen es, wie hier gezeigt: sqlfiddle.com/#!1/18891/4
Dank Craig 🙂 Dies ist nur ein Beispiel-dataset aus einer sehr großen Tabelle. Ich möchte nur, um die Abfrage richtig.

InformationsquelleAutor Glicious | 2013-02-07

15

SQL Fiddle
```
select
    "date",
    shop_id,
    amount,
    extract(dow from date),
    case when
        row_number() over (order by date) > 3
        then
            avg(amount) OVER (
                ORDER BY date DESC
                ROWS BETWEEN 1 following AND 3 FOLLOWING
            )
        else null end
from (
    select *
    from ro
    where extract(dow from date) = 4
) s
```
Was ist Los mit der OP die Abfrage der Rahmen Spezifikation:
```
ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING
```
Anderes, als dass meine Abfrage vermeidet unnötige computing durch die Filterung donnerstags, bevor die teuren Fenster-Funktionen.

Wenn es notwendig ist, auf partition shop_id offensichtlich dann fügen Sie die partition by shop_id für beide Funktionen, avg und row_number.
- Zwar scheint das Verhalten in Ordnung, es könnte sich lohnen, zu erklären, was falsch war mit der alten; warum es fehlgeschlagen ist. Das wird der OP und andere lernen, nicht nur ein unmittelbares problem zu beheben
- Dank Clodoalo 🙂 Es scheint mir, dass die windowing-Funktion, die ich verwendet, ZEILEN ZWISCHEN 0 VORANGEHENDEN UND 2 FOLGENDEN soll wurden ZEILEN ZWISCHEN 1 UND 3 FOLGENDE Macht durchaus Sinn! Ich werde versuchen, diese auf meinem größeren Datensatz in ein paar Stunden und beraten 🙂 vielen Dank nochmal!
InformationsquelleAutor Clodoaldo Neto
6

Ich denke, eine bessere Antwort könnte sein:
```
SELECT date, shop_id, amount, 
    extract(dow from date) AS dow,
    CASE WHEN count(amount) OVER w = 3 
        THEN avg(amount) OVER w END AS average_amt             
FROM ro
WHERE extract(dow from date) = 4 
WINDOW w AS (ORDER BY date DESC ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING)
```
Ich denke, es ist sauberer, um das gleiche Fenster nutzen sowohl für die überprüfung der Anzahl der Zeilen im Fenster und unter dem Durchschnitt. (Das spart auch zwei Fenster Aggregationen, wie in der ursprünglichen Antwort.)

Bezug auf den Anspruch in der früheren Antwort, dass "meine Abfrage vermeidet unnötige computing durch die Filterung donnerstags, bevor die teuren Fenster-Funktionen", dies gilt auch für die query vorgeschlagen, durch die OP und auf meine Anfrage, Anhängen EXPLAIN entweder zeigt.
- Das war so eine alte post, aber danke für deinen Lösungsvorschlag. 🙂
InformationsquelleAutor Ian Gow

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.