Lineare regression mit postgres

Benutze ich Postgres und ich habe eine große Anzahl von Zeilen mit Werten und Datums-pro station.
(Termine können getrennt werden durch mehrere Tage).

id      | value | idstation | udate
--------+-------+-----------+-----
1       |  5    | 12        | 1984-02-11 00:00:00
2       |  7    | 12        | 1984-02-17 00:00:00
3       |  8    | 12        | 1984-02-21 00:00:00
4       |  9    | 12        | 1984-02-23 00:00:00
5       |  4    | 12        | 1984-02-24 00:00:00
6       |  8    | 12        | 1984-02-28 00:00:00
7       |  9    | 14        | 1984-02-21 00:00:00
8       |  15   | 15        | 1984-02-21 00:00:00
9       |  14   | 18        | 1984-02-21 00:00:00
10      |  200  | 19        | 1984-02-21 00:00:00

Vergeben, was mag eine dumme Frage sein, aber ich bin nicht viel von einem Datenbank-guru.

Ist es möglich, direkt eine SQL-Abfrage, die berechnet lineare regression pro station für jeden Tag, wohl wissend, dass die regression werden muss, berechnen Sie nur mit der tatsächlichen id, Datum, bisherige id Datum und die next-id Datum ?

Beispielsweise die lineare regression für id 2 muss berechnen mit dem Wert 7(aktuell),5(der vorige),8(nächsten) Termine 1984-02-17 , 1984-02-11 und 1984-02-21

Bearbeiten : ich regr_intercept(Wert,udate) aber ich weiß wirklich nicht, wie zu tun, wenn ich nur die aktuellen, vorherigen und nächsten Wert/das Datum für die einzelnen Linien.

Edit2 : 3 Zeilen Hinzugefügt idstation(12); id zahlen und Daten sind geändert

Hoffe Ihr könnt mir helfen, danke !

Nicht klar; Sie verwenden möchten value als abhängige variable udate als die unabhängige variable, und die partition/Schichten durch idstation?
Ja, so ähnlich. Eigentlich habe ich Regr_intercept(udate,Wert) für jeden Tag an jeder station, aber ich brauche zur Berechnung der linearen regression mit den aktuellen, vorherigen und nächsten Tag. Ich sah, es ist ein Fehler in meinem Beispiel, die gut sein müssen : Zum Beispiel die lineare regression für id 2 muss berechnen mit dem Wert 7(aktuell),5(der vorige),8(nächsten) Termine 1984-02-23 , 1984-02-21 und 1984-02-26

InformationsquelleAutor Leasye | 2013-12-10

Dies ist die Kombination von Joop Statistiken und Denis Fenster Funktionen:

WITH num AS (
        SELECT id, idstation
        , (udate - '1984-01-01'::date) as idate -- count in dayse since jan 1984
        , value AS value
        FROM thedata
        )
        -- id + the ids of the {prev,next} records
        --  within the same idstation group
, drag AS (
        SELECT id AS center
                , LAG(id) OVER www AS prev
                , LEAD(id) OVER www AS next
        FROM thedata
        WINDOW www AS (partition by idstation ORDER BY id)
        )
        -- junction CTE between ID and its three feeders
, tri AS (
                  SELECT center AS this, center AS that FROM drag
        UNION ALL SELECT center AS this , prev AS that FROM drag
        UNION ALL SELECT center AS this , next AS that FROM drag
        )
SELECT  t.this, n.idstation
        , regr_intercept(value,idate) AS intercept
        , regr_slope(value,idate) AS slope
        , regr_r2(value,idate) AS rsq
        , regr_avgx(value,idate) AS avgx
        , regr_avgy(value,idate) AS avgy
FROM num n
JOIN tri t ON t.that = n.id
GROUP BY t.this, n.idstation
        ;

Ergebnisse:

INSERT 0 7
 this | idstation |     intercept     |       slope       |        rsq        |       avgx       |       avgy       
------+-----------+-------------------+-------------------+-------------------+------------------+------------------
    1 |        12 |               -46 |                 1 |                 1 |               52 |                6
    2 |        12 | -24.2105263157895 | 0.578947368421053 | 0.909774436090226 | 53.3333333333333 | 6.66666666666667
    3 |        12 | -10.6666666666667 | 0.333333333333333 |                 1 |             54.5 |              7.5
    4 |        14 |                   |                   |                   |               51 |                9
    5 |        15 |                   |                   |                   |               51 |               15
    6 |        18 |                   |                   |                   |               51 |               14
    7 |        19 |                   |                   |                   |               51 |              200
(7 rows)

Dem clustering der Gruppe von drei wahrscheinlich mehr getan, elegant mit einem Rang() oder row_number () - Funktion, die würde es auch erlauben, größere Schiebefenster verwendet werden.

