interpolationen berechnen

Hallo nochmal Forum,
Leider habe ich schon wieder ein Problem bei dem ich anstehe. Es geht diesmal darum Interpolierte Werte zu berechnen und in eine Tabelle zu schreiben.

Ich habe meine Tabelle mit

raumeinheit | jahr | datenwert | interpoliert
301 | 1980 | 10 | false
301 | 1981 | 15 | true
301 | 1982 | 20 | false
301 | 1983 | 25 | true
301 | 1984 | 30 | true
301 | 1985 | 35 | true

301 | 1990 | 60 | false

Ich möchte jetzt gern alle “Zwischenjahre” (alle mit interpoliert = false) berechnen (linear interpolieren) und in die Tabelle schreiben. Der Benutzer liest also die Werte für bestimmte Jahre ein (alle mit interpoliert = true) die fehlenden Jahre sollen jetzt nach Möglichkeit automatisch ergänzt werden.
Jedesmal wenn ich die Tabelle update (vorhandene Jahre ändern oder neue Jahre hinzufügen) sollen die Zwischenjahre auch mit eingefügt oder aktualisiert werden.
Ich stehe jetzt wieder etwas an, weil ich nicht genau weiss wie ich das am besten machen kann. Sorry, dass ich immer so Spezialfragen habe, aber was wäre denn die eleganteste Lösung für so etwas? Das geht ja bestimmt irgendwie automatisiert beim Einfügen oder Updaten mit Triggern oder etwas ähnlichem? Hat da jemand Lösungsansätze oder Ideen wie man das am Besten angehen könnte?

Vielen herzlichen Dank
Hanno

Ich denke, auch hier können Dir Window-Funktionen helfen. lead() und lag().

Hallo nochmal,

Ich habe das Ganze jetzt nochmal weiter probiert und bin bis zu diesem Statement gekommen.

insert into ind10006 (indikator,raumeinheit,jahr,datenwert,original_gerechnet) select
raumeinheit as raumeinheit,
(jahr+1) as jahr,
((lead(datenwert) over(partition by raumeinheit order by jahr) - datenwert)/(lead(jahr) over(partition by raumeinheit order by jahr)-jahr)+datenwert) as datenwert,
false as interpoliert
from (select raumeinheit,
jahr,
datenwert,
lead(jahr) over(partition by raumeinheit order by jahr),
lead(datenwert) over(partition by raumeinheit order by jahr)
from ind10006
where original_gerechnet is true) as x

Leider wird dadurch ja immer nur der jeweils erste fehlende Wert berechnet. Also 1981 und 1983 im Beispiel. Ich möchte aber ja gern alle fehlenden Werte 1984, 1985 usw. auch interpolieren. Allerdings stehe ich irgendwie auf dem Schlauch und weiss nicht wie ich das machen muss. Hat eventuell noch jemand eine Idee und kann mir Hilfestellung geben? Vielen vielen Dank schon einmal im Voraus für die Hilfe.

Hab da jetzt auch keine ausgereifte Lösung dafür, ich denke, Du wirst wohl nicht um eine Stored Proc drum herum kommen. Sollte aber nicht so dolle kompliziert sein.

Hallo,

Ich habe inzwischen eine Funktion hinbekommen, die die Interpolationen richtig rechnet. Jetzt funktioniert das aber nur für eine Tabelle, welche ich direkt in der Funktion angegeben habe. Und irgendwie weiss ich gerade nicht ob ich der Funktion auch einen Tabellennamen als Parameter übergeben kann, damit das variable ist? Kann mir jemand helfen? Wie bekomme ich das hin, dass ich in dem delete und den insert statements immer einen übergebenden tabellennamen benutze?

