✔ [MySQL] GROUP BY mit Wildcards

[Tobias Menzel]

Hallo,

ich versuche mich im Moment an einer kleinen Zugriffsstatistik (bzw. an der Ausgabe selbiger). Ich habe eine MySQL-dB mit Spalten für den Client, IP-Adresse, Datum usw.

Um die Anzahl eines bestimmten Clienten oder die Zugriffe pro Tag zu zählen, bediene ich mich einer Gruppierung.

Meine Frage: Kann ich nach Spaltenwerten gruppieren, in denen ähnliche Werte stehen, ohne Subabfragen o.ä. zu verwenden? Beispiel: Ich habe das Datum in einem Feld des Typs DATETIME gespeichert, möchte aber nur nach dem Tag (und nicht nach der Uhrzeit) gruppieren.

(Das gleiche gilt für die Browsertypen: Gruppierung nach Spalten, in denen die Zeichenkette "MSIE" vorkommt. Natürlich könnte ich die Daten in mehrere Felder aufsplitten, aber das möchte ich wenn es geht vermeiden)

Gruß
.

 

[hpvw]

Soweit ich weiss kannst Du nach beliebigen Ausdrücken gruppieren.
Um z.B. nach dem Tag zu gruppieren kannst Du

... GROUP BY (TO_DAYS(`datumsspalte`)) ...

verwenden.

Für die Browser wirst Du Dich mit Zeichenketten-Funktionen beschäftigen müssen, um für jede zu gruppierende Gruppe aus dem CHAR einen eindeutigen (und nur innerhalb der Gruppe gleichen) CHAR zu erzeugen. Mit IF wirst vermutlich auch arbeiten müssen. Hui, hui, wir kommen wieder zu meinen Workarounds. Es geht vielleicht einfacher, aber hier ein Versuch:

... GROUP BY 
    IF(LOCATE('MSIE',`browserfeld`)>0,'MSIE',
        IF(LOCATE('Opera',`browserfeld`)>0,'OP',
            'unbekannt' # ggf. weiter verschachteln
        )
    ) ...

Im Sinne der Normalisierung (atomare Attribute) kann ich allerdings nur dazu raten, den CHAR in mehrere Felder zu splitten und ggf. sogar eine weitere Tabelle mit den Browsern anzulegen, auf die nur verwiesen wird.

Gruß hpvw

EDIT:
getestet, funktioniert:

SELECT 
count(*) AS Anzahl,  
IF(LOCATE('MSIE',`browser`)>0,'MSIE',
    IF(LOCATE('Opera',`browser`)>0,'OP',
        'unbekannt' 
    )
) AS Browser
FROM `browsers` 
GROUP BY 
    IF(LOCATE('MSIE',`browser`)>0,'MSIE',
        IF(LOCATE('Opera',`browser`)>0,'OP',
            'unbekannt' 
        )
    )

auf folgender tabelle:

-- phpMyAdmin SQL Dump
-- version 2.6.2-pl1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Erstellungszeit: 08. Juni 2005 um 16:32
-- Server Version: 4.1.10
-- PHP-Version: 5.0.3
-- 
-- Datenbank: `testDB`
-- 

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `browsers`
-- 

CREATE TABLE `browsers` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `browser` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Daten für Tabelle `browsers`
-- 

INSERT INTO `browsers` (`id`, `browser`) VALUES (1, 'bfdsbfMSIEsdag');
INSERT INTO `browsers` (`id`, `browser`) VALUES (2, 'sdfasdfhgrwqehgwerMSIEsdgasdag');
INSERT INTO `browsers` (`id`, `browser`) VALUES (3, 'sdsdOperafadhadf');
INSERT INTO `browsers` (`id`, `browser`) VALUES (4, 'dfhfsdhOperasdfhgfdd');
INSERT INTO `browsers` (`id`, `browser`) VALUES (5, 'dfgfdgOperadasgsdg');
INSERT INTO `browsers` (`id`, `browser`) VALUES (6, 'sdagasfhaasdfhhfasd');

 

[Tobias Menzel]

Danke Dir.

Mit den Browsertypen komme ich so wohl nicht großartig weiter; das würde eine ewig lange Klausel ergeben, wenn ich alle Typen testen will (ich habe mich nun auf 8-10 Typen "geeinigt" und frage die einzeln ab), aber die Aufsplittung des Datums ist sehr praktisch.

