MySQL Unterstützung bei Datenbankdesign Aggregationsebenen

[CrazySash]

Da ich datenbanktechnisch länger nicht aktiv unterwegs war benötige ich Unterstützung bei einem Datenbankdesign

Szenario:
Ich bekomme einen Timestamp und eine Zahl geliefert. Diese sollen später nach Stunde/Tag/Woche/Monat/Jahr ausgewertet werden.
Pro Tag werden aktuell ca. 100 neue Einträge erwartet. Kann jedoch bis zu 10.000/Tag ansteigen.

geplante Umsetzung:
Speicherung der Daten auf Stundenbasis (kleinste Granularität).
Es gibt zu beginn 5 verschiedene ID's. Pro ID kann es in Verbindung mit einer Stunde nur 1! Wert geben. Deswegen ergibt die Kombination aus id und timestamp den primary key.

tabelle(id,timestamp,zahl);

Fragen
Es werden Auswertungen nach Stunden/Tag/Woche/Monat/Jahr vorgenommen.
Ist es sinnvoll Aggregate in eigenen Tabellen (Tag,Woche,Monat, Jahr) anzulegen?
Wenn ja, gibt es eine Möglichkeit bei einem neuen Eintrag automatisch in die anderen Tabellen(Tag/Woche...) weiterzuschreiben (Aggregation)?

Ich hoffe mein Anliegen ist soweit verständlich

Freue mich auf Unterstützung/Verbesserungen

 

[theHacker]

Bisher hab ich das immer mit MySQLs Datumsfunktionen realisiert. Ob das die beste Lösung is..., *den Thread beobachte*

 

[CrazySash]

was genau meinst du mit MySQL Datumsfunktionen?

 

[flaschenkind]

Bisher hab ich das immer mit MySQLs Datumsfunktionen realisiert. Ob das die beste Lösung is..., *den Thread beobachte*

Ich mache das auch so.
Du musst dazu nur den Timestamp in ein MySQL-Datumstypen (DATETIME) konvertieren und kannst dann mithilfe von den Funktionen HOUR(), DAY(), MONTH() usw. die Daten beliebig gruppieren und zählen lassen.

 

[CrazySash]

Ah, da werde ich mal einen Blick in das manuell werfen

Also dementsprechend alles in eine Tabelle rein und dann jeweils bei Auswertungen gruppieren?

 

[flaschenkind]

Also dementsprechend alles in eine Tabelle rein und dann jeweils bei Auswertungen gruppieren?

Dann hast du zumindest alle Möglichkeiten offen. Das kann bei sehr großen Datenmengen dann aber auch seine Zeit brauchen, das alles zu berechnen.

 

[CrazySash]

und lässt allein mit MySQL Boardmitteln ein automatisiertes Aggregieren in die Folgetabellen realisieren oder nur über manuelle einzelnen inserts?

 

[ice-breaker]

Fragen
Es werden Auswertungen nach Stunden/Tag/Woche/Monat/Jahr vorgenommen.

was heisst nach Tagen? jeden 4. eines Monats der letzten 2 Jahre? oder einfach aggregiert die Werte eines Tages in einer Time-Range von X Tagen darstellen?
Und was wird da überhaupt berechnet? Ich habe einen Zeitpunkt eine Zahl, was mache ich damit wenn ich es mir Tagesweise anzeigen lassen will? Die Summe? Der Durchschnitt?

Ist es sinnvoll Aggregate in eigenen Tabellen (Tag,Woche,Monat, Jahr) anzulegen?

werden wir rausfinden

Wenn ja, gibt es eine Möglichkeit bei einem neuen Eintrag automatisch in die anderen Tabellen(Tag/Woche...) weiterzuschreiben (Aggregation)?

nennt sich Trigger

tabelle(id,timestamp,zahl);

welche Bedeutung hat die id?

Ich mache das auch so.
Du musst dazu nur den Timestamp in ein MySQL-Datumstypen (DATETIME) konvertieren und kannst dann mithilfe von den Funktionen HOUR(), DAY(), MONTH() usw. die Daten beliebig gruppieren und zählen lassen.

dann kann aber kein Index genutzt werden, was zu einem Fulltable-Scan führt und bei ihm tödlich ist.

 

[CrazySash]

was heisst nach Tagen? jeden 4. eines Monats der letzten 2 Jahre? oder einfach aggregiert die Werte eines Tages in einer Time-Range von X Tagen darstellen?