CREATE OR REPLACE FUNCTION gines_missing_years(r integer, i integer, j integer, t integer, d2 real, d1 real, tn anyelement)
RETURNS real AS $$
DECLARE
tablename varchar(50);
result integer;
k numeric;
wert numeric;
d_first numeric;
temp integer;
zaehler integer;
BEGIN
tablename := tn;
d_first := d1;
delete from tablename where original_gerechnet = true;
zaehler := 1;
temp := j-t;
if temp > 1 THEN
WHILE zaehler < temp LOOP
result := t + zaehler;
k := (d2-d1)/temp;
wert := d_first + (((d2-d1)/temp) * zaehler);
insert into tablename (indikator,jahr,datenwert,raumeinheit,original_gerechnet)
values(i,result,wert,r,false);
zaehler := zaehler+1;
END LOOP;
elsif temp <> NULL THEN
WHILE zaehler < temp LOOP
result := t + zaehler;
k := (d2-d1)/temp;
wert := d_first + (((d2-d1)/temp) * zaehler);
insert into tablename (indikator,jahr,wert,raumeinheit,original_gerechnet)
values(i,result,NULL,r,true);
zaehler := zaehler+1;
END LOOP;
end if;
RETURN wert;
END;
$$LANGUAGE plpgsql;

Für eure Hilfe bin ich wie immer sehr dankbar.

Viele liebe Grüsse
Hanno

Geht auch. Da must die Query als String basteln und dannvia EXECUTE ausführen.

Guten Morgen Freunde,

Ich habe jetzt darüber nachgedacht. Da ich die Funktion immer nur ausführen möchte wenn sich etwas in der entsprechenden tabelle ändert (insert,update) Habe ich jetzt versucht das als Trigger Funktion (After insert or update) umzuschreiben. Dann brauche ich ja theoretisch auch den Tabellennamen nicht übergeben. Leider funktioniert das nicht wie gewünscht. Die Funktion läuft noch für das delete. Die Werte werden also gelöscht. Aber die beiden Insert Statements danach gehen nicht. Und ich finde mal wieder den Fehler nicht. In “temp” steht zum beipsiel immer nichts drin. Ich bin jetzt nicht sicher wie man in einer Trigger Funktion Variablen übergeben kann und ob es überhaupt möglich ist eine window Funktion wie lead() innerhalb des Triggers aufzurufen. Hab da jetzt schon auf den Postgres Seiten gesucht, aber bin da irgendwie nicht weiter gekommen. Geht das überhaupt oder bin ich da jetzt völlig auf dem Holzweg? Wie kann ich denn das korrekt umsetzen? ich hab mal die Tabelle und ein paar inserts angehängt, wenn vielleicht jemand Zeit und Lust hat sich das mal anzusehen? Ich bedanke mich sehr für die Hilfe und bin für jeden Tipp dankbar.

Gruss
Hanno

/-------------------------------------/
CREATE OR REPLACE FUNCTION gines_interpolate()
RETURNS trigger AS $gines_interpolate$
DECLARE
result integer;
/k numeric;/
wert numeric;
d_first numeric;
temp integer;
zaehler integer;
d1 numeric;
d2 numeric;
/tablename text;/
BEGIN
d_first := NEW.value;
execute format(‘delete from ’ || TG_TABLE_NAME || ’ where interpolated = true’) USING NEW;
zaehler := 1;
temp := lead(NEW.year) over (partition by NEW.area_id order by NEW.year)-NEW.year;
RAISE NOTICE ‘variable is %’, temp;
if temp > 1 THEN
WHILE zaehler < temp LOOP
result := NEW.year + zaehler;
d1 := NEW.value;
d2 := lead(NEW.value) over(partition by NEW.area_id order by NEW.year);
/k := ((d2-d1)/temp);/
wert := d_first + (((d2-d1)/temp) * zaehler);
execute format(‘insert into || TG_TABLE_NAME || (year,value,area_id,interpolated)
values(result,wert,NEW.area_id,true)’) USING NEW;
zaehler := zaehler+1;
END LOOP;
elsif temp <> NULL THEN
WHILE zaehler < temp LOOP
result := NEW.year + zaehler;
d1 := NEW.value;
d2 := lead(NEW.value) over(partition by NEW.area_id order by NEW.year);
/k := (d2-d1)/temp;/
wert := d_first + (((d2-d1)/temp) * zaehler);
execute format(‘insert into || TG_TABLE_NAME || (year,value,area_id,interpolated)
values(result,wert,NEW.area_id,true)’) USING NEW;
zaehler := zaehler+1;
END LOOP;
end if;
RETURN NULL;
END;
$gines_interpolate$LANGUAGE plpgsql;

