supergecko
Goto Top

SQL Abfrage möglich?

Hallo liebe Comunity,

diesesmal habe ich wieder eine knifflige Aufgabe bekommen.
Ich weiss auch nicht ganz genau wie ich den Fall klar definieren kann und ob dieser überhaupt lösbar ist.
Nun ich versuche es mal damit:
Ich soll einen Datenbankvergleich der Datenfelder durchführen, dabei muss ich die höchsten Stückzahlen der letzten 20 Tage bestimmen.


An meinem Fallbeispiel: (Angehängtes Bild)
Anhand meines Beispiels sollen muss wird die Stückzahl 7112500 vom 20080208 und 5190900 vom 200817 herausgegeben werden.
Es sollen also nur die Spitzenwerte/Höchsten Stückzahlen/ Größsten Ausreisser der Datenbank ermttelt werden.

Was soll ausgegeben werden:
ID, Datum (an den der Höchstand war), Stückzahl des Höchststandes

Vielleicht noch eine kleine Ergänzung:
Es sollen nicht alle Ministückzahlenwert angegeben werden, sondern wirklich nur die Spitzenwerte.
Vielleicht lassen sich auch die Spitzenwerte anderst ermitteln (Prozentuale Höchststände?!), ich bin offen für Alternativvorschläge offen.
Ich hoffe, ich habe die Aufgabenbeschreibung einigermaßen gut erklären können.

Vielen Dank für eure Hilfe im Vorraus.
Grüße Supergecko

P.S.
Offenbar lässt sich das Image nicht einbinden, daher der Datenbankausschnitt per TXT
ID Datum Stück
2653820 20080130 2129700
2653821 20080131 3761500
2653822 20080201 2588700
2653823 20080204 1798600
2653824 20080205 3690000
2653825 20080206 6960000
2653826 20080207 7112500
2653827 20080208 3894700
2653828 20080211 2074500
2653829 20080212 3294700
2653830 20080213 1915000
2653831 20080214 1382100
2653832 20080215 1304200
2653833 20080219 1440300
2653834 20080220 2106400
2653835 20080221 1658200
2653836 20080222 1303200
2653837 20080225 1942100
2653838 20080226 1770500
2653839 20080227 1301700
2653840 20080228 1361200
2653841 20080229 1397400
2653842 20080303 2091000
2653843 20080304 2210200
2653844 20080305 4800400
2653845 20080306 3201500
2653846 20080307 3381600
2653847 20080310 3566100
2653848 20080311 3061700
2653849 20080312 1833000
2653850 20080313 3473600
2653851 20080314 2671900
2653852 20080317 5190900
2653853 20080318 2477500
2653854 20080319 2191200
2653855 20080320 2682100
2653856 20080324 1544500
2653857 20080325 1323800
2653858 20080326 1720900
2653859 20080327 1385400
2653860 20080328 1098300
2653861 20080331 1750800
2653862 20080401 1598400

Content-Key: 105734

Url: https://administrator.de/contentid/105734

Printed on: April 19, 2024 at 23:04 o'clock

Member: dog
dog Jan 12, 2009 at 10:15:15 (UTC)
Goto Top
SELECT ID,date,quantitiy, avg(quantity) as mwert, stddev(quantity) as stdabw FROM tbl WHERE date >= ... AND quantity > (mwert + stdabw)  ORDER BY date DESC 
(nicht getestet, mysql syntax)

Das sollte alle Werte der letzten ... Tage (von deinem Beispiel ist nicht ersichtlich ob das Tage Feld ein DATE-Feld ist) raussuchen, deren Wert größer als der Mittelwert + die Standardabweichung ist (also statistisch ein tatsächlicher "Ausreißer").
Ist aber nur so ein Gedankenexperiment.

Grüße

Max
Member: Supergecko
Supergecko Jan 12, 2009 at 10:47:38 (UTC)
Goto Top
Hallo Max,

