Sub Queries

ermöglicht durch WITH

SELECT MatrNr, avg(Note)
FROM pruefen
GROUP BY MatrNr
HAVING avgNote >= all ( SELECT avg(Note)
FROM pruefen
GROUP BY MatrNr
);

WITH
-- Matrikelnummer mit dazugehöriger Durchschnittsnote
avgNote AS (
SELECT MatrNr, avg(Note) AS dsN FROM pruefen GROUP BY MatrNr
),
-- Tupel aus avgNote mit maximaler dsN
bestAvgNote AS (
SELECT MatrNr, dsN FROM avgNote WHERE dsN = (SELECT max(dsN) FROM avgNote))
)SELECT MatrNr, dsN FROM bestAvgNote;

Rekursive Queries

https://www.postgresql.org/docs/current/queries-with.html

https://towardsdatascience.com/recursive-sql-queries-with-postgresql-87e2a453f1b

https://www.citusdata.com/blog/2018/05/15/fun-with-sql-recursive-ctes/

ermöglicht durch WITH RECURSIVE

WITH RECURSIVE tablename(attr, attr, ...) AS (
-- nicht rekursive query
SELECT ...
UNION [ALL] -- ALL erlaubt duplicates
-- rekursive query
SELECT ...
)SELECT ...

WITH RECURSIVE t (n) AS ( -- wie create table t (n);
SELECT 1
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)SELECT n FROM t;

• Erklärung

  1. CTE : definierst eine temporären table über eine Abfrage.

  2. SELECT 1 : das dient quasi als Basis für alles andere. Bis dahin hast eine Tabelle t(n integer) wo nur 1 drin steht.

  3. Union SELECT n+1 from t : das ist der rekursive Teil. Da beziehst du dich auf die Tabelle die du gerade definierst und generierst dir somit rekursiv neue Rows in der Tabelle bis du abbrechen möchtest.

     Mit +1 fügst du allen rows aus n eins hinzu - es ist keine neue Spalte n+1

WITH RECURSIVE voraus (v) AS (
-- alle direkten vorgänger von 5216
SELECT vorgnr
FROM voraussetzen
WHERE nachfnr = 5216
UNION
-- alle vorgänger, der vorgänger aus 'voraus'
SELECT voraussetzen.vorgnr
FROM voraus, voraussetzen -- vorheriger schritt x voraussetzungstabelle
WHERE voraus.v=voraussetzen.nachfnr
)SELECT v FROM voraus;

WITH RECURSIVE voraus (v, c) AS (
SELECT vorgnr, 1
FROM voraussetzen
WHERE nachfnr = 5216
UNION
SELECT voraussetzen.vorgnr, voraus.c+1
FROM voraus, voraussetzen
WHERE voraus.v=voraussetzen.nachfnr
)-- findet zusätzlich die distanz von jeder node zu 5216
SELECT v, max(c) FROM voraus;

Views

Gespeicherte queries als virtuelle Tabellen

zB aus Datenschutzgründen

create view prüfenSicht as
select MatrNr, VorlNr, PersNr from prüfen;

Implementierung von Generalisierung

create table Professor (PersNr ..., Name ..., Rang ..., Raum ...);
create table Assistent (PersNr ..., Name ..., Fachgebiet ..., Boss ...);
create table AndereAngestellte (PersNr ..., Name ...);

create view Angestellte as
(select PersNr, Name from Professor) union
(select PersNr, Name from Assistent) union
(select * from AndereAngestellte)

create view Professor as
select * from Angestellte natural join ProfDaten;
create view Assistent as
select * from Angestellte natural join AssiDaten;

Window Functions

Aggregatsfunktionen ohne GROUP BY durch OVER (...)

SELECT StockNr, Typ
FROM Bienenstock
WHERE (
SELECT COUNT(*) OVER ()
FROM Arbeiter NATURAL JOIN bestaeubt
WHERE arbeitetInTyp=Typ AND ...
GROUP BY Feldkennzahl, Ort
LIMIT 1) -- mit LIMIT ist Aufruf von count(*) sinnlos
) ...;

SELECT matrnr, count(*) AS anzahl
FROM hoeren
GROUP BY matrnr;

SELECT DISTINCT matrnr, count(*) OVER (PARTITION BY matrnr) AS anzahl
FROM hoeren;

Sequences

erzeugt Zahlenfolge

CREATE SEQUENCE name
[INCREMENT [BY] inc]
[MINVALUE min | NO MINVALUE]
[MAXVALUE max | NO MAXVALUE]
[START [WITH] start] [CACHE cache]
[[NO] CYCLE] -- falls max erreicht

nextval(name) inkrementiert und returned Wert

currval(name) returned letzten nextval(...) Wert

setval(name) setzt counter in neuen Zustand

CREATE SEQUENCE persnr_seq
START WITH 2200 INCREMENT BY 5
MINVALUE 2200
MAXVALUE 15000
NO CYCLE;INSERT INTO Professor VALUES (nextval('persnr_seq'), 'Peter')
SELECT curval('persnr_seq')