/---------------------------------------------------------------------------/

CREATE TABLE public.ind_1 (
id serial NOT NULL,
area_id integer NOT NULL,
“year” integer NOT NULL,
value double precision,
interpolated boolean NOT NULL DEFAULT false,
/* Keys */
CONSTRAINT ind_1_pkey
PRIMARY KEY (id)
) WITH (
OIDS = FALSE
);

CREATE TRIGGER gines_interpolate
AFTER UPDATE OR INSERT
ON public.ind_1
FOR EACH ROW
EXECUTE PROCEDURE public.gines_interpolate();

/---------------------------------------------/

INSERT INTO ind_1 (id, area_id, year, value, interpolated) VALUES
(1, 301, 1980, 10, false),
(2, 301, 1981, 20, false),
(3, 301, 1985, 40, false),
(4, 301, 1990, 50, false),
(5, 301, 1993, 25, false),
(8, 302, 1986, 40, false),
(7, 302, 1982, 15, false),
(6, 302, 1980, 20, false),
(9, 302, 1987, 30, false),
(10, 302, 1992, 35, false),
(11, 301, 1996, 20, false);

Ich denke mal, da sollte ein vollständiges select stehen. So a al select into oder so…

Guten Morgen nochmal,

Ich bin langsam am verzweifeln, weil ich das immer noch nicht hinbekommen habe. Ich habe jetzt folgende Funktion (ohne Trigger usw.)

CREATE OR REPLACE FUNCTION gines_interpolate(tbl text, r integer, j integer, t integer, d2 double precision, d1 double precision)
RETURNS VOID AS $$
DECLARE
result integer;
wert numeric;
d_first numeric;
temp integer;
zaehler integer;
seq integer;
BEGIN
d_first := d1;
/delete from ind_1 where interpolated = true;/
zaehler := 1;
temp := j-t;
result := t + zaehler;
IF d1 IS NOT NULL AND d2 IS NOT NULL THEN
IF temp > 1 THEN
WHILE zaehler < temp LOOP
wert := d_first + (((d2-d1)/temp) * zaehler);
RAISE NOTICE ‘result is %’, result;
RAISE NOTICE ‘wert is %’, wert;
RAISE NOTICE ‘zaehler is %’, zaehler;
RAISE NOTICE ‘temp is %’, temp;
execute format(‘insert into ‘|| tbl ||’ (year,value,area_id,interpolated)
values(’|| result|| ‘,’|| wert|| ‘,’|| r || ‘,true);’);
zaehler := zaehler + 1;
result := t + zaehler;
END LOOP;
END IF;
END IF;
END;
$$LANGUAGE plpgsql;

Die Werte werden jetzt richtig berechnet und auch eingetragen. Leider ist es jetzt so, dass die fehlenden jahre jetzt immer viel zu oft eigetragen werden. Die Schleife läuft also irgendwie falsch. Es passiert jetzt folgendes: Das erste fehlende Jahr (1982 bei raumeinheit = 301) wird noch korrekt 1x eingetragen. Das zweite fehlende Jahr (1983) wird 2x eingetragen, das dritte Jahr (1984) wird 3x eingefügt. Das fünfte Jahr (1986) wird 4x eingefügt usw. Irgendwas stimmt da mit der While Schleife nicht. Aber irgendwie weiss ich mal wieder nicht wie ich herausfinden kann was da falsch gemacht wird. Kann man solche Funktionen eigentlich irgendwie debuggen?
Hat irgendwer eine Idee oder kann mir dabei helfen? Vielleicht erbarmt sich ja jemand und wirft mal einen Blick darauf. Ich seh langsam den Wald vor lauter Bäumen nicht mehr.
Vielen vielen Dank euch schon einmal im Voraus.

Gruss
Hanno

Ich versuch mal ein triviales Beispiel.

