watchdogg

Hallo Leute,

ich habe eine MySQL Datenbank, die in etwa Folgends beinhaltet:

In einer Tabelle ZEIT werden An- und Abmeldezeiten von Usern gespeichert.

In einer anderen Tabelle, nennen wir sie mal AKTION, werden vorgenomme Aktionen von Benutzern gespeichert.
Diese Tabelle enthält Informationen über die Aktionen, und zu welcher Zeit sie ausgeführt wurden.

Es gibt also in beiden Tabellen, Spalten mit Datum(Datentyp Date) und Zeit(Datentyp TIME).

In der Tabelle ZEIT gibt es die Spalte Benutzer, in der Tabelle Aktion jedoch nicht.

Mein Ziel ist es jetzt, die Aktionen aus der Tabelle AKTION den jeweiligen Benutzern zuzuweisen. Es muss also das Datum und die Zeit der Aktion mit der Anmeldezeit überprüft werden.
Der Benutzer, der zur Zeit der AKTION angemeldet war, bekommt die Aktion also zugewiesen.

Jetzt ist es aber so, das der Benutzer sich mehrmals täglich an- und abmelden kann, sodass sich für jeden Benutzer mehrere Zeitfenster ergeben.

Als erstes müssten ja die Zeitfenster ermittelt werden:

SELECT * FROM ZEIT WHERE user='benutzername' and art='Anmeldung'
sowie
SELECT * FROM ZEIT WHERE user='benutzername' and art='Abmeldung'

So jetzt hätte ich jeweils die An- und Abmeldezeiten. Da es mehrere An- und Abmeldungen gibt muss ich die Zeiten ja irgendwie speichern, oder?
Würdet ihr hier ein Array nehmen?

Im nächsten Schritt müssen die gespeicherten Zeitfensten nun mit der Tabelle AKTION verglichen werden.

SELECT * FROM AKTION WHERE Datum='gespeichertesDatum' AND Zeit BETWEEN 'gespeicherteStartzeit' AND 'gespeicherteEndzeit'

Und das für alle Einträge im Array.


Die Tabellenstrukturen sind festgelegt und können nicht verändert werden.
Ziel der ganzen Aktion soll eine Monatsauswertung sein.


Habt ihr eventuell TIPPS für mich, wie ich das sauber und effektiv realisieren kann?


MfG watchdogg

Mister Ad

watchdogg

Hat keiner ne Idee, oder liegts an meiner Beschreibung?

lstegelitz

Ja: Das Datenmodell ist be..scheiden.

Unter diesen Voraussetzungen leider nicht.

Mein Vorschlag wäre gewesen, eine vernünftige Tabellenstruktur zu erstellen, in etwa so:

Tabelle "Benutzer"
ID | Name

Tabelle "Aktion"
ID | Benutzer_ID | Art_der_Aktion | von | bis

- Direkte Verbindung von Aktion zu Benutzer (über Fremdschlüssel)
- Komponente "Zeit" in Aktion verlagern, weg vom Benutzer
- Pro Aktion ein Eintrag in Tabelle "Aktion".

(Beitrag auf die Schnelle erstellt)

__________________
Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

watchdogg

Ich habe in meinem ersten Beitrag völligen Blödsinn geschrieben.
So passierts eben, wenn man rumdöst - also nochmal.


Ich habe folgende Tabellen:


Tabelle USER
------------
UserID | UserName


Tabelle USERLOG
---------------
UserlogID | UserlogDate | UserlogStartTime | FK_User_ID | UserlogArt


In der Tabelle Userlog werden die Anmeldezeit und die Abmeldezeit gespeichert.
Jede Anmeldung und jede Abmeldung ist ein eigener Userlog für sich, es wird immer nur die Zeit des Userlogs gespeichert.

Also z.B.:

1 | 2011-09-06 | 12:00:00 | 4 | Anmeldung
2 | 2011-09-06 | 12:30:00 | 4 | Abmeldung
3 | 2011-09-06 | 13:00:00 | 1 | Anmeldung
4 | 2011-09-06 | 13:10:00 | 1 | Abmeldung
5 | 2011-09-06 | 14:00:00 | 4 | Anmeldung
6 | 2011-09-06 | 14:30:00 | 4 | Abmeldung
...


Jetzt kommt meine dritte Tabelle ins Spiel:

Tabelle AKTION
--------------

AktionID | AktionDate | AktionStartTime | AktionWert

1 | 2011-09-06 | 12:05:12 | 1200
2 | 2011-09-06 | 12:13:44 | 3000
3 | 2011-09-06 | 13:04:18 | 1444
4 | 2011-09-06 | 14:15:22 | 400

