Lässt sich diese "Aufgabe" (ich habe kein Namen dafür) direkt/sinnvoll in Postgresql lösen?

C

chris2023

Benutzer
Beiträge
10
Hallo,

hoffe es passt hier rein. Gleich vorweg, ich bin jetzt nicht der DB-Profi, komme aber gut zurecht. :)

Es geht um eine Postgresql-DB.
Aus 6/7 Tabellen habe ich eine schon fast nahezu perfekte Ausgabe gestaltet...


Kurz gesagt es geht um folgendes...
Der Bereich "start/ende" muss gleich/(kleiner/größer) sein als gesamt_start/ende. Und sollten IDs mehrfach vorkommen müssen diese "direkt anschließend sein" oder einen Überhang" haben.

Hier ein bsp. mit nur 1x vorkommende IDs. Diese konnte ich gleich passend filtern, also welche Korrekt waren, um mir die Fehlerhaften anzuzeigen.
Ich habe einfach, start_gleich/kleiner, mit ende_gleich/größer geprüft.

OK:
idgesamt_startgesamt_endestartende
11111001100

OK:
idgesamt_startgesamt_endestartende
22211001110

FEHLERHAFT:
idgesamt_startgesamt_endestartende
3331100180


---------------------------------------------------------------------------------------------

Jetzt gibt es aber Fälle mit mehreren IDs.
Bsp:

idgesamt_startgesamt_endestartende
9995300295
999530090223
999530095223
9995300223284
9995300284291
9995300284291
9995300284300

Dies würde ...
2-95
90-223
223-284
284-300
... passen. Da die IDs direkt anschließen oder einen Überstand haben und es keine Lücken zu "gesamt" 5-300 gibt.

Jetzt meine Frage.
Ist so etwas sinnvoll in ein Query einzubauen, oder sollte solch eine Bearbeitung eher außerhalb der DB (ich würde es dann in Pandas realisieren) zu lösen?

Das für mich eigentliche Problem. Lässt sich start/ende, vorab, sinnvoll Auflösen... Damit wär ja eiegntlich schon das Problem gelößt.
Falls es dazu eine Lösung gibt, hätte ich "irgendwie" geprüft, ob alle Zahlen anschließen und/oder überstand haben. Sollte dies Valide sein, müsste ich "nur noch" Start/Ende Prüfen ob diese mit gesamt passen.

Ja, so geschrieben klingt es irgendwie einfach... :)
Aber ich stehe auf dem Schlauch, gibt es Mathematisch dafür überhaupt ein Weg/Namen, oder müsste man alle try/error durchprobieren...
Schon Begriffe für dieses Problem wären Gold Wert.

Viele Grüße
Chris
 
Werbung:
den zweiten Teil versteh ich nur irgendwie als Bahnhof in Ägypten, aber für den ersten Teil:

Code: 
postgres=# create table chris2023(id int primary key, gstart int, gende int, start int, ende int, check (gstart <= gende and start <= gstart and ende >= gende));
CREATE TABLE
postgres=# insert into chris2023 values (111, 1, 100, 1, 100);
INSERT 0 1
postgres=# insert into chris2023 values (222, 1, 100, 1, 110);
INSERT 0 1
postgres=# insert into chris2023 values (333, 1, 100, 1, 80);
ERROR:  new row for relation "chris2023" violates check constraint "chris2023_check"
DETAIL:  Failing row contains (333, 1, 100, 1, 80).
postgres=#
 
Hallo akretschmer,

danke für dein Feedback :)
Zu den "einzeiligen IDs" habe ich schon die Lösung...

AND NOT (xx.count = 1 AND x.gesamt_start >= x.start and x.gesamt_stop<= x.stop)

Mit xx.count suche ich mir nur 1x vorkommende IDs raus und prüfe mit größer/kleiner/gleich ob es passt und Filtere somit korrekte Daten raus.
Somit werden mir nur noch die Fehlerhaften angezeigt.

Dann gibt es aber IDs, welche merhfach vorkommen, und hierzu das bsp. mit gleich 7 Zeilen.
Ob aber nur 2 oder mehr ist eigentlich egal, da ich zu keine eine Lösung habe.
Dachte zur Veranschaulichung ist dies besser.


Dies ist die Ausgabe aus meinem aktuellen Query... Auf eben eine id eingegrenzt.

