Jörg: sql: Alle IDs suchen, die in Zeitraum x zum ersten Mal vorkamen

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

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

    --
    sumpsi - posui - obstruxi
    1. 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

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

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

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

          --
          sumpsi - posui - obstruxi
          1. 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

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

              --
              Es gibt Tage, da gelingt dir einfach alles. Aber keine Sorge, das geht schnell vorbei.
              1. Hallo Martin,

                ohne die Anführungszeichen steht da einfach der numerische Ausdruck 2021-01-01, also 2019.

                Oh 😅

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

              --
              sumpsi - posui - obstruxi
              1. 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

              2. 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")
                
                1. 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

                  --
                  sumpsi - posui - obstruxi
                  1. 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ße mehr Rows als DISTINCT 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(*) > 1
                    

                    sollte 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

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

                      --
                      sumpsi - posui - obstruxi
                      1. 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

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

                        --
                        Es gibt Tage, da gelingt dir einfach alles. Aber keine Sorge, das geht schnell vorbei.
                        1. 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

                          --
                          sumpsi - posui - obstruxi
              3. 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

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

                  --
                  sumpsi - posui - obstruxi
                  1. 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

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

                      --
                      sumpsi - posui - obstruxi
  2. 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

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

      --
      sumpsi - posui - obstruxi

      1. Ich schreibe leichthin „Hürden“, es sind aber wohl eher steile, vergletscherte Bergwände. ↩︎

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