...


Ich möchte nun gerne eine Monatsabrechnung erstellen.
Man gibt also einen Usernamen, einen Monat und das Jahr ein.

Anhand der Zeiträume der Tabelle USERLOG sollen nun die Aktionen ermittelt werden, die in die Zeiträume für die jeweilige UserID zwischen An- und Abmeldung fallen.
Die Werte in AktionWert sollen addiert werden.

Beispiel:
---------

User 4 bekommt die AktionsWerte 4600, User1 bekommt 1444 zugewiesen.


Wie erstelle ich nun die Abfrage?
Ich muss doch erstmal prüfen in welchen Zeiträumen User 4 angemeldet war und dann die zugehörigen Aktionen selektieren, die in diese Zeiträume fallen.
Dazu muss ich ja die einzelnen Zeiträume zwischenspeichern.

Wie geht man dabei am Besten vor? Es wäre echt gut wenn ihr mir TIPPs dazu geben könntet.

Ich hoffe, dass meine Beschreibung verständlich ist.


MfG watchdogg

wolf29

Hi.

Entweder ist deine BEschreibung fehlerhaft, oder ich sehe den Wald vor lauter Bäume nicht, aber

wo kommen die Werte den genau her?? In deiner Beispieltabelle stehen doch andere (Verwirrung). Ansonsten kannst du alles über eine SQL Abfrage doch machen.

mfg Wolf29

__________________
while (!asleep()) sheep++;

Unterschätze nie jemanden der einen Schritt zurück geht! Er könnte Anlauf nehmen.

lstegelitz

Trenne Datum und Uhrzeit NICHT voneinander, sondern benutz DATETIME. Es macht das Rechnen mit Zeiten wesentlich einfacher, vor allem wenn An- und Abmeldung über eine Tagesgrenze hinweggehen.

Sehe ich genauso. Mit nur einer Query ist das nicht zu erreichen.

Als erstes musst du die einzelnen Zeiträume bestimmen, das wird mit reinem SQL nicht ganz leicht werden, weil du immer nur die zueinander passenden An- und Abmeldungen betrachten darfst. Der erste Datensatz muss eine Anmeldung sein, der Startzeitpunkt UND der Endzeitpunkt (d.h. der passende Abmelde-Datensatz) muss innerhalb des Betrachtungszeitraums liegen.

Die Bedingungen für die zweite Query werden aus den Daten der ersten Query (Bestimmung der Zeiträume für den User) zusammenschustern und mit OR verknüpfen:
Zeitraum1, Zeitraum2 bezeichnet ein passendes Wertepaar aus der ersten Abfrage (Start=Anmeldung, Ende=Abmeldung)

__________________
Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

lstegelitz

User 4:
1 | 2011-09-06 | 12:00:00 | 4 | Anmeldung
2 | 2011-09-06 | 12:30:00 | 4 | Abmeldung

5 | 2011-09-06 | 14:00:00 | 4 | Anmeldung
6 | 2011-09-06 | 14:30:00 | 4 | Abmeldung

User 4 war angemeldet am 06.09.2011 zwischen 12:00-12:30 und zwischen 14:00-14:30

Folgende Aktionen waren in den beiden Zeiträumen "aktiv":
1 | 2011-09-06 | 12:05:12 | 1200
2 | 2011-09-06 | 12:13:44 | 3000
4 | 2011-09-06 | 14:15:22 | 400

Die Punkte zusammengezählt: 4600

edit: Betrachtungszeitrum ist der 06.09.2011 (von 0 bis 23:59Uhr)

__________________
Über 90% aller Gewaltverbrechen passieren innerhalb von 24 Stunden nach dem Konsum von Brot.

tr0y

Spar dir die 2 Spalten für Datum und Zeit und nutze eine die einen Timestamp ( Unix oder DateTime ) speichert.

__________________
Lasse mir ohne Anwendung von Gewalt Dinge schenken, Amazon weiß darüber bald mehr.

wolf29

@lstegelitz: thanx...manchmal ist man einfach blind

__________________
while (!asleep()) sheep++;

Unterschätze nie jemanden der einen Schritt zurück geht! Er könnte Anlauf nehmen.

watchdogg

Vielen Dank für eure Antworten.
Wie gesagt die Spalten mit Datum und Zeit sind vorgegeben, ich kann die nicht einfach ändern.

Kann man die Spalten Datum und Zeit nicht irgendwie bei einer Abfrage in ein DATETIME Format casten?

MfG watchdogg