count aus mapping tabelle...
cheops
- datenbank
hi,
ich habe folgendes problem: in einer tabelle mappe ich visitorIDs mit seitenaufrufen, sieht ungefähr so aus:
mapping_id | page_id | visitor_id | created
...jetzt ruft der gleiche visitor mehrere pages auf, woraufhin er mit seiner visitor_id und mehreren pageIDs mehrere einträge (rows) generiert...
und nun möchte ich folgendes wissen:
wie viele user haben pageID #1 aufgerufen? (ziemlich einfach!)
wie viele user haben pageID #1 UND pageID #2 aufgerufen?
wie viele user haben pageID #1, pageID #2 und pageID #3 aufgerufen?
usw.
die verknüpfung "where page_id=1 and page_id=2 group by visitor_id" funktioniert natürlich nicht, da ja pro zeile jeweils nur ein wert für die page existieren kann und das "and" daher nutzlos ist...
hab im netz nix passendes zu dem problem gefunden, sollte aber eigentlich ein "standard-problem" sein, oder?
hat jemand eine idee/lösung?
danke!
gruß
cheops
Lieber cheops,
die verknüpfung "where page_id=1 and page_id=2 group by visitor_id" funktioniert natürlich nicht, da ja pro zeile jeweils nur ein wert für die page existieren kann und das "and" daher nutzlos ist...
und schon merken wir, dass die Sprachlogik der gesprochenen Sprache "und" anders versteht, als die Sprachlogik einer Computersprache. Versuche es im Deutschen einmal mit "entweder"...
Liebe Grüße,
Felix Riesterer.
hi felix,
na das "or" bringt mich auch nicht weiter, weil dann ja BEIDE rows gezählt werden...
anyway, hat sich erübrigt, weil ich meine lösung dann doch noch ENDLICH gefunden habe (übrigens hier im forum)
danke & gruß
Hallo Felix,
meine Ausführungen in diesem Beitrag sind vor allem dafür da, Dich beim Erlernen von SQL weiterzubringen.
die verknüpfung "where page_id=1 and page_id=2 group by visitor_id" funktioniert natürlich nicht, da ja pro zeile jeweils nur ein wert für die page existieren kann und das "and" daher nutzlos ist...
und schon merken wir, dass die Sprachlogik der gesprochenen Sprache "und" anders versteht, als die Sprachlogik einer Computersprache. Versuche es im Deutschen einmal mit "entweder"...
zwar könnte man im ersten Schritt OR verwenden, aber der IN-Operator ist hier deutlich besser geeignet. Nur reicht dies noch nicht ganz aus, wie Du meinem Beitrag entnehmen kannst.
Nehmen wir folgendes Beispiel
table_mapping:
mapping_id | page_id | visitor_id | created
----------------------------------------------
1 | 1 | 2 | egal
2 | 1 | 2 | auch
3 | 2 | 111 | erst
4 | 2 | 10 | recht
5 | 2 | 2 | egal
6 | 3 | 10 | auch
7 | 1 | 111 | erst
8 | 4 | 88 | recht
9 | 3 | 2 | egal
10 | 2 | 10 | auch
Durch scharfes Draufschauen sehen wir, dass die Benutzer mit der visitor_id 2 und 111 die Bedingung erfüllen, sowohl Seite 1 als auch Seite 2 angeschaut zu haben.
Die gewünschte Ergebismenge ist also
visitor_id
----------
2
111
Gehen wir nun so vor, wie ich vorgeschlagen habe (und was in Deinem Ansatz ebenfalls enthalten ist):
SELECT
page_id,
visitor_id
FROM
table_mapping
WHERE
page_id IN (1, 2)
liefert:
page_id | visitor_id |
----------------------
1 | 2 |
1 | 2 |
2 | 111 |
2 | 10 |
2 | 2 |
1 | 111 |
2 | 10 |
Du siehst, dass man noch nicht einfach zählen kann, weil der Benutzer 2 sich Seite 1 und der Benutzer 10 die Seite 2 zweimal angeschaut haben. Wir dürfen daher nur die *unterschiedlichen* Werte zählen und nutzen dazu die Aggregatsfunktion COUNT(DISTINCT), die zusammen mit einer Gruppierung mit der GROUP-BY-Klausel verwendet wird:
SELECT
visitor_id,
COUNT(DISTINCT page_id) Anzahl
FROM
table_mapping
WHERE
page_id IN (1, 2)
GROUP BY
visitor_id
[/code]
was uns
visitor_id | Anzahl
--------------------
2 | 2
10 | 1
111 | 2
zurückliefert.
Nur diejenigen, die in der Spalte Anzahl eine 2 stehen haben, können beide Seiten (1 und 2) besucht haben. Deswegen können wir diese Ergebnismenge auf die Datensätze einschränken, bei denen Anzahl den Wert 2 aufweist. Anzahl ist das Ergebnis einer Aggregatsfunktion und eine Einschränkung kann deswegen erst nach Ermittlung aller Datensätze mit der HAVING-Klausel erfolgen - und nicht mit der WHERE-Klausel:
SELECT
visitor_id,
COUNT(DISTINCT visitor_id) Anzahl
FROM
table_mapping
WHERE
page_id IN (1, 2)
GROUP BY
a.visitor_id
HAVING
Anzahl = 2 -- in Standard-SQL darf man hier keinen Alias
-- benutzen, [link:http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html@title=MySQL lässt es zu].
und bekommen das oben geforderte Ergebnis:
visitor_id | Anzahl
--------------------
2 | 2
111 | 2
bzw. wenn wir die Anzahl nicht nutzen müssen (weil wir sie sowieso kennen):
SELECT
visitor_id
FROM
table_mapping
WHERE
page_id IN (1, 2)
GROUP BY
a.visitor_id
HAVING
COUNT(DISTINCT visitor_id) = 2
mit der Ergebnistabelle:
visitor_id
-----------
2
111
Anmerkung:
MySQL sortiert wegen der GROUP-BY-Klausel implizit nach der Spalte visitor_id.
Dies ist *ein* möglicher Weg zum gewünschten Ziel und man erkennt leicht, wie man das Statement für weitere besuchte Seiten erweitern kann: Aufnahme in die Liste der Werte für IN und Erhöhen der notwendigen Anzahl bei HAVING.
Vermutlich hat Cheops eine vergleichbare Lösung in unserem Archiv gefunden, denn dieses Problem wurde hier in der Tat schon mehrfach behandelt. Schade, dass er den Beitrag, der ihm geholfen hat, nicht verlinkt hat.
Freundliche Grüße
Vinzenz
Hallo,
um diese Zeit sollte ich keine Beiträge mehr schreiben. Der Weg ist in Ordnung, die Statements sind es nicht, einer Optimierung und C&P sei Dank.
Nehmen wir folgendes Beispiel
table_mapping:
mapping_id | page_id | visitor_id | created
1 | 1 | 2 | egal
2 | 1 | 2 | auch
3 | 2 | 111 | erst
4 | 2 | 10 | recht
5 | 2 | 2 | egal
6 | 3 | 10 | auch
7 | 1 | 111 | erst
8 | 4 | 88 | recht
9 | 3 | 2 | egal
10 | 2 | 10 | auch
[...] richtiges gestrichen.
Nur diejenigen, die in der Spalte Anzahl eine 2 stehen haben, können beide Seiten (1 und 2) besucht haben. Deswegen können wir diese Ergebnismenge auf die Datensätze einschränken, bei denen Anzahl den Wert 2 aufweist. Anzahl ist das Ergebnis einer Aggregatsfunktion und eine Einschränkung kann deswegen erst nach Ermittlung aller Datensätze mit der HAVING-Klausel erfolgen - und nicht mit der WHERE-Klausel:
SELECT
visitor_id,
COUNT(DISTINCT page_id) Anzahl -- wir zählen unterschiedliche page_ids
-- nicht visitor_ids
FROM
table_mapping
WHERE
page_id IN (1, 2)
GROUP BY
visitor_id -- Tabellenalias entfernt
-- vorher war's komplizierter
HAVING
Anzahl = 2 -- in Standard-SQL darf man hier keinen Alias
-- benutzen, [link:http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html@title=MySQL lässt es zu].
und bekommen das oben geforderte Ergebnis:
visitor_id | Anzahl
2 | 2
111 | 2bzw. wenn wir die Anzahl nicht nutzen müssen (weil wir sie sowieso kennen):
SELECT
visitor_id
FROM
table_mapping
WHERE
page_id IN (1, 2)
GROUP BY
visitor_id -- Tabellenalias entfernt
HAVING
COUNT(DISTINCT page_id) = 2 -- richtige Spalte gezählt
mit der Ergebnistabelle:
visitor_id
2
111
Code (copy & paste)-getestet mit MySQL 5.1.41 und MS SQL-Server 2005. Letzterer meckert (korrekt) erstes Statement mit dem Spaltenalias an, so wie es in meinem Kommentar steht ...
Freundliche Grüße
Vinzenz
Hallo,
table_mapping:
mapping_id | page_id | visitor_id | created
und nun möchte ich folgendes wissen:
2) wie viele user haben pageID #1 UND pageID #2 aufgerufen?
wähle im ersten Schritt alle *unterschiedlichen* Datensätze aus, die page_id und die visitor_id enthalten, wobei Du die Ergebnismenge zusätzlich auf die gewünschten page_ids einschränkst (sprich die Spalten mapping_id und created interessieren nicht). Zähle nun die Anzahl der Datensätze je visitor_id und betrachte nur die Datensätze, bei denen die Anzahl genau 2 ist.
Für 3, 4, und mehr unterschiedliche Seiten gehst Du analog vor.
Freundliche Grüße
Vinzenz