SQL Query Count Rows für Nutzungsverhalten nach Monat

[cppnap]

Hey Leute,

ich möchte das User-Verhalten der Dashboard-Seite in unserem System ermitteln über die Monate hinweg.

Bei dem Dashboard möchte ich sehen wie viele User haben das Dashboard x-mal geöffnet, also zwischen 1-10 mal usw, verteilt über die letzten Monate hinweg.

Jetzt habe ich mit SQL eine Tabelle erstellt, die mir für jeden User-Account anzeigt wie oft hat der User über die Monate hinweg
die Dashboards aufgerufen.

Was ich jetzt noch benötige - und ich habe keine Ahnung wie ich das mit SQL anstelle ist - wie fasse ich diese Anzahl der Aufrufe über die User-Accounts zusammen, so dass die oben gezeigte Tabelle sichtbar wird.

Mein aktueller Query lautet wie folgt:

SELECT
        MAccount_Id,
        SUM(IF(Month = 11, COUNT, "")) AS 'November',
        SUM(IF(Month = 12, COUNT, "")) AS 'December',
        SUM(IF(Month = 1,  COUNT, "")) AS 'January',
        SUM(IF(Month = 2,  COUNT, "")) AS 'Feburary',
        SUM(IF(Month = 3,  COUNT, "")) AS 'March',
        SUM(COUNT) AS Total
FROM (
        SELECT     MAccount_Id,
                MONTH(CreationTime) AS Month, COUNT(*) as COUNT
        FROM MTrackingEvent
        GROUP BY MAccount_Id, month
) AS UserTracking GROUP BY MAccount_Id

Über jegliche Hilfe & Ansätze wie ich diese Tabelle mit SQL erstelle wäre ich sehr dankbar

 

[umask007]

SELECT  Grouping,   
        SUM(IF(Month = 11, 1, 0)) AS 'November',
        SUM(IF(Month = 12, 1, 0)) AS 'December',
        SUM(IF(Month = 1,  1, 0)) AS 'January',
        SUM(IF(Month = 2,  1, 0)) AS 'Feburary',
        SUM(IF(Month = 3,  1, 0)) AS 'March'      
FROM (
        SELECT     MAccount_Id,         
                MONTH(CreationTime) AS Month,  LEAST(((COUNT(*)+9) / 10),8)  as Grouping
        FROM MTrackingEvent
        GROUP BY MAccount_Id, month
) AS UserTracking GROUP BY Grouping

habs jetzt nicht getestet, aber als Ansatz sollte obiges SQL tauglich sein.
Frage wäre viel einfacher zu beantworten, wenn du ein funktionierendes SQLFiddle
(natürlich mit anonymisierten Daten) zur Verfügung stellen könntest.
Außerdem scheint in deinem SQL das Jahr im Gegensatz zur Tabelle noch nicht berücksichtigt, was dazu führen
könnte, das die Query unerwartete Daten zurückgibt.

 

[abcddcba]

obiges SQL aggregiert aber nicht ueber alle User und erstellt dann Bereiche "von ... bis ..."

SQL Pivot kann helfen, je nach DBS.

Ob nun alles in SQL immer passieren muss, oder man am Ende nicht die Aufbereitung der Daten dann beim Client laesst, das sein mal dahingestellt

 

[RalphS]

Das sieht doch mal richtig interessant aus.

Darf davon ausgegangen werden, daß immer die letzten n Monate berücksichtigt werden sollen? Oder ist das Fenster statisch?

Man könnte da mit Windowfunktionen ran. Oder ein bißchen clever mit GROUP BY und Pagination.


Ziel muß an der Stelle zunächst sein, eine aufsteigende Folge für die Monate zu haben, unabhängig vom realen "Überlauf" nach der 12. Das kann man über Monat in Verbindung mit dem zugehörigen Jahr abfackeln, zB als simple Konkatenation, dann numerisch interpretiert, sodaß im Ergebnis dann 201912 < 202001 ist. (*) Dann kann man nach dieser berechneten Spalte sortieren und LIMIT 5 sagen und hat das Richtige in bezug auf die letzten fünf Monate.

(*) Statt wild herumzucasten reicht an der Stelle natürlich JAHR * 100 + Monat.

Die Aufrufe kannst Du klassifizieren. Üblich ist zwar immer nur GROUP BY column(s), aber was da real passiert ist GROUP BY expression. Es ist problemlos möglich, GROUP BY numeric_column mod 10 da reinzuwerfen. Dann werden die Werte in numeric_column eben in zehn Klassen klassifiziert. Oder mit Division, dann gibt es Zehnerschritte (ganzzahlige Division verwenden oder FLOOR/CEIL gebrauchen).

Das Ganze 90° rotiert gibt PIVOT. Da muß man aber glaub ich ein gewisses Faible für haben.Ja, Pivot ist ne feine Sache.. aber man muß hinterher durchsteigen, was da passiert. Vor allem kann es Dir mit PIVOT passieren, daß das DBMS auf fixe, vorher bekannte Spaltenbreite besteht. Dann mußt Du aber zur Laufzeit die Anzahl der klassifizierenden Spalten kennen und Monat/Jahr ist aber nach oben offen. Was dazu führt, daß Du die Abfrage erst zur Laufzeit erstellen kannst -- und hierbei ist dann Vorsicht geboten, daß keine ungeprüften Daten in der Abfrage landen (können).

Wenn Du Dich aber auf MONATE beschränken kannst, sodaß zB links die Jahre stehen und rechts die Spalten für die zwölf Monate... dann würde ich vorsichtig behaupten wollen müßte sich das Ganze mit einer einzelnen Query abhandeln lassen können.

Aber wie gesagt. Ob Du diese vollständige Query dann nach ein paar Monaten noch verstehst, das steht wieder auf einem ganz anderen Blatt.

 

[parats]

Klassifikation der user nach Aufruf und Datum. Danach Count distinct auf user gruppiert nach Klassifikation und Datum.