====== Januar ====== ==== leicht ==== 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 <>. === Lösung === 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"; ==== mittel ==== 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 === Lösung === /* * 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"; ==== schwer ==== 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 === Lösung === 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"; ====== Februar ====== 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. ==== leicht ==== Der Spieler mit der höchsten Punktzahl ist zu ermitteln: player|score ------|----- 1| 96 === Lösung === SELECT * FROM scores ORDER BY score DESC LIMIT 1; ==== mittel ==== Für jeden Spieler ist die höchste Punktzahl zu ermitteln: player|score ------|----- 1| 96 2| 83 3| 84 4| 95 5| 50 === Lösung === SELECT player, max(score) AS score FROM scores GROUP BY player ORDER BY player; ==== schwer ==== 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 === Lösung === 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; ====== März ====== ==== leicht ==== 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 === Lösung === CREATE TEMPORARY TABLE artikel (nr,bezeichnung,anzahl) AS VALUES (1::int,'Bleistift'::varchar,5::int), (2,'Radiergummi',3), (3,'Lineal',2); ==== mittel ==== 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 === Hinweise === - Man kann Tabellen auch mit sich selbst ''JOIN''en. Alle ''JOIN''-Typen sind dabei erlaubt, auch die eher selten benutzten. - PostgreSQL kennt Möglichkeiten mit ''NULL''-Werten umzugehen. Du auch? === Lösung === 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); ==== schwer ==== 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 === Hinweise === - Schritt 1: ''beginnt'' und ''endet'' zu ''zeitpunkt'' zusammenfassen und eine weitere Spalte ''ereignis'' erzeugen mit den Werten ''+1'' für //beginnt// und ''-1'' für //endet// === Lösung === 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'); ====== April ====== ==== leicht ==== 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| | | +------------------------------+ === Lösung === 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); ==== mittel ==== 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. === Lösung === CREATE TEMPORARY TABLE d (i,v) AS SELECT i, (10 * random())::int AS v FROM setseed(0), generate_series(1,20) i; ==== schwer ==== Für Tabelle ''d'' sind alle [[https://de.wikipedia.org/wiki/Permutation#/media/Datei:Permutations_with_repetition_cropped.svg|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 └---┘ └---┘ └---┘ === Lösung === CREATE TEMPORARY TABLE d (v) AS VALUES ('A'),('B'),('C'),('D');