Du hast:

test=*# select * from interpolieren ;
 jahr | wert | i
------+------+---
 1990 |   10 | f
 1991 |   12 | f
 1995 |   20 | f
 2000 |   40 | f
(4 rows)

und willst nun die Zwischenwerte berechnen.

Folgendes als unvollständiger Ansatz:

test=*# create or replace function tuewas(start int, ende int, schrittweite int) returns bool  as $$begin raise notice 'von % - %,%',start,ende, schrittweite; return true; end;$$language plpgsql;
CREATE FUNCTION

wenn ich das aufrufen bekomme ich:

test=*# select tuewas(jahr-y, jahr, (wert/y)::int) from (select *, jahr - lag(jahr) over (order by jahr) y from interpolieren) x where y is not null and y > 1 ;
NOTICE:  von 1991 - 1995,5
NOTICE:  von 1995 - 2000,8
 tuewas
--------
 t
 t
(2 rows)

Ich hab also 2 Funktionsaufrufe und innerhalb der Funktion nun Ahnung, was zu machen ist, nämlich von-bis habe ich und die Schrittweite. Stimmt jetzt nicht alles perfekt, das Prinzip sollte aber so, denke ich, klar sein und funktionieren. Die Funktion bekommst sicher hin, ich denke mal, so schlecht schaut die gar nicht aus, vermutlich hast Du die nur falsch aufgerufen.

Hey,
Ich bin echt begeistert, dass man hier sogar am Wochenende eine Antwort bekommt. Vielen Dank dafür.
Ich habe das jetzt auch soweit hibekommen, dass die Funktion richtig läuft. Weiss zwar nicht so genau wo jetzt der fehler lag, aber inzwischen geht alles.

CREATE OR REPLACE FUNCTION gines_interpolate(tbl regclass, r integer, j integer, t integer, d2 double precision, d1 double precision)
RETURNS VOID AS $$
DECLARE
fehlendesJahr integer;
wert numeric;
d_first numeric;
temp integer;
zaehler integer;
BEGIN
d_first := d1;
zaehler := 1;
temp := j-t;
fehlendesJahr := t + zaehler;
IF d1 IS NOT NULL AND d2 IS NOT NULL THEN
IF temp > 1 THEN
WHILE zaehler < temp LOOP
wert := d_first + (((d2-d1)/temp) * zaehler);
RAISE NOTICE ‘fehlendes Jahr is %’, fehlendesJahr;
RAISE NOTICE ‘wert is %’, wert;
RAISE NOTICE ‘zaehler is %’, zaehler;
RAISE NOTICE ‘temp is %’, temp;
execute format(‘insert into ‘|| tbl ||’ (year,value,area_id,interpolated)
values(’|| fehlendesJahr|| ‘,’|| wert|| ‘,’|| r || ‘,true);’);
zaehler := zaehler + 1;
fehlendesJahr := t + zaehler;
END LOOP;
END IF;
END IF;
END;
$$LANGUAGE plpgsql;

Jetzt habe ich noch eine Anschluss-Frage. Im Prinzip müsste die Funktion jetzt immer aufgerufen werden, wenn sich in der Tabelle Werte ändern (insert or update). Als Trigger Funktion habe ich das ja leider nicht hinbekommen. Wäre es aber irgendwie möglich die Funktion innerhalb einer Rule zu benutzen? Dort könnte ich ja theoretisch eine Rule erstellen für die Tabelle, die immer beim Insert oder Update aufgerufen wird. Da jetzt aber meine Funktion ebenfalls Insert Statements auf diesselbe Tabelle enthält würde das ja ewig laufen, oder? Weil die Funktion sich dann ja immer wieder selbst aufrufen würde? Kann man die Rule so anlegen, dass die nur aufgerufen wird wenn alle insert statements schon ausgeführt wurden? Also nur 1x am Ende?
Ich hoffe das hab ich jetzt irgendwie verständlich beschrieben?

Gruss
Hanno

Ich weiß nicht so recht. Würde es nicht ausreichen, daß zyklisch zu machen, z.B. via CRON?