mysql: Query gesucht

[Pit]

Hallo Forum,

ich habe eine String-Spalte in mysql, die ich je nach Status in verschiedenen Nummernkreisen mit einem Kürzel vorangestellt hochzähle. Der Status des Vorganges steht in einer eigenen Spalte.

Beispiel: 
KL-0001 bis KL-9999 (innerhalb dieses Nummernkreises stehen auch KLB-Vorgänge)
FC-0001 bis FC-9999 (innerhalb dieses Nummernkreises stehen auch FCL-Vorgänge)
usw.

.

Tabelle:
ID|myStatus|MeineSpalte
1|KL|KL-0012
2|KLB|KLB-0013
3|FCL|FCL-0001
4|FC|FC-0002
usw.

Nun möchte ich in einer Abfrage z.b für den Status KL den derzeit höchsten Wert ermitteln:

SELECT
LPAD(MAX(CAST(SUBSTRING_INDEX(MeineSpalte, "-", -1) AS Wert)),4,"0") 
FROM myTable 
WHERE (myStatus= 'KL' OR myStatus = 'KLB')

Das funktioniert soweit auch sehr gut, selbst mit Datenbanken mit sehr vielen Einträgen.

Meine Frage:

Ich hätte gerne aus derselben Zeile des Maximumwertes auch die dazugehörige ID dieses Eintrages.

Also zum Beispiel:

Wert=0013 --> dazugehörige ID=2

Ich habe zwar auch hierzu eine Query, die funktioniert, aber die ist riesig lang und ich frage mich, ob es nicht auch leichter geht:

Select ID 
from myTable 
where 
(myStatus = 'KL' OR myStatus='KLB') 
AND (CAST(SUBSTRING_INDEX(MeineSpalte, "-", -1) AS Wert)) = 
(SELECT LPAD(MAX(CAST(SUBSTRING_INDEX(MeineSpalte, "-", -1) AS Wert)),4,"0") 
FROM myTable 
WHERE (myStatus = 'KL' OR myStatus='KLB')) 

Gruß, Pit

[Rolf B]

Hallo Pit,

doch, das ist genau richtig so. Willst Du den ganzen Satz zum MAX-Wert haben, brauchst Du entweder zwei Queries (und setzt in die zweite den gefundenen MAX Wert ein), oder Du musst die MAX-Ermittlung in die "ganzer Satz" Query einsetzen.

Wüst wird das ganze, weil dein Datenmodell nicht in Ordnung ist. Ich nehme an, dass Du mittlerweile genug Legacy-Code am Bein hast, dass Du das Modell nur mit massivem Aufwand ändern kannst. Aber diese Query schreit nach Modellverbesserungen, also denk bitte drüber nach und hol Dir vorher ein Buch über Datenmodellierung.

So, wie es jetzt ist, verletzt Du mit "meineSpalte" die erste Normalform, weil zwei Informationen in einer Spalte stehen: Status und Nummer. Deswegen musst Du "meineSpalte" mühsam zerlegen, um an die Nummer heranzukommen.

Die Unterscheidung KL / KLB kommt mir auch unnormalisiert vor. Du bestimmst das Maximum der laufenden Nummer für "KL" OR "KLB" - d.h. die beiden teilen sich einen Nummernkreis für die laufenden Nummern? Daraus folgere ich, dass das B in KLB sehr gerne eine eigene DB-Spalte wäre. Ich kenne die Semantik dieses B nicht, darum kann ich da nur hypothetisieren. Ich nenne das mal "statusOpt" wie "Status-Option". Aber vielleicht geht's ja auch nicht anders. Ich kenne deine Daten nicht.

Wäre die Table so designed:

|ID|yourStatus|statusOpt|deineSpalte |-|-|-|-|- |1|KL| |12 |2|KL|B|13 |3|FC|L|1 |4|FC| |2

dann könntest Du das Maximum für KL viel einfacher bestimmen:

