MySQL] Matrix aus Tabelle erzeugen

[Moritz123]

Hallo miteinander,

ich muss für unser Usertracking eine Reporting-CSV erstellen, in ähnlich einer Matrix aufgebaut sind:

		Event_ID0	Event_ID1	Event_IDn

Channel_1	124		12		n
Channel_2	45		145		n
Channel_n	87		17		n

Meine Tabelle für das Tracking sieht (vereinfacht) so aus

id | channel_id | event_id

wobei die Zahlen aus der Matrix oben jeweils die Summe der Einträge in dieser Tabelle mit der Kombination an der entsprechenden Stelle der Matrix darstellen.
Bsp: Für die Stelle Event_ID0xChannel_1 gibt es 124 Einträge in der Datenbank.

Meine Frage ist nun, wie ich diese Matrix möglichst effizient erzeugen kann. Die erste Idee war für jeden Channel eine Abfrage mit COUNT und GROUP BY event_id zu machen, wobei ich dafür für n Channels n Abfragen machen muss, was sicher nicht mehr so effizient ist.

Daher würde ich mich sehr freuen, wenn Ihr mir da mal ein bisschen Input geben könntet.

Vielen Dank!

 

[Yaslaw]

Mit MySQL selber geht es nur statisch (also für jeden Event einem Eintrag im SELECT-Teil). Mit neuen Events musst du das SQL anpassen.

Greiffst du mit PHP auf die Daten? Mit PHP kannst du das SQL flexibel zusammensetzen und so eune Kreuztabelle erstellen. Hier mal ein Beispiel

<?php 

    mysql_connect("localhost", "root", "");
    mysql_select_db("test");

    $eventIds = array();

    //Auslesen aller event_ids um aus ihnen neue Felder zu erstellen
    
    $sql = "SELECT DISTINCT event_id FROM channels ORDER BY event_id;";
    $result = mysql_query($sql);   
    
    //Berechnen wie die Felder heissen werden und die Formel zusammensetzen 
    while($row = mysql_fetch_object($result)){
        //erstellt in etwa den SELECT-Ausdruk:
        //COUNT(IF(event_id = 15, 1, NULL)) as event15
        $eventIds[] = "COUNT(IF(event_id = {$row->event_id}, 1, NULL)) AS event{$row->event_id}";
    }
    mysql_free_result($result);

    //Alle Felder mit , aneinander hängen
    $sql = implode(", ", $eventIds);
    //Das neue SELECT zusammenstellen
    $sql = "SELECT                
                channel_id,
                {$sql}
            FROM
                channels
            GROUP BY 
                channel_id;";
             
    $result = mysql_query($sql);
 
    //Resultat ausegben
    
    //zuerst die Feldbezeichnungen
    $fields = array();
    while($i < mysql_num_fields($result)){
        $fields[] = mysql_fetch_field($result, $i)->name;   
        $i++;
    } 
    echo implode(" | ", $fields)."<br />";
    
    // dann die Zeilen
    while($row = mysql_fetch_array($result, MYSQL_NUM )){        
        echo implode(" | ", $row)."<br />";    
    }
    
    mysql_free_result($result);
?>

 

[Moritz123]

Hallo,

vielen Dank für deine Antwort. Ich nutze Python, was aber ja keinen großen Unterschied macht.
Wichtig ist es mir vorallem die Anzahl der Anfragen an die Datenbank relativ gering zu halten, da die Tabelle im Mittel zw. 2 und 4 Millionen Einträge hat.

Ich werd das mal so versuchen und mit meiner Lösung performancemäßig vergleichen.

Vielen Dank nochmal!

 

[Moritz123]

Hallo nochmals,

ich habe deine Lösung mal ausgiebig durchgestest und sie funktioniert einwandfrei.

Ich habe allerdings jetzt noch eine weitere Anforderung dazu bekommen, die ich hier auch gerne mal zu Diskussion stellen würde:
Meine Usertracking-Tabelle enthält ebenfalls eine Spalte, die die User-ID des auslösenden Benutzers enthält, welche aber wegen der Verfügbarkeit nur bei einigen Events gefüllt wird. Was ich nun möchte ist die Matrix um einige Spalten zu erweitern, die die Anzahl der User enthält die das Event ausgelöst haben.
Zum Beispiel: Im Channel_01 haben XX User das Event event_1 ausgelöst.
Mein Ansatz ist nun, für jeden Channel folgende Abfrage zu machen:

SELECT COUNT(DISTINCT user_id) FROM tracking where channel_id = $channel_id AND event_id = $event_id

was allerdings bedeuten würde, dass immer #channels x #events Abfragen machen müsste, was ich nicht so toll finde.
Gibt es da vielleicht was ähnliches zur Lösung oben, dass die Anzahl der Abfragen auf ein erträgliches Maß reduziert?

Besten Dank und schöne Grüße,

Moritz

 

[Yaslaw]

Falls die leere user_id NULL ist, sollte das folgende funktionieren

$eventIds[] = "COUNT(IF(event_id = {$row->event_id}, 1, NULL)) AS event{$row->event_id}";

ersetzen durch

$eventIds[] = "
COUNT(IF(event_id = {$row->event_id}, 1, NULL)) AS event{$row->event_id}, 
COUNT(IF(event_id = {$row->event_id} , {$row->user_id}, NULL)) AS user_count_{$row->event_id}
";

 

[Moritz123]

Hallo yaslaw,

vielen Dank erst mal für deine Antwort. Ich weiß nicht, ob das so genau das ist, da ich auch nicht ganz weiß wo $row->user_id herkommt. Wenn ich das richtig verstehe, gib mit das doch auch nicht die Informationen, wieviele user haben welchen event in welchem channel ausgelöst oder? Ein user kann natürlich das gleiche event mehrfach auslösen, darf aber eben nur einmal gezählt werden. Daher auch mein Ansatz mit DISTINCT.

Besten Dank nochmal!

 

[Yaslaw]

Hast recht. das $row->user_id macht wenig Sinn und der Distinct muss her. War keine gute Idee gestern eine Lösung zu presentieren wenn man auf den Zug eilt...

Also der Ort wo ich ansetzen wollte, gefällt mir immer noch. Nur das wie ist falsch

$eventIds[] = "
COUNT(IF(event_id = {$row->event_id}, 1, NULL)) AS event{$row->event_id}, 
COUNT(DISTINCT IF(event_id = {$row->event_id} , user_id, NULL)) AS usercount{$row->event_id}
";

Also, wenn es der entsprechende event ist, dann soll er den user nehmen und mit DISTINCT zählen. Das ganze ausgeben als Feld mit dem Namen usercount#

 

[Moritz123]

Hallo nochmal,

vielen Dank für deine schnelle Antwort! Das klappt diesesmal einwandfrei - komisch ich dachte ich hätte das gestern auch schon versucht, jedoch ohne Erfolg.
Vielen vielen Dank nochmals!