Also bisher sollen folgende simple Fragen beantwortet werden können:

• Anzahl in der Stunde X
• Anzahl am Tag X
• Anzahl Woche X
• Anzahl Monat X
• Anzahl Jahr X

An eine Auswertung in der Form jeden 4. eines Monats der letzten 2 Jahre habe ich bisher nicht gedacht.

Wenn ich jedoch darüber nachdenke, würde es Sinn machen das ganze so dynamisch wie möglich zu halten.

Für Analysen können Fragen wie:
Anzahl jeden Montag von 12:00 - 18:00 im Zeitraum 15.02.2010 - 17.09.2010 durchaus interessant sein.

Und was wird da überhaupt berechnet? Ich habe einen Zeitpunkt eine Zahl, was mache ich damit wenn ich es mir Tagesweise anzeigen lassen will? Die Summe? Der Durchschnitt?

Grundsätzlich soll die Summe angezeigt werden.

Wobei aber auch die Frage: Durchschnitt im Zeitraum x interessant ist. Aber um den Durchschnitt zu bekommen benötige ich doch eh die Summen

nennt sich Trigger

Lese dazu gerade das Handbuch

welche Bedeutung hat die id?

Es wird die Anzahl von verschiedenen Schnittstellen abgefragt. Die id stellt die ID der Schnittstelle da, die in einer anderen Tabelle definiert ist. Grundsätzlich könnte man auf die id verzichten und für jede Schnittstelle eine Tabelle anlegen. Da es sich aber dabei immer um die gleichen Daten (Zeitpunkt X und Anzahl) handelt hab ich mir gedacht, dass man bei gleicher Funktion das das Ganze in einer gemeinsamen (Stunden) bzw. mehrerer gemeinsamer (Stunden/Tag/Woche...) Tabellen ablegen kann.

Insgesamt merke ich, dass ich mir noch zu wenig Gedanken über die genauen Anforderungen gemacht habe...

 

[theHacker]

Die id stellt die ID der Schnittstelle da, die in einer anderen Tabelle definiert ist.

... ist also FK.

 

[ice-breaker]

• Anzahl in der Stunde X
• Anzahl am Tag X
• Anzahl Woche X
• Anzahl Monat X
• Anzahl Jahr X

dann würde ich die Daten einmal Stundenweise und ggf. Tageweise speichern.
Denn alle Stunden vom Jahr 2010 wird ziemlich heftig.

An eine Auswertung in der Form jeden 4. eines Monats der letzten 2 Jahre habe ich bisher nicht gedacht.

Wenn ich jedoch darüber nachdenke, würde es Sinn machen das ganze so dynamisch wie möglich zu halten.

Für Analysen können Fragen wie:
Anzahl jeden Montag von 12:00 - 18:00 im Zeitraum 15.02.2010 - 17.09.2010 durchaus interessant sein.

umso dynamischer es werden soll, umso weniger Performanceoptimierungen können vorgenommen werden.

Grundsätzlich soll die Summe angezeigt werden.

Wobei aber auch die Frage: Durchschnitt im Zeitraum x interessant ist. Aber um den Durchschnitt zu bekommen benötige ich doch eh die Summen

k, dann als Tipp, wie ich es umsetzen würde, bzw. es so ähnlich schon in manchen Anwendungen umgesetzt habe in denen es um Statistiken ging:

CREATE TABLE stats (
  apiid int(10) unsigned NOT NULL,
  datehours int(10) unsigned NOT NULL,
  sum int(10) unsigned NOT NULL,
  count int(10) unsigned NOT NULL,
  PRIMARY KEY (apiid,datehours)
) ENGINE=InnoDB;

INSERT INTO stats (apiid, datehours, sum, count) VALUES (1, 2011013020, 5, 3);

Das Datum ist in der Form "JahrMonatTagStunden".
Getroffene Annahme: Es wird jeweils nur zu einer API-ID die Statistik generiert, dadurch sind SELECT-Anfragen effizienter

 

[CrazySash]

Danke für den Vorschlag.

Ich werde das am WE testen und gebe dann Feedback!


EDIT: Leider bekommt man bei der Zeitumstellung Sommer/Winter Probleme...

EDIT 2: Hab da was Nettes in Bezug auf Sommer/Winterzeit gefunden.