SELECT MAX(deineSpalte)
FROM yourTable
WHERE myStatus = 'KL'

und den ganzen Satz bekommst Du mit

SELECT id, yourStatus, statusOpt, deineSpalte, fooSpalte, barSpalte, bazSpalte
FROM yourTable
WHERE myStatus = 'KL'
  AND deineSpalte = (SELECT MAX(deineSpalte) FROM yourTable WHERE myStatus = 'KL'))

Datenmodellierung vor dem Bau der Anwendung mag lästig klingen und auch Kopfschmerzen bereiten. Daten, die aus der Datenbank kommen, für die Darstellung immer erstmal aufbereiten zu müssen, ist auch lästig.

Aber deine Query ist ein Musterbeispiel für die Folgen fehlender Normalisierung und display-orientierter Datenspeicherung. Das macht einem am Ende viel mehr die Birne weich, als die vorherigen Workshops und zähen Diskussionen über "was wollen wir mit den Daten eigentlich machen" und "wie speichert man was am besten". Ich hab da Erfahrung mit. Ich arbeite seit '85 in einer großen Versicherung. Unsere DB-Admins sind wirklich anstrengend, wenn sie Modellierungsfehler wittern. Aber bisher hat es sich immer gelohnt.

Rolf

[Pit]

Hallo Rolf,

zum einen muß ich einen Fehler im Forum melden: Ich kann auf Deine Antwort keine Zitatantwort machen... 😟

Dann möchte ich mich für Deine ausführliche und schnelle Antwort bedanken. Danke! 😀

doch, das ist genau richtig so. Willst Du den ganzen Satz zum MAX-Wert >haben, brauchst Du entweder zwei Queries (und setzt in die zweite den >gefundenen MAX Wert ein), oder Du musst die MAX-Ermittlung in die "ganzer >Satz" Query einsetzen.

Ist wahr? Ui... na gut, dann muß ichs so machen. Dafür, dass die Query so arbeitsaufwändig für das db-System ist, läuft sie aber echt ganz ok von der Geschwindigkeit her.

Wüst wird das ganze, weil dein Datenmodell nicht in Ordnung ist. Ich >nehme an, dass Du mittlerweile genug Legacy-Code am Bein hast, dass Du >das Modell nur mit massivem Aufwand ändern kannst.

Ich ärgere mich grad, weil ich eigentlich ins Ausgangspost schreiben wollte, dass ich das heute ganz anders modellieren würde ;-) Das Datenmodell ist heute genau 15 Jahre alt. 😏 Ich lebe seit graumer Zeit damit und Du hast recht... der hieran hängende Code ist ziemlich gewaltig.

Aber deine Query ist ein Musterbeispiel für die Folgen fehlender >Normalisierung und display-orientierter Datenspeicherung.

