[MySQL] Frage zu SQL-Befehl: Vorherige Zeile als Vergleichswert in nächster Zeile?

[Fuchur84]

Hallo !

Ich bin neu hier – habt also bitte Geduld mit mir .
Ich konnte leider (vielleicht weil ich den richtigen Begriff nicht kenne) keine passende Antwort auf meine Frage finden und bin mir auch nicht ganz sicher, ob das überhaupt möglich ist, aber wenn es ginge wäre es ein riesen Performance-Helfer für mich:

Ich hab ein kleines Analyse-Tool gebaut, mit dem ich Zugriffszahlen analysieren möchte.
Es geht um eine MySQL 5.5-DB.

In der Tabelle "visitor_views" sind Aufrufe der Website mit dem Zeitpunkt "visit_time" (als UNIX-Timestamp, int(11)) gespeichert sowie "visitor_ids" und "page_ids" hinterlegt. Die "page_ids" sind Seiten des Webauftritts, Visitor_ids ist der Primary Key einer anderen Tabelle "visitors" in der verschlüsselte IP-Adressen und Browser-Version des Besuchers abgespeichert wurden.

Nun würde ich gerne Aufrufe und Besuche von Unterseiten ermitteln. Dies funktioniert schon ganz gut.
Nur die Besuche müsste ich eben ermitteln, und zwar so, dass ein Besuch dann gewertet wird, wenn der Besucher nach 30 Minuten oder mehr wieder auf der Seite erscheint.

Dafür habe ich mir gedacht, ich müsste die Tabelle "visitor_views" eigentlich nur nach der "visit_time" sortieren und dann schauen, ob die vorherige Zeile mit dem gleichen Benutzer mindestens 30 Minuten älteren Timestamp abgespeichert hat. Ist dies der Fall, wird dies als ein Besucht gezählt, sonst nicht. Aber wie komme ich denn an die nächste oder vorherige Zeile dran?

Hier mein Ansatz:

	SELECT
		count(view_id) AS view_count, 
		count(vis_id WHEN (visit_time + (30 * 60)) <= [nextRow].visit_time) AS visit_count, 
		page_id
	FROM
		visitor_views
	GROUP BY
		page_id
	ORDER BY
		visit_time DESC

Bei [nextRow] müsste ich eben den Wert der nächsten Zeile für visit_time herausfinden... (oder man drehts um und guckt einfach nach der vorherigen Zeile, weil die MySQL ja schon kennt?) Gibt es da eine Möglichkeit und könnte mir jemand einen Tipp dazu geben?

Vielen Lieben Dank im Voraus für Eure Hilfe
*Fuchur*

 

[Yaslaw]

Nächste Zeile

SELECT
    @last_id AS id_vor_dieser_zeile,
    @last_id := view_id
FROM
    (SELECT @last_id := 0) as vars,
    my_table

 

[Fuchur84]

Hallo Yaslaw,

vielen Dank für den Tipp, leider komm ich aber noch nicht ganz weiter. Ich hab es jetzt mal so umgeschrieben, krieg es aber leider nicht lauffähig hin:

SELECT
		count(view_id) AS visit_count,
		count(vis_id CASE WHEN (visit_time + (60 * 30)) < (SELECT 
															visit_time 
													FROM 
															visitor_views 
													WHERE 
															view_id = (SELECT
														    				@last_id AS id_vor_dieser_zeile,
														    				@last_id := view_id
																	FROM
															    			(SELECT @last_id := 0) AS vars,
															    			visitor_views
															    		)
													)
		) 
FROM
		visitor_views
WHERE
		visit_time >= 0
		AND visit_time <= 9999999999999999999999
GROUP BY
		page_id
ORDER BY
		visit_time ASC

Problematisch dabei ist vermutlich mindestens, dass ich zwei Werte erhalte wenn ich @last_id AS id_vor_dieser_zeile, @last_id :=view_id selektiere und ich bin mir auch nicht ganz sicher, ob man in einem SELECT direkt wieder ein SELECT verwenden kann. Müsste sowas nicht nach einem WHERE kommen? visit_time >= 0 bzw 9999... ist nur ein Beispiel. Dies wird später per PHP eingetragen.

Hast Du eine Idee was ich falsch mache bzw. wie ich den Fehler lösen kann?

Viele Grüße und vielen Dank nochmal für die Hilfe
*Fuchur*

 

[Fuchur84]

Hallo,
hab mich nochmal damit beschäftigt und denke, ich bin näher dran, aber bekomme immernoch eine Fehlermeldung beim Ausführen. Hier der SQL-Code:

SELECT
							count(view_id) AS visit_count,
							sum((CASE WHEN (visit_time + (60 * 30)) < (SELECT 
																						visit_time 
																				FROM 
																						visitor_views 
																				WHERE 
																						view_id IN (SELECT
																							    			@last_id := view_id
																									FROM
																							    			(SELECT @last_id := 0) AS vars,
																							    			visitor_views v1
																							    	WHERE
																							    			v1.vis_id = v2.vis_id  
																								)
																				ORDER BY
																						visit_time ASC
																				LIMIT 0,1
																		)
							THEN 1 ELSE 0 END)) AS visitor_count
					FROM
							visitor_views v2
					WHERE
							visit_time >= 0
							AND visit_time <= 999999999999999999999999999
					GROUP BY
							page_id
					ORDER BY
							visit_time ASC

Leider killt das aber wohl den MySQL-Dienst.. oder besser ich kann ihn nicht mehr aufrufen. Ich hätte ja gesagt, der Aufruf braucht einfach lange, aber von ein paar Millisekunden zu mehrere Minuten finde ich trotzdem übertrieben.

Viele Grüße
*Fuchur*

 

[Fuchur84]

Okay ich denke ich krieg es so nicht hin. Vermutlich gibt das einfach eine Endlosschleife, weil er immer wieder mit allem vergleicht.
Die einfachste Art die Werte zu ermitteln wäre dieser hier (visit_time-Werte stehen für den 25.5.2013 in diesem Zusammenhang):

SELECT
			page_id, count(view_id) AS visit_count,
			count(DISTINCT vis_id) AS visitor_count,
			avg(visit_duration) AS avg_visit_duration
FROM
			visitor_views
WHERE
			visit_time >= 1369432800
			AND visit_time <= 1369519199
GROUP BY
			page_id
ORDER BY
			visit_count DESC

So richtig glücklich bin ich damit aber nicht, weil ich eben nur die absolute Anzahl an unterschiedlichen Benutzern erhalte, nicht aber eine Anzahl, die den Zeitfaktor miteinrechnet. Also den, der einen Besucher, der 30 Minuten lang nicht auf der Seite war als neuen Besucher identifiziert.

Habe ich da irgendeine Chance das zu ermitteln? Oder muss ich das zwingend mit dem Result-Set in PHP machen?

Viele Grüße
*Fuchur*