PHP/mySQL: TimeStamp (lastSeen) auswerten zu "Verweildauer"

[WulfmanGER]

Hallo in die Runde,

Denkblockade - bekomme es gerade nur megakomplex hin (so komplex das ich nach der 3. While-Verschachtelung mit zig "Hilfsvariablen" aufgehört habe [30 Zeilen PHP und kein Ergebnis])

Ich habe u.a. UserID, SessionID (die ist nur 3stellig und daher wiederholbar - in einer anderen Session!) und ein TimeStamp (lastSeen).

Ich suche jetzt eine Lösung wie ich "lastSeen" so zusammenfassen das ich die Verweildauer in sekunden ermitteln kann...

Quelle Tabelle (sortiert nach lastSeen):

UserID|    session_id    |    lastSeen
A123        |    12            |    2022-05-22 18:03:37
A123        |    12            |    2022-05-22 18:06:14
A123        |    12            |    2022-05-22 18:12:03
A123        |    5            |    2022-05-22 18:15:03

In die Zieltabelle soll rein (eine Zeile):
firstSeen = 2022-05-22 18:03:37
lastSeen = 2022-05-22 18:12:03
verweildauer = ~500s (jetzt nicht ausgerechnet )
Aus den 4 Datensätzen oben möchte ich somit nur noch 2 machen (session_id 12 und 5)

Wenn das lastSeen zu komplex ist - ist das aber auch kein Problem - Hauptsache firstSeen und Verweilsdauer ... das "von bis" kann ich mir damit in der PHP-Ausgabe dann ja auch ausrechnen.

Hier komme ich nur auf komplexe Lösungen (mit vielen Whiles, IFs und temp-variablen und min. eine Zwischen-Tabelle) ... wenn man Logfiles mit div. Tools auswerten hat man auch nur ein Timestamp und ermitteln damit wie lang jemand online war. Vermutlich kann php/mySQL sowas schon von hause aus?

Im Prinzip suche ich sowas wie: GROUP BY lastSeen ... aber nur für Aufeinanderfolgende Datensätze mit gleicher session_id.

Hat da jemand einen Ansatz?

Grüße

PS: die QuellTabelle hat noch mehrere "WHERES" die einen Datensatz "unique" mache. Die session_id ist 3stellig - kann also im Gesamtlog oder über mehrere server also mehrfach da sein - sogar von der gleichen Person und im WorstCase wechselt der User den Server und hat die gleiche session_id wie auf dem vorherigen Server. 3 Werte (UserID, Application, Session_ID) müssen also übereinstimmen (WHERE / AND) damit ich das lastSeen einer Session zuordnen kann. Hab das oben nur etwas komprimierter zusammengefasst.

 

[Phrasendreher]

Damit es keine redundanten Einträge für UserID/session_id gibt:
Mach einen Unique-Key auf diese beiden Spalten; ändere das INSERT, mit dem Du die EIntragungen machst, mit ON DUPLICATE KEY UPDATE lastSeen=neuer_timestamp.

Da liessen sich natürlich auch noch weitere Spalten hinzufügen (Du schreibst von "Application"); weil Du die Logik für das Updating so dem DBMS überlässt, kommst Du sicherlich auf weniger und einfacheren Code.

 

[floq0r]

Die Frage zu der du bei so einem iterativen Ansatz zwangsläufig kommen wirst: Ab welchem Schwellwert ist eine Verweildauer keine Verweildauer mehr sondern eine Abwesenheit?
Ansonsten kalkuliere dir im ersten Step für jede Zeile die Differenz zwischen dem aktuellen Timestamp und dem ersten Timestamp für diese userID davor über eine Subquery ala (pseudocode) SELECT T1.timestamp - (SELECT TOP 1 timestamp FROM table AS T2 WHERE T2.timestamp < T1.timestamp AND T2.userID = T1.userID) AS diff FROM table AS T1
Diese diffs rechnest du dann zusammen, gruppiert nach Tag oder whatev

 

[MisC]

Sollte das nicht mit MAX() - MIN() und GROUP BY session_id funktionieren? Oder ich versteh es falsch?

 

[floq0r]

