[SQL] Durchschnittsbewertung über Zeitraum

[Valle]

Hi DPler! :hi:

Wir haben ein Tabelle "bewertungen", welche eine Integer-Spalte für eine Bewertung (in Form von Schulnoten) enthält, ein Fremdschlüssel zur Zuordnung was damit bewertet wird und ein Datum mit Uhrzeit wann diese Bewertung abgegeben wurde. Ziel ist es, eine graphische Auswertung zu erstellen.

Da ein simples Auftragen der Bewertungen in ein Diagramm nur in einem Haufen von nichtssagenden, überlappenden Punkten endet, möchte ich stattdessen einen Verlauf des Durchschnitts erstellen. Jede einzelne Bewertung zieht so die Linie ein wenig mehr in ihre Richtung. Wie setze ich sowas effizient in SQL um? Oder ist es hier besser nur die Daten zu holen und auf dem Client zu verrechnen? Letztendlich brauche ich die Daten in so einer Struktur:

Code:

[<Datum>, <Durchschnitt zu dieser Zeit>], [<Datum>, <Durchschnitt zu dieser Zeit>], ...

Ob der Durchschnitt über alle Bewertungen, oder nur über zB. die letzten 100 oder die letzten 12 Monate gemacht werden soll steht außerdem zur Frage. Kommt drauf an was mehr Sinn macht. (Es handelt sich um Bewertungen wie Sauberkeit, Freundlichkeit usw.) Ich bin da offen. ^^

Ich hoffe das Problem ist einigermaßen verständlich. Würde mich freuen wenn ihr mir da auf die Sprünge helfen könnt. :)

Liebe Grüße,
Valentin

[mkinzler]

Es kommt darauf an, wie viele Daten, wie viele dieser speziellen Aggegate du brauchst und wie oft du diese benötigst.
Dies ist eine spezielle DataWarehouse-Anwendung. Hierfür gibt es spezielle Datenbanksysteme, welche nicht zeilen-, sonder spaltenorientiert speichern und Prozesse solchen Datenbestände aus einer Produktionsdatenbank zu ziehen (fortlaufens, zeitgesteuert.

[Bummi]

Ich kenne MySQL kaum, geht etwas in der Art

Code:

Select Datum, AVG(Wert) as Wert 
from Tabelle
Group by Datum
Order By Datum

[Valle]

@mkinzler: Eigentlich sind es sehr wenige Daten. In der gesamten Tabelle befinden sich im Moment etwa 1000 Bewertungen. Für die eigentliche Auswertung pro auszuwertendem Objekt kommt allerdings nur ein Bruchteil davon in Frage. Es gibt 6 verschiedene Bewertungskriterien, die je als einzelne Spalte aufgeführt sind. Die Daten werden auch nicht wirklich häufig aufgerufen. Immer dann wenn ein Mitarbeiter diese Statistik sehen will. Es geht auch weniger darum das Problem "irgendwie" zu lösen, als zu lernen wie ich es richtig mache. Wie man hier effizient optimieren kann, wie (und ob) man das mit SQL macht usw. Der Lerneffekt ist mir wichtig. :) Edit:// Andere Lösungen als Python <-> MySQL stehen nicht zur Auswahl. Wäre dann auch eher Overkill.

@Bummi: Das Query funktioniert in der Tat. Ich weiß allerdings nicht so recht was ich mit den Daten anfangen soll. Eigentlich kann es rechnerisch gar nicht mehr passieren, dass der Durchschnitt einen Extremwert annimmt, sobald ein Wert davon abweicht. (sprich: mein Schnitt kann nie und nimmer 1,0 sein, wenn ich auch nur ein einziges mal keine 1 habe) Dieses Ergebnis besteht allerdings überwiegend aus ganzzahligen Werten.

Danke für eure Antworten! :)

[p80286]

Ich denke sosollte es klappen:

Code:

select AVG(Bewertung)
where Datum >= MinDatum
  and Datum <= MaxDatum
  and -- weitere Parameter

Gruß
K-H

[Bummi]

Ich vermute in diesem Fall es handelt sich um ein Integerfeld, versuch es mal mit einem Cast

Code:

AVG(Cast(Bewertung as float))