idgesamt_startgesamt_endestartende
9995300295
999530090223
999530095223
9995300223284
9995300284291
9995300284291
9995300284300

... aktuell Suche ich immernoch, wie das Problem richtig benannt wird.
Habe es mit Zahlenreihe/natürliche Zahlenreihe/verketten/prüfen, probiert etc.. :D

Eigentlich ganz einfach, 5 bis 300 muss mit start/ende "vollständig" (ohne lücken) nachgebaut werden können.
Sollten Lücken vorhanden sein, so will ich mit diesen Block "id" ausgeben lassen.

Dies wär solch ein Fehler, welchen ich zu erkennen versuche.
idgesamt_startgesamt_endestartende
888288250
8882885580
8882888088

Dort fehlt der Zusammenhang zwischen 50 und 55.

Es gibt meiner Meining nach nur zwei Schritte welche dafr nötig sind.
1. ??? Ich müsste aus start/ende eine durchgehende Zahlenreihe bilden/versuchen. Hier 2-50, 55-88. Also schon weil es keine durchgehende Kette bildet, ist dies als Fehler zu erkennen
2. min(start) mit max(ende) mit gesamt_start und gesamt_ende prüfen. gleich oder jeweils kleiner/größer

Hoffe jetzt ist es etwas verständlicher. :)

Viele Grüße
Chris


**** EDIT ****
Gerade ist mir ein Super beispiel eingefallen...

Ich hab eine Tabelle "staffellauf" und eine weitere "laufer".

In staffellauf gibt es eben diese 5 - 300. Sagen wir dies sind die zwischenstationen von Hamburg nach München. (ich habe extra mal nicht bei 1 angefangen)

Und dann gibt es die läufer.
L1 Läuft von Station 1 bis 30
L2 - läuft 20 bis 80
L3- läuft 80 bis 120
L4- läuft 110 bis 200
L5- läuft 200 bis 300

Ich möchte überprüfen ob "alle" Stationen an/durchgelaufen wurden.
Wenn es zu überschneidungen, wie im bsp. von L1 zu L2 kommt, so ist dies egal.

Hoffe jetzt kann man sich es besser vorstellen. :D
 
Zuletzt bearbeitet:
hrm, Du kannst mit Window-Funktionen z.B. auf die vorherige Zeile (vorherig im Sinne einer Sortierung) zugreifen. Damit könntest Du auch die Differenz zum Wert der aktuellen Zeile ermitteln etc.
 
Stimmt, unter Windows-Funktion hatte ich es nicht im Kopf. Aber als ich die begriffe "PARTITION BY" gerade gesehen habe wusste ich es wieder.
Habe ich bisher nur 1-2x benutzt, dies wäre ein Weg.
Dies wäre aber der try/error Weg.

Ich frag jetzt aber mal ganz Dumm. Eine direkte Funktion, "Prüfe Zahlenreihe(1er schritte) auf vollständigkeit" gibt es nicht? :D

Gerade eben habe ich noch ein anderes Bild im Kopf.

Statt die Zahlen, dies einfach als Zahlenstrang sich vorstellen ...
1------10
5----------16
14--------------------30
20----------------40

Und so, wenn dies übereinandergelegt ist...
1--------------------------------------------40

Eine Suche mit Zahlenreihe und Merge, damit hatte ich gerade eben kein Glück.
Es muss doch einen richtigen Begriff geben. :D
 
Nun ja, da gibt es auch wege ...

Code: 
postgres=# create table bla (von int, bis int);
CREATE TABLE
postgres=# insert into bla values (1,5);
INSERT 0 1
postgres=# insert into bla values (3,7);
INSERT 0 1
postgres=# insert into bla values (10,12);
INSERT 0 1
postgres=# select * from bla left join lateral (select * from generate_series(bla.von, bla.bis)) as x on true;
 von | bis | generate_series 
-----+-----+-----------------
   1 |   5 |               1
   1 |   5 |               2
   1 |   5 |               3
   1 |   5 |               4
   1 |   5 |               5
   3 |   7 |               3
   3 |   7 |               4
   3 |   7 |               5
   3 |   7 |               6
   3 |   7 |               7
  10 |  12 |              10
  10 |  12 |              11
  10 |  12 |              12
(13 rows)