@MisC Wenn es global über alle entries gehen soll ginge das, wäre aber wahrscheinlich wenig aussagekräftig weil ja niemand durchgehend vom ersten bis zum letzten log entry online war.
Dazu wäre es sinnvoller zu wissen was @Wulfman_SG überhaupt genau auswerten will.

 

[WulfmanGER]

Damit es keine redundanten Einträge für UserID/session_id gibt:

Redundanz hab ich natürlich schon raus gefiltert.

Das JSON was ich auslese wird alle 3min aktualisiert - ich selber lese das JSON alle 5min aus. Die Wahrscheinlichkeit das ich identische Daten einlese ist also gering und falls das doch mal passiert (weil das JSON nun alle 6min ausgewertet wird...) hab ich mit entsprechenden UNIQUEs vorgesorgt.

Trotzdem kommen da extrem viel Daten zusammen [aktuell 240.000; nach gut einer Woche] die ich nun versuchen möchte zusammenzufassen. Ich hab hier teils "Session-Blöcke" mit 400 Datensätzen die in der neuen Zieltabelle als EIN Datensatz erfasst wären: firstSeen: 10 Uhr, lastSeen 23 Uhr. Gleiche quote bei 240.000 = 600 Datensätze mit GLEICHEN Informationsgehalt (nur leichter zu verwerten)

Die Frage zu der du bei so einem iterativen Ansatz zwangsläufig kommen wirst: Ab welchem Schwellwert ist eine Verweildauer keine Verweildauer mehr sondern eine Abwesenheit?

Ergibt sich eigentlich aus den JSON-Daten:

Import 1: UserID+Session_id ist drin
Import 2..10: weiterhin drin.
Import 11: User wird nicht mehr erfasst -> Er ist Abwesend oder wurde disconnectet und hat jetzt neue Session_ID (das will ich nicht extra behandeln - das ist dann so! Neue SessionID = neue Zeile im Zieltabelle - das darf so!)
Das heißt "lastSeen" in Import 1 = FirstSeen ... "lastSeen" in Import 10 = wirklich "lastSeen" (die ungenauigkeit durch erstellung JSON und Import ist halt so)

Dazu wäre es sinnvoller zu wissen was @Wulfman_SG überhaupt genau auswerten will.

Grundsätzlich mache ich vieles einfach aus Lernzwecken. Hier: Statistiken. In dem Fall möchte ich einfach Onlinezeiten erfassen. UserID x = Mi 01.06. 15-16 Uhr (60min) [Session_ID 1], Mi 01.06. 17-23 Uhr )(6h) [Session_ID 438]

Das ganze würde ich dann in der PHP-Ausgabe auch grafisch auswerten: "[15 Uhr ... Sess 12 ... App 29 ... 19 Uhr] [19:15 Uhr ... Sess 398 ... App 11 ... 23:40 Uhr]"

Sollte das nicht mit MAX() - MIN() und GROUP BY session_id funktionieren? Oder ich versteh es falsch?

das muss ich dann aber weiter einschränken. Wie floq0r schon schreibt: keiner ist vom ersten bis letzten mal sehen wirklich dauerhaft Online. Das Problem ist die "falsche" Session_ID ... diese ist nur 1-3 Stellig. Das hat innerhalb der Application für den USER auch gründe - der muss diese nämlich dort nutzen - so eine 16stellige+ hex-SessionID wäre hier nicht nutzbar (das JSON hab ich leider nicht zu verantworten). Hätte ich hier eine echte SessionID würde ich mit jede UserID mit DISTINCT SessionID weiterverarbeiten/nochmal schleifen und dann ein max/min. Fertig.

Aktuell sieht das für mich so aus das ich hier nur mit PHP und 2-3 schleifen und "Merk"-Variablen (mir den letzten Datensatz merke, wo ich den nächsten schon bearbeite). Merk und aktuell abgleichen und dann entsprechend handeln.

Nur das bedeutet ich muss genau aufpassen wann ich die temp-Variablen erstelle - nicht das ich die zu früh erstelle und somit alles kaputt schreibe. Und da hab ich gerade echt eine Denkblockade - der code ist bereits 30 Zeilen lang und macht im prinzip bisher kaum was.... daher hier der Post für sinnigere Ansätze