[Valle]

@p80286: Danke. Aber das schränkt nur den Zeitraum ein. Ändert an den Daten ja nichts.

@Bummi: Ein Großteil der Daten sind Ganzzahlen, nicht alle. Ein Cast ist also nicht nötig. Hier mal ein Ausschnitt der Ausgabe:

Code:

| 2011-08-17 |              4.5 |
| 2011-08-18 |                5 |
| 2011-08-19 |                4 |
| 2011-08-20 |                5 |
| 2011-08-21 |              4.8 |
| 2011-08-22 |                5 |
| 2011-08-23 |             4.25 |
| 2011-08-27 |                4 |
| 2011-08-28 |                4 |
| 2011-08-29 |                5 |

Das kann ja eigentlich keinen Sinn machen.

[Bummi]

Wie gesagt, MySQL kenne ich kaum, unter MSSQL würde ich mich auf das Ergebnis verlassen.
Die Sinnhaftigkeit kann ich allein anhand der Ausgabe nicht nicht beurteilen.

[Valle]

Zur Sinnhaftigkeit: Das mit den Schulnoten war nur vereinfacht. Die Bewertung geht von 1 bis 5, wobei 1 das schlechteste ist. In einem Durchschnitt kann also die 5 gar nicht mehr vorkommen, wenn der Durchschnitt ohne die letzte Bewertung schon 4 war.

[Bummi]

Vielleicht handelt es sich ja auch um ein Missverständnis. Das Ergebnis sind jeweils Summes alle "Noten" eines Tages durch die Anzahl der Noten dieses Tages.

[Valle]

Im Anhang ist eine Grafik, die das gewünschte Ergebnis anzeigt.

Jeder Punkt der blauen Linie entspricht dem Durchschnitt der letzten 10 Bewertungen. Bewertungen sind die roten Punkte, die (logischer Weise) immer ganzzahlig sind. Die Umsetzung habe ich jetzt recht quick 'n dirty gemacht, indem ich die Daten per SQL geholt und anschließend im Client verarbeitet habe. Ziele waren ja, die Daten direkt in SQL zu verarbeiten bzw. zu erfahren ob das sinnvoll ist.

Außerdem stellt sich mir noch immer die Frage, ob der Schnitt über die letzten 10 Bewertungen Sinn macht. Irgendwie erscheint mir das willkürlich. Wenn ich aber den Gesamtschnitt nehme (also jeder Punkt auf der blauen Linie entspricht dem Durchschnitt aller vorhergehenden Bewertungen), dann pendelt sich die Kurve mehr und mehr auf einen festen Wert ein.

@Bummi: Ja, ich bin eben auch darauf gekommen, dass wir uns wohl misverstehen. Das Query tut zwar was es soll, aber das ist nicht mein Ziel. Ist es jetzt durch Grafik und Erklärung klarer geworden was ich meine? :oops:

[jobo]

Wie listest Du nicht einfach mal die Table Def oder auch gleich die Daten?

[Valle]

Weil ich nicht weiß inwiefern es legal ist, Code von der Arbeit zu veröffentlichen.

Code:

+----------------------------------+---------------------------+------+-----+---------+-------+
| Field                            | Type                      | Null | Key | Default | Extra |
+----------------------------------+---------------------------+------+-----+---------+-------+
| buchungen_id                     | int(10) unsigned          | NO   | PRI | NULL    |       |
| service_freundlichkeit_vermieter | enum('1','2','3','4','5') | YES  |     | NULL    |       |
| sauberkeit_innen                 | enum('1','2','3','4','5') | YES  |     | NULL    |       |
| sauberkeit_aussen                | enum('1','2','3','4','5') | YES  |     | NULL    |       |
| service_vermittler               | enum('1','2','3','4','5') | YES  |     | NULL    |       |
| preis_leistung                   | enum('1','2','3','4','5') | YES  |     | NULL    |       |
| ausstattung                      | enum('1','2','3','4','5') | YES  |     | NULL    |       |
+----------------------------------+---------------------------+------+-----+---------+-------+

[jobo]

Ich seh hier kein Datum oder ist es schon so spät?
Tagesdurchschnitt, 10er Durchschnitt, wer weiß das schon. Vielleicht gibt es ja eine Anforderung dazu.

Deine Grafik hat jedenfalls eine Zeitachse. Was nebenbei bemerkt nicht Deinen Ausgabeformatwunsch aus Post #1 rechtfertigt.
Die Datenmenge und Deine Grafik "klingen" nach einem Wochenschnitt oder so.

[Bummi]

Bei den geringen Datenmengen, bzw. langen Auwertungszeiträumen könntest Du es mal mit

Code:

Select Year(Datum) as Year,Month(Datum) as Month, AVG(Bewertung) as Wert 
from Tabelle
Group by Year(Datum),Month(Datum)
Order By Year(Datum),Month(Datum)

oder ähnlichem versuchen

[Valle]

Zitat:

Zitat von jobo (Beitrag 1141474)

Ich seh hier kein Datum oder ist es schon so spät?

Deswegen, veröffentliche ich nicht die Tabellen-Definition. Das Datum steht in der Buchung.

Zitat:

Zitat von jobo (Beitrag 1141474)

Tagesdurchschnitt, 10er Durchschnitt, wer weiß das schon. Vielleicht gibt es ja eine Anforderung dazu.

Nein, ich schrieb oben, dass ich offen für eine sinnvolle Darstellung bin. :)

@Bummi: Mh, okay. Danke, das sieht interessant aus. Allerdings Wäre möglicherweise ein Tagesdurchschnitt sinnvoller. An der Grafik sieht man, dass die Bewertungen sehr stoßartig in den Sommermonaten kommen. Ich werde mir das aber morgen mal grafisch darstellen lassen. Mal sehen wie das so aussieht. :)

Edit:// Ah okay, ich glaube ich habe gerade den Grund für die Verwirrung gefunden. Das Schema im ersten Post ist etwas missverständlich formuliert. Ich hätte besser schreiben sollen "Durchschnitt bis zu dieser Zeit". Es geht nicht um den Durchschnitt an dem Datum selbst, sondern um den Schnitt aller Bewertungen vorher. (oder eben einer bestimmten Menge an Bewertungen vorher - je nach dem was sinnvoll ist)

[Bummi]

dann so ..

Code:

Select DISTINCT Datum,(Select AVG(Bewertung) from dbo.DatumWert d2 where d2.Datum<=d1.Datum) as Bewertung
from dbo.DatumWert d1
Order by Datum

[Jumpy]

Weil ichs gerade für ein Schulprojekt machen muss, werf ich mal das Stichwort Regelkarte in den Raum. Das ist ein Hilfsmittel aus dem Qualitätmanagment. Dabei geht es eigentlich um die Auswertung von Stichproben (aus der Fertigung) und dem Frühzeitigen erkennen, wenn Prozesse schief laufen.

Mmn ist das aber auf dein Problem übertragbar. Eine Stichprobe entspricht bei dir den einzelnen Noten (in einem Kriterieum, z.B. Sauberkeit) eines Tages. Die Durchschnitte der Noten der einzelnen (früheren) Tage werden dann wiederum genutzt um sog. Warngrenzen festzulegen. So ist dann erkennbar, wenn die Durchschnitte einmal aus dem Rahmen fallen, oder ein Trend ist leichter erkennbar.

[Furtbichler]

Ist das Feld "Datum" ein Datum oder ein Zeitstempel. Wenn es ein Datum ist, dann würde ich es so machen:
1. Tagesdurchschnitt

Code:

select Datum, 
       Avg (1.0*Bewertung) as Tagesdurchschnitt 
 from Tabelle 
Group by Datum
-- Order by Datum -- sollte im Client erfolgen => Server sind zum sortieren zu schade

2. Kummulierter Durschnitt

Code:

select d1.Datum, 
       (Select Avg (1.0*d2.Bewertung) from Tabelle d1 where d1.Datum<=d2.Datum) as KummulierterTagesdurchschnitt 
 from (
  select distinct Datum from Tabelle) d1

Oder Vorschlag von Bummi (meiner ist -glaube ich- schneller)

Das Ganze kannt Du ganz leicht in eine Grafik packen (Datum=Y, Durschnitt = X)