postgres=# with tmp as (select * from bla left join lateral (select * from generate_series(bla.von, bla.bis) as zahlen) as x on true) select distinct zahlen from tmp order by zahlen;
 zahlen 
--------
      1
      2
      3
      4
      5
      6
      7
     10
     11
     12
(10 rows)

postgres=#

Du kannst Dir also so eine Tabelle erstellen, welche die (unique) Zahlen der einzelnen Bereiche enthält. Hier kannst Du weiter machen, z.B. wieder via generate_series() joinen um zu prüfen, ob und welche da fehlen etc.
 
Mit einem Begriff kann ich dir jetzt auch nicht helfen aber lag() OVER (PARTION BY id ORDER BY start) kannst du es am elegantesten lösen. Es ginge auch mit einem Self-join aber nein, schöner wirds nicht.

lag() kannst du allerdings nicht im WHERE-Teil nutzen, du musst also erst lag() als Spalte in einem inneren Select an einen Äußeren übergeben und kannst dort dann filtern oder mit CASE Warnungen anzeigen wenn lag_ende > start

Rein logisch kannst du noch einen (vermutlich unwahrscheinlichen) Fall bekommen:
idgesamt_startgesamt_endestartende
888288255
8882884950
8882885288
Dann ist natürlich die ganze Strecke vollständig (und teilweise mehrfach) abgebildet aber lag_ende 50 aus Zeile 2 ist natürlich > start 52 Zeile 3.

Anderer Ansatz wäre die gesamte Strecke in Teilstücke zerlegen (generate_series), also z.B. 1-2,2-3,3-4 usw. und auf jeden Abschnitt einen Datensatz mit LEFT JOIN. Ist der an irgendeinem Punkt NULL dann ist die nicht abgedeckt, doppelte Datensätze sind dann unschädlich. Aber performanter ist das nicht.
 
Hallo und danke euch beiden.

generate_series kannte ich bisher noch nicht.
Eine komplette Zahlenreihe in ein array zu verpacken hatte ich zwar schon, aber ich wusste nicht wie.das es dafür aber eine Funktion gibt, dies war mir nicht bekannt.

postgres=# with tmp as (select * from bla left join lateral (select * from generate_series(bla.von, bla.bis) as zahlen) as x on true) select distinct zahlen from tmp order by zahlen;
Zum Vergrößern anklicken....

lag() kannst du allerdings nicht im WHERE-Teil nutzen, du musst also erst lag() als Spalte in einem inneren Select an einen Äußeren übergeben und kannst dort dann filtern oder mit CASE Warnungen anzeigen wenn lag_ende > start
Zum Vergrößern anklicken....

Ich habe gerade noch ein Brett vor dem Kopf, aber eine Idee...

Ich integriere und generiere evtl. gleich in meinem bisherigen Prozess ein generate_series(start, ende) dann hätte ich eine zusätzliche Spalte mit einem fertigen array, pro Zeile.
Dann bräuchte ich "nur" noch diese arrays/zeilen mit obrigen prinzip zusammenwürfeln.

Das fertige Ergebnis array, könnte ich mit "array_length(anyarray, int)" auf die länge Prüfen.
Die muss mindestens in gesamt_start und gesamt_ende rein passen, trifft dies zu, dann den zweiten Schritt mit small/large den kleinsten und größten Wert extrahieren und wieder prüfen.

Also bisher logisch, und klingt geschrieben recht einfach ... :D
Ich berichte was/wie ich etwas zusammengebastelt bekommen habe und wie auch die Performance bei meiner Umsetzung so ist.

Jetzt schon mal ein Dankeschön an euch beide.
Das Brett gestern/heute war so groß, das ich ohne diese Ideen erst einmal gar nicht weiter gekommen wäre...
 
vielleicht noch der dezente Hinweis, daß in meinem SQL ein LATERAL JOIN steckt - sehr viele andere DB-Systeme kennen das nicht.
 
chris2023 schrieb:
gibt es Mathematisch dafür überhaupt ein Weg/Namen
Zum Vergrößern anklicken....
Du arbeitest letztlich- aus DB Sicht bzw. aus Sicht von Postgres- mit Daten, die Ranges bilden. Postgres unterstützt den Range Typ und seit Version 14 auch Multiranges. Das passt hier sehr gut zu Deinem Problem!

Verwendet man den Rangetyp, so stehen unter Postgres eine Vielzahl von Operatoren und Funktionen zur Verfügung.