=> ELEGANT finde ich das nicht. Da sowas beim erstellen von Stastiken ja schon genutzt wird, hatte ich gehofft hier gibt es schon paar fertige Ansätze...

SELECT T1.timestamp - (SELECT TOP 1 timestamp FROM table AS T2 WHERE T2.timestamp < T1.timestamp AND T2.userID = T1.userID) AS diff FROM table AS T1

damit komm ich so nicht weiter. Beim TOP 1 sagt mir mysql ich sollte doch mal das Handbuch lesen ... hab ich gemacht - aber bisher nicht verstanden was ich da genau mache (wobei gerade doof ist - Migräne Deluxe ... nach dem Post hier stoppe ich auch erstmal bis ich wieder gerade aus denken kann... hoffe das Home-Office-Wohnzimmer wird bald "frei" )

 

[floq0r]

Das war am Montag schnell dahingetippselt, ich habe ausschließlich mit TSQL / SQL Server zu tun. In MySQL gibt es TOP nicht, da läuft das über LIMIT.

Ich hab mich mal in der db fiddle gespielt, leider kann man von dort scheinbar nicht sharen:

Schema:

CREATE TABLE log (userID NVARCHAR(100), session_id INT, lastSeen datetime);

INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 12, '2022-05-22 18:03:37');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 12, '2022-05-22 18:06:14');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 12, '2022-05-22 18:12:03');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 5, '2022-05-22 18:15:03');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 5, '2022-05-22 18:25:10');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 5, '2022-05-22 20:10:05');
INSERT INTO log (userID, session_id, lastSeen) VALUES ('A123', 5, '2022-05-22 20:29:33');

Abfrage:

WITH CTE AS
(
    SELECT
        userID,
          session_id,
          lastSeen,
          ROW_NUMBER() OVER (PARTITION BY userID, session_id) AS rownum
    FROM
        log
),
CTE2 AS
(
    SELECT
          *,
          TIMESTAMPDIFF(SECOND, (
            SELECT lastSeen
            FROM CTE AS C2
            WHERE
                C2.session_id = C1.session_id AND
                C2.lastSeen < C1.lastSeen
            ORDER BY lastSeen DESC
            LIMIT 1
        ), lastSeen) AS diff
    FROM
        CTE AS C1
)
SELECT
    *
FROM
    CTE2
WHERE
    diff < 1200 #20min
;

Von hier aus hast du die Differenz zum vorherigen Timestamp dieser Session-ID in Sekunden und hast Abwesenheiten >=20min rausgefiltert. Von hier aus kannst du diese Differenzen zu einer Anwesenheitsdauer aufsummieren.

 

[coolmodi]

Das JSON was ich auslese wird alle 3min aktualisiert

Nur mal zum Verständnis: Die Daten werden gar nicht beim Nutzen der Website(?) in eine Datenbank geschrieben, sondern du liest das aus einem Log aus, schreibt das in eine DB, und willst dann anhand dieser die Aufenthaltsdauern berechnen? Das klingt nämlich alles unnötig umständlich.

Eigentlich sollte es von vornherein nur 2 Datenpunkte je "Aufenthalt" geben, nämlich Start und Ende. Dafür brauchst du ja nur eindeutige Identifikatoren. Und falls ich beim Überfliegen nicht irgendetwas wichtiges übersehen habe, so hast du die ja mit UserId und SessionId, denn die kann es ja nicht mehrfach zur gleichen Zeit geben. Damit kann man es doch von Anfang an auf einen Eintrag für jeweils Start und Ende reduzieren.

Wenn ein [UserId, SessionId] Paar unbekannt ist => Start des Aufenthalts / Login
Wenn eine Anfrage von einem bekannten Paar kommt => (Neues) Ende des Aufenthalts

Falls sich die Session ändert und der Zeitabstand kleiner x zur letzten bekannten von UserId ist kann man das dann noch zusammenfügen wenn man will. Aber das ist dann ja auch trivial.

Einfach alles aus einem Log in eine DB werfen und dann aus 99% unnötigen Einträgen die richtigen filtern scheint mir in jedem Fall der falsche Ansatz zu sein. Entweder man liest direkt nur die relevanten Daten aus dem Log aus, oder man lässt den Server direkt die relevanten Daten in die DB schreiben.