Unter Verwendung der Tabelle nachweisverwaltung.n_nachweise sollen alle erfassten Dokumente für die bearbeiter Jana Dräger, Grit Sewekow, Markus Hentschel, Hendrik Reißland, Steffen Bakos und Andreas Breede sowie der zeit-raum der jeweiligen Erfassung folgendermaßen ausgegeben werden:
Bearbeiter |Dokumente|von |bis ----------------+---------+----------+---------- Andreas Breede | 337|2019-08-14|2019-09-27 Grit Sewekow | 52946|2015-11-12|2022-01-11 Hendrik Reißland| 30|2018-05-29|2020-06-10 Jana Dräger | 669|2020-01-22|2020-09-25 Markus Hentschel| 78|2015-12-21|2021-10-28 Steffen Bakos | 65|2017-11-16|2019-06-27
Einen Zusatzpunkt gibt es für die Ausgabe und Sortierung der Spalte bearbeiter nach «Nachname, Vorname».
SELECT concat_ws( ', ', split_part(bearbeiter, ' ', 2), split_part(bearbeiter, ' ', 1)) AS "Bearbeiter", -- funktioniert nicht für mehr als einen Vor- bzw. Nachnamen count(*) AS "Dokumente", min(zeit)::date AS von, max(zeit)::date AS bis FROM nachweisverwaltung.n_nachweise WHERE bearbeiter IN ( 'Grit Sewekow','Jana Dräger','Markus Hentschel', 'Steffen Bakos','Hendrik Reißland','Andreas Breede') GROUP BY bearbeiter ORDER BY "Bearbeiter";
Unter Verwendung der Tabelle nachweisverwaltung.n_nachweise sollen alle erfassten Dokumente für die Jahre 2017 bis einschließlich 2021 für die bearbeiter Jana Dräger, Grit Sewekow, Markus Hentschel, Hendrik Reißland, Steffen Bakos und Andreas Breede folgendermaßen ausgegeben werden:
Jahr|Bakos|Breede|Dräger|Hentschel|Reißland|Sewekow ----+-----+------+------+---------+--------+------- 2017| 17| 0| 0| 3| 0| 11278 2018| 0| 0| 0| 25| 4| 11058 2019| 48| 337| 0| 45| 25| 5317 2020| 0| 0| 669| 1| 1| 4478 2021| 0| 0| 0| 3| 0| 4341
/* * If FILTER is specified, then only the input rows for which the filter_clause * evaluates to true are fed to the aggregate function; other rows are discarded. * https://www.postgresql.org/docs/9.4/sql-expressions.html#SYNTAX-AGGREGATES */ SELECT to_char(zeit, 'YYYY')::int AS "Jahr", count(*) FILTER (WHERE bearbeiter = 'Steffen Bakos') AS "Bakos", -- statt "count(*) FILTER (WHERE ..." geht auch count(CASE WHEN bearbeiter = '...' THEN 1 ELSE NULL END) AS "..." count(*) FILTER (WHERE bearbeiter = 'Andreas Breede') AS "Breede", count(*) FILTER (WHERE bearbeiter = 'Jana Dräger') AS "Dräger", count(*) FILTER (WHERE bearbeiter = 'Markus Hentschel') AS "Hentschel", count(*) FILTER (WHERE bearbeiter = 'Hendrik Reißland') AS "Reißland", count(*) FILTER (WHERE bearbeiter = 'Grit Sewekow') AS "Sewekow" FROM nachweisverwaltung.n_nachweise WHERE bearbeiter IN ( 'Grit Sewekow','Jana Dräger','Markus Hentschel', 'Steffen Bakos','Hendrik Reißland','Andreas Breede' ) AND zeit >= '2017-01-01'::timestamp AND zeit < '2022-01-01'::timestamp GROUP BY "Jahr" ORDER BY "Jahr";
Unter Verwendung der Tabelle nachweisverwaltung.n_nachweise soll die fortlaufende Summe für die Monate Januar 2020 bis einschließlich Dezember 2020 der monatlich erfassten Dokumente für die bearbeiter Jana Dräger und Grit Sewekow folgendermaßen ausgegeben werden:
Monat |Dräger|Sewekow -------+------+------- 01/2020| 219| 281 02/2020| 314| 585 03/2020| 319| 1057 04/2020| 515| 1573 05/2020| 666| 1877 06/2020| 666| 2448 07/2020| 666| 2881 08/2020| 666| 3073 09/2020| 669| 3669 10/2020| 669| 4091 11/2020| 669| 4270 12/2020| 669| 4478
SELECT to_char(zeit, 'MM/YYYY') AS "Monat", sum( count(*) FILTER (WHERE bearbeiter = 'Jana Dräger') -- Aggregatfunktionen können innerhalb von Window-Funktionen verwendet werden ) OVER w::int AS "Dräger", sum( count(*) FILTER (WHERE bearbeiter = 'Grit Sewekow') ) OVER w::int AS "Sewekow" FROM nachweisverwaltung.n_nachweise WHERE bearbeiter IN ( 'Jana Dräger','Grit Sewekow' ) AND zeit >= '2020-01-01'::timestamp AND zeit < '2021-01-01'::timestamp GROUP BY "Monat" WINDOW w AS (ORDER BY to_char(zeit, 'MM/YYYY')) ORDER BY "Monat";
Als Datengrundlage für die Februar-Aufgaben muss zunächst eine temporäre Tabelle generiert werden:
SELECT setseed(0.52); -- Wird benötigt, damit alle dieselben random()-Daten generieren. 0.52 ist zufällig gewählt. CREATE TEMPORARY TABLE scores (player,score) AS SELECT (1 + random() * 4)::int, (1 + random() * 99)::int FROM generate_series(1,30) g;
Innerhalb der Session, in der die Tabelle angelegt wurde, lässt sie sich nun ganz normal aufrufen:
SELECT * FROM scores;
Temporäre Tabellen existieren nicht über eine Session hinaus. Sie existieren nicht Script- bzw. User-übergreifend. Sollte die Connection verloren gehen, muss die Tabelle erneut angelegt werden, um sie zu verwenden.
Der Spieler mit der höchsten Punktzahl ist zu ermitteln:
player|score
------|-----
1| 96
SELECT * FROM scores ORDER BY score DESC LIMIT 1;
Für jeden Spieler ist die höchste Punktzahl zu ermitteln:
player|score
------|-----
1| 96
2| 83
3| 84
4| 95
5| 50
SELECT player, max(score) AS score FROM scores GROUP BY player ORDER BY player;
Für jeden Spieler sind die 3 höchsten Punktzahlen zu ermitteln:
player|score
------|-----
1| 96
1| 47
1| 12
2| 83
2| 77
2| 60
3| 84
3| 46
3| 19
4| 95
4| 93
4| 87
5| 50
5| 7
SELECT player, score FROM (SELECT player, score, rank() OVER (PARTITION BY player ORDER BY score DESC) FROM scores) foo WHERE rank <= 3 ORDER BY player, score DESC;
Die Tabelle artikel…
nr|bezeichnung|anzahl --+-----------+------ 1|Bleistift | 5 2|Radiergummi| 3 3|Lineal | 2
… soll folgendermaßen ausgegeben werden:
bezeichnung|anzahl -----------+------ Bleistift | 1 Bleistift | 1 Bleistift | 1 Bleistift | 1 Bleistift | 1 Radiergummi| 1 Radiergummi| 1 Radiergummi| 1 Lineal | 1 Lineal | 1
CREATE TEMPORARY TABLE artikel (nr,bezeichnung,anzahl) AS VALUES (1::int,'Bleistift'::varchar,5::int), (2,'Radiergummi',3), (3,'Lineal',2);
Die Tabelle d …
id|wert --+---- 1| 3 2| 8 3| 12 4| 15 5| 19 6| 22 7| 28 8| 33
… soll folgendermaßen ausgegeben werden:
low|high|dist ---+----+---- 0| 3| 3 3| 8| 5 8| 12| 4 12| 15| 3 15| 19| 4 19| 22| 3 22| 28| 6 28| 33| 5 33| 40| 7
JOINen. Alle JOIN-Typen sind dabei erlaubt, auch die eher selten benutzten.NULL-Werten umzugehen. Du auch?
CREATE TEMPORARY TABLE d (id,wert) AS VALUES (1::int,3::int), (2,8), (3,12), (4,15), (5,19), (6,22), (7,28), (8,33);
Die Tabelle zeitraum …
beginnt |endet ----------+---------- 2021-12-23|2021-12-27 2021-12-25|2021-12-31 2021-12-31|2022-01-03 2022-01-04|2022-01-05 2022-01-07|2022-01-12 2022-01-11|2022-01-15
… soll ohne überlagernde Zeiträume ausgegeben werden:
beginnt |endet ----------+---------- 2021-12-23|2022-01-03 2022-01-04|2022-01-05 2022-01-07|2022-01-15
beginnt und endet zu zeitpunkt zusammenfassen und eine weitere Spalte ereignis erzeugen mit den Werten +1 für beginnt und -1 für endet
CREATE TEMPORARY TABLE zeitraum (beginnt,endet) AS VALUES ('2021-12-23'::date,'2021-12-27'::date), ('2021-12-25','2021-12-31'), ('2021-12-31','2022-01-03'), ('2022-01-04','2022-01-05'), ('2022-01-07','2022-01-12'), ('2022-01-11','2022-01-15');
Aus einer Stichprobe d mit 100 annähernd normalverteilten Werten v sind folgende statistische Lageparameter zu berechnen:
+------------------------------+
| -> 1 Zusatzpunkt |
| |
Minimum|Maximum|Mittelwert|Standardabweichung|Median|Modus|unteres Quartil|oberes Quartil|
-------+-------+----------+------------------+------+-----+---------------+--------------|
-32| 192| 95.91| 49.918| 98.0| 160| 67.0| 138.5|
| |
+------------------------------+
CREATE TEMPORARY TABLE d (v) AS SELECT (( random() + random() + random() + random() + random() + random() + random() + random() + random() + random() + random() + random() - 6) * 49 + 100)::int FROM setseed(0), generate_series(1,100);
Die Tabelle d enthält Duplikate in v:
i |v --+- 1|0 2|0 3|0 4|2 5|4 6|1 7|1 8|5 9|5 10|5 11|2 12|8 13|9 14|6 15|6 16|1 17|8 18|0 19|3 20|3
Es ist eine DELETE-Anweisung zu schreiben, welche alle Duplikate entfernt und die Datensätze mit dem jeweils kleinsten i behält.
CREATE TEMPORARY TABLE d (i,v) AS SELECT i, (10 * random())::int AS v FROM setseed(0), generate_series(1,20) i;
Für Tabelle d sind alle Permutationen ohne Wiederholung zu erzeugen:
perm ---- ┌--┐ ┌--┐ ┌--┐ ABCD | BACD | CABD | DABC ABDC | BADC | CADB | DACB ACBD | BCAD | CBAD | DBAC ACDB | BCDA | CBDA | DBCA ADBC | BDAC | CDAB | DCAB ADCB | BDCA | CDBA | DCBA └---┘ └---┘ └---┘
CREATE TEMPORARY TABLE d (v) AS VALUES ('A'),('B'),('C'),('D');
