Wie lautet das SQL-Statement für Bestandsgraph?

[Axos]

Hallo zusammen.

Allgemeines Problem: Ich habe Datensätze mit einem Zeitstempel und möchte nun gern den Bestand an Datensätzen im Verlauf der Zeit als Graph darstellen. Der Wert Y für den Zeitpunkt X errechnet sich also aus der Anzahl der Datensätze vom Anfangszeitpunkt bis X. Für einen einzelnen Wert sieht das Select-Statement also so aus:

SQL-Code:

SELECT COUNT(D.ID) FROM Datensätze D WHERE (Anfangszeitpunkt <= D.Zeitstempel) AND (D.Zeitstempel <= X)

Wie kann ich nun aber, damit ein Graph entsteht, Wertepaare (X; Select(Anfangszeitpunk,X)) mit SQL zusammenstellen?

Noch ein konkretes Beispiel: Die Datensätze seien die aus Tabelle 1:

SQL-Code:

Tabelle 1
+----+--------------+-------+
| ID |  Zeitstempel |  Feld |
+----+--------------+-------+
|  1 |   2006-01-08 |    29 |
|  2 |   2006-01-12 |    26 |
|  3 |   2006-01-15 |    47 |
|  4 |   2006-02-24 |     5 |
|  5 |   2006-02-17 |    16 |
|  6 |   2006-03-04 |    30 |
|  7 |   2006-05-29 |     6 |
|  8 |   2006-04-30 |    34 |
|  9 |   2006-05-21 |     2 |
| 10 |   2006-06-06 |    41 |
| 11 |   2006-06-19 |     3 |
+----+--------------+-------+

Die Tabelle für den Graphen könnte dann so aussehen (es wurde nach Jahr, Monat gruppiert; Zeitraum: gesamter Datenbesand von 2006-01 bis 2006-06):

SQL-Code:

Tabelle 2
+--------------+----------+
|  Zeitpunkt X |  Bestand |
+--------------+----------+
|      2006-01 |        3 |
|      2006-02 |        5 |
|      2006-03 |        6 |
|      2006-04 |        7 |
|      2006-05 |        9 |
|      2006-06 |       11 |
+--------------+----------+

Wie kann man das machen? Evtl. mit Stored Procedures? Wenn da jemand eine Lösung kennt oder weiß, das es damit funktionieren kann, bin ich für jeden Ansatz dankbar. Ich habe nur leider keine Ahnung von Stored Procedures.

Gruß, Axos.

[mkinzler]

Versuch mal:

SQL-Code:

SELECT Extract( YEAR From Zeitstempel) || '-' || Extract( MONTH From Zeitstempel), COUNT(D.ID) FROM Datensätze D WHERE (Anfangszeitpunkt <= D.Zeitstempel) AND (D.Zeitstempel <= X) GROUP BY Extract( YEAR From Zeitstempel), Extract( DATE From Zeitstempel)

[Axos]

Hallo.

Leider ist das nicht die Antwort auf meine Frage. Das von dir angegebene Statement gibt nur die Anzahl im Intervall (jeweiliger Monat) an. Hier ist das Statement in leicht abgewandelter und funktionstüchtiger Form:

SQL-Code:

SELECT DATE_FORMAT(D.Zeitstempel,'%Y-%m') AS 'Zeitstempel X', COUNT(D.ID)
FROM Datensätze D WHERE ('2006-01-01' <= D.Zeitstempel) AND (D.Zeitstempel <= '2006-12-31')
GROUP BY Extract(YEAR FROM D.Zeitstempel), Extract(MONTH FROM D.Zeitstempel)

Die Ausgabe ist:

SQL-Code:

+---------------+-------------+
| Zeitstempel X | COUNT(D.ID) |
+---------------+-------------+
| 2006-01       |           3 |
| 2006-02       |           2 |
| 2006-03       |           1 |
| 2006-04       |           1 |
| 2006-05       |           2 |
| 2006-06       |           2 |
+---------------+-------------+

Ich hätte aber gern ein Ergebnis wie oben in Tabelle 2 gezeigt. So, dass alle vorhergehenden Count-Werte addiert werden. Hier noch einmal Tabelle 2 mit ergänzenden Erklärungen:

SQL-Code:

Tabelle 2 (mit Ergänzungen)
+--------------+----------+ 
|  Zeitpunkt X |  Bestand | Count-Werte addieren (Bestand = Vormonatsbestand + Monatsbestand)
+--------------+----------+ 
|      2006-01 |        3 | = 3
|      2006-02 |        5 | = 3 + 2
|      2006-03 |        6 | = 3+2 + 1
|      2006-04 |        7 | = 3+2+1 + 1
|      2006-05 |        9 | = 3+2+1+1 + 2
|      2006-06 |       11 | = 3+2+1+1+2 + 2
+--------------+----------+

