Komplizierte Abfrage geling nicht.

Aksels · 25. November 2019 um 14:20

Liebe Mitleser,
ich habe folgende Daten:

SELECT * FROM "A_Mess_ID_Ber_Geraden";

"T_Messungen_ID"	"T_Messungen_Auswertung_Typ"	"T_Messungen_Auswertung_ID"	"T_Messungen_Auswertung_von"	"T_Messungen_Auswertung_bis"	"T_Messungen_Geraden_ID"	"T_Messungen_Geraden_Steig"	"T_Messungen_Geraden_Achsenab"
125	0	2702	7.992	8.912	3900	2.6272954716530976	-0.47492025196960697
125	0	2703	11.512	12.472	3901	-2.554782692308296	52.39045404616109
125	0	2704	14.632	15.592	3902	2.5820019230780487	-17.312017061555473
125	0	2705	18.192	19.112	3903	-2.5395978260877574	69.14083698552217
125	0	2706	21.352	22.272	3904	2.598395652174373	-34.88059763189409

select * from "T_Messungen_Daten" where "T_Messungen_Daten_Art" = 5;

"T_Messungen_Daten_ID"	"T_Messungen_ID"	"T_Messungen_Daten_Art"	"T_Messungen_Daten_x"	"T_Messungen_Daten_y"	"T_Messungen_Daten_OK"
58595451	103	5	512.9853487840834	26.362540249449605	false
58595571	103	5	0.88	26.348999118918456	true
58595572	103	5	0.92	26.348999118918456	true
58595573	103	5	0.96	26.348999118918456	true
58595574	103	5	1	26.348999118918456	true

Das Obere sind Geradenstücke mit einer Steigung und einem Achsenabschnitt definiert im x-Bereich von bis.
Das Untere sind Punkte einer unregelmässigen Kurve. Zwischen den Punkten durch eine Gerade angenähert.
Ich möchte nun die Schnittpunkte per SQL berechnen. Diese sollten relativ mittig zwischen von und bis der oberen Tabelle liegen.
Mein Ansatz:

Ich nehme die obere Tabelle als Ausgangspunkt.
Dann möchte ich die Punkte aus der unteren dazufiltern, die zwischen von und bis liegen.
Da zu einem Punkt immer der nächste gehört (um Steigung und Achsenabschnitt zu berechnen) habe ich die untere Tabelle ein zweites mal hinzugefügt. Der x- und y-Wert muss vom darauffolgenden Punkt kommen.
Dann würde ich die Steigung und den Achsenabschnitt ausrechnen und in jeder Zeile die beiden Geraden schneiden.
Dann müsste ich nur noch die wegfiltern, deren x-Wert vom Ergebnis nicht im Bereich von bis liegt.

Meine Abfrage läuft aber scheinbar unendlich seit ich den Teil mit dem select t3 eingefügt habe.
Entweder habe ich ein Brett vor dem Kopf…ich komme einfach nicht weiter.

SELECT 
a1."T_Messungen_ID",
a1."T_Messungen_Auswertung_von",
a1."T_Messungen_Auswertung_bis",
t1."T_Messungen_Daten_x" as px1,
t1."T_Messungen_Daten_y" as py1,
t2."T_Messungen_Daten_x" as px2,
t2."T_Messungen_Daten_y" as py2

FROM "A_Mess_ID_Ber_Geraden" as a1,"T_Messungen_Daten" as t1,"T_Messungen_Daten" as t2
where t1."T_Messungen_Daten_Art" = 5
and t2."T_Messungen_Daten_Art" = 5
and t2."T_Messungen_Daten_x" = (select t3."T_Messungen_Daten_x" from "T_Messungen_Daten" as t3 where t3."T_Messungen_Daten_x" > t1."T_Messungen_Daten_x" order by t3."T_Messungen_Daten_x" limit 1) 
and t1."T_Messungen_Daten_x" 
between a1."T_Messungen_Auswertung_von" 
and a1."T_Messungen_Auswertung_bis";

Kann mal jemand drüberschauen und sagen “ach was, das geht doch ganz einfach”…
Gruß,
Aksels