Ich könnt noch mit 2-3 weiteren Beispielen aus meiner Anwendung diesbzgl. dienen :-( Und trotzdem...dafür, dass ich vor 15 Jahren mit der Datenmodellierung ganz am Anfang stand, ists wirklich bis heute stabil und performant... (natürlich bezogen auf die bei mir anfallende Datenmenge).

Das macht einem am Ende viel mehr die Birne weich, als die vorherigen Workshops und zähen >Diskussionen über "was wollen wir mit den Daten eigentlich machen" und >"wie speichert man was am besten". Ich hab da Erfahrung mit. Ich arbeite >seit '85 in einer großen Versicherung. Unsere DB-Admins sind wirklich >anstrengend, wenn sie Modellierungsfehler wittern. Aber bisher hat es >sich immer gelohnt.

Herrlich, so Geschichtchen aus dem Nähkästchen... 😀

Wie gesagt, heute mache ich viele Dinge anders und durchdachter...aber wer kennt das nicht... Altlasten (von einem selber oder auch von anderen) mit sich herumschleppen zu müssen 😉

Gruß, Pit

[Rolf B]

Hallo Pit,

Ich kann auf Deine Antwort keine Zitatantwort machen...

Nanu? Warum? Fehlt der Button "Antwort mit Zitat verfassen"? Guck mal in den Einstellungen unter "Komfort-Funktionen", da findest Du:

zitiere den vorherigen Beitrag per Default  
o  ja  
o  nein  
o  zeige zwei Buttons

Steht das bei Dir auf "Nein"?

Dafür, dass die Query so arbeitsaufwändig für das db-System ist, läuft sie aber echt ganz ok von der Geschwindigkeit her.

Ja, das ist bei einer großen DB wirklich erstaunlich. Die WHERE Bedingung grenzt die Menge der zu verarbeitenden Sätze auf bestimmte myStatus-Werte ein. Wenn das über Index gelingt und pro myStatus nicht zu viele Sätze in der DB sind, mag das der Grund sein. Die DB muss danach alle Sätze, die auf WHERE gepasst haben, lesen und deinen CAST(SUBSTRING_INDEX(myStatus, '-', -1) AS Wert) darauf anwenden, bevor das Maximum gebildet werden kann.

Wobei ich diesen Cast nicht verstehe. Die Syntax ist CAST(foo AS type), und Wert ist mal mit Sicherheit kein MYSQL-Typ. Dass es benutzerdefinierte Typen in MYSQL gäbe, wäre mir auch neu. Ist da beim Übertragen ins Forum was verändert worden? Es sieht aus wie: konvertiere den gefundenen Teilstring in eine Zahl, bilde das Maximum und konvertiere zurück in einen String.

Die Konvertierungen kannst Du sparen, wenn Du es so schreibst:
MAX(LPAD(SUBSTRING_INDEX(myStatus, '-', -1), 4, '0'))

Und es ist 14 Zeichen kürzer 😉

Rolf

[Matthias Apsel]

Hallo Rolf B,

Ich kann auf Deine Antwort keine Zitatantwort machen...

Nanu? Warum? Fehlt der Button "Antwort mit Zitat verfassen"?

Es gab einen JS-Bug.

Guck mal in den Einstellungen unter "Komfort-Funktionen", da findest Du:

Pit ist kein registrierter Benutzer.

Bis demnächst
Matthias

[Pit]

Hallo Rolf,

Nanu? Warum? Fehlt der Button "Antwort mit Zitat verfassen"? Guck mal in den Einstellungen unter "Komfort-Funktionen", da findest Du:

Nein, der war da. Das sah nach einem JS-Fehler aus, ich habs aber nicht weiter untersucht. Heute habe ich es nochmal versucht und es ging problemlos.

Ja, das ist bei einer großen DB wirklich erstaunlich. Die WHERE Bedingung grenzt die Menge der zu verarbeitenden Sätze auf bestimmte myStatus-Werte ein. Wenn das über Index gelingt und pro myStatus nicht zu viele Sätze in der DB sind, mag das der Grund sein. Die DB muss danach alle Sätze, die auf WHERE gepasst haben, lesen und deinen CAST(SUBSTRING_INDEX(myStatus, '-', -1) AS Wert) darauf anwenden, bevor das Maximum gebildet werden kann.

Ich frage mal ganz naiv in den Raum: Wäre es ggf. sinnvoller, über die Tabelle eine temporäre Tabelle zu erzeugen, innerhalb derer man die relevante Spalte in 3-4 Einzelspalten zerlegt und darin sucht? Diese Frage stelle ich auch vor allem vor dem Hintergrund meiner Nachfrage.

Wobei ich diesen Cast nicht verstehe. Die Syntax ist CAST(foo AS type), und Wert ist mal mit Sicherheit kein MYSQL-Typ. Dass es benutzerdefinierte Typen in MYSQL gäbe, wäre mir auch neu. Ist da beim Übertragen ins Forum was verändert worden? Es sieht aus wie: konvertiere den gefundenen Teilstring in eine Zahl, bilde das Maximum und konvertiere zurück in einen String.

Ich dachte, dass mysql nur das Max. von Integern suchen kann, daher der Cast. Das AS Wert ist ein Zurückkonvertieren zum String? Wußt ich nicht...

Die Konvertierungen kannst Du sparen, wenn Du es so schreibst:
MAX(LPAD(SUBSTRING_INDEX(myStatus, '-', -1), 4, '0'))

Mysql kann das? Muß ich veruschen...

Pit

[Rolf B]

Hallo Pit,

MAX kannst Du auf alles anwenden was sortierbar ist.

Wegen des AS: ich habe mich in deinen Klammern verirrt. Ich versuch's mal zu sortieren.

LPAD(
   MAX(
      CAST(
         SUBSTRING_INDEX(MeineSpalte,
                         "-",
                         -1
         ) AS Wert
      )
   ),4,"0"
) 

Das AS Wert gehört gar nicht zum CAST, da habe ich mich verlesen. Es ist ein Spalten-Alias für das SUBSTRING_INDEX Ergebnis.

Aber das macht es nicht besser. Nun fehlt dem CAST die Typenangabe, und die ist in keiner mir verfügbaren MYSQL-Doku optional. D.h. eigentlich ist das ein Syntaxerror, das SQL dürfte so nicht laufen.

Wäre es ggf. sinnvoller, über die Tabelle eine temporäre Tabelle zu erzeugen, innerhalb derer man die relevante Spalte in 3-4 Einzelspalten zerlegt

Wenn Deine "meineSpalte" nun tatsächlich NOCH mehr Attribute bündelt, tja, dann solltest Du vielleicht doch mal schauen, an wie vielen Stellen Du die Tabelle nutzt und über ein Redesign nachdenken.

Nun ja. Du könntest einen VIEW erzeugen, der meineSpalte zerlegt, und deine Query auf diesen View absetzen. Das macht die Query lesbarer. Das Erzeugen einer temporären Tabelle macht die Sache nicht wirklich performanter, und das Erzeugen der Einzelspalten geht zwar (z.B. mit geschachtelten SUBSTRING_INDEX), ist aber mühsam. Wenn es nur um "dieses Jahr" geht, könnte es einfacher sein, das in die Filterbedingung zu setzen, also myStatus = 'KL' AND meineSpalte LIKE 'KL-19%'.

Rolf

[MudGuard]

Hi,

Nun ja. Du könntest einen VIEW erzeugen, der meineSpalte zerlegt, und deine Query auf diesen View absetzen.

Oder 3 "generated" columns für die 3 Einzelteile.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

gute Idee. Die kann man dann auch als "stored" klassifizieren und einen Index draufsetzen.

Ich kann's hier nur nicht ausprobieren, mein Spiel-MYSQL ist aus Gründen Version 5.6, da gibt's das noch nicht.

Rolf

[Pit]

Hi,

Nun ja. Du könntest einen VIEW erzeugen, der meineSpalte zerlegt, und deine Query auf diesen View absetzen.

Oder 3 "generated" columns für die 3 Einzelteile.

cu,
Andreas a/k/a MudGuard

Wäre nett gewesen...

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (SQRT(sidea * sidea + sideb * sideb))
)' at line 4 

