Durchlaufen PostgreSQL-Datensätze. Wie Referenzdaten aus der nächsten Zeile?

Ich bin neu in PostgreSQL und schreiben von Funktionen ist hier knallhart. So ich hoffe jemand kann helfen, lassen Sie mich wissen, wie zu tun, was ich versuche zu tun.

Habe ich eine Tabelle Kurse und Termine. Ich will berechnen Sie die prozentuale Veränderung gegenüber dem vorherigen Tag für jeden Eintrag. Für die früheste Tag Daten gibt es nicht einen Tag zuvor, so dass Eintrag kann einfach Null. Kann jemand sich über meine Funktion und helfen mir mit

a) wie Referenz-Daten aus der nächsten Zeile und

b) mir helfen, es sauber?

Ich bin mir bewusst, dass die WITH Aussage ist wohl nicht, soll über die IF - Anweisung. Aber logisch, das ist, wie ich gedacht habe, über das es bisher und so, wie ich geschrieben habe. Wenn Sie beraten könnte, wie das Aussehen soll, es wäre sehr geschätzt werden, wie gut.

CREATE FUNCTION percentage_change_func(asset_histories) RETURNS 
numeric LANGUAGE sql IMMUTABLE AS $func$

DECLARE
r asset_histories%rowtype
BEGIN   
WITH twodaysdata AS (SELECT date,price,asset_symbol FROM asset_histories 
           WHERE asset_symbol = $1.asset_symbol 
           AND asset_histories.date <= $1.date 
           ORDER BY date DESC LIMIT 2), 
         numberofrecords AS (SELECT count(*) FROM twodaysdata) 

IF numberofrecords = 2 THEN
        RETURN r.price / (r+1).price - 1  <---How do I reference r + 1??/
ELSE
        RETURN NIL
ENDIF
END
$func$

PostgreSQL 9.2.

InformationsquelleAutor Terence Chow | 2013-03-30

  1. 8

    Möchte ich berechnen Sie die prozentuale Veränderung gegenüber dem vorherigen Tag, für jede
    Eintrag.

    In der Regel müssen Sie Studie die Grundlagen, bevor Sie beginnen, Fragen zu stellen.

    Lesen Sie die ausgezeichneten Handbuch über CREATE FUNCTION, PL/pgSQL und SQL-Funktionen.

    Wichtigsten Punkte, warum das Beispiel ist Unsinn

    • Ersten, können Sie nicht hand in eine identifier wie Sie tun. Bezeichner dürfen nicht parametriert werden, das in plain-SQL. Sie müssten dynamisches SQL.

      Natürlich, Sie nicht wirklich benötigen, die nach Ihren Anforderungen. Es ist nur eine Tabelle beteiligt. Es ist Unsinn, zu versuchen und zu parametrisieren.

    • Nicht verwenden, geben Sie Namen als Bezeichner. Ich benutze _date statt date als parameter name und umbenannt in Ihrer Tabelle Spalte asset_date. ALTER Ihre Tabellendefinition entsprechend.

    • Einer Funktion abrufen von Daten aus einer Tabelle kann nie IMMUTABLE. Lesen Sie die Bedienungsanleitung.

    • Mischen Sie die SQL-syntax mit plpgsql Elemente in unsinniger Weise. WITH ist Teil eines SELECT - Anweisung und kann nicht gemischt werden mit plpgsql Kontrollstrukturen wie LOOP oder IF.

    Ordnungsgemäße Funktion

    Eine einwandfreie Funktion könnte so Aussehen (nur eine von vielen Möglichkeiten):

    CREATE FUNCTION percentage_change_func(_asset_symbol text)
  RETURNS TABLE(asset_date date, price numeric, pct_change numeric) AS
$func$
DECLARE
   last_price numeric;
BEGIN

FOR asset_date, price IN
   SELECT a.asset_date, a.price
   FROM   asset_histories a
   WHERE  a.asset_symbol = _asset_symbol 
   ORDER  BY a.asset_date  -- traverse ascending
LOOP
   pct_change := price / last_price; -- NULL if last_price is NULL
   RETURN NEXT;
   last_price := price;