Deine Anforderung lässt sich mit diesem SQL z.B. lösen. Vielleicht geht es noch eleganter, ich hab bisher kaum damit gearbeitet.
Code: 
select id, gesamtrange, rangearray
  from (
        select id, gesamtrange, range_agg(teilrange) as rangearray
                             -- teilrange aggregieren
          from (
                select id,   -- ranges aus den spaltendaten erzeugen
                       numrange(gesamt_start, gesamt_ende) as gesamtrange,
                       numrange(start,ende) as teilrange
                  from brokenrangetest
               )x
         group by x.id, x.gesamtrange ) y
 where not(rangearray @> gesamtrange)

Ich vermute, dass diese Lösung deutlich schneller ist, als die Nutzung von Window Functions, da keine zusätzlichen Daten generiert werden, sondern nur zusammengefasst und verglichen wird.

An der Stelle muss ich einfach mal sagen, Postgres ist ein super Werkzeug!

Hier ist ein vollständiges Beispiel:

Postgres 15

| version | |:--------| | PostgreSQL 15.0 on x86\_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit | > ``` status > SELECT 1 > ```
dbfiddle.uk dbfiddle.uk

P.S.:
Die ()x Selection dient nur dazu, aus Deinen Spaltenwerten Ranges zu bilden. Man könnte die Tabelle auch so deklarieren, dass es von Anfang Rangetypen sind. Dann fällt der ()x Teil weg. Man kann das auch noch als CTE schreiben, wenn man mag. Ist vielleicht sinnvoll, wenn man Teilergebnisse (die Rangebildung oder die Aggregation) mehrfach verwenden / joinen muss.
 
P.P.S.
Wenn man es nicht so übersichtlich haben muss, was in den Schritten geschieht, kann man es auch zu einem Statement zusammenfassen.
Mir gefällt das selten wirklich gut, weil man die Aliase nicht im Group oder Having verwenden kann. Also muss man die Ausdrücke wiederholen.
Das ist kürzer, aber nicht unbedingt verständlicher.
Man könnte im Group noch mit Spaltenindex arbeiten, statt mit Ausdrücken, das ist aber gefährlich, wenn man mal was ändert.
Code: 
        select id,  
               numrange(gesamt_start, gesamt_ende) as gesamtrange, 
               range_agg(numrange(start,ende)) as rangearray 
          from brokenrangetest 
         group by id,numrange(gesamt_start, gesamt_ende)
        having not( range_agg(numrange(start,ende)) @> numrange(gesamt_start, gesamt_ende))
Ist das Statement aus dem vorigen Post, nur zusammengefasst.
 
Die Lösung, eine Wertereihe zu generieren und dann alles darauf zu joinen mag erstmal logisch wirken, dürfte aber immer langsamer sein als z.B. mit Windows-Functions oder mit Range-Funktionen zu arbeiten (die ich auch noch nicht kenne). Ich schätze mal das sich die beiden Optionen nicht mehr so viel nehmen, aber mit generierten Wertelisten würde ich nicht arbeiten wenn nicht nötig.
 
Was ich nicht verstehe ist, warum ich ID's verwende, welchen einen identischen Wert aufweisen. Für ID's verwende ich Autowert, welcher eigenständig hochzählt. Viele Grüße
 
Werbung:
ukulele schrieb:
wie verrückt Performance Themen sein können
Zum Vergrößern anklicken....
Sowas ist nach meiner Erfahrung sehr DB spezifisch, dort dann der entsprechende Optimizer -je nach Version (bei gleichem Hersteller)-
Dann die Statistiken (aktuell, nicht aktuell, eingefroren, nicht eingefroren, ..) oder ohne beim alten Rulebased Optimizer von Oracle.

Ich habe mir abgewöhnt, das spezifisch Verstehen zu wollen. Abfragen oder Vorgehensweise sind beruhen dann auf Erfahrungswerten bzw. Grundverständnis und natürlich Logik / Physik.

Es kümmert mich alles so lange nicht, bis es irgendwo kneift. Stichwort "premature optimization".
Das bedeutet natürlich nicht, dass man ohne Sinn und Verstand rumhackt, aber zum Glück funktionieren mehr als die berühmten 80% auch ganz gut, wenn man seiner Erfahrung folgt und ein paar Spielregeln einhält.
 
Zurück
Oben