nicht angemeldet
Rolf B
MudGuard
MudGuard
Rolf B
Der Martin
Rolf B
Rolf B
Rolf B
Der Martin
Rolf B
MudGuard
Rolf B
Hallo,
ich suche gerade nach einer query, die mir aus einer Tabelle alle ID heraussucht, die in einem Zeitraum X (bei mir "das Jahr 2021) zum ersten Mal vorkamen.
Macht man das nach dem Motto
"Suche (einmal = distinct) alle IDs, die dem Kriterium 2021 entsprechen und die zugleich nicht dem Kriterium kleiner als 2021 entsprechen"
oder geht das anders?
Jörg
Hallo Jörg,
mein Ansatz wäre dieser:
SELECT DISTINCT id
FROM table
WHERE bedingung >= 2021
AND NOT EXISTS (SELECT DISTINCT id FROM table WHERE bedingung < 2021)
Ob das performant ist, muss man am konkreten Fall untersuchen.
Rolf
Hi,
SELECT DISTINCT id FROM table WHERE bedingung >= 2021 AND NOT EXISTS (SELECT DISTINCT id FROM table WHERE bedingung < 2021)
das würde dann auch die finden, deren erstes Vorkommen bei bedingung = 2022 ist.
Paßt nicht so ganz zu
die in einem Zeitraum X (bei mir "das Jahr 2021) zum ersten Mal vorkamen.
Ob das performant ist, muss man am konkreten Fall untersuchen.
Evtl. ist es performanter (das muß man aber ausprobieren), hier mit MINUS zu arbeiten. also
SELECT DISTINCT id FROM TABLE WHERE bedingung = 2021
MINUS
SELECT DISTINCT id FROM TABLE WHERE bedingung < 2021
Was performance-mäßig besser ist, hängt von den Indizes, der Tabellengröße, der Anzahl der Treffer für beide Teile, Häufigkeit der Abfrage usw. ab.
cu,
Andreas a/k/a MudGuard
Hallo Rolf, hallo Andreas,
SELECT DISTINCT id FROM table WHERE bedingung >= 2021 AND NOT EXISTS (SELECT DISTINCT id FROM table WHERE bedingung < 2021)das würde dann auch die finden, deren erstes Vorkommen bei bedingung = 2022 ist.
Paßt nicht so ganz zu
Doch, doch, das wäre schon ok, weil ich die query genau 1 x nutze, nämlich heute. 😉 Insofern spielt auch Performance eine untergeordnete Rolle.
Dummerweise erhalte ich aber keine Ergebnismange.
SELECT DISTINCT
ID
FROM
myTable
WHERE
Auftragsdatum > "2021-01-01" AND NOT EXISTS(
SELECT DISTINCT
ID
FROM
myTable
WHERE
Auftragsdatum < "2021-01-01"
)
Evtl. ist es performanter (das muß man aber ausprobieren), hier mit MINUS zu arbeiten. also
SELECT DISTINCT id FROM TABLE WHERE bedingung = 2021 MINUS SELECT DISTINCT id FROM TABLE WHERE bedingung < 2021
Diese Query will bei mir gar nicht (mysql).
#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'MINUS
SELECT DISTINCT ...
Jörg
Hi,
SELECT DISTINCT ID FROM myTable WHERE Auftragsdatum > "2021-01-01" AND NOT EXISTS( SELECT DISTINCT ID FROM myTable WHERE Auftragsdatum < "2021-01-01" )
Ergibt das innere Select eine Treffermenge?
Ergibt das äußere Select ohne das AND NOT EXISTS(...) eine Treffermenge?
Diese Query will bei mir gar nicht (mysql).
Ok, ich hab nicht überprüft, ob mysql das MINUS kennt - ich kenn's von ORACLE.
cu, Andreas a/k/a MudGuard
Hallo Jörg,
MINUS ist ein Mengenoperator aus Oracle SQL, andere SQLs nennen das EXCEPT DISTINCT. Dazu gehört aber nicht MySQL - merkwürdigerweise gibt's da nur UNION als Mengenoperator.
MariaDB kennt EXCEPT seit Version 10.3 und unterstützt MINUS ab Version 10.6.1 als Synonym.
Wegen des SQL - sorry, mein Mist.
SELECT DISTINCT
ID
FROM
myTable a
WHERE
Auftragsdatum > "2021-01-01" AND NOT EXISTS(
SELECT
ID
FROM
myTable b
WHERE
b.id = a.id
AND
Auftragsdatum < "2021-01-01"
Das DISTINCT kannst Du im Subselect weglassen, bei EXISTS geht's ja nur darum, ob mindestens einer existiert. Ich würde annehmen, dass der Server das DISTINCT an der Stelle eh wegoptimiert.
Wichtig ist aber der ID-Vergleich, denn Du willst ja wissen, ob diese ID vor 2021 existiert, und nicht irgendein Satz.
Rolf
Hi Rolf,
Wegen des SQL - sorry, mein Mist.
Hätte ich auch selber drauf kommen können/sollen.
SELECT DISTINCT ID FROM myTable a WHERE Auftragsdatum > "2021-01-01" AND NOT EXISTS( SELECT ID FROM myTable b WHERE b.id = a.id AND Auftragsdatum < "2021-01-01"
Ja, die läuft sauber.
Frage: Kannst Du mir erklären, warum die Anführungszeichen beim Datum wichtig sind? Oder anders, warum die ergebnissmenge ca. 10 x so groß ist, wenn ich die Anführungszeichen bei beiden Datums weglasse?
Jörg
Hallo,
Frage: Kannst Du mir erklären, warum die Anführungszeichen beim Datum wichtig sind? Oder anders, warum die ergebnissmenge ca. 10 x so groß ist, wenn ich die Anführungszeichen bei beiden Datums weglasse?
ohne die Anführungszeichen steht da einfach der numerische Ausdruck 2021-01-01, also 2019.
Make the most of your time
Martin
Hallo Martin,
ohne die Anführungszeichen steht da einfach der numerische Ausdruck 2021-01-01, also 2019.
Oh 😅
Hallo Jörg,
so weit ich weiß, gibt es in SQL kein Format für Datumsliterale. Deswegen schreibt man sie als String, typischerweise im ISO Format YYYY-MM-DD.
Update: Im ODBC API gibt es die Schreibweise { typ '....' }, mit d (date), t (time) oder ts (timestamp) als Typ. Aber nur da.
Ohne Anführungszeichen steht bei Dir
2021-01-01
und das ist ein numerischer Term, nämlich 2021 - 1 - 1 = 2019.
Man müsste jetzt nachschlagen, was MYSQL tut, wenn es ein Datum mit einer Zahl vergleichen soll. Gerade MYSQL ist für sein großzügiges Type Juggling berüchtigt - andere SQLs hauen Dir das Statement eher um die Ohren.
Möglicherweise gibt es in MYSQL eine Datumsdarstellung der Form "Tage seit Stichtag x" - wie bspw. in Excel, wo es Tage seit 1904 oder 1900 sind - und er konvertiert das Datum in diese Darstellung. Tage seit 190x ist eine recht hohe Zahl, selbst Tage seit dem UNIX time_t Stichtag 1970 ist schon fünfstellig. Und damit würde dann wohl außen jeder Satz deiner DB getroffen und im Subselect keiner.
Rolf
Hallo Rolf,
und das ist ein numerischer Term, nämlich 2021 - 1 - 1 = 2019.
Dir an dieser Stelle auch nochmal Dank für die erklärung 👍
Jörg
Habe jetzt 2 verschiedene Queries, die seltsamerweise zu unterschiedlichen Ergbnismengen führen:
Query 1 joined einfach noch die Kundentabelle (tableK) und hat 10% mehr ergebnisse. Würde das nicht dafür sprechen, dass zu einer KundenID mehrere Einträge in tableM gefunden werden? Falls ja, wie eliminiere ich die? Die Frage hat zwar für mich rein sportlichen wert, weil ich alle relevanten daten auch ohne tableK erhalte, aber interessant find ichs schon.
SELECT DISTINCT
m1.KundenID AS Kunden_ID,
k.... AS Kundennummer,
k.... AS Strasse,
FROM
tableM m1
JOIN tableK k ON m1.KundenID = k.KundenID
WHERE
m1.Auftragsdatum > "2021 -01 -01" AND NOT EXISTS(
SELECT
m2.KundenID
FROM
tableM m2
WHERE
m1.KundenID = m2.KundenID AND
m2.Auftragsdatum < "2021 -01 -01")
SELECT DISTINCT
m1.KundenID AS Kunden_ID
FROM
tableM m1
WHERE
m1.Auftragsdatum > "2021 -01 -01" AND NOT EXISTS(
SELECT
m2.KundenID
FROM
tableM m2
WHERE
m1.KundenID = m2.KundenID AND
m2.Auftragsdatum < "2021 -01 -01")
Hallo Jörg,
das sind deine Daten und deshalb solltest Du darüber auch den Überblick haben.
Meine Fernhypothese wäre: In TableK sind zu einer KundenId mehrere Sätze mit unterschiedlicher Kundennummer und/oder Straße. Deswegen liefert DISTINCT id, nummer, straße mehr Rows als DISTINCT id.
Die Query
SELECT xy.Kunden_ID, COUNT(*)
FROM (SELECT DISTINCT Kunden_ID, Kundennummer, straße
FROM tableK) xy
GROUP BY xy.Kunden_ID
HAVING COUNT(*) > 1
sollte Dir die IDs liefern, für die das der Fall ist.
Was mich zu der Frage führt: Warum Kunden_ID und Kundennummer? Reicht die Kundennummer nicht als eindeutiger und unveränderlicher Key? Hast Du eine Vorgabe, dass fachliche Attribute keine technischen Keys sein dürfen? Es ist jetzt sicherlich zu spät, um das zu ändern, aber neugierig bin ich schon.
Rolf
Hallo Rolf,
das sind deine Daten und deshalb solltest Du darüber auch den Überblick haben.
Stimmt. 😉
Meine Fernhypothese wäre: In TableK sind zu einer KundenId mehrere Sätze mit unterschiedlicher Kundennummer und/oder Straße. Deswegen liefert
DISTINCT id, nummer, straßemehr Rows alsDISTINCT id.
Nein, das ist so nicht. Kundenid und Kundennummer sind beide unique. Kundenid ist in tableK "primary Index", Kundennummer ist unique. Einzig in Tabelle tableM könnten mehrere identische Kundennummern oder Ids auftauchen.
Die Query
SELECT xy.Kunden_ID, COUNT(*) FROM (SELECT DISTINCT Kunden_ID, Kundennummer, straße FROM tableK) xy GROUP BY xy.Kunden_ID HAVING COUNT(*) > 1sollte Dir die IDs liefern, für die das der Fall ist.
MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze). (Die Abfrage dauerte 0.0211 Sekunden.)
Was mich zu der Frage führt: Warum Kunden_ID und Kundennummer? Reicht die Kundennummer nicht als eindeutiger und unveränderlicher Key? Hast Du eine Vorgabe, dass fachliche Attribute keine technischen Keys sein dürfen? Es ist jetzt sicherlich zu spät, um das zu ändern, aber neugierig bin ich schon.
Ja, diese Vorgabe habe ich selber an mich erteilt. Spaß beiseite, das lag schlicht daran, dass ich je nach Kunde Kundennummern aus altbeständen importieren musste und nicht alle waren "sauber". Damit aber gem. Kundenvorgabe dieser etwas zeit hatte, seinen Kundenstamm sukzessive zu bereinigen, benötigte ich einen eigenen unique Index und konnte erst später die Kundennummernspalte "unique" machen.
Jörg
Hallo Jörg,
m1.Auftragsdatum > "2021 -01 -01"
Das funktioniert? Die Spaces gehören nicht ins Date-Literal, würde ich behaupten. Aber auf die Menge der gefundenen Rows sollte das keinen Einfluss haben.
Duplikate der KundenID in TableM sind normal, schreibst Du. Ist ja auch logisch, wenn da Aufträge drinstehen, die KundenID kann in TableM dann nur ein Teilschlüssel sein.
In diesem Fall sollte der JOIN von TableK keine zusätzlichen Rows produzieren; für eine ID in TableM sollten immer die gleiche Kundennummer und Straße hinzugefügt werden und der DISTINCT sollte die gleiche Reduktion durchführen.
Ich habe keine Ahnung, warum sich die Ergebnismenge erhöht.
Gibt es einen Mengenunterschied`, wenn Du die NOT EXISTS Klausen weglässt, also zwischen
SELECT DISTINCT m1.KundenID AS Kunden_ID, k.... AS Kundennummer, k.... AS Strasse
FROM tableM m1 JOIN tableK k ON m1.KundenID = k.KundenID
und
SELECT DISTINCT m1.KundenID AS Kunden_ID FROM tableM m1
Wenn ja, versuch mal, die Ergebnismengen abzugleichen. Bei 10% sollte das relativ schnell einen Unterschied liefern. Ein ORDER BY Kunden_ID hilft ungemein 😉. Wenn es zu viele sind, versuch mal die Kunden_ID auf ein Teilintervall einzuschränken, das nicht zu viele Sätze liefert und wo dennoch ein Unterschied ist. Da kann man sich ggf. durch Intervallhalbierung herantasten.
Ah - Moment. I Spy With My Little Eye
k.... AS Strasse,
A Comma.
Sind in deiner realen Query noch weitere Spalten, die den DISTINCT beeinflussen könnten?
Rolf
Hallo Rolf,
Sind in deiner realen Query noch weitere Spalten, die den DISTINCT beeinflussen könnten?
Ja...sorry für den Fehlalarm. 😔
SELECT DISTINCT
m1.KundenID AS Kunden_ID,
m1.Bemerkungen
Daran lag es. So konnten sich einige KundenIds hinzuschmuggeln, die eine Bemerkung beinhalteten.
Mist, nicht gemerkt.
Jörg
Hallo Rolf,
m1.Auftragsdatum > "2021 -01 -01"Das funktioniert?
warum denn nicht? Es ist, so wie es da steht, ein Stringvergleich. Und wenn Jörg sowieso nur die Jahreszahl betrachtet (so habe ich den Thread bisher verstanden), macht der Rest danach keinen Unterschied.
Die Spaces gehören nicht ins Date-Literal, würde ich behaupten.
Ja, wenn das DBMS das wirklich als Datum interpretieren soll.
Make the most of your time
Martin
Hallo Martin,
Es ist, so wie es da steht, ein Stringvergleich.
Ach ja, stimmt. Aber das bedeutet dann, dass MYSQL jedes Date in der DB vor dem Vergleich in einen String konvertieren muss. Falls das Auftragsdatum in einem Index steht, würde es dazu führen, dass der Index nicht genutzt werden kann.
Verwendet man Auftragsdatum > '2021-01-01' ist der String ein gültiges Datum und MYSQL kann daraus einen Date-Typ machen. Das sollte dann performanter sein, bzw. die Nutzung eines Index nicht blockieren.
Müsste man durchprobieren und -explainen…
Und nebenbei müsste man wohl auch >= statt > testen.
Rolf
Hi,
so weit ich weiß, gibt es in SQL kein Format für Datumsliterale. Deswegen schreibt man sie als String, typischerweise im ISO Format YYYY-MM-DD.
für Ad-Hoc-Queries ist das akzeptabel, aber für Programme, bei denen man nicht weiß, wie genau die DB konfiguriert sein wird, würde ich im Sinne von defensive programming auf jeden Fall STR_TO_DATE verwenden.
Dann ist man sicher, daß es funktioniert, auch wenn ggf. ein anderes Datumsformat im String-Literal erwartet wird.
cu,
Andreas a/k/a MudGuard
Hallo Andreas,
Jörg hat MYSQL als Tag angegeben. In MYSQL ist ein Date-Literal lauf Handbuch festgelegt, als String im Format 'YYYY-MM-DD' oder 'YY-MM-DD'. Und dabei haben sich die Kameraden noch die Prüfung auf "ist es ein '-'" gespart (bzw. sie sagen, dass es jedes "punctuation character" sein kann), man kann auch '2021*11+05' schreiben. Weitere erlaubte Formate sind die Stringformate 'YYYYMMDD', 'YYMMDD' und die Integerformate YYYYMMDD und YYMMDD. Immer vorausgesetzt, dass YY/YYYY, MM und DD sinnvolle Werte für ein Datum haben.
Eine Einstellung, die es erlauben würde, bspw. '05.11.2021' als Datumsstring zu verwenden, habe ich weder in MYSQL noch in MARIA-DB gefunden. In Microsoft SQL Server gibt's SET DATEFORMAT, aber nicht in MYSQL.
Übersehe ich was?
Rolf
Hi,
Eine Einstellung, die es erlauben würde, bspw. '05.11.2021' als Datumsstring zu verwenden, habe ich weder in MYSQL noch in MARIA-DB gefunden.
Ich hab's nicht überprüft, ob es eine solche Möglichkeit gibt.
Und selbst wenn es keine gibt: kannst Du garantieren, daß es auch in 5 Jahren keine gibt?
cu,
Andreas a/k/a MudGuard
Hallo MudGuard,
nö, kann icht nicht. Muss ich auch nicht. Wenn so was hinzukommt, erwarte ich, dass es eine Einstellung für "bleibe beim Alten" gibt.
Aber Du hast natürlich recht, dass man sich damit von den Defaults entkoppelt.
STR_TO_DATE habe ich tatsächlich noch nie gesehen. Aber ich mach auch nicht so viel MYSQL…
Rolf
Nachdem ich nun die Ergebnismenge habe, erzeugt mir php-myadmin leider sowohl als csv-export, als auch als csv for ms-excel ein defektes Archiv (hat sonst immer geklappt).
Wie bekomme ich nun meine Daten in eine exceltabelle für den Kunden?
Jörg
Hallo Jörg,
ich nehme an, dass deine DB irgendwo auf einem gehosteten Server läuft und nicht auf der gleichen Maschine wie Excel, oder? In dem Fall bist Du auf phpmyadmin angewiesen. Wenn der DB Server auf deiner eigenen Maschine läuft, könnte man es auch mit einer ODBC-Datenquelle versuchen und den Zugriff direkt aus EXCEL machen (was eigene „Hürden“[1] hat).
Das phpmyadmin meines Hosters hat Version 5.1.1 - wenn Du eine andere hast, reden wir jetzt möglicherweise aneinander vorbei.
Ist das Archiv defekt? Oder die CSV-Datei? Gibt's in irgendeinem Feld einen Zeilenumbruch oder ein Anführungszeichen? Dann solltest Du auf jeden Fall CSV für EXCEL verwenden, das garantiert Dir, dass Strings in Anführungszeichen stehen und Anführungszeichen im Text korrekt verdoppelt werden. Wenn Du "CSV" auswählst, kannst Du ganz unten noch verschiedene CSV-Optionen einstellen.
Wenn Du in phpmyadmin beim Export angibst, dass Du nicht nur die schnellen Optionen, sondern alle sehen willst, dann kannst Du die Kompression auch abschalten. Bzw. auswählen, ob es ZIP oder GZIP ist - hast Du GZIP erstellt und willst es mit ZIP auspacken?
Oder Du kannst sagen, dass Du die Sätze als Text im Browser haben willst, dann kannst Du kontrollieren, was dabei rauskommt, und dann machst Du Copy+Paste (ggf. in Excel gefolgt von "Text in Spalten").
Rolf
Ich schreibe leichthin „Hürden“, es sind aber wohl eher steile, vergletscherte Bergwände. ↩︎
Hallo Rolf,
ich nehme an, dass deine DB irgendwo auf einem gehosteten Server läuft und nicht auf der gleichen Maschine wie Excel, oder?
Ich selber nutze gar kein Excel.
Ist das Archiv defekt? Oder die CSV-Datei? Gibt's in irgendeinem Feld einen Zeilenumbruch oder ein Anführungszeichen? Dann solltest Du auf jeden Fall CSV für EXCEL verwenden, das garantiert Dir, dass Strings in Anführungszeichen stehen und Anführungszeichen im Text korrekt verdoppelt werden. Wenn Du "CSV" auswählst, kannst Du ganz unten noch verschiedene CSV-Optionen einstellen.
Das Archiv ist defekt.
Wenn Du in phpmyadmin beim Export angibst, dass Du nicht nur die schnellen Optionen, sondern alle sehen willst, dann kannst Du die Kompression auch abschalten. Bzw. auswählen, ob es ZIP oder GZIP ist - hast Du GZIP erstellt und willst es mit ZIP auspacken?
Achja, sooo ging das. Ich hatte mich schon gefragt, wo ich das packen ausschalten kann. Habe ich soeben ausprobiert und erhalte m,eine csv-Datei. Prima, denn ab hier weiß ich wieder, wie es geht.
Vielen Dank für Deine Hilfe. 👍🙂
Gruß, Jörg