Es macht wohl auch Sinn, eine Tabelle mit relevanten Clients anzulegen und in der Haupttabelle nur entsprechende IDs zu speichern - mich hat allerdings die Thematik der Gruppierung im Moment generell interessiert.

Gruß

P.S.: Irgendwas mache ich falsch: Ich habe erstaunlich große Probleme, auf dev.mysql.com/doc/mysql/de das zu finden, was ich suche.
.

 

[hpvw]

Danke Dir.

Mit den Browsertypen komme ich so wohl nicht großartig weiter; das würde eine ewig lange Klausel ergeben, wenn ich alle Typen testen will (ich habe mich nun auf 8-10 Typen "geeinigt" und frage die einzeln ab), aber die Aufsplittung des Datums ist sehr praktisch.

Gruß

P.S.: Irgendwas mache ich falsch: Ich habe erstaunlich große Probleme, auf dev.mysql.com/doc/mysql/de das zu finden, was ich suche.
.

Bitteschön.
Das Manual von MySQL ist wirklich nicht das übersichtlichste.
Meine beiden Ausgangspunkte sind

http://dev.mysql.com/doc/mysql/de/data-manipulation.html

und

http://dev.mysql.com/doc/mysql/de/functions.html

Wenn man da erstmal hingekommen ist, hat sich die Übersichtlichkeit schon enorm erhöht.
Aber dann muss man sich doch meist durchklicken, da nicht immer ganz klar ist, wo das Gewünschte nun zu finden ist.

Ich kenne ja Deine CHARs nicht, aber wenn es ausreicht, die ersten n Zeichen (oder per Position definierte in der Mitte oder am Ende) zu nehmen, falls diese eindeutig sind, dann könnte auch LEFT, RIGHT oder SUBSTRING helfen.

Gruß hpvw

 

[Tobias Menzel]

Die beiden Ausgangsseiten sind auch meine. Das war im Prinzip das, was ich meinte: Ich muss mich durchklicken, wobei die Antwort (für mich als mit der Terminologie dieser Datenbank noch nicht so vertrauter User) nicht immer da zu finden ist, wo ich sie vermute (und die Sunfunktion auf viele Schlüsselwörter keine Referenz ausspuckt) - aber ich beginne mich mit der Seite anzufreunden.

Im Moment habe ich in dem Browserfeld den kompletten Inhalt von $_SERVER['HTTP_USER_AGENT'] stehen (zugegebenermaßen nicht das schlaueste, aber ich wollte erstmal alles speichern).

Was mich eigentlich interessiert, sind Zugriffsstatistiken über einen bestimmten Zeitraum (Monate, Wochen, Tage, Tagesverlauf - gibt schöne Diagramme) und da komme ich mit Deinem Tipp gut weiter.

Gruß

P.S.: So schaut im Moment meine Browserabfrage für DAUS aus:

$amt_client = array("Firefox"=>0, "MSIE"=>0, "Opera"=>0, "Safari"=>0, "Bot"=>0);

$sum = 0;

foreach($amt_client as $key=>$val) {
	$query = "SELECT COUNT(agent) as client FROM stats WHERE agent like '%".$key."%'";
	$result = mysql_query($query);
	$line = mysql_fetch_array($result, MYSQL_ASSOC);
	$amt_client[$key] = $line["client"];
	$sum += $amt_client[$key];
}

(ich schäm mich auch dafür und machs nie wieder ;-) )
.

 

[hpvw]

Wäre mal interessant, zu prüfen, ob meine ewige Performanceempfehlung "vermeide Querys" auch hier gilt, obwohl das Group by dann so murksig mit if gemacht wäre.
Du kannst Dir den zuerst geposteten Code auch aus einem Array zusammenbauen (dann sind Änderungen nicht so mühsam) und das natürlich auch mit Deiner Bedingung:

<?
/*Zeitmessung*/
$start=microtime(true);

$uagents=array(
    "Firefox",
    "MSIE",
    "Opera",
    "Safari",
    "Bot");

function buildTheDirrtyGroupByClause($uas) {
    $ua=array_shift($uas);
    return "IF(`agent` LIKE '%".$ua."%','$ua',\n"
        .((count($uas)>0)
            ?buildTheDirrtyGroupByClause($uas)
            :"'andere'")
        ."\n)";
}

$dgbc=buildTheDirrtyGroupByClause($uagents);

