SprintAuswertung

Aus ProgrammingWiki

Archivierte Version vom 2010-12-20T13:52:36+00:00. Aktuelle Version

Inhaltsverzeichnis

1 Übung: Auswertungen von Arbeit für verschiedene Projekte mit SQL GROUP BY, SUM, AVG, ...
- 1.1 Alle Einträge der Tabelle work_report anzeigen
- 1.2 Aufgaben zur Auswertung von Tabellen
  - 1.2.1 Arbeit pro Projekt anzeigen, absteigend sortiert (also das Projekt mit der meisten Arbeit zuerst)

Übung: Auswertungen von Arbeit für verschiedene Projekte mit SQL GROUP BY, SUM, AVG, ...

Wir betrachten in dieser Übung eine Tabelle, in der geleistete Arbeit erfasst wurde, aufgeschlüsselt nach verschiedenen Kriterien:

 +------+--------+----------+------------+----------------+--------------+-----------+---------+
 | id   | sprint | status   | resolution | project        | story_points | work_done | planned |
 +------+--------+----------+------------+----------------+--------------+-----------+---------+

Jede Zeile in dieser Tabelle beschreibt eine Aufgabe:

id ist eine eindeutige Identifikationsnummer einer erledigten Aufgabe (die Beschreibung der Aufgabe ist der Anonymisierung zum Opfer gefallen und in der Tabelle nicht mehr enthalten).
sprint ist eine Zahl zwischen 1 und 3 und gibt an, in welchem Projektabschnitt (Sprint genannt) die Aufgabe abgeschlossen wurde.
project ist das Projekt, dem diese Aufgabe verrechnet wird.
work_done sind die geleisteten Stunden für diese Aufgabe.
planned gibt an, ob die Aufgabe ursprünglich eingeplant war oder nicht.

DROP TABLE IF EXISTS work_report;
CREATE TABLE work_report (
  id INT NOT NULL,
  sprint INT NOT NULL,
  status VARCHAR(20),
  resolution VARCHAR(20),
  project VARCHAR(20),
  story_points INT,
  work_done DOUBLE,
  planned BOOLEAN
);