vielen Dank für die schnelle Antwort. - Irgendwie bist du mir unheimlich.
Wir benutzen zur Zeit MS Access, ich weiß jetzt nicht auf Anhieb ob der Zusatz "quantity" in Access funktioniert?!
Gibt es den Befehl vielleicht in Access?
Zu deiner Frage: Die Datumsdefinition ist "Int" also Zahl, somit ist die Sortierung einfacher.
Grüße
SG
Member: dog
dog Jan 12, 2009 at 12:01:22 (UTC)
Goto Top
Mit "quantitiy" habe ich nur die Spalte bezeichnet, die bei dir "Stück" heißt. face-smile

Das Datum als Int zu speichern macht die ganze Sache etwas komplizierter, denn so kannst du nicht mehr so einfach damit rechnen (Mit Datenfeldern kann man auch mal 20 Tage zurückrechnen etc.).
Member: Supergecko
Supergecko Jan 12, 2009 at 12:34:52 (UTC)
Goto Top
Habe mich nun Informiert, danke für die Aufklärung.
AVG kannte ich aber den Befehl "stddev" nicht.
Es ist nicht zwingend erforderlich das Datum der letzten X Tage anzugeben, sondern die tatsächlichen Hochpunkte zu ermitteln.
Der Beobachtungszeitraum ist in dem Fall fiktiv.
Kann es sein, dass mit der oben genannten Abfrage der nur das erste Datum verglichen wird? Ich sehe keinen Primären Schlüsselzähler?
Ist dies überhaupt mit der Standardabweichung lösbar? Ich meine es ist auf jeden Fall eine Lösung die ich heute mal umsetzen werde. Mal sehen....
Dennoch:
Ist das gesamte Problem überhaupt mit dem Tageszähler überhaupt lösbar oder muss vielleicht noch zusätzlich ein prozentualer Filter hinzugefügt werden.
Nach dem Motto: Zeige mir alle im Zeitraum X mit einem prozentualen Stückzahlen anstieg?
Danke im Voraus.
Member: Supergecko
Supergecko Jan 12, 2009 at 12:40:16 (UTC)
Goto Top
Ach ja noch etwas, wo wir der Zeitraum/Intervall für die Standardabweichung definiert?
Wenn Sie den gesamten Zeitraum als Grundlage nimmt, ist diese für mich unbrauchbar.
Die Spitzen müssen relativ an dem Druchschnitt eines Intervallzeitraumes ermittelt werden.
Das bedeutet, wenn ich in nach deiner Standardabweichung den gesamten Zeitraum zu Rate ziehe, wird bei einer steigenen Stückzahl der Anfang des zur berüksichtigenden Zeitraumes nicht beachtet. Das ist nicht das Ziel.
Also, ich hoffe ich habe die Problemstellung berichtigen können.
Nun, das Problem ist doch nicht so einfach wie gedacht.
face-wink
Member: dog
dog Jan 12, 2009 at 15:01:15 (UTC)
Goto Top
Nun, du kannst das ganze Spielchen auch noch etwas weiter treiben:

SELECT id,datum,menge
FROM average
WHERE menge > ((SELECT AVG(menge) FROM average WHERE datum > 20080310) + (SELECT STDDEV(menge) FROM average WHERE datum > 20080310))
AND datum > 20080310
ORDER by menge DESC;

Diese Abfrage würde jetzt im Zeitraum ab dem 2008-03-11 alle herrausstechenden Werte suchen (2653852 und 2653850).

Als Diagramm in Excel würde das dann so aussehen:

http://img.i7m.de/show/2c2tr-3jq22-2tsns-iidwc.png

Du siehst hier, dass die Standardabweichung natürlich nur eine grobe Abschätzung ist.

Grüße

Max
Member: Supergecko
Supergecko Jan 12, 2009 at 15:45:57 (UTC)
Goto Top
Lieber Max,

zunächst möchte ich dir für all die Mühe danken.
Leider werde ich den Eindruck nicht los dass wir uns missverstehen - Ich habe mich wahrscheinlich kompliziert ausgedrückt.
Nach deiner Überlegung ist die Standardabweichung + Durchschnittswert eine ernstzunehmende Stückzahl.
Der Zeitraum zur Ermittlung des Höchstwertes verschiebt sich ständig mit jedem Tag.
Ok nochmals zur Definition:
Die Abfrage sollte lauten:
Zeige mir alle ID´s, Datumzeiten und Stückzahlen an,
bei denen der Stückzahlenwert > ist 125%*(als der Durchschnitt der letzten 15 Tage + Standardabweichung der letzten 15 Tage)
geordnet nach Datum.