[Rolf B]

Hallo Pit,

MYSQL vor 5.7? Da gips das noch nicht. Nächstes Projekt. SQL Server upgraden 😉

Rolf

[Pit]

Hallo Pit,

MYSQL vor 5.7? Da gips das noch nicht. Nächstes Projekt. SQL Server upgraden 😉

Rolf

Hallo Rolf,

Genau...mysql 5.6... Ist aber nur mein Testserver...im Produktivbetrieb läuft die Version 7.2,

Ich habe übrigens heute über Tag die 3 nötigen Spalten an die Tabelle angehängt, gefüllt und dann alle Scripte angepasst.

Somit ist dieser 15 jahre alte Modellierungsfehler nun behoben :-)

Danke für die Hilfe,

Pit

[Pit]

Hallo Rolf,

Aber das macht es nicht besser. Nun fehlt dem CAST die Typenangabe, und die ist in keiner mir verfügbaren MYSQL-Doku optional. D.h. eigentlich ist das ein Syntaxerror, das SQL dürfte so nicht laufen.

Doch, doch...die läuft.

Wenn Deine "meineSpalte" nun tatsächlich NOCH mehr Attribute bündelt, tja, dann solltest Du vielleicht doch mal schauen, an wie vielen Stellen Du die Tabelle nutzt und über ein Redesign nachdenken.

