Ich habe eine Datenbanktabelle, die die Checkins jedes Benutzers in Städten enthält. Ich muss wissen, wie viele Tage ein Benutzer in einer Stadt war und wie viele Besuche ein Benutzer in einer Stadt gemacht hat (ein Besuch besteht aus aufeinanderfolgenden Tagen in einer Stadt).
Betrachten wir also die folgende Tabelle (vereinfacht, die nur DATETIME s enthält - derselbe Benutzer und dieselbe Stadt):
Die Anzahl der Tage, die dieser Nutzer in dieser Stadt war, wäre 6 ( 30.06 , 01.07 , 02.07 , 01.08 , 05.08 , 06.08 ).
Ich dachte daran mit SELECT COUNT(id) FROM table GROUP BY DATE(datetime)
Für die Anzahl der Besuche, die dieser Nutzer in dieser Stadt gemacht hat, sollte die Suchanfrage 3 ( 30.06-02.07 , 01.08 ) zurückgeben , 05.08-06.08 ).
Das Problem ist, dass ich keine Ahnung habe, wie ich diese Abfrage erstellen soll.
Jede Hilfe wäre sehr willkommen!
Sie können den ersten Tag jedes Besuchs finden, indem Sie Checkins finden, bei denen am Tag zuvor kein Check-in stattgefunden hat.
%Vor%Diese Abfrage enthält mehrere wichtige Teile.
Zuerst wird jeder Check-in mit einem Check-in vom vorherigen Tag verbunden. Da es sich jedoch um einen Outer-Join handelt, hat die rechte Seite des Joins% ce_de% results, wenn am vorherigen Tag kein Check-in stattgefunden hat. Die NULL -Filterung erfolgt nach dem Join, so dass nur diejenigen Checkins von der linken Seite bleiben, wo keine von der rechten Seite sind. WHERE ist wirklich praktisch, um herauszufinden, wo nicht ist.
Dann zählt distinct Check-in-Daten, um sicherzustellen, dass es nicht doppelt zählt, wenn der Nutzer am ersten Tag des Besuchs mehrfach eingecheckt hat. (Ich habe diesen Teil bei der Bearbeitung hinzugefügt, als ich den möglichen Fehler entdeckte.)
Bearbeiten: Ich lese gerade Ihre vorgeschlagene Abfrage für die erste Frage. Ihre Abfrage würde Ihnen die Anzahl der Checkins an einem bestimmten Datum anstelle einer Anzahl von Daten liefern. Ich denke, dass du stattdessen so etwas willst:
%Vor%Versuchen Sie, diesen Code auf Ihre Aufgabe anzuwenden -
%Vor%Erläuterung:
Um zu verstehen, wie es funktioniert, überprüfen wir die Unterabfrage, hier ist es.
%Vor% Wie Sie sehen, gibt die Abfrage alle Zeilen zurück und führt eine Rangfolge für die Anzahl der Besuche durch. Dies ist eine bekannte Ranking-Methode basierend auf Variablen. Beachten Sie, dass Zeilen nach Benutzer- und Datumsfeldern geordnet sind. Diese Abfrage berechnet Benutzerbesuche und gibt den nächsten Datensatz aus, wobei die Spalte days den Rang für die Anzahl der Besuche angibt -
Dann gruppieren wir diesen Datensatz nach Benutzer und verwenden Aggregatfunktionen:
'COUNT (DISTINCT (DATE (dt)))' - zählt die Anzahl der Tage
'MAX (Tage)' - die Anzahl der Besuche, es ist ein maximaler Wert für das Feld days aus unserer Unterabfrage.
Das ist alles;)
Als Datenbeispiel, das von Devart bereitgestellt wird, arbeitet das innere "PreQuery" mit SQL-Variablen. Wenn der @ LUuser standardmäßig auf -1 (wahrscheinliche nicht vorhandene Benutzer-ID) gesetzt wird, prüft der IF () - Test, ob ein Unterschied zwischen dem letzten Benutzer und dem aktuellen Benutzer besteht. Sobald ein neuer Benutzer einen Wert von 1 erhält ... Wenn das letzte Datum mehr als 1 Tag vom neuen Datum des Eincheckens entfernt ist, erhält es zusätzlich den Wert 1. In den nachfolgenden Spalten wird dann der Wert zurückgesetzt @LUser und @LDate auf den Wert des eingehenden Datensatzes, für den gerade getestet wurde, für den nächsten Zyklus. Dann fasst die äußere Abfrage sie zusammen und zählt sie für die endgültigen korrekten Ergebnisse pro Devart-Datensatz von
%Vor%Ich denke, Sie sollten die Datenbankstruktur ändern. Sie könnten Tischbesuche und visit_id in Ihre Checkins-Tabelle aufnehmen. Jedes Mal, wenn Sie ein neues Check-in registrieren möchten, prüfen Sie, ob es einen Check-in pro Tag gibt. Wenn ja, fügen Sie ab dem gestrigen Check-in ein neues Check-in mit visit_id hinzu. Wenn nicht, dann fügen Sie einen neuen Besuch zu den Besuchen und neuen Einchecken mit neuer visit_id hinzu.
Dann könnten Sie Daten in einer Abfrage mit so etwas erhalten:
SELECT COUNT(id) AS number_of_days, COUNT(DISTINCT visit_id) number_of_visits FROM checkin GROUP BY user, city
Es ist nicht sehr optimal, aber immer noch besser als irgendetwas mit der aktuellen Struktur zu tun und es wird funktionieren. Auch wenn die Ergebnisse separate Abfragen sein können, wird es sehr schnell funktionieren.
Aber natürlich müssen Sie die Datenbankstruktur ändern, ein wenig mehr Scripting durchführen und aktuelle Daten in eine neue Struktur konvertieren (d. h. Sie müssen visit_id zu den aktuellen Daten hinzufügen).
Tags und Links datetime mysql gaps-and-islands