castorp · 25. November 2019 um 14:32

Unabhängig von Deinem Problem: bitte vermeide diese unsäglichen doppelten Anführungszeichen für Spalten- oder Tabellenname. Die machen mehr Probleme als sie es wert sind. https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names

Aksels · 25. November 2019 um 14:41

Sorry, wurde von einer Access-Datenbank migriert. Aber ich werde mir mal anschauen, wieviel Aufwand das wäre es zu ändern.

frastr · 25. November 2019 um 15:42

Da zu einem Punkt immer der nächste gehört (um Steigung und Achsenabschnitt zu berechnen) habe ich die untere Tabelle ein zweites mal hinzugefügt. Der x- und y-Wert muss vom darauffolgenden Punkt kommen.

Hört sich für mich nach Verwendung von Window-Funktions an: lead() / lag()
Am besten mal nach Beispielen im Web suchen.

Zumal gibt es bei Verwendung von t3 nur die Einschränkung.

t3.T_Messungen_Daten_x > t1.T_Messungen_Daten_x

Ich hätte hier zumindest noch einmal.

"T_Messungen_Daten_Art" = 5

Aber das sollte mit Window-Funktion eh nicht mehr nötig sein.

Gruß Frank

Aksels · 26. November 2019 um 07:37

Guten Morgen.
Ich habe das mal in einzelne Abrfragen zerlegt. Die erste soll nur die Steigung und den Achsenabschnitt der Kurvenabschnitte bestimmen.

select 
t1."T_Messungen_ID",
t1."T_Messungen_Daten_ID" as id1,
t2."T_Messungen_Daten_ID" as id2,
t1."T_Messungen_Daten_x" as px1,
t1."T_Messungen_Daten_y" as py1,
t2."T_Messungen_Daten_x" as px2,
t2."T_Messungen_Daten_y" as py2,
(t2."T_Messungen_Daten_y" -t1."T_Messungen_Daten_y" )/(t2."T_Messungen_Daten_x"-t1."T_Messungen_Daten_x")as m_steigung,
t2."T_Messungen_Daten_y" - ((t2."T_Messungen_Daten_y" -t1."T_Messungen_Daten_y" )/(t2."T_Messungen_Daten_x"-t1."T_Messungen_Daten_x"))*t2."T_Messungen_Daten_x" as m_achsenabschnitt
from "T_Messungen_Daten" as t1 
inner join "T_Messungen_Daten" as t2 
on t1."T_Messungen_Daten_ID" + 1 = t2."T_Messungen_Daten_ID"
where t1."T_Messungen_Daten_Art" = 5 and t1."T_Messungen_ID" = 125 order by t1."T_Messungen_Daten_x";

Successfully run. Total query runtime: 293 msec.
25660 rows affected.

Hier das gleiche mit lead:

select 
t1."T_Messungen_ID",
t1."T_Messungen_Daten_ID" as id1,
LEAD(t1."T_Messungen_Daten_ID",1) OVER (ORDER BY t1."T_Messungen_Daten_x") as id2,
t1."T_Messungen_Daten_x" as px1,
t1."T_Messungen_Daten_y" as py1,
LEAD(t1."T_Messungen_Daten_x",1) OVER (ORDER BY t1."T_Messungen_Daten_x") as px2,
LEAD(t1."T_Messungen_Daten_y",1) OVER (ORDER BY t1."T_Messungen_Daten_x") as py2,
((LEAD(t1."T_Messungen_Daten_y",1) OVER (ORDER BY t1."T_Messungen_Daten_x")) -t1."T_Messungen_Daten_y" )/((LEAD(t1."T_Messungen_Daten_x",1) OVER (ORDER BY t1."T_Messungen_Daten_x"))-t1."T_Messungen_Daten_x")as m_steigung,
(LEAD(t1."T_Messungen_Daten_y",1) OVER (ORDER BY t1."T_Messungen_Daten_x")) - (((LEAD(t1."T_Messungen_Daten_y",1) OVER (ORDER BY t1."T_Messungen_Daten_x")) -t1."T_Messungen_Daten_y" )/((LEAD(t1."T_Messungen_Daten_x",1) OVER (ORDER BY t1."T_Messungen_Daten_x"))-t1."T_Messungen_Daten_x"))*(LEAD(t1."T_Messungen_Daten_x",1) OVER (ORDER BY t1."T_Messungen_Daten_x")) as m_achsenabschnitt
from "T_Messungen_Daten" as t1 
where t1."T_Messungen_Daten_Art" = 5 and t1."T_Messungen_ID" = 125 order by t1."T_Messungen_Daten_x";

Successfully run. Total query runtime: 289 msec.
25660 rows affected.
Gefällt mir besser, da die erste Abfrage nur funktioniert, wenn die ID wirklich incrementierend in der korrekten Reihenfolge ist.

Geschwindigkeit ist bei beiden gleich.
Kann man die zweite eigentlich nicht vereinfachen? In Access kann ich zum beispiel auf bereits berechete vorherige Spalten mit dem gegebenen Namen zugreifen. Und ich verwende die Steigung bei Achsenabschnitt, oder weiter oben das bereits gefundene px2 und py2.
Gruß,
Aksels

castorp · 26. November 2019 um 07:55

Das kann man schon vereinfachen, indem man die berechneten Felder in eine sog. Derived Table packt:

select x."T_Messungen_ID",
       x.id1,
       x.id2,
       x.px1,
       x.py1,
       x.px2,
       x.py2,
       py2 - - py1 / px2 - px1 as m_steigung,
       ... und so weiter ...
from (
  select t1."T_Messungen_ID", 
         t1."T_Messungen_Daten_ID" as id1,
         LEAD(t1."T_Messungen_Daten_ID",1) OVER w as id2,
         t1."T_Messungen_Daten_x" as px1,
         t1."T_Messungen_Daten_y" as py1,
         LEAD(t1."T_Messungen_Daten_x",1) OVER w as px2,
         LEAD(t1."T_Messungen_Daten_y",1) OVER w as py2,
  from "T_Messungen_Daten" as t1            
  where t1."T_Messungen_Daten_Art" = 5 
    and t1."T_Messungen_ID" = 125 
  order by t1."T_Messungen_Daten_x";         
  window w as (order by ORDER BY t1."T_Messungen_Daten_x")
) x

Aksels · 26. November 2019 um 08:05

Noch eine Variante, die ich übersichtlicher finde:

with t1 as (select 
"T_Messungen_ID",
"T_Messungen_Daten_ID" as id1,
LEAD("T_Messungen_Daten_ID",1) OVER (ORDER BY "T_Messungen_Daten_x") as id2,
"T_Messungen_Daten_x" as px1,
"T_Messungen_Daten_y" as py1,
LEAD("T_Messungen_Daten_x",1) OVER (ORDER BY "T_Messungen_Daten_x") as px2,
LEAD("T_Messungen_Daten_y",1) OVER (ORDER BY "T_Messungen_Daten_x") as py2
	from "T_Messungen_Daten" where "T_Messungen_Daten_Art" = 5)
	select px1,px2,py1,py2,
CASE WHEN px1=px2 THEN 0 ELSE (py2 - py1 )/(px2 - px1) END as m_steigung,
CASE WHEN px1=px2 THEN 0 ELSE py2 - (py2 - py1 )/(px2-px1)*(px2) END as m_achsenabschnitt
from t1
where "T_Messungen_ID" = 125   order by px1;

Muss die Daten mal untersuchen: px1=px2 sollte nicht vorkommen und verursacht Division by zero.
Auch diese Abfrage ist nicht schneller.
Gruß,
Aksels

Aksels · 26. November 2019 um 09:12

So sieht es bis jetzt aus:

-- View: public.a_tk_kurve_steig_achsa

-- DROP VIEW public.a_tk_kurve_steig_achsa;

CREATE OR REPLACE VIEW public.a_tk_kurve_steig_achsa
 AS
 WITH t1 AS (
         SELECT "T_Messungen_Daten"."T_Messungen_ID" AS t_mess_id,
            "T_Messungen_Daten"."T_Messungen_Daten_ID" AS id1,
            lead("T_Messungen_Daten"."T_Messungen_Daten_ID", 1) OVER (ORDER BY "T_Messungen_Daten"."T_Messungen_Daten_x") AS id2,
            "T_Messungen_Daten"."T_Messungen_Daten_x" AS px1,
            "T_Messungen_Daten"."T_Messungen_Daten_y" AS py1,
            lead("T_Messungen_Daten"."T_Messungen_Daten_x", 1) OVER (ORDER BY "T_Messungen_Daten"."T_Messungen_Daten_x") AS px2,
            lead("T_Messungen_Daten"."T_Messungen_Daten_y", 1) OVER (ORDER BY "T_Messungen_Daten"."T_Messungen_Daten_x") AS py2
           FROM "T_Messungen_Daten"
          WHERE "T_Messungen_Daten"."T_Messungen_Daten_Art" = 5
        )
 SELECT t1.t_mess_id,
    t1.id1,
    t1.id2,
    t1.px1,
    t1.px2,
    t1.py1,
    t1.py2,
        CASE
            WHEN t1.px1 = t1.px2 THEN 0::double precision
            ELSE (t1.py2 - t1.py1) / (t1.px2 - t1.px1)
        END AS m_steigung,
        CASE
            WHEN t1.px1 = t1.px2 THEN 0::double precision
            ELSE t1.py2 - (t1.py2 - t1.py1) / (t1.px2 - t1.px1) * t1.px2
        END AS m_achsenabschnitt
   FROM t1
  ORDER BY t1.px1;

ALTER TABLE public.a_tk_kurve_steig_achsa
    OWNER TO postgres;

Dann:

with t1 as (select t_mess_id,id1,px1,px2,"T_Messungen_Geraden_ID",
("T_Messungen_Geraden_Achsenab" - m_achsenabschnitt) / (m_steigung - "T_Messungen_Geraden_Steig") as spx,
("T_Messungen_Geraden_Steig" * (("T_Messungen_Geraden_Achsenab" - m_achsenabschnitt) / (m_steigung - "T_Messungen_Geraden_Steig")) + "T_Messungen_Geraden_Achsenab") as spy
from a_tk_kurve_steig_achsa 
inner join "A_Mess_ID_Ber_Geraden" 
on a_tk_kurve_steig_achsa.t_mess_id = "A_Mess_ID_Ber_Geraden"."T_Messungen_ID"
where a_tk_kurve_steig_achsa.px1 
between "A_Mess_ID_Ber_Geraden"."T_Messungen_Auswertung_von" 
and "A_Mess_ID_Ber_Geraden"."T_Messungen_Auswertung_bis"
and a_tk_kurve_steig_achsa.px2 
between "A_Mess_ID_Ber_Geraden"."T_Messungen_Auswertung_von" 
and "A_Mess_ID_Ber_Geraden"."T_Messungen_Auswertung_bis"
and "T_Messungen_Auswertung_Typ" = 0)
insert into "T_Messungen_Punkte" ("T_Messungen_Geraden_ID","T_Messungen_Punkte_Typ","T_Messungen_Punkte_x","T_Messungen_Punkte_y")
select "T_Messungen_Geraden_ID",7 as m_typ,spx,spy from t1 where spx between px1 and px2
where t_mess_id = 125;

Diesen letzten Code hätte ich gerne als view abgespeichert. Aber da fehlt noch die dynamische Auswahl der Messung.
Muss ich die Abfrage in eine procedure mit der MessID als Argument packen, oder kann man eine view abspeichern, bei der man von aussen die m_mess_id = 125 irgendwie übergibt?
Gruß,
Aksels

pogomips · 28. November 2019 um 18:59

Der letzte Code enthält ein Insert, das kann man nicht als View abspeichern.

Du kannst ein beliebiges Selectstatement als View abspeichern. Gibt es Deine benötigten Filterkriterien aus, dann kannst Du diese unter Verwendung eines Views in der Where Clause abfragen. Das Ergebnis kann im Insert genutzt werden oder sonst irgendwo.