...bin schon dabei (zu überlegen)

Nun ja. Du könntest einen VIEW erzeugen, der meineSpalte zerlegt, und deine Query auf diesen View absetzen. Das macht die Query lesbarer. Das Erzeugen einer temporären Tabelle macht die Sache nicht wirklich performanter

Ich meinte auch einen View, kannte aber den Namen hierfür nicht. Ich hatte gerade auch mal versucht, einen Trigger anzulegen, der für künftige Inserts ein Update auf die eingefügte Zeile macht, dass die Gesamtspalte nochmal auf Einzelspalten aufteilt. Das scheint Mysql aber nicht zu machen. Schade, das hätte mir erspart, diesbzgl. an den Quelltext herangehen zu müssen.

und das Erzeugen der Einzelspalten geht zwar (z.B. mit geschachtelten >SUBSTRING_INDEX), ist aber mühsam. Wenn es nur um "dieses Jahr" geht, >könnte es einfacher sein, das in die Filterbedingung zu setzen, also >myStatus = 'KL' AND meineSpalte LIKE 'KL-19%'.

Ja...das würde gehen. Ich bin bei LIKE, insbesondere mit Wildcarts immer ein bischen vorsichtig, die sehen für mich sehr arbeitsintensiv aus. Insgesamt scheint die Query echt zu einem Monster zu mutueren 😉

Pit

[Der Martin]

Moin,

Ich bin [...] insbesondere mit Wildcarts immer ein bischen vorsichtig

das ist auch gut so. Wildgewordene Einkaufswagen sollte man nicht unterschätzen!

*scnr*
 Martin

[Pit]

Moin,

Ich bin [...] insbesondere mit Wildcarts immer ein bischen vorsichtig

das ist auch gut so. Wildgewordene Einkaufswagen sollte man nicht unterschätzen!

*scnr*
 Martin

lol

[Rolf B]

Hallo Pit,

Ich bin bei LIKE, insbesondere mit Wildcarts immer ein bischen vorsichtig

Ein LIKE ohne % ist wenig hilfreich (es sei denn, es geht darum, Spaces am Feldende zu berücksichtigen). Und ich denke, ein LIKE ist immer noch besser im Einkaufswagen als geschachtelte SUBSTRING_INDEX-Aufrufe.

Die Generated Columns klingen aber nach einer verdammt guten Idee für Performance, kosten nur Speicher auf der Platte.

Rolf

[Pit]

Hallo,

was nun, wenn meine Tabelle eine andere wäre:

|ID|mySpalte |-|-|-|- |1|KL-19-12 |2|KLB-19-13 |3|FC-19-1 |4|FC-19-2 |5|KL-18-20

Nun möchte ich die Spalte des höchsten KL/KLB, aber aus diesem Jahr (an der 19 im "2.Sektor" zu erkennen).

Habe ich da eine Chance, in meine WHERE-Bedingung noch zusätzlich nach dem höchsten "2.-Sektorwert" zu suchen?

Pit

[Rolf B]

Hallo Pit,

siehe oben, mein Beitrag von 11:15

Rolf