Manche Anfragen reifen mit der Fragestellung face-wink
Member: dog
dog Jan 12, 2009 at 15:58:38 (UTC)
Goto Top
Zeige mir alle ID´s, Datumzeiten und Stückzahlen an,
bei denen der Stückzahlenwert > ist 125%*(als der Durchschnitt der letzten 15 Tage + Standardabweichung der letzten 15 Tage)

Das sähe dann so aus:

SELECT id,datum,menge
FROM table
WHERE menge > ((SELECT AVG(menge) FROM average WHERE datum > ...) + (SELECT STDDEV(menge) FROM average WHERE datum > ...))*1.25
AND datum > ...
ORDER by menge DESC;

Gehen wir es mal Zeile für Zeile durch:

SELECT id,datum,menge

Hole mir die Spalten ID,Datum,Menge

FROM average

Aus der Tabelle average

WHERE menge >=

für die Zeilen, wo die menge größer als

((SELECT AVG(menge) FROM average WHERE datum > ...) + (SELECT STDDEV(menge) FROM average WHERE datum > ...))*1.25

125% des Durchschnittswerts+Standardabweichung der letzten ... Tage ist

AND datum > ...

und die innerhalb der letzten ... Tage liegen

ORDER by menge DESC;

und sortiere sie nach der Menge absteigend.

Das Datum habe ich hier durch ... ersetzt, weil dein Datum kein DATE-Feld ist und man deshalb nicht die Datumsfunktionen benutzen kann mit denen man ziemlich leicht "Heute - 15 Tage" hätte rechnen können.

Übrigens sind Durchschnitt+Standardabweichung*1.25 schon ziemliche statistische Ausreißer.
Bereits Werte außerhalb der Standardabweichung sind idR schon Seltenheiten.
Der Bereich Durchschnitt ± Standardabweichung deckt, betrachtet man dein Beispiel bereits > 91% der Werte ab.
Mit 125% sind es 93%, mit 181% Toleranz werden 100% der Werte abgedeckt.

Grüße

Max
Member: Supergecko
Supergecko Jan 12, 2009 at 16:17:40 (UTC)
Goto Top
Das ist so ungefähr was ich haben wollte.
Nun kommt der Test und im Anschluss folgt die Rückmeldung.
Sag mal, du bist nicht zufällig Mathematiker oder so was ähnliches? face-wink
Grüße
SG
Member: Supergecko
Supergecko Jan 12, 2009 at 16:22:12 (UTC)
Goto Top
Ach so, noch etwas:
Das fehlende Datum kann man über die ID lösen.
Somit hat man auch eine brauchbare Grundlage.
Member: Supergecko
Supergecko Jan 13, 2009 at 07:54:52 (UTC)
Goto Top
Also, die Abfrage hat recht gut funktioniert.
Ein Befehl ist in Access anderst, aber die Abfrage hat funktioniert.
Zur Ergänzung: Es befindet sich noch eine weitere Spalte in der Datenbanktabelle.
Diese Spalte lautet "Typ" bringt mir in der Abfage leider fehlerhafte Ergebnisse sobald sich der Typ ändert. (Mit Änderung des Typs, ändert sich natürlich auch die Stückzahl)
Vielleicht hast du für mich einen kleinen Tip wie ich das eingrenzen kann?
Im Übrigen, die Abfrage lautet zur Zeit:

SELECT ID, Datum, Stück, Typ
FROM Tabelle
WHERE (((ID)>36) AND ((Stück)>((SELECT AVG(Stück) FROM Tabelle WHERE ID > 36) + (SELECT STDEV(Stück) FROM Tabelle WHERE ID > 36))*1.25))
ORDER BY ID ASC;

Für Vorschläge bin ich offen.
Grüße
SG
Member: Supergecko
Supergecko Jan 13, 2009 at 12:27:10 (UTC)
Goto Top
Hallo nochmal,

