Datenbankinhalte effizient auslesen (Seite 4)

Listing 3 zeigt einen View namens AverageParticleToday, der den durchschnittlich gemessenen Feinstaubwert für alle Sensoren für den aktuellen Tag ermittelt. Es benutzt dazu die PostgreSQL-eigene Funktion avg(), die den Durchschnitt von numerischen Werten errechnet.

CREATE VIEW AverageParticleToday (value) AS
  SELECT avg(particles)
  FROM sensorData
  WHERE timeOfCapture BETWEEN 'today' AND 'tomorrow';

Die beiden Angaben today und tomorrow in der Select-Anweisung sind spezielle Werte für Zeitstempel, die PostgreSQL in “heute, 00:00:00” und “morgen, 00:00:00” übersetzt. Das Schlüsselwort BETWEEN sorgt dabei für eine Bereichsbegrenzung von “heute, 00:00:00” bis “heute, 23:59:59” (“morgen, 00:00:00” minus 1 Sekunde).

Listing 4 zeigt den Aufruf des Views. Das Ergebnis ist eine Spalte value, die nur eine einzige Zeile besitzt: den vorab berechneten Durchschnittswert. Der Spaltenname wurde in der ersten Zeile von Listing 3 als Parameter in der Anweisung CREATE VIEW definiert.

sensornetwork=> SELECT * FROM AverageParticleToday;
      value
------------------
 16.6666666666667
(1 Zeile)

Soll die Spalte im Ergebnis einen anderen Namen tragen, ändern Sie entweder den View aus Listing 3 oder geben der Spalte in der Select-Anweisung einen Alias. Listing 5 erzeugt durch Übergabe eines Alias eine Ergebnisspalte namens AverageParticles.

sensornetwork=> SELECT value AS AverageParticles FROM AverageParticleToday;
 AverageParticles
------------------
 16.6666666666667
(1 Zeile)

Der Nachteil von Views liegt darin, dass sie keine Aufrufparameter akzeptieren. Das schränkt die Flexibilität ein. Hier kommen benutzerdefinierte Funktionen oder Stored Procedures ins Spiel, die diesen Nachteil kompensieren. Beachten Sie aber: Stored Procedures stehen erst ab PostgreSQL 11 bereit. In einer Funktion können Sie zudem keine Transaktion beginnen (BEGIN TRANSACTION), abschließen (COMMIT) oder zurückrollen.

Funktionen und Stored Procedures haben außerdem zunächst keinen Rückgabewert (abgesehen vom Fehlercode zum Aufruf). In Funktionen definieren Sie dazu eine Variable, in der Stored Procedure einen Cursor. Das Ergebnis ist dann eine Ergebnismenge, die intern als temporäre Tabelle implementiert ist [32]. Bei Funktionen bestimmen Sie den Datentyp der Ergebnismenge, bei Stored Procedures ist es immer der spezielle Datentyp [33] refcursor [34].

In Listing 6 geht es diesmal nicht um Feinstaubpartikel, sondern um die Durchschnittstemperatur. Sie sehen nun eine Funktion namens AverageTemperatureSensor, die die durchschnittlich gemessene Temperatur für eine bestimmte Messstation für einen von Ihnen angegebenen Zeitraum ermittelt.

CREATE FUNCTION AverageTemperatureSensor (
  id INTEGER,
  startTime TIMESTAMP,
  endTime TIMESTAMP
)
RETURNS REAL
LANGUAGE plpgsql
AS $$
DECLARE AverageTemperature REAL;
BEGIN
  SELECT avg(temperature)
  INTO AverageTemperature
  FROM sensorData
  WHERE (timeOfCapture BETWEEN startTime AND endTime) AND (id = stationId);
  RETURN AverageTemperature;
END;$$;

Als Parameter akzeptiert die Funktion die ID der Messstation sowie den Beginn und das Ende des Zeitraums (Zeile 2 bis 4). Der Rückgabewert ist ein Zahlenwert vom Datentyp REAL (Zeile 6). Die Funktion selbst ist in PLPGSQL geschrieben (ab Zeile 7), wobei PostgreSQL auch andere Sprachen wie C oder Python akzeptiert.

Der Funktionsinhalt befindet sich in den Zeilen 9 bis 17 und besteht aus der Deklaration der Variablen AverageTemperature vom Datentyp REAL, aus einer Select-Anweisung und dem Beenden der Funktion samt Rückgabe der Berechnung. Zeile 12 sorgt dafür, dass das Ergebnis der Select-Anweisung in der Variablen AverageTemperature gespeichert wird. In Zeile 16 wird der errechnete Wert zurückgegeben.

Listing 7 zeigt den Aufruf der Funktion in PostgreSQL, der als Ergebnis die Spalte averagetemperature mit nur einer einzige Zeile (dem vorab berechneten Durchschnittswert) liefert. Im Listing sehen Sie zwei Aufrufe für die Messstation mit der ID 1, zuerst mit zwei festen Zeitwerten und danach für den aktuellen Tag mithilfe der beiden Schlüsselwörter today und tomorrow. Beim Aufruf der Funktion ermittelt PostgreSQL darüber den aktuellen Zeitpunkt und ersetzt die beiden Schlüsselwörter mit passenden Werten.

sensornetwork=> SELECT AverageTemperature FROM AverageTemperatureSensor(1,'2022-01-01 00:00:00','2022-01-03 23:59:59');
    averagetemperature
--------------------------
                       10
(1 Zeile)
sensornetwork=> SELECT AverageTemperature FROM AverageTemperatureSensor(1,'today','tomorrow');
    averagetemperature
--------------------------
                  16.6667
(1 Zeile)

Performance-Tests

Ist die Datenbank fertig strukturiert und das Messnetz eingerichtet, kann es eigentlich mit dem Erfassen der Umweltdaten losgehen. Ratsam ist jedoch noch ein Performance-Test, um herauszufinden, ob die konzipierte Anwendung mit der prognostizierten Last überhaupt umgehen kann. Es wäre ja schlecht, wenn sich ein Flaschenhals offenbart, nachdem Sie Ihr Netz in der freien Wildbahn ausgerollt haben.