$query="SELECT COUNT(*) AS Anzahl, "
    .$dgbc
    ." AS UserAgent FROM `stats` GROUP BY (".$dgbc.") DESC";

$result=mysql_query($query);

$sum = 0;
$amt_client = array();

while ($row=mysql_fetch_assoc($result)) {
    $sum += $row['Anzahl'];
    $amt_client[$row['UserAgent']] = $row['Anzahl'];
}

/*Zeitmessung Ausgabe*/
echo (microtime(true)-$start);
?>

ich schäm mich auch dafür und machs nie wieder

Vielleicht machst Du ja mal einen kleinen Performancevergleich. Je nach dem, wie der ausgeht, kann ich meinen Kopf dann auch in den Sand stecken.

aber ich beginne mich mit der Seite anzufreunden

Da hast Du mir was voraus. Leider sind die Alternativen (die ich bisher gesehen habe) auch nicht besser. Oft erklärt übrigends die englische Variante wesentlich mehr, als die deutsche.
Einer der User hier hatte mal seine (My)SQL-Ultrakurz-Referenz (1 HTML-Seite) angehängt, die ist gut zum Nachschlagen. Musst Du mal nach suchen, ich fände es nicht gut, die jetzt als dritter noch mal anzuhängen. Vielleicht will derjenige sie ja nicht weiter veröffentlicht wissen.

Gruß hpvw

 

[Tobias Menzel]

Deine Version ist wohl schnelller:

Microtime bei meinem Script zwischen 0.00215 und 0.00445

Microtime bei Deinem Script zwischen 0.000671 und 0.000837

Für meine Zwecke macht das zwar keinen großen Unterschied, aber man sollte schon auf Performance achten (ausserdem will ich mich ja grade in dem Gebiet SQL weiterbilden).

Da hast Du mir was voraus.

ich meinte natürlich "laaaaangsam" anfreunden. (ist auch nichts neues, dass die englischen Originale oft ausführlicher sind)

Nach der Kurzreferenz werde ich mal suchen, danke.

Gruß

P.S.: So soll das dann in etwa mal aussehen: http://www.ekto.net/karma_V/stats.html

An die Datums- und Zeitgeschickten mach ich mich morgen mal.
.

 

[hpvw]

Nach der Kurzreferenz werde ich mal suchen, danke!

Sag Bescheid, wenn Du den Thread gefunden hast, ich würde mir den auch gerne noch mal bookmarken.

Ich wollte den Thread auch noch "mal eben" raussuchen. Eine halbe Stunde bin ich dabei und kein Ergebnis. Vielleicht habt ihr Mods ja noch detailliertere Suchfunktionen zur Verfügung. Oder gibt es für normalsterbliche eine Möglichkeit, alle Threads ohne Anhänge auszuschließen?

Über Google habe ich dann einen Thread von Dir gefunden, da musste ich lachen:

Außerdem hat vor ein paar Monaten einer hier Forum seine selbstgeschriebene Mysql-Ultra-Kurzreferenz (eine HTML-Seite) einem Beitrag angehängt. Die ist perfekt zum Nachschlagen, wenn man, wie ich, ein Gedächnis wie ein Sieb hat.

Da ist es wieder, mein Sieb, ich wiederhole mich.

Gruß hpvw

 

[Tobias Menzel]

Nee, ich habe sie auch noch nicht gefunden ( ).

Ich bin nur ein "Hilfsmoderator", und meine Adminrechte beschränken auf das Flashforum - das man generell nach Anhängen suchen kann, ist mir auch noch nicht aufgefallen - als Admit mit vollem Zugriff mag es da sicher andere Möglichkeiten geben.

Gruß

P.S.: Deinen Hinweis in dem anderen Thread habe ich damals tatsächlich überlesen.
.

 

[hpvw]

P.S.: So soll das dann in etwa mal aussehen: http://www.ekto.net/karma_V/stats.html

Das hier ist kein Showroom
Mal im Ernst: Das sieht ja genial aus. Vielleicht werde ich mich doch noch mal mit Flash beschäftigen.
Fragen:
Baut das Flash selbst eine HTTP-Verbindung auf, um an die Daten zu kommen oder sind die noch fest im .swf eingetragen und kommen später über die Parameter?
Gehört zu einem <object> nicht auch immer eine textuelle Alternative mit den entsprechenden Informationen (wg. Accessibilty Guidelines)?

Gruß hpvw