Vielen Dank wildplasser 🙂 Deine Abfrage kombiniert sich sehr gut diejenigen, die von Denis und Joop, guter job 🙂

InformationsquelleAutor wildplasser

DROP SCHEMA zzz CASCADE;
CREATE SCHEMA zzz ;
SET search_path=zzz;

CREATE TABLE  thedata
        ( id      INTEGER NOT NULL PRIMARY KEY
        , value INTEGER NOT NULL
        , idstation  INTEGER NOT NULL
        , udate DATE NOT NULL
        );
INSERT INTO thedata(id,value,idstation,udate) VALUES
 (1      ,5   ,12       ,'1984-02-21' )
,(2      ,7   ,12       ,'1984-02-23' )
,(3      ,8   ,12       ,'1984-02-26' )
,(4      ,9   ,14       ,'1984-02-21' )
,(5      ,15  ,15       ,'1984-02-21' )
,(6      ,14  ,18       ,'1984-02-21' )
,(7      ,200 ,19       ,'1984-02-21' )
        ;

WITH a AS (
        SELECT idstation
        , (udate - '1984-01-01'::date) as idate -- count in dayse since jan 1984
        , value AS value
        FROM thedata
        )
SELECT  idstation
        , regr_intercept(value,idate)  AS intercept
        , regr_slope(value,idate)  AS slope
        , regr_r2(value,idate)  AS rsq
        , regr_avgx(value,idate)  AS avgx
        , regr_avgy(value,idate)  AS avgy
FROM a
GROUP BY idstation
        ;

Ausgabe:

 idstation |     intercept     |       slope       |        rsq        |       avgx       |       avgy       
-----------+-------------------+-------------------+-------------------+------------------+------------------
        15 |                   |                   |                   |               51 |               15
        14 |                   |                   |                   |               51 |                9
        19 |                   |                   |                   |               51 |              200
        12 | -24.2105263157895 | 0.578947368421053 | 0.909774436090226 | 53.3333333333333 | 6.66666666666667
        18 |                   |                   |                   |               51 |               14
(5 rows)

Hinweis: wenn Sie möchten, eine spline-wie regression sollten Sie auch mit der lag() und lead () - Fenster-Funktionen, wie in Denis ' s Antwort.

Ihre Antwort ist nah an der Lösung, und ich danke Ihnen für die Zeit nehmen, mir zu Antworten. Diese Lösung ist jedoch nicht vollständig, da es nicht funktioniert wenn ich zum Beispiel 6 records für idstation 12 (Sie tut regression aller Werte, anstatt nur mit 3 Werten). Sie und Denis hat mir sehr geholfen, danke nochmal 🙂 Edit : die "3 Werte", die ich verwenden muss sind Aktueller Wert vorheriger Wert und nächsten Wert.
Ja, das ist das, was ich sagte, über den spline-Sache: du wirst dich einschränken müssen, die recors Sie uns für die regression zu aktuellen und diejenigen, die vor und nach dem aktuellen Datum (gegeben die gleiche stationid) müssen Sie einige window-Funktion lag() und lead() zu tun, die, wie in Denis ' s Antwort. Könnte noch etwas pre-aggregation Schritt. Vielleicht finde ich einige Zeit später.

InformationsquelleAutor joop

0

Wenn der Durchschnitt ist ok für Sie Sie nutzen könnten, avg build-in... so Etwas wie
```
SELECT avg("value") FROM "my_table" WHERE "idstation" = 3;
```
Tun sollte. Für kompliziertere Dinge, die Sie schreiben müssen, einige pl/SQL-Funktion, die ich fürchte oder zu überprüfen, ob ein addon auf PostgreSQL.
- Dies ist nicht genau das, was ich suchte (Sie sprechen von Durchschnitt, verwendet einen Ausdruck, als Suche ich eine regression, die verwendet ein paar yx) aber danke für deine Antwort.
InformationsquelleAutor frlan

Blick in Fenster-Funktionen. Wenn ich Ihre Frage richtig, lead() und lag() dürfte Ihnen genau, was Sie wollen. Beispiel:

select idstation as idstation,
       id as curr_id,
       udate as curr_date,
       lag(id) over w as prev_id,
       lag(udate) over w as prev_date,
       lead(id) over w as next_id,
       lead(udate) over w as next_date
from dates
window w as (
partition by idstation order by udate, id
)
order by idstation, udate, id

http://www.postgresql.org/docs/current/static/tutorial-window.html

Vielen Dank Denis, ich habe noch zu sehen, wie diese Abfrage verwenden, um zu schaffen, a (Y,X) pair-Mädchen in meine reg_intersect, aber schon hat mir sehr geholfen 🙂

InformationsquelleAutor Denis de Bernardy

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.