Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Select Table mit Datumsbereichen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Ben2003, 6 August 2020.

  1. Ben2003

    Ben2003 Aktiver Benutzer

    Hallo,

    in einer Tabelle sind verschiedene Datumsangaben enthalten. z.B.

    CREATE TABLE TMP_Datum (
    ID int NOT NULL AUTO_INCREMENT,
    ID_NBKst int DEFAULT NULL,
    DatumStart date DEFAULT NULL,
    DatumEnd date DEFAULT NULL,
    PRIMARY KEY (ID)
    )

    Folgende Beispieldaten sind vorhanden:


    INSERT INTO TMP_Datum(ID, ID_NBKst, DatumStart, DatumEnd) VALUES
    (1, 1, '2019-09-01', '2019-10-31'),
    (2, 1, '2019-06-01', '2019-06-30');

    Wie man sehe kann, sind zwischen den einzelnen Datums-Bereichen Lücken (z.B. vom 2019-07-01 - 2019-08-31)

    Wenn als zu testender Bereich der 2019-01-01 bis 2019-12-31 angegeben wird, soll herauskommen:

    ID, ID_NbKst, DatumStart, DatumEnd
    ------------------------------------------------
    0, 1, '2019-01-01', '2019-05-31'
    2, 1, '2019-06-01', '2019-06-30'
    0, 1, '2019-07-01', '2019-08-31'
    1, 1, '2019-09-01', '2019-10-31'
    0,1,'2019-11-01', '2019-12-31'

    Diese Tabelle wird benötigt, um weitergehende Abfragen und Berechnungen ausführen zu können.

    Mir fällt nur folgende Lösung ein:

    1. Neue Tabelle erstellen z.B. Tab_BerechnetDatum
    2. Insert- Update-Trigger auf die Tabelle TMP_Datum erstellen mit der Funktion mit Insert bei jedem Anlegen bzw. Aktualisieren von Einträgen (in der Tabelle TMP_Datum) die komplette Tabelle Tab_BerechnetDatum neu zu füllen

    Das Füllen der neuen Tabelle könnte mit Hilfe einer For-Next-Schleife o.ä. erfolgen.

    Gibt es noch einen anderen Lösungsansatz ohne Einsatz einer zusätzlichen Tabelle? Das Füllen der Tabelle Tab_BerechnetDatum ist je nach Umfang der vorhandenen Einträge entsprechend aufwendig.

    Da MySql keine Tabelle als Rückgabewert von einer Funktion akzeptiert kann auch keine solche in einer Funktion generiert und als Return zurückgegeben werden.
     
  2. akretschmer

    akretschmer Datenbank-Guru

    also, ich glaube, Dein Ergebniss paßt nicht ganz zu Deinen Daten. Du suchst doch die Lücken, oder? Laß uns mal sehen:

    Code:
    test=*# select * from d;
             von_bis         
    -------------------------
     [2019-09-01,2019-10-31)
     [2019-06-01,2019-06-30)
    (2 rows)
    
    Das sind Deine Daten. Hier fehlt auch die Info, ob die Grenzen inclusive oder exclusive sein sollen. Du suchst nun zusammenhängende Bereiche innerhalb 2019, die davon nicht abgedeckt sind:

    Code:
    test=*# with x as (select * from generate_series('2019-01-01'::date, '2019-12-31'::date, '1day'::interval) as d), y as (select *, (d::date - lag(d::date,1) over (order by d)) as diff from x left join d on d.von_bis @> x.d::date where von_bis is null), z as (select *, sum (case when diff is null or diff > 1 then 1 else null end) over (order by d) as period from y) select min(d), max(d), period from z group by period;;
              min           |          max           | period
    ------------------------+------------------------+--------
     2019-01-01 00:00:00+01 | 2019-05-31 00:00:00+02 |      1
     2019-06-30 00:00:00+02 | 2019-08-31 00:00:00+02 |      2
     2019-10-31 00:00:00+01 | 2019-12-31 00:00:00+01 |      3
    (3 rows)
    
    Tabelle x stellt alle Tage des Jahres bereit
    Tabelle y die Tage, die davon nicht in Deinem Bereich sind
    Tabelle z berechnet daraus die Perioden

    und zum Schluß ermittle ich aus den einzelnen Perioden min und max. Das sind dann die Bereiche aus 2019, die nicht in Deiner Tabelle erfaßt sind.

    Und ja: PostgreSQL und RANGE-Typen, weil das das alles vereinfacht ...
     
  3. Ben2003

    Ben2003 Aktiver Benutzer

    Hallo akretschmer,

    auch wenn der Code nicht für MySQL Server geeignet ist, bin ich dadurch auf eine Idee gekommen, wie man es auch in MySQL lösen kann:

    Auf dieser Webseite wird der fehlende Befehl "generate_series" vorgestellt:
    generate_series() equivalent in MySQL
     
    akretschmer gefällt das.
  4. akretschmer

    akretschmer Datenbank-Guru

    Na prima, Danke für das Feedback.
     
  5. Ben2003

    Ben2003 Aktiver Benutzer

    Nachtrag:

    Nachteilig für die Ersatz-Lösung in MySQL ist, dass immer eine View mit einer ausreichenden Zahlenkolonne existieren muss, die als Quelle herangezogen werden kann.

    z.B.:

    Code:
    create view generator as
    Select 0 union Select 1 union Select 2 ....
    Anschließend kann man mit Hilfe dieser Liste alle weiteren Datums-Listen generieren und diese beliebig ausfiltern.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Tja, kann ja nicht alles so gut sein wie PostgreSQL, oder?
     
  7. dabadepdu

    dabadepdu Datenbank-Guru

    Das Beispiel mit Select 0 union .. ist zwar eine mögliche Lösung, aber es geht mit Hilfe von Variablen auch ohne "ausreichend Zahlenkolonnen" oder das Union Gebastel. Im Link wird ja gezeigt wie.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden