Zeitreihen-Abfrage mit Lücken

[Sebastian Suchanek]

Hallo NG!

Um zu verhindern, dass das Folgende zu einem XY-Problem wird, muss ich
etwas ausholen...

Also: In einer Tabelle habe ich - neben anderen Daten - in jedem
Datensatz ein Datumsfeld. Die Daten in der Tabelle könnten zum Beispiel
so aussehen:

2019-09-29
2019-11-15
2020-01-03
2020-01-03

Wenn ich diese Daten nach Jahr *und* Monat gruppiert abfrage, erhalte
ich bei o.g. Beispieldaten das hier:

SELECT YEAR(Datum), MONTH(Datum), COUNT(*)
FROM Tabelle GROUP BY YEAR(Datum), MONTH(Datum)

2019 9 1
2019 11 1
2020 1 2

So weit, so klar. Was ich allerdings erreichen möchte, ist, dass evtl.
vorhandene zeitliche "Lücken" nicht gar nicht, sondern als 0 im Ergebnis
auftauchen. Bezogen auf o.g. Beispieldaten also so:

2019 9 1
2019 10 0
2019 11 1
2019 12 0
2020 1 2

Natürlich habe ich dazu selbst schon recherchiert. Man findet dabei für
dieses Problem allenthalben den Vorschlag, sich eine Hilfstabelle zu
bauen, die alle im Endergebnis gewünschten Zeitblöcke enthält und die
eigentliche Datentabelle dann mit einem LEFT JOIN mit der Hilfstabelle
zu verknüpfen. Vom Prinzip ebenfalls völlig logisch.

Aber: Wie bekommt so eine Hilfstabelle in der Praxis am besten?
Natürlich kann ich mir eine leere TEMPORARY TABLE erstellen und dann auf
Applikationsebene mir zuerst den frühesten und spätesten Datensatz von
Interesse geben lassen und darauf aufbauend mit Schleifenkonstrukten
ebenfalls von der Applikationsebene aus die Hilfstabelle zeilweise
befüllen. Klappt sicherlich, wirkt auf mich aber ziemlich ineffizient.

Also: Kann man sich eine solche Hilfstabelle irgendwie relativ effizient
sozusagen mit MySQL-Bordmitteln erstellen, ohne den Umweg über die
Applikationsebene gehen zu müssen? NB: Wie man am obigen Beispiel sehen
kann, können Start und Ende auch jeweils mitten im Jahr liegen.
Oder hat jemand noch völlig andere Lösungsvorschläge für das
Ursprungsproblem, "leere" Zeitgruppen als 0 im Ergebnis zu haben?


TIA,

Sebastian

[Axel Schwenke]

On 19.01.2020 21:14, Sebastian Suchanek wrote:

(schnipp)

> Man findet ...

> allenthalben den Vorschlag, sich eine Hilfstabelle zu
> bauen, die alle im Endergebnis gewünschten Zeitblöcke enthält und die
> eigentliche Datentabelle dann mit einem LEFT JOIN mit der Hilfstabelle
> zu verknüpfen. Vom Prinzip ebenfalls völlig logisch.
>
> Aber: Wie bekommt so eine Hilfstabelle in der Praxis am besten?

IMHO am elegantesten ist die Verwendung der Sequence Engine. Die gibt es als
Plugin für MySQL. Bzw. wenn man MariaDB benutzt, dann ist man schon da:

MariaDB [none]> use test;
Database changed
MariaDB [test]> show tables;
Empty set (0.01 sec)

MariaDB [test]> select seq, 2019+floor(seq/12) as year, (seq%12)+1 as month
from seq_2_to_13;
+-----+------+-------+
| seq | year | month |
+-----+------+-------+
| 2 | 2019 | 3 |
| 3 | 2019 | 4 |
| 4 | 2019 | 5 |
| 5 | 2019 | 6 |
| 6 | 2019 | 7 |
| 7 | 2019 | 8 |
| 8 | 2019 | 9 |
| 9 | 2019 | 10 |
| 10 | 2019 | 11 |
| 11 | 2019 | 12 |
| 12 | 2020 | 1 |
| 13 | 2020 | 2 |
+-----+------+-------+
12 rows in set (0.00 sec)


https://mariadb.com/kb/en/sequence-storage-engine/


HTH, XL

[Sebastian Suchanek]

Am 20.01.2020 um 16:04 schrieb Axel Schwenke:
> On 19.01.2020 21:14, Sebastian Suchanek wrote:
>
> (schnipp)
>
>> Man findet ...
>> allenthalben den Vorschlag, sich eine Hilfstabelle zu
>> bauen, die alle im Endergebnis gewünschten Zeitblöcke enthält und die
>> eigentliche Datentabelle dann mit einem LEFT JOIN mit der Hilfstabelle
>> zu verknüpfen. Vom Prinzip ebenfalls völlig logisch.
>>
>> Aber: Wie bekommt so eine Hilfstabelle in der Praxis am besten?
>
> IMHO am elegantesten ist die Verwendung der Sequence Engine.

> [...]
> https://mariadb.com/kb/en/sequence-storage-engine/

Danke, das klingt interessant - das werde ich mir mal näher ansehen.


Tschüs,

Sebastian