END LOOP;

END
$func$ LANGUAGE plpgsql STABLE

    Leistung sollte nicht so schlimm sein, aber es ist einfach sinnlos Komplikation.

    Richtige Lösung: einfache Abfrage

    Die einfachste (und vermutlich auch schnellsten) Weg wäre, mit der Fenster-Funktion lag():

    SELECT asset_date, price
      ,price / lag(price) OVER (ORDER BY asset_date) AS pct_change
FROM   asset_histories
WHERE  asset_symbol = _asset_symbol 
ORDER  BY asset_date;

    Standardabweichung

    Gemäß Ihrer späteren Kommentar, den Sie berechnen möchten statistische zahlen wie die Standardabweichung.

    Es gibt engagierte Aggregatfunktionen für Statistik in PostgreSQL.

    vielen Dank für das zeigen der Verwendung von lag(), nicht bekannt war
    Fenster-Funktionen sind ziemlich vielseitige Werkzeuge.
    Also window-Funktionen funktionieren nicht in einer Ansicht aufrufen ein Aggregat? zB. Create or Replace View tmp AS Select timestmp, sum(Preis) ALS this_wk_price, lag(this_wk_price,1) from... group by ... order by ... ;
    Nicht sicher, wie würden Sie folgern, dass aus meiner Antwort. Es ist möglich, kombinieren Sie Fenster-und Aggregat-Funktionen in der gleichen WÄHLEN. Bitte Fragen Sie eine tatsächliche Frage von bietet den notwendigen Kontext für Ihre Frage.
    Danke @ErwinBrandstetter - nichts zu tun mit Ihrer Antwort nur den Bau mehr info als noch nicht benutzt, lag vor wegen unserer server-version älter. Einer meiner tests lief in ein Problem, aber Sie bekommen es sortiert.

    InformationsquelleAutor Erwin Brandstetter

  2. 0

    Einfache Dinge, wie einfach die Berechnung der per_change getan werden kann, innerhalb einer view statt, würde dies auch dazu führen, schneller Ergebnisse

    create view view_stock_details AS ( SELECT 
    date, 
    price, 
    symbol, 
    pervious_day_close, 
    (price-pervious_day_close)/pervious_day_close*100 as per_change 
FROM (
    SELECT
        date,
        price,
        symbol,
        ( SELECT price FROM asset_histories t WHERE t.symbol = outers.symbol AND t.date < outers.date limit 1 ) as pervious_day_close
    FROM 
        asset_histories as outers
    );

    Anzeigen der Lager Einzelheiten, die Sie dann verwenden können,

    SELECT 
    *
FROM
    view_stock_details
WHERE
    date = '2012-01-03' 
    AND symbol = 'A'
    Der eigentliche Zweck des Erhaltens Prozent änderung zu finden, die Standardabweichung der prozentualen Veränderungen über die letzten 20 Einträge. Ich plan auf die Darstellung der Standardabweichung in einem Diagramm. Deshalb habe ich create function so dass ich später Abfragen schreiben, die die Berechnung aus, dass die neue Spalte. Wäre es besser zu schreiben, ein Blick für std dev mithilfe einer Unterabfrage, die wie Ihr, calcates percent_changes? Es scheint so, dass wäre schrecklich kompliziert. Auch, mit meinen Bedürfnissen im Hinterkopf, was würdest du sagen, ist der Schnellste Weg, um Daten für den Benutzer? Eine Funktion, die vorberechneten Wert oder eine view?
    Natürlich, dies sollte in der Frage. Machen Sie nicht die Menschen für Sie arbeiten, nur um zu zeigen Sie Ihre tatsächlichen Absichten später.
    Sorry, über eine Sache zu tun und dann in die andere. Dachte ich 1 Schritt auf einmal und nicht erkennen, es war eine Möglichkeit, beide Schritte in einem. Ich werde sicherstellen, dass ich bin klarer in die Zukunft und danke für all Eure Hilfe vor kurzem!

    InformationsquelleAutor Akash

Schreibe einen Kommentar