PostgreSQL PL/pgSQL Schulung: Fensterfunktionen und Trigger

Zusammenfassende Statistiken durch Gruppierung

• Datenzeilen mit GROUP BY aggregieren, HAVING für Gruppenbedingungen
• Allgemeine Aggregatstatistiken: count, sum, avg, min, max
• Spalten mit booleschen Aggregaten bool_and und bool_or auswerten
• Standardabweichung und Varianz: stddev_samp, stddev_pop, var_samp
• Zwischen- und Gesamtsummen mit ROLLUP einbeziehen
• Alle Gruppenkombinationen mit CUBE und gezielte Kombinationen mit GROUPING SETS
• Summenzeilen mit GROUPING() erkennen und beschriften
• Gruppen segmentieren mit Aggregatfiltern: count(*) FILTER (WHERE …)
• Gruppenstatistiken kombinieren: Anteile, Durchschnitt je Segment, string_agg

Fensterfunktionen für Berechnungen über Zeilensätze

• Fensterfunktion mit der OVER-Klausel: Aggregat ohne Zeilen zu verdichten
• Zeilen innerhalb eines Fensters mit PARTITION BY aufteilen
• Fensterdefinitionen mit der WINDOW-Klausel benennen und wiederverwenden
• Daten innerhalb einer Partition mit ORDER BY ordnen: laufende Summen
• Gleitender Durchschnitt mit ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
• Rahmenarten ROWS, RANGE und GROUPS, Ausschlüsse mit EXCLUDE
• Werte an bestimmten Stellen: first_value, last_value, nth_value
• Typische Anwendungen: Anteil an der Gruppensumme, Abstand zum Gruppenmaximum
• Fensterfunktionen im Ausführungsplan: Sortierkosten und passende Indizes

Statistiken auf sortierten Daten innerhalb von Gruppen

• Median mit percentile_cont(0.5) WITHIN GROUP (ORDER BY …) berechnen
• Erstes und drittes Quartil, Unterschied zwischen percentile_cont und percentile_disc
• Häufigsten Wert eines Datensatzes mit mode() ermitteln
• Wertebereich und Interquartilsabstand bestimmen
• Mehrere Perzentile auf einmal per Array und Statistiken je Gruppe abrufen

Rangfolgen und hypothetische Mengen

• Zeilen reihen mit rank, dense_rank und row_number – Verhalten bei Gleichstand
• Hypothetischen Rang eines neuen Werts mit rank(x) WITHIN GROUP finden
• Spitzenleistungen mit percent_rank und ntile sichtbar machen
• Wahrscheinlichkeiten mit der kumulativen Verteilung cume_dist auswerten
• Top-N je Gruppe auswerten und Rangfolgen über Zeiträume vergleichen

Ausgabewerte mit bedingten Ausdrücken definieren

• Werte klassifizieren mit einfachem und durchsuchtem CASE
• Spalten und Ersatzwerte zusammenführen mit COALESCE
• Werte gezielt in NULL umwandeln mit NULLIF, etwa gegen Division durch null
• Größten und kleinsten Wert einer Zeile mit GREATEST und LEAST

Zusätzliche Abfragetechniken für häufige Aufgaben

• Zeilennummern im Abfrageergebnis mit row_number() ausgeben
• Werte in andere Datentypen umwandeln: CAST, ::, to_char, to_date
• Zeilen verschieben mit LEAD und LAG: Vorperiodenvergleich und Differenzen
• IN, EXISTS und ANY mit Unterabfragen, Vorsicht bei NOT IN und NULL
• WHERE-Kriterien mit BETWEEN, Bereichstypen und generate_series definieren
• Berechnungen über Zeilen hinweg: Lückenlose Zeitreihen und kumulierte Werte
• Abfragen gliedern mit Common Table Expressions und LATERAL

PL/pgSQL: Funktionen, Prozeduren und Trigger

• SQL-Funktionen und PL/pgSQL-Funktionen: wann sich die prozedurale Sprache lohnt
• Aufbau einer Funktion: DECLARE, Variablen, %TYPE, RETURN und RETURNS TABLE
• Kontrollstrukturen: IF, CASE, LOOP, FOR … IN SELECT und FOREACH
• Fehler behandeln mit EXCEPTION-Block, RAISE und GET STACKED DIAGNOSTICS
• Dynamisches SQL mit EXECUTE und format() ohne SQL-Injection
• Prozeduren mit CREATE PROCEDURE, CALL und COMMIT innerhalb der Prozedur
• Zeilentrigger für Prüfungen und Änderungsprotokolle mit NEW und OLD
• Anweisungstrigger mit Übergangstabellen, Risiken von Trigger-Ketten
• Volatilität (IMMUTABLE, STABLE), SECURITY DEFINER und sicherer search_path

Analytisches SQL und PL/pgSQL mit KI-Assistenten

• Assistent entwirft Fensterfunktion, Prüftabelle mit Sollwerten deckt falsche Rahmen auf
• Generierte PL/pgSQL-Funktionen mit der Erweiterung plpgsql_check statisch prüfen
• Vorschlag mit drei Unterabfragen in eine PostgreSQL-Fensterfunktion umschreiben lassen, Plan vergleichen
• Trigger aus KI-Vorschlägen nur mit Testfällen für INSERT, UPDATE und DELETE übernehmen

Praxisprojekt: Kennzahlen für einen Onlineshop

• Monatsumsätze mit Vorjahresvergleich, gleitendem Durchschnitt und Rang je Kategorie
• Median und Quartile der Warenkorbwerte je Kundensegment als View
• PL/pgSQL-Funktion für Kennzahlen je Zeitraum, Trigger protokolliert Preisänderungen

Lernziele

• Sie berechnen Gruppenstatistiken mit Aggregaten, FILTER, ROLLUP, CUBE und GROUPING SETS
• Sie lösen Vergleichs- und Verlaufsfragen mit Fensterfunktionen und passenden Fensterrahmen
• Sie bestimmen Median, Quartile, Modus, Ränge und hypothetische Ränge in PostgreSQL
• Sie schreiben PL/pgSQL-Funktionen und Prozeduren mit Fehlerbehandlung
• Sie setzen Trigger für Prüfungen und Änderungsprotokolle gezielt ein

Zielgruppe

• Datenanalyst:innen und BI-Entwickler:innen, die Kennzahlen in PostgreSQL berechnen
• Entwickler:innen mit SQL-Grundkenntnissen, die Logik in die Datenbank verlagern wollen