INSERT INTO work_report VALUES (3656, 1, 'closed', 'fixed', 'Admin', 0, 64.1, 1);
INSERT INTO work_report VALUES (3638, 1, 'closed', 'fixed', 'Admin', 0, 1, 1);
INSERT INTO work_report VALUES (3636, 1, 'closed', '', 'Admin', 0, 39, 1);
INSERT INTO work_report VALUES (3644, 1, 'closed', 'fixed', 'Admin', 0, 8.75, 1);
INSERT INTO work_report VALUES (3652, 1, 'closed', 'fixed', 'Admin', 0, 59, 1);
INSERT INTO work_report VALUES (3650, 1, 'closed', 'fixed', 'Slytherin', 0, 16, 1);
INSERT INTO work_report VALUES (3648, 1, 'closed', 'fixed', 'Hufflepuff', 0, 12.4, 1);
INSERT INTO work_report VALUES (3646, 1, 'closed', 'fixed', 'Gryffindor', 0, 3, 1);
INSERT INTO work_report VALUES (3647, 1, 'closed', 'fixed', 'Gryffindor', 0, 23.5, 1);
INSERT INTO work_report VALUES (3651, 1, 'closed', 'fixed', 'Operation', 0, 4.75, 1);
INSERT INTO work_report VALUES (3640, 1, 'closed', 'fixed', 'Operation', 0, 71.75, 1);
INSERT INTO work_report VALUES (3649, 1, 'closed', 'fixed', 'Ravenclaw', 0, 11, 1);
INSERT INTO work_report VALUES (3808, 2, 'closed', 'fixed', 'Gryffindor', 0, 12.3, 1);
INSERT INTO work_report VALUES (3807, 2, 'closed', 'fixed', 'Admin', 0, 75.5, 1);
INSERT INTO work_report VALUES (3798, 2, 'closed', 'fixed', 'Admin', 0, 14.3, 1);
INSERT INTO work_report VALUES (3784, 2, 'closed', 'fixed', 'Admin', 0, 57.4, 1);
INSERT INTO work_report VALUES (3924, 2, 'closed', 'fixed', 'Gryffindor', 0, 4, 1);
INSERT INTO work_report VALUES (3794, 2, 'closed', 'fixed', 'Operation', 0, 25.5, 1);
INSERT INTO work_report VALUES (3809, 2, 'closed', 'wontfix', 'Hufflepuff', 0, 0, 1);
INSERT INTO work_report VALUES (3792, 2, 'closed', 'fixed', 'Admin', 0, 3.4, 1);
INSERT INTO work_report VALUES (3804, 2, 'closed', 'fixed', 'Admin', 0, 1.5, 1);
INSERT INTO work_report VALUES (3767, 2, 'closed', 'fixed', 'Admin', 0, 22, 1);
INSERT INTO work_report VALUES (3803, 2, 'closed', '', 'Slytherin', 0, 10.8, 1);
INSERT INTO work_report VALUES (3801, 2, 'closed', 'fixed', 'Hufflepuff', 0, 10, 1);
INSERT INTO work_report VALUES (3790, 2, 'closed', 'fixed', 'Admin', 0, 30.5, 1);
INSERT INTO work_report VALUES (3800, 2, 'closed', 'fixed', 'Gryffindor', 0, 14, 1);
INSERT INTO work_report VALUES (3802, 2, 'closed', 'fixed', 'Ravenclaw', 0, 9, 1);
INSERT INTO work_report VALUES (3955, 3, 'new', '', 'Admin', 0, 13.5, 1);
INSERT INTO work_report VALUES (3961, 3, 'closed', 'fixed', 'Admin', 0, 62.2, 1);
INSERT INTO work_report VALUES (3947, 3, 'closed', '', 'Admin', 0, 37.5, 1);
INSERT INTO work_report VALUES (4098, 3, 'closed', 'fixed', 'Hufflepuff', 0, 5, 1);
INSERT INTO work_report VALUES (3957, 3, 'closed', 'fixed', 'Hufflepuff', 0, 6, 1);
INSERT INTO work_report VALUES (4092, 3, 'closed', '', 'Operation', 0, 2, 1);
INSERT INTO work_report VALUES (3978, 3, 'new', '', 'Admin', 0, 60.5, 1);
INSERT INTO work_report VALUES (3959, 3, 'new', '', 'Ravenclaw', 0, 6, 1);
INSERT INTO work_report VALUES (3953, 3, 'new', '', 'Admin', 0, 3.6, 1);
INSERT INTO work_report VALUES (4069, 3, 'new', '', 'Gryffindor', 0, 8.5, 1);
INSERT INTO work_report VALUES (3949, 3, 'accepted', '', 'Operation', 0, 61.5, 1);
INSERT INTO work_report VALUES (3628, 1, 'closed', 'fixed', 'Gryffindor', 1, 36, 1);
INSERT INTO work_report VALUES (3589, 1, 'closed', 'fixed', 'Gryffindor', 8, 162.9, 1);
INSERT INTO work_report VALUES (3660, 3, 'closed', 'fixed', 'Slytherin', 8, 158.4, 1);
INSERT INTO work_report VALUES (3591, 2, 'closed', 'fixed', 'Hogsmeade', 1, 19, 1);
INSERT INTO work_report VALUES (3899, 3, 'closed', 'fixed', 'ShriekingShack', 1, 9.5, 0);
INSERT INTO work_report VALUES (3590, 2, 'closed', 'fixed', 'Gryffindor', 5, 86, 1);
INSERT INTO work_report VALUES (3621, 3, 'closed', 'fixed', 'Hufflepuff', 1, 17, 1);
INSERT INTO work_report VALUES (3623, 2, 'closed', 'fixed', 'Gryffindor', 5, 82.5, 1);
INSERT INTO work_report VALUES (528, 1, 'closed', 'fixed', 'Improvements', 3, 44, 1);
INSERT INTO work_report VALUES (3936, 3, 'closed', 'fixed', 'Gryffindor', 5, 62.5, 0);
INSERT INTO work_report VALUES (3601, 3, 'closed', 'fixed', 'Ravenclaw', 3, 36, 1);
INSERT INTO work_report VALUES (3578, 3, 'closed', 'fixed', 'Ravenclaw', 1, 11.5, 1);
INSERT INTO work_report VALUES (3613, 3, 'closed', 'fixed', 'Hufflepuff', 3, 32, 1);
INSERT INTO work_report VALUES (3565, 2, 'closed', 'fixed', 'Operation', 2, 19.5, 0);
INSERT INTO work_report VALUES (3627, 1, 'closed', 'fixed', 'Gryffindor', 1, 9.25, 1);
INSERT INTO work_report VALUES (3931, 2, 'closed', 'fixed', 'Improvements', 1, 9, 1);
INSERT INTO work_report VALUES (3632, 2, 'closed', 'fixed', 'Gryffindor', 13, 111.5, 1);
INSERT INTO work_report VALUES (3945, 3, 'closed', '', 'Improvements', 1, 8.5, 1);
INSERT INTO work_report VALUES (3631, 1, 'closed', 'fixed', 'Gryffindor', 5, 42, 1);
INSERT INTO work_report VALUES (3946, 3, 'closed', 'fixed', 'Gryffindor', 2, 15.5, 1);
INSERT INTO work_report VALUES (3607, 3, 'closed', 'fixed', 'Gryffindor', 3, 23, 1);
INSERT INTO work_report VALUES (3606, 3, 'closed', 'fixed', 'Gryffindor', 8, 61, 1);
INSERT INTO work_report VALUES (3908, 3, 'closed', 'fixed', 'Hufflepuff', 2, 15, 1);
INSERT INTO work_report VALUES (3605, 3, 'closed', 'fixed', 'Gryffindor', 5, 37, 1);
INSERT INTO work_report VALUES (3773, 3, 'closed', 'fixed', 'Slytherin', 2, 14.5, 1);
INSERT INTO work_report VALUES (3568, 3, 'closed', 'fixed', 'Operation', 5, 36, 0);
INSERT INTO work_report VALUES (3609, 3, 'closed', 'fixed', 'Hufflepuff', 8, 57.5, 1);
INSERT INTO work_report VALUES (3635, 1, 'closed', 'fixed', 'Operation', 1, 7, 1);
INSERT INTO work_report VALUES (3917, 3, 'closed', 'fixed', 'Hufflepuff', 1, 7, 1);
INSERT INTO work_report VALUES (4072, 3, 'new', '', 'Gryffindor', 0, 32, 1);
INSERT INTO work_report VALUES (3776, 2, 'closed', 'fixed', 'Gryffindor', 3, 19, 1);
INSERT INTO work_report VALUES (4062, 3, 'new', '', 'Ravenclaw', 0, 8, 1);
INSERT INTO work_report VALUES (4061, 3, 'new', '', 'Slytherin', 0, 6.5, 1);
INSERT INTO work_report VALUES (4090, 3, 'new', '', 'Slytherin', 0, 5.75, 1);
INSERT INTO work_report VALUES (4076, 3, 'closed', '', 'Hogsmeade', 0, 1.5, 1);
INSERT INTO work_report VALUES (3581, 2, 'closed', 'fixed', 'Ravenclaw', 5, 30.5, 1);
INSERT INTO work_report VALUES (3909, 3, 'closed', 'fixed', 'Hufflepuff', 3, 18.25, 1);
INSERT INTO work_report VALUES (3543, 1, 'closed', 'fixed', 'Operation', 3, 18, 0);
INSERT INTO work_report VALUES (3898, 3, 'closed', 'fixed', 'ChangeRequest', 1, 6, 0);
INSERT INTO work_report VALUES (3905, 3, 'closed', 'fixed', 'Gryffindor', 1, 6, 0);
INSERT INTO work_report VALUES (3633, 2, 'closed', 'fixed', 'Gryffindor', 13, 77.2, 1);
INSERT INTO work_report VALUES (3612, 2, 'closed', 'fixed', 'Hufflepuff', 8, 47, 1);
INSERT INTO work_report VALUES (3967, 3, 'closed', 'fixed', 'ShriekingShack', 2, 11.5, 1);
INSERT INTO work_report VALUES (3906, 3, 'closed', 'fixed', 'Gryffindor', 3, 16, 0);
INSERT INTO work_report VALUES (3770, 3, 'closed', 'fixed', 'Slytherin', 5, 23, 1);
INSERT INTO work_report VALUES (3777, 1, 'closed', 'fixed', 'Hufflepuff', 5, 22.5, 1);
INSERT INTO work_report VALUES (3428, 1, 'closed', 'fixed', 'Improvements', 13, 57.5, 1);
INSERT INTO work_report VALUES (3604, 2, 'closed', 'fixed', 'Gryffindor', 3, 13.1, 1);
INSERT INTO work_report VALUES (3547, 1, 'closed', 'fixed', 'Operation', 8, 34.5, 0);
INSERT INTO work_report VALUES (3293, 3, 'closed', '', 'Improvements', 2, 8.25, 1);
INSERT INTO work_report VALUES (3937, 3, 'closed', 'fixed', 'Slytherin', 5, 16.5, 1);
INSERT INTO work_report VALUES (3966, 3, 'closed', 'fixed', 'ShriekingShack', 2, 6, 1);
INSERT INTO work_report VALUES (3554, 2, 'closed', 'fixed', 'Operation', 8, 23, 1);
INSERT INTO work_report VALUES (3625, 3, 'closed', 'fixed', 'Gryffindor', 3, 6.5, 1);
INSERT INTO work_report VALUES (3037, 2, 'closed', 'fixed', 'Slytherin', 13, 14.05, 1);

Alle Einträge der Tabelle work_report anzeigen

<eval id="4d0f5784b85f6"> SELECT * FROM work_report; </eval>

Aufgaben zur Auswertung von Tabellen

Arbeit pro Projekt anzeigen, absteigend sortiert (also das Projekt mit der meisten Arbeit zuerst)

SprintAuswertung

Inhaltsverzeichnis

Übung: Auswertungen von Arbeit für verschiedene Projekte mit SQL GROUP BY, SUM, AVG, ...

Alle Einträge der Tabelle work_report anzeigen

Aufgaben zur Auswertung von Tabellen

Arbeit pro Projekt anzeigen, absteigend sortiert (also das Projekt mit der meisten Arbeit zuerst)

Suche