In Excel würde ich eine Formel der Art "=ZelleLinks+ZelleDarüber" eintragen und in der Spalte nach unten ausfüllen. Wobei in der "ZelleLinks" der Monatsbestand und in der "ZelleDatüber" der Vormonatsbestand steht.

Gruß,
Axos.

[marabu]

Hallo Axos,

die fortlaufende Aufsummierung für eine Zeitreihe erreichst du so:

SQL-Code:

SELECT distinct datum, (select count(*) as saldo from tbl i where i.datum <= o.datum) from tbl o order by datum

Du wirst das sicher an deine Bezeichner anpassen können.

Grüße vom marabu

[Axos]

Hallöchen nochmal.

Problem gelöst! Danke!

Danke für den Hinweis SQL-Statements zu schachteln. Nachdem ich die nötigen Anpassungen vorgenommen habe, kann ich num 2 SQL-Statements anführen, welche die selbe, gewünschte Ergebnismenge produzieren.

SQL-Statement 1 verwendet kein Distinct und die Group-by-Klausel.

SQL-Code:

SELECT DATE_FORMAT(D1.Zeitstempel,'%Y-%m') AS 'Zeitstempel',
  (SELECT COUNT(D2.ID) AS 'Betrag'
  FROM Datensätze D2
  WHERE DATE_FORMAT(D2.Zeitstempel,'%Y-%m')<=DATE_FORMAT(D1.Zeitstempel,'%Y-%m'))
FROM Datensätze D1
GROUP BY DATE_FORMAT(D1.Zeitstempel,'%Y-%m') ASC

SQL-Statement 2 verwendet Distinct und die Order-by-Klausel.

SQL-Code:

SELECT DISTINCT DATE_FORMAT(D1.Zeitstempel,'%Y-%m') AS 'Zeitstempel',
  (SELECT COUNT(D2.ID) AS 'Betrag'
  FROM Datensaetze D2
  WHERE DATE_FORMAT(D2.Zeitstempel,'%Y-%m')<=DATE_FORMAT(D1.Zeitstempel,'%Y-%m'))
FROM Datensaetze D1
ORDER BY DATE_FORMAT(D1.Zeitstempel,'%Y-%m') ASC

Wenn jemand herausfindet, welches der Statements das schnellere ist und wovon das evtl. abhängt kann es ja hier posten.

Noch ein Hinweis zum Vergleich der Where-Klausel im eingebetteten Statement: In den angegebenen Statements wird die Funktion Date_Format verwendet. Beide verglichenen Werte werden so für die sinngemäß korrekte Vergleichbarkeit vorbereitet. Bei Zeit-Vergleichen macht es einen Unterschied,

• Angaben unterschiedlicher Genauigkeit zu vergleichen; z.B. Jahr-Monat-Tag und Jahr-Monat, also bspw. '2006-01-01' und '2006-01'. Und,
• Werte zu großer Genauigkeit als in der Select-Klausel oder Group-by-Klausel angegeben zu vergleichen; z.B. in der Select-Klausel durch Distinct oder Group-by-Klausel wird auf Jahr-Monat gruppiert, und der Vergleich findet taggenau statt.

Die Auswirkung ist nicht allzu augenscheinlich, da sie vorrangig den ersten und den letzten Datensatz betrifft. Wenn an diesen Stellen das richtige Ergebnis zudtande kommt, ist das "Glück". Da dieses Ergebnis jedoch als Graph dargestellt werden soll, würde der Graphanfang bzw. das Graphende verwirrende Werte annehmen. Gerade das Graphende ist mit seiner anzeigenden Tendenz von Bedeutung. Der sichere Weg sind die Statements wie oben im Beitrag angegeben.
Bei Vergleichen mit Zeiten lässt sich z.B. mit der Date_Format-Funktion leicht der Detaillierungsgrad leicht ändern: '%Y', '%Y-%m', '%Y-%m-%d', usw. Details siehe bei Date_Format & Co. im MySQL 4.1 Manual auf MySQL.com.

Gruß,
Axos.

PS: Ich würde mir wünschen, dass mehr die Ergebnisse ihrer gelösten Probleme am Ende kurz darstellen würden. Auch ungelöste bzw. abgebrochene Fragen. Damit würde man nicht immer manchmal seitenlange Threads durchwühlen müssen um anschließend vielleicht immernoch "dumm wie zuvor" dazustehen.