Seite 2 von 2 ErsteErste 12
Ergebnis 11 bis 19 von 19

Thema: Ausreßertest (Grubbs-Test) in Postgresql

  1. #11
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    525

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Nun, nach dieser geistigen Steilvorlage ist mir auch eine Abfrage mit EXCEPT geglückt:
    Interessant.
    Die PG Dokumentation scheint da nicht ganz vollständig zu sein:

    Zitat Zitat von Online Handbuch
    If RECURSIVE is specified, it allows a subquery to reference itself by name. Such a subquery must have the form

    non_recursive_term
    UNION [ ALL ] recursive_term
    Aus: http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-WITH

  2. #12
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von castorp Beitrag anzeigen
    Interessant.
    Die PG Dokumentation scheint da nicht ganz vollständig zu sein:


    Aus: PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: SELECT
    Ich denke mal, das Handbuch ist an der Stelle schon okay, denn das, was ads und ich da verbrochen haben, ist alles Mögliche, vor allem ist es Unfug, aber keine rekursive Abfrage.

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #13
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Hallo an alle, zuerst möchte ich mich bei allen bedanken die am Thema teilgenommen haben. Der Vorschlag mit WITH RECURSIVE+EXCEPT habe ich ausprobiert, der geht aber nur mit konstanten Werten und ist auch nicht rekursiv.

    Der Vorschlag von symbiont hat mich ein wenig weitergebracht und habe folgende Abfrage zusammengestellt:

    SELECT set_id, id, value, min, max, mean, std, case when abs(max - mean) > abs(min - mean) then abs(max - mean)/std else abs(min - mean)/std end as pg
    FROM
    (
    SELECT set_id, id, value, min(value) OVER w as min, max(value) OVER w as max, avg(value) OVER w as mean, coalesce(stddev_samp(value) OVER w, 0) as std FROM data WINDOW w AS (PARTITION BY set_id ORDER BY value ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY value ASC
    ) as d

    Unter der Annahme dass der Ausreißer immer der größte, next größte usw. Wert der Menge ist, funktioniert das. Ich kriege es aber nicht hin, falls die Ausreißer abwechselnd min und/oder max sind. Einfacher gesagt, ich muss in WINDOW nach maximalen Abstand zum Mittelwert sortieren und ich weiß nicht wie das geht, weil dann eine aggregat. Fkt. in ORDER BY auftauchen würde. Falls einer gut mit WINDOW Klausel auskennt, könnte er mir eventuell helfen.

    Danke

    Alger

  4. #14
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Unter der Annahme dass der Ausreißer immer der größte, next größte usw. Wert der Menge ist, funktioniert das. Ich kriege es aber nicht hin, falls die Ausreißer abwechselnd min und/oder max sind. Einfacher gesagt, ich muss in WINDOW nach maximalen Abstand zum Mittelwert sortieren und ich weiß nicht wie das geht, weil dann eine aggregat. Fkt. in ORDER BY auftauchen würde. Falls einer gut mit WINDOW Klausel auskennt, könnte er mir eventuell helfen.

    Danke

    Alger
    Sollte doch einfach so gehen:

    Code:
    test=# create table data (id serial, data float);
    NOTICE:  CREATE TABLE will create implicit sequence "data_id_seq" for serial column "data.id"
    CREATE TABLE
    test=*# insert into data (data) select random() from generate_series(1,10);
    INSERT 0 10
    test=*# select id, data, avg, avg-data, abs(avg-data) as abstand from data, (select avg(data) from data) foo order by abstand desc;
     id |        data        |        avg        |      ?column?       |      abstand
    ----+--------------------+-------------------+---------------------+--------------------
      1 | 0.0543986530974507 | 0.606656908197328 |   0.552258255099878 |  0.552258255099878
      7 |  0.190013178158551 | 0.606656908197328 |   0.416643730038777 |  0.416643730038777
      5 |  0.992218289524317 | 0.606656908197328 |  -0.385561381326988 |  0.385561381326988
      8 |  0.930751285050064 | 0.606656908197328 |  -0.324094376852736 |  0.324094376852736
      6 |  0.818015911150724 | 0.606656908197328 |  -0.211359002953395 |  0.211359002953395
      4 |  0.774203835986555 | 0.606656908197328 |  -0.167546927789226 |  0.167546927789226
     10 |  0.443240758031607 | 0.606656908197328 |   0.163416150165722 |  0.163416150165722
      3 |  0.451245051342994 | 0.606656908197328 |   0.155411856854334 |  0.155411856854334
      9 |  0.706385356374085 | 0.606656908197328 | -0.0997284481767565 | 0.0997284481767565
      2 |  0.706096763256937 | 0.606656908197328 | -0.0994398550596088 | 0.0994398550596088
    (10 rows)
    Oder?

    In der letzten Spalte hast Du, von oben nach unten sortiert, nun Deine 'Ausreißer'.

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #15
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Gut,
    angenommen der Datensatz mit id=1 isr ein Ausreißer, den ich aus der Menge entferne. Jetzt soll ich den Test rekursiv für die verbleibende wiederholen. d.h. avg, ..., abstand neu rechnen... Ausreißer bestimmen ...usw. bis es am Ende keine Ausreißer in der Restmenge gibt. Und gerade diese rekursivität schaffe ich nicht in einer SQL-Abfrage unterzubringen.

  6. #16
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard

    also ich sagte ja schon - man muss nicht alles auf einmal machen

    ich wuerde 2 table expressions nehmen:


    Code:
    -- mit der 1. berechnest du fuer alle diesen grubbs wert G
    WITH grubbs(id, set_id, grubbs) AS (
       .... hier das select mit den windowing-functions, welches dir für ALLE den wert grubbs berechnet 
       ... was ??? wahrscheinlich ??? nicht geht -> SELECT <grubbs> FROM <set_id> WHERE id NOT IN ausreisser
    ),
    -- hier nun die 2. recursive, welche eine tabelle darstellt die NUR ausreisser enthaelt - im prinzip added die rekursion jeden ausreisser aus grubbs hinzu - UNION ALL
    ausreisser(id) AS (
       SELECT id FROM grubbs
       UNION ALL
       SELECT id FROM grubbs ... 
       WHERE id NOT IN ausreisser 
    )
    -- und nun kannst du die "Tabelle" ausreisser dazu verwenden irgendwelche Datensätze woanders zu markieren 
    SELECT i.*, a.id IS NOT NULL AS "das_ist_ein_ausreisser"
    FROM irgendwas i
    LEFT JOIN ausreissser a ON a.id = i.id
    ah ich glaub das funzt net, weil du auch grubbs nach dem aussortieren nur ueber die verbleibende menge berechnen darfst?

    da waere natuerlich cool, wenn die table-expressions sich gegenseitig referenzieren koennen .. ich glaube aber genau das geht nicht!
    Geändert von symbiont (16.12.2009 um 17:28 Uhr)

  7. #17
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Gut,
    angenommen der Datensatz mit id=1 isr ein Ausreißer, den ich aus der Menge entferne. Jetzt soll ich den Test rekursiv für die verbleibende wiederholen. d.h. avg, ..., abstand neu rechnen... Ausreißer bestimmen ...usw. bis es am Ende keine Ausreißer in der Restmenge gibt. Und gerade diese rekursivität schaffe ich nicht in einer SQL-Abfrage unterzubringen.
    Für eine Rekursion brauchst Du eine Abbruchbedingung, andernfalls hättest Du bei Deinem Plan oben als Resultat eine komplett leere Tabelle.

    So, ich hab mal was gebastelt.

    Code:
    test=# create table daten (id serial primary key, d float);NOTICE:  CREATE TABLE will create implicit sequence "daten_id_seq" for serial column "daten.id"
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "daten_pkey" for table "daten"  
    CREATE TABLE                                                                                   
    Zeit: 5,412 ms                                                                                 
    test=*# insert into daten (d) select random() from generate_Series(1,100);INSERT 0 100                                                              
    Zeit: 1,266 ms                                                            
                                                             
    
    test=*# create or replace function del_ausreiser(float) returns bool as $$begin loop delete from daten where id in (select id from (select *, abs(d-avg) as abstand from daten, (select avg(d) from daten) foo order by abstand desc limit 1) bar where abstand > $1); if not found then return true; end if; end loop; end; $$ language plpgsql;
    CREATE FUNCTION
    Zeit: 0,678 ms
    test=*# select * from del_ausreiser(0.3);
     del_ausreiser
    ---------------
     t
    (1 Zeile)
    
    Zeit: 6,166 ms
    test=*# select count(*) from daten;
     count
    -------
        71
    (1 Zeile)
    
    Zeit: 0,288 ms
    Ich lösche also immer einen, solange dieser mehr als 0.3 vom Durchschnitt abweicht. Ob dies der exakte Rechenweg ist bezweifle ich aber. Jedenfalls - es bleiben 71 von 100 Datensätze übrig.

    Code:
    test=*# select 'Minimum', min(d) from daten group by 1 union select 'Maximum', max(d) from daten group by 1;
     ?column? |        min
    ----------+--------------------
     Maximum  |  0.977650795597583
     Minimum  | 0.0283374600112438
    (2 Zeilen)
    
    Zeit: 0,488 ms
    test=*# select * from del_ausreiser(0.3);
     del_ausreiser
    ---------------
     t
    (1 Zeile)
    
    Zeit: 6,225 ms
    test=*# select 'Minimum', min(d) from daten group by 1 union select 'Maximum', max(d) from daten group by 1;
     ?column? |        min
    ----------+-------------------
     Maximum  | 0.853486565407366
     Minimum  | 0.286866198293865
    (2 Zeilen)

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  8. #18
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard

    ich befürchte fast, das problem laesst sich so nicht wirklich loesen

    das problem am problem ist naemlich, dass man hier elemente entfernen muss - und dann mit der restmenge in der rekursion weiterarbeiten

    die recursiven queries in pg erlauben aber
    1. nur das hinzuefuegen von elementen ... daher dachte ich zunaechst man koennte ja ausreisser "hinzufuegen" ... dazu benoetigt man aber
    2. eine subquery auf ausreisser um den naechsten ausreisser zu ermitteln .. das geht aber leider auch (noch??) nicht:

    FEHLER: rekursiver Verweis auf Anfrage »ausreisser« darf nicht in einer Unteranfrage erscheinen
    LINE 12: where not exists (select a.id from ausreisser a where a.se...

    hier mein (auch falscher und nicht funktionierender) ansatz
    Code:
    with recursive ausreisser (id, set_id, Xn, Sn) as (
     
      select w.id, w.set_id, w.x as Xn, w.x as Sn 
      from werte w where false
      
      union all 
      
      select w.id, w.set_id, 
    	 abs(w.x - avg(w.x) OVER (PARTITION BY w.set_id)) Xn, 
    	 variance(x) OVER (PARTITION BY w.set_id) as Sn 
      from werte w
      where not exists (select a.id from ausreisser a where a.set_id = w.set_id)
    )
    
    select  * from ausreisser
    die aufgabe ist aber wirklich eine schoene

    für das verfahren muss eine menge verringert (restmenge) und eine andere vergroessert (ausreisser) werden und in jedem schritt muss man noch ein max() ermitteln ..
    das braucht viele dinge die pg in dem zusammenhang glaube ich nicht einfach so kann
    Geändert von symbiont (16.12.2009 um 18:42 Uhr)

  9. #19
    symbiont ist offline Erfahrener Benutzer
    Registriert seit
    17.03.2008
    Ort
    NULL
    Beiträge
    1.320

    Standard Neues vom Ausreisser

    Also ich bin zu dem Schluss gekommen, dass sich das Problem (leider) nicht bzw. nicht wirklich schoen mit einer einfachen Abfrage lösen läßt.
    Daher hier ein neuer Vorschlag:

    Eine stable funktion - weil die für jedes set (gleiche parameter-werte id_set und Gn) nur einmal ausgefuehrt wird - lassen wir den grubbs iterieren:
    Code:
    CREATE TABLE grubbs.werte (
      id serial not null primary key,
      set_id int  not null,
      x double precision not null
    ) WITH (OIDS=FALSE);
    
    insert into grubbs.werte VALUES 
    (DEFAULT, 1, 0.5), 
    (DEFAULT, 1, 0.7), 
    (DEFAULT, 2, 1.0); 
    
    CREATE OR REPLACE FUNCTION grubbs.test(test_set_id integer, g double precision) 
        RETURNS SETOF grubbs.werte AS $$
    
    DECLARE
      ausreisser int[];
      row_grubbs RECORD;
    BEGIN
         
         -- array allociieren
         -- wie allociiert man eigentlich ein array mit laenge 0?
         ausreisser = ARRAY[0];
    
         LOOP 
          -- den top-ausreisser ermitteln
          SELECT id, set_id, x, Xn / Sn as Gn FROM (
            -- kennzahlen
            SELECT id, set_id, x,
               abs(x - avg(x) OVER (PARTITION BY set_id)) Xn, 
               variance(x) OVER (PARTITION BY set_id) as Sn 
            FROM grubbs.werte w
            WHERE w.set_id = test_set_id
              AND NOT (w.id = ANY(ausreisser)) 
            ) AS kennzahlen 
          ORDER BY Xn DESC LIMIT 1 -- max(abs(Xn - X) ... )
          INTO row_grubbs; 
              
          IF row_grubbs.Gn >= g THEN
    
            -- ausreisser aussortieren
            ausreisser = ausreisser || row_grubbs.id;
    
            RAISE NOTICE 'aktuelle ausreisser %', ausreisser;
    
            -- spalten anzahl anpassen 
            --     darf man leider nicht direkt hinter RETURN NEXT!
            -- ausreisser zurückliefern
            row_grubbs = ROW(row_grubbs.id, row_grubbs.set_id, row_grubbs.x);
            RETURN NEXT row_grubbs;
          ELSE
            -- keine weiteren ausreisser    
            RETURN;
          END IF;
         END LOOP;
      
    END;
    $$ LANGUAGE plpgsql STABLE;
    
    select w.*, (SELECT w.id IN (SELECT id FROM grubbs.test(w.set_id, 1))) as "AUSREISSER111!!!1"
    from grubbs.werte w
    liefert dann z.B:
    Code:
    id    set_id    x    AUSREISSER111!!!1
    -----------------------------------------
    1;    1;    0.5;    t
    2;    1;    0.7;    f
    3;    2;    1;    f
    Man koennte natuerlich auch nur einen Vector von IDs liefern lassen, dann schauen später die abfragen nicht so strange aus

    und der parameter G ist auch ueberfluessig - den müsste man ja eigentlich in der fuktion aus dem set berechnen koennen?!
    Geändert von symbiont (17.12.2009 um 10:46 Uhr)

Seite 2 von 2 ErsteErste 12

Ähnliche Themen

  1. [News]: Continuent Announces Database Performance Test Tool
    Von Xandrian im Forum Neuigkeiten & Ankündigungen
    Antworten: 0
    Letzter Beitrag: 04.12.2007, 21:46
  2. Antworten: 12
    Letzter Beitrag: 13.08.2007, 14:20
  3. [News]: Automated Test Data Generation for PostgreSQL
    Von Xandrian im Forum Neuigkeiten & Ankündigungen
    Antworten: 0
    Letzter Beitrag: 06.05.2007, 20:54
  4. [News]: Time to Test Version 8.2 Beta 2
    Von Xandrian im Forum Neuigkeiten & Ankündigungen
    Antworten: 0
    Letzter Beitrag: 26.10.2006, 23:30

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum