etwas wie GROUP BY mit LIKE oder REGEX?

[alaninalanin]

Hallo,

ich habe eine Frage. Ich logge seit einiger Zeit auf meiner Seite die HTTP Refers mit um zu wissen, von welchen Seiten die Besucher kommen.

Nun stehen die Daten allerdings mit dem kompletten Querystring in der Datenbank also etwa so:

http://www.domain.tdl/dateiname.php?value=wasweisssich

Nun möchte ich eine Statistik aus diesen Daten machen, die mir sagt wie viele Besucher von welcher Domain kam (bzw. subdomains). Ich würde da gern (wenn es denn möglich ist) einen MySQL Query machen.

Ich dachte da an etwas wie SELECT COUNT(*) AS zahl FROM logs GROUP BY tolle_funktion(Refer)

Diese tolle_funktion würde dann den Vergleich machen, oder die ersten n Zeichen zum Vergleich nehmen.

Vielen Dank schonmal in Voraus
Jörg

 

[lmarkus31]

Hallo,

das kannst du z.B. folgendermassen realisieren:

damit bekommst du die Liste der Domains inklusive Toplevel-Domain

select substring_index(referer,'.',-2) as refdomain, 
       COUNT(*) accesses 
  from referer
 group by 1 
 order by count(*) desc;

Wenn du statt -2 -1 einsetzt, bekommst du die Liste der Referrer gruppiert nach Toplevel-Domains.
Wenn du stattdessen -3 einsetzt, bekommst du die Subdomain bzw. Hostnamen usw.

Hoffe es hilft,
Markus

 

[lmarkus31]

Hallo nochmal,

sorry, hab erst jetzt gelesen, dass du ja den kompletten Referrer in der Datenbank speicherst.

Für http-URLs kannst du z.B. so arbeiten:
(Die Tabelle hab ich "logs" genannt, die Spalte mit dem Referer "referer".)

select SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(LEADING 'http://' FROM referer),'/',1),'.',-2) AS refdomain,
       count(*) AS accesses
  from logs
 group by 1
 order by count(*) desc

Lässt sich natürlich noch weiter anpassen,
Markus

 

[alaninalanin]

Wow, vielen Dank dafür - funktioniert super!