wenn dir das hin- und hergeschreibe zu anstrengend ist würde ich mich über einen alternativen Internetlink freuen.
Die bisherigen Links waren:
http://www.w3schools.com/sql/sql_functions.asp
http://www.teialehrbuch.de/Kostenlose-Kurse/SQL/22350-Vorwort.html

Ich bin für andere Lösungswege offen und kann mich auch gerne in die Materie hineinlesen.
Also, vielen Dank für die Mühe im vorraus.

SG
Member: Biber
Biber Jan 13, 2009 at 20:22:56 (UTC)
Goto Top
Moin Supergecko,

ich lese ja schon die ganze mit in diesem Thread, aber ich habe noch nicht ganz den Eindruck, dass wir Losungswilligen hier alles wissen, was wir wissen müssen.

Bitte nochmal, um das Problem vom Kopf auf die Füße zu bekommen:
  • welche für die Abfrage relevanten Felder (Name+Datentyp) hat die Tabelle denn nu'??
  • was ist der PK??
  • An was hängt die Stückzahl? Die ID sagt ja nichts aus, außer dass in Deinen Beispieldaten immer genau ein Satz=eine Auto-ID pro Tag kommt. Es sind aber unterschiedliche "Typen", sagst Du ???
  • Spricht irgendetwas dagegen, die Auswertung tatsächlich datumsbezogen zu machen?? "Die letzten 30 Auto-IDs entsprechen 30 Tagen"... das kann ich gar nicht glauben. dann kommen nie an einem Tag mehr als 1 Datensatz????

Grüße
Biber
Member: Supergecko
Supergecko Jan 13, 2009 at 23:07:10 (UTC)
Goto Top
Also zu deinen Fragen:

1. Die derzeit relevanten Datenfelder sind:
ID (Autowert) und Primary Key
Datum (Zahl) - Relevant für die manuelle Sichtprüfung
Stück (Zahl) - Relevant um die Spitzen zu ermitteln
Typ (Zahl) - Es gibt von jeder Stückzahl unterschiedliche Typen

2. ID (Siehe 1)

3. Die Tabelle wurde mir ohne Primary Key mitgegeben, wobei die Grundtabelle an für sich sehr wohl eine Struktur hat. Nämlich das diese anhand folgender Reihenfolge eingelesen wurde:
Datum
Stückzahl
Typ

Nach dem Datenimport hat die Tabelle die Datenstruktur
(Siehe Punkt 1)
Wobei die Typen immer Blockweise übertragen werden, das heisst als erstes kommen
alle Datumsangaben (Bsp. 19980101 bis20090101) sequentiell mit der jeweiligen Stückzahl "xxxx" des Typs "1" dann folgen
alle Datumsangaben (Bsp. 19980101 bis20090101) sequentiell mit der jeweiligen Stückzahl "xxxx" des Typs "2" dann folgen
alle Datumsangaben (Bsp. 19980101 bis20090101) sequentiell mit der jeweiligen Stückzahl "xxxx" des Typs "3" dann folgen ....
Auf diese Struktur wurde einfach sequentiell der Primary Key vorgestellt und in Access eingelesen.

4. Wenn du Punkt 3 gelesen hast, müsste doch so die Auswertung gar nicht so schwer sein oder?
Natürlich kann ich zukünftig die Tabelle mit einem Datumsformat einlesen. Ich hatte noch von damals die Erinnerung das dies bei Access immer Schwierigkeiten machte, deshalb dachte ich mir "bisch´a´käpsele" und "nimmsch" das amerikanische Datumsformat als Zahl definiert. Dabei dachte ich noch damals nicht daran das es auch Wochenenden gibt und das vielleicht auch irgendwanneinmal Feiertage kommen könnten und das mir das sooo zum Verhängnis werden könnte. Jedoch sehe ich gerade keinen Grund nun die Tabelle anders zu definieren, da die Tabelle Jetzt und auch in Zukunft sequentiell eingelesen wird. (ausser man stimmt mich um <- hab ich nicht gesagt)

Jetzt hab ich mich aber verplappert.
Sch.... jetzt ist es schon so spät.
- Gute Nacht

Grüße
SG