Tabelle auslesen

[Ben2003]

Verwendeter Datenbank-Server: MySQL 8.0 Community Edition

Hallo,

in einer Tabelle werden von diversen Zählern (z.B. Strom- Gas- Wasserzählern) regelmäßig die Stände eingetragen.

Die Tabelle hat folgenden Aufbau:

CREATE TABLE tab_ZaehlerAblese (
ID int NOT NULL AUTO_INCREMENT,
ID_Zaehler int DEFAULT NULL,
Stand decimal(18, 4) DEFAULT NULL,
Ablesedatum datetime DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 35,
AVG_ROW_LENGTH = 819,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

In der Tabelle sind Einträge wie z.B. diese vorhanden:
ZaehlerAbleseDaten.png

Die Tarif-Informationen stehen u.a. in dieser Tabelle:

CREATE TABLE tab_ZaehlerTarifPostenDetails (
ID int NOT NULL AUTO_INCREMENT,
ID_Zaehler int NOT NULL,
StartDatum datetime DEFAULT NULL,
EndDatum datetime DEFAULT NULL,
ZusatzText varchar(510) DEFAULT NULL,
Betrag decimal(15, 5) DEFAULT NULL,
PRIMARY KEY (ID)
)
ENGINE = INNODB,
AUTO_INCREMENT = 23,
AVG_ROW_LENGTH = 1365,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_0900_ai_ci;

Über das Feld ID_Zaehler sind die beiden Tabellen verbunden.

Die Tarif-Gültigkeit wird mit den Angaben in den Feldern StartDatum und EndDatum begrenzt. Das StartDatum ist immer angegeben; das EndDatum kann auch Null-Werte enthalten, falls der Tarif kein EndDatum aufweist.

Zu meinem Anliegen:

Ich stehe nun etwas auf dem Schlauch, da mir nicht einfällt, wie man die Verbrauchswerte für die einzelnen Ablese-Zeiten ermitteln kann, für die passende Tarife (Tabelle: tab_ZaehlerTarifPostenDetails) existieren.

Hat da jemand eine Idee?

 

[akretschmer]

Na, ich zeig dir das mal, aber mit PostgreSQL, weil ist einfacher zu erklären.

Du hast:

test=*# select * from zaehler ;
 id |         von_bis         | betrag
----+-------------------------+--------
  1 | [2020-01-01,2020-02-01) |     10
  1 | [2020-02-01,2020-03-01) |     15
  1 | [2020-03-01,2020-04-01) |     25
  2 | [2020-01-01,2020-02-01) |    100
  2 | [2020-02-01,2020-03-01) |    150
  2 | [2020-03-01,2020-04-01) |    180
(6 rows)

test=*# select * from ablese ;
 zaehler |   datum    | stand
---------+------------+-------
       1 | 2020-01-02 |    50
       1 | 2020-01-22 |    55
       1 | 2020-02-12 |    65
       1 | 2020-03-20 |   165
       2 | 2020-01-02 |   500
       2 | 2020-02-02 |   580
(6 rows)

1. Teilaufgabe: die Differenzen berechnen bei der Ablesung

test=*# with ablesungen as (select zaehler, datum, coalesce(stand - lag(stand) over (partition by zaehler order by datum),0) as diff from ablese) select * from ablesungen;
 zaehler |   datum    | diff
---------+------------+------
       1 | 2020-01-02 |    0
       1 | 2020-01-22 |    5
       1 | 2020-02-12 |   10
       1 | 2020-03-20 |  100
       2 | 2020-01-02 |    0
       2 | 2020-02-02 |   80
(6 rows)

soweit, so gut. Nun joinen wir unsere Zähler-Stammdaten dazu und rechnen das aus, Ist einfach jetzt:

test=*# with ablesungen as (select zaehler, datum, coalesce(stand - lag(stand) over (partition by zaehler order by datum),0) as diff from ablese) select a.zaehler, a.datum, a.diff, z.von_bis, z.betrag, a.diff*z.betrag as preis from ablesungen a left join zaehler z on a.zaehler=z.id and a.datum <@ z.von_bis;
 zaehler |   datum    | diff |         von_bis         | betrag | preis
---------+------------+------+-------------------------+--------+-------
       1 | 2020-01-02 |    0 | [2020-01-01,2020-02-01) |     10 |     0
       1 | 2020-01-22 |    5 | [2020-01-01,2020-02-01) |     10 |    50
       1 | 2020-02-12 |   10 | [2020-02-01,2020-03-01) |     15 |   150
       1 | 2020-03-20 |  100 | [2020-03-01,2020-04-01) |     25 |  2500
       2 | 2020-01-02 |    0 | [2020-01-01,2020-02-01) |    100 |     0
       2 | 2020-02-02 |   80 | [2020-02-01,2020-03-01) |    150 | 12000
(6 rows)

Kaffee!

 

[Ben2003]

Hallo akretchmer,

das ist ja eine geniale Lösung. Da hast Du Dir absolut den Morgenkaffee redlich verdient.

Auch in MySQL funktioniert es prima.

Nur beim letzten Select-Befehl kennt MySQL von der Angabe "and a.datum <@ z.von_bis" das "@" Zeichen nicht.

Wird durch das "@"-Zeichen der varchar-Inhalt vom Feld "von_bis" in eine Datums-Angabe konvertiert?

 

[akretschmer]

Nur beim letzten Select-Befehl kennt MySQL von der Angabe "and a.datum <@ z.von_bis" das "@" Zeichen nicht.

Wird durch das "@"-Zeichen der varchar-Inhalt vom Feld "von_bis" in eine Datums-Angabe konvertiert?

Nein, das ist kein varchar, sondern ein DATERANGE:

test=*# \d+ zaehler
                                    Table "public.zaehler"
 Column  |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description
---------+-----------+-----------+----------+---------+----------+--------------+-------------
 id      | integer   |           | not null |         | plain    |              |
 von_bis | daterange |           | not null |         | extended |              |
 betrag  | integer   |           |          |         | plain    |              |
Indexes:
    "zaehler_pkey" PRIMARY KEY, btree (id, von_bis)
    "zaehler_id_von_bis_excl" EXCLUDE USING gist (id WITH =, von_bis WITH &&)
Access method: heap

test=*#

und das <@ prüft, ob ein Wert innerhalb der Range liegt. In der Tabellendefinition ist übrigens noch ein Constraint, der verhindert, daß sich überlappende Zeitbereiche für eine Zähler-ID eingegeben werden können:

test=*# insert into zaehler values (2, '[2020-01-01,2020-04-01)', 999);
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint »zaehler_id_von_bis_excl«
DETAIL:  Schlüssel (id, von_bis)=(2, [2020-01-01,2020-04-01)) kollidiert mit vorhandenem Schlüssel (id, von_bis)=(2, [2020-01-01,2020-02-01)).
test=*#

 

[Ben2003]

Hallo akretschmer,

vielleicht hast Du noch eine Idee zu dieser Aufgabe?

Hinter manchen Zählern hängen Zwischenzähler dran. Der Hauptzähler zählt dann natürlich auch den Verbrauch der Zwischenzähler mit.

In der Tabelle sind die Zähler schon entsprechend der Hirarchie verfasst worden:

ID int
OID int
Zaehler varchar(100) -- Bezeichnung eines Zählers

Als Baumstruktur schaut es so beispielsweise aus:

HauptZähler A
.- ZwischenZähler A1
.- ZwischenZähler A2
....- ZwischenZähler B1
.....- ...
.- ZwischenZähler A3
.- ZwischenZähler A4

Info: Die Punkte zu Anfang einer Zeile haben keine Bedeutung, diese wurde nur eingesetzt, weil die Leerzeichen in der HTML-Ansicht verschwinden.

In einem Fall sind sogar vier Zwischenzähler hinter einem Hauptzähler

In der View ermittelt ja ...

coalesce(stand - lag(stand) over (partition by zaehler order by datum),0) AS diff

... die Differenz zwischen zwei Ständen.

Gibt es auch eine ergänzende "Berechnungsformel", mit der eine Nettodifferenz ermittelt werden kann?

Es gibt schon eine View, die ein JSON-Array mit allen IDs von den Zählern, die Hirarchisch unterhalb eines Zählers folgen:

Z.B.:

ID......OID..JSON
3........0......[4, 7, 9 12]
6........0
8........0......[10]
11.......0
4........3
7........3
9........3......[12]
10......8
12......9

Zur Info: Bei einer Ablese-Aktion werden immer alle Zähler an einem Tag abgelesen. Es brauchen also nicht noch irgendwelche zusätzlichen Mathematische Dreisatz-Berechnungen ausgeführt werden, um den mathematischen Stand ermitteln zu können.

 

[Ben2003]

Nachtrag:

Beim Schreiben ist mir aufgefallen, dass mir ein Fehler unterlaufen ist. Es darf nämlich nur in der Hirarchie eine Ebene weiter "geschaut" werden, da die nächste Ebene ja schon alles mitzählt, was noch in der Hirarchie weiter tiefer folgen würde.

In der View wäre die "12" beim ID-Zähler "3" demnach falsch.

 

[Ben2003]

Zwischenzeitlich wurde eine Lösung gefunden:

Um die Differenz von allen Zwischenzählern in der nächsten Ebene zu bekommen, muss die Berechnungszeile ...

coalesce(stand - lag(stand) over (partition by zaehler order by datum),0) AS diff

... für alle relevanten Zwischenzähler unter Berücksichtigung des Ablesedatums der nächsten Ebene ermittelt und anschließend summiert werden.

Anstelle der JSON-Angaben ...

ID......OID..JSON
3........0......[4, 7, 9 12]
6........0
8........0......[10]
11.......0
4........3
7........3
9........3......[12]
10......8
12......9

... brauchen lediglich die OID-Angaben direkt aus der Tabelle der Zähler als Kriterium eingesetzt werden.