Abfrage Bohrungen

Liebe Postgres-Gemeinde,
ich versuche mich gerade an einer etwas komplizierteren Abfrage.
Hintergrund ist folgender:
Ich habe zwei Tabellen: 1. Bohrungen (bp_jw_diss)
2. Sedimentdaten zu einzelnen Schichten in den Bohrungen(lagen_sed).

die Tabellen hängen folgendermaßen zusammen:
“Id_bp_jw”(lagen_sed) --> “Id” (bp_jw_diss)

CREATE TABLE "bp_jw_diss" (
  "Id" INTEGER NOT NULL PRIMARY KEY, 
  "Bp" VARCHAR(255), 
  "x" DOUBLE PRECISION NULL, 
  "y" DOUBLE PRECISION NULL, 
  "z" DOUBLE PRECISION NULL, 
  "z_jw" DOUBLE PRECISION NULL
);

SELECT AddGeometryColumn('bp_jw_diss','geomz',32636, 'POINT', 3);
UPDATE "bp_jw_diss" SET geomz = ST_SetSRID(ST_MakePoint("x", "y", "z_jw"),32636);



CREATE TABLE "lagen_sed" (
  "Id" SERIAL NOT NULL PRIMARY KEY, 
  "Id_bp_jw" INTEGER, 
  "Bp" VARCHAR(255), 
  "Tiefe_oben" DOUBLE PRECISION NULL, 
  "Tiefe_unten" DOUBLE PRECISION NULL, 
  "Habitus" VARCHAR(255), 
  "mean_grainsize" DOUBLE PRECISION NULL
);

In der Tabelle lagen_sed habe ich das Feld Tiefe_unten und Tiefe_oben wie auch die mittlere Korngröße für jede einzelne Sedimentschicht einer Bohrung.

Ich möchte jetzt eine Abfrage machen, die mir als Ergebnis zeigt welche Bohrungen zu mehr als 50 % aus Ton bestehen (mean_grainsize <= 2) und zwar anteilig an der Gesamtmächtigkeit der Bohrung.

Derzeitig bin ich mit meiner Abfrage soweit, dass ich mir für jede Schicht den prozentualen Anteil an der Gesamtheit aller Schichten ausgeben kann.
Also die Abfrage liefert als Ergebnis eine laufende ID für jede einzelne Schicht, die Id der Bohrung, die Geometrie, Gesamtmächtigkeit aller Schichten, Mächtigkeit einer Schicht, Anteil der Schicht an der Gesamtmächtigkeit und die Korngröße der Schicht. Jetzt sollten noch alle Schichten gleicher Korngröße innerhalb einer Bohrung aggregiert werden und dann nach > 50 % Anteil für Schichten mit <= 2 grainsize gefiltert werden. Hier hänge ich leider gerade.
Mein bisheriger Stand sieht wie folgt aus:

create view bp_jw_clay as
	select b."Id" as ID, a."Id" as IDcore, a. geomz, c.m_bohrung, b.m_schicht,((b.m_schicht/c.m_bohrung)*100) as proz, b.grain
	
	from bp_jw_diss as a inner join
	
		(SELECT x."Id", x."mean_grainsize" as grain, x."Id_bp_jw", (x."Tiefe_unten"-x."Tiefe_oben") AS m_schicht 
		FROM lagen_sed AS x)as b
	
	on a."Id" = b."Id_bp_jw" inner join
	
		(SELECT y."Id_bp_jw", SUM(y."Tiefe_unten"-y."Tiefe_oben") AS m_bohrung 
		FROM lagen_sed AS y GROUP BY (y."Id_bp_jw"))as c
		
	on a."Id" = c."Id_bp_jw";

Ich hoffe meine Anfrage ich deutlich genug und dass sie Interesse fürs Lösen weckt.
Liebe Grüße
confugere

Anbei ein Bild der Abfrage um eine Vorstellung der Daten zu bekommen
abfrage.png

Ich arbeite auch kontinuierlich an der lösung und jetzt fehlt nur noch das Korngrößenklassen <= 2 (also 2 und 1 aggregiert werden). Die Gruppierung mehrer Schichten mit gleicher Korngröße funktioniert jetzt. Vielleicht hat Jemand hierfür eine Lösung.

create view bp_jw_clay_2 as

select *, row_number() OVER() AS num
from
	(select a."Id" as IDcore, a.geomz, c.m_bohrung, sum(b.m_schicht),sum(((b.m_schicht/c.m_bohrung)*100)) as proz, b.grain
	
	from bp_jw_diss as a inner join
	
		(SELECT x."Id", x."mean_grainsize" as grain, x."Id_bp_jw", (x."Tiefe_unten"-x."Tiefe_oben") AS m_schicht 
		FROM lagen_sed AS x)as b
	
	on a."Id" = b."Id_bp_jw" inner join
	
		(SELECT y."Id_bp_jw", SUM(y."Tiefe_unten"-y."Tiefe_oben") AS m_bohrung 
		FROM lagen_sed AS y GROUP BY (y."Id_bp_jw"))as c
		
	on a."Id" = c."Id_bp_jw"
	GROUP BY a."Id", b.grain, a.geomz, c.m_bohrung
	ORDER BY a."Id") z;

ich habs gelöst die unten stehende abfrage liefert mir die Anteile der Schichten < 5 (hab ich neu definiert) für jede Bohrung und mit der Kriterienauswahl in qgis kann ich die Daten selektieren.

CREATE VIEW bp_jw_clay AS
SELECT a.“Id” AS IDcore, c.m_bohrung, b.m_schicht, (b.m_schicht/c.m_bohrung)*100 AS Anteil, a.geomz

FROM bp_jw_diss AS a INNER JOIN

(SELECT x.“Id_bp_jw”, SUM((x.“Tiefe_unten”-x.“Tiefe_oben”)) AS m_schicht
FROM lagen_sed AS x
WHERE x.“mean_grainsize” < 5
GROUP BY x.“Id_bp_jw”, x.“mean_grainsize” < 5
ORDER BY x.“Id_bp_jw”) as b

ON a.“Id” = b.“Id_bp_jw” INNER JOIN

(SELECT y.“Id_bp_jw”, SUM(y.“Tiefe_unten”-y.“Tiefe_oben”) AS m_bohrung
FROM lagen_sed AS y GROUP BY (y.“Id_bp_jw”))as c

ON a.“Id” = c.“Id_bp_jw”;