--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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
Sollte doch einfach so gehen:
Oder?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)
In der letzten Spalte hast Du, von oben nach unten sortiert, nun Deine 'Ausreißer'.
Andreas
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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.
also ich sagte ja schon - man muss nicht alles auf einmal machen
ich wuerde 2 table expressions nehmen:
ah ich glaub das funzt net, weil du auch grubbs nach dem aussortieren nur ueber die verbleibende menge berechnen darfst?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
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)
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.
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=# 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
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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
die aufgabe ist aber wirklich eine schoeneCode: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
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)
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:
liefert dann z.B: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
Man koennte natuerlich auch nur einen Vector von IDs liefern lassen, dann schauen später die abfragen nicht so strange ausCode:id set_id x AUSREISSER111!!!1 ----------------------------------------- 1; 1; 0.5; t 2; 1; 0.7; f 3; 2; 1; f
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)
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen