Komplizierte Abfrage geling nicht.

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
Aksels
Neuer Benutzer
Beiträge: 5
Registriert: Mo 25. Nov 2019, 14:47

Komplizierte Abfrage geling nicht.

Beitrag von Aksels » Mo 25. Nov 2019, 15:20

Liebe Mitleser,
ich habe folgende Daten:

Code: Alles auswählen

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

Code: Alles auswählen

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:
1. Ich nehme die obere Tabelle als Ausgangspunkt.
2. Dann möchte ich die Punkte aus der unteren dazufiltern, die zwischen von und bis liegen.
3. 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.
4. Dann würde ich die Steigung und den Achsenabschnitt ausrechnen und in jeder Zeile die beiden Geraden schneiden.
5. 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.

Code: Alles auswählen

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
Erfahrener Benutzer
Beiträge: 1177
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Komplizierte Abfrage geling nicht.

Beitrag von castorp » Mo 25. Nov 2019, 15: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%27 ... lumn_names
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Aksels
Neuer Benutzer
Beiträge: 5
Registriert: Mo 25. Nov 2019, 14:47

Re: Komplizierte Abfrage geling nicht.

Beitrag von Aksels » Mo 25. Nov 2019, 15:41

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

frastr
Benutzer
Beiträge: 44
Registriert: Di 9. Sep 2014, 12:20
Wohnort: Hoyerswerda, Sachsen

Re: Komplizierte Abfrage geling nicht.

Beitrag von frastr » Mo 25. Nov 2019, 16:42

3. 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.

Code: Alles auswählen

t3.T_Messungen_Daten_x > t1.T_Messungen_Daten_x
Ich hätte hier zumindest noch einmal.

Code: Alles auswählen

"T_Messungen_Daten_Art" = 5
Aber das sollte mit Window-Funktion eh nicht mehr nötig sein.

Gruß Frank

Aksels
Neuer Benutzer
Beiträge: 5
Registriert: Mo 25. Nov 2019, 14:47

Re: Komplizierte Abfrage geling nicht.

Beitrag von Aksels » Di 26. Nov 2019, 08:37

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

Code: Alles auswählen

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:

Code: Alles auswählen

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
Erfahrener Benutzer
Beiträge: 1177
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Komplizierte Abfrage geling nicht.

Beitrag von castorp » Di 26. Nov 2019, 08:55

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

Code: Alles auswählen

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
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Aksels
Neuer Benutzer
Beiträge: 5
Registriert: Mo 25. Nov 2019, 14:47

Re: Komplizierte Abfrage geling nicht.

Beitrag von Aksels » Di 26. Nov 2019, 09:05

Noch eine Variante, die ich übersichtlicher finde:

Code: Alles auswählen

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
Neuer Benutzer
Beiträge: 5
Registriert: Mo 25. Nov 2019, 14:47

Re: Komplizierte Abfrage geling nicht.

Beitrag von Aksels » Di 26. Nov 2019, 10:12

So sieht es bis jetzt aus:

Code: Alles auswählen

-- 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:

Code: Alles auswählen

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
Erfahrener Benutzer
Beiträge: 114
Registriert: Fr 18. Nov 2016, 09:55

Re: Komplizierte Abfrage geling nicht.

Beitrag von pogomips » Do 28. Nov 2019, 19: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.

Antworten