mysql Ausgabe Spaltenweise limit 3 für alle Einträge mit verschiedenen Kundennummern

  • PHP

Es gibt 24 Antworten in diesem Thema. Der letzte Beitrag () ist von samson.

    mysql Ausgabe Spaltenweise limit 3 für alle Einträge mit verschiedenen Kundennummern

    Moin Moin zusammen,

    ich versuche mich an einer "Statistischen Auswertung" meine SQL Daten... Ich steh aber mal wieder auf dem Schlauch.
    In der Datenbank habe ich zeilen wie auf dem Bild

    Gehen wir davon aus, ich habe 40 Einträge, unterschiedliche Kundennummern und unterschiedliches Datum...
    Ich möchte eine Ausgabe der letzten 3 Einträge (anhand Datum) (soweit kein Problem) ~ORDER BY datum DESC LIMIT 3~
    aber von jedem Kunden... und dann soll das ganze noch "Spaltenweise" ausgegeben werden...

    Sprich:


    Ich habs nun mit verschiedenen wegen versucht, scheitere aber an der Suche bzw. Ausgabe.

    Hat von euch noch jemand eine Idee?
    Nein! Doch! OHH!
    Probiere mal:

    SQL-Abfrage

    1. SELECT a.kdnr, a.datum
    2. FROM foo a
    3. WHERE (SELECT COUNT(*) FROM foo b WHERE a.kdnr = b.kdnr AND b.datum > a.datum) < 3
    4. ORDER BY a.kdnr, a.datum DESC


    Falls datum in Abhängigkeit von kdnr nicht einmalig ist, können mehr als drei Ergebnisse pro Kundennummer selektiert werden können. Das lässt sich im Anschluss (in PHP) aber leicht filtern. Die spaltenweise Formatierung sollte mit PHP kein Problem sein, oder?
    Empfehlenswert wäre bei größeren Datenmengen ein Index auf kdnr und datum.
    Das ginge mit Pivot.

    Hier ein Beispiel, ich weiß nicht wie deine Tabellen und Spalten genau heißen, aber du solltest es anpassen können:

    SQL-Abfrage

    1. Select Id, [1] as Besuch1, [2] as Besuch2, [3] as Besuch3 from
    2. (select Id, date, rn from (select Id, date, rn = row_number() over (Partition by Id order by date DESC) from Customer) C where rn < 4) C
    3. pivot (max(date) for rn in ([1], [2], [3])) AS P


    Der Trick ist, das man die Top 3 über row_number lößt und auch darüber den Pivotaggregation bildet.
    Das MAX(date) ist hierbei eigentlich nur da, weil Pivot eben eine Aggregatsfunktion erfordert
    LG
    Das ist meine Signatur und sie wird wunderbar sein!
    @Mono Danke für den Tipp, hab es getestet,... geht leider nicht. meine Tabelle heißt 'ms_hotline' :)

    SQL-Abfrage

    1. SELECT id, [1] as Besuch1, [2] as Besuch2, [3] as Besuch3 FROM ms_hotline (SELECT * FROM ms_hotline (SELECT id, datum, rn = row_number() over (PARTITION BY id ORDER BY datum DESC) FROM ms_hotline) C WHERE rn < 4) C pivot (max(date) for rn IN ([1], [2], [3])) AS P


    bekomme das als Fehlermeldung:

    #1064
    - You have an error in your SQL syntax; check the manual that
    corresponds to your MySQL server version for the right syntax to use
    near '[1] as Besuch1, [2] as Besuch2, [3] as Besuch3 FROM ms_hotline
    (SELECT * FROM ms' at line 1

    Bin ich blind? Ich hab nirgends "ms" als alleiniges Statement angegeben?!
    Nein! Doch! OHH!
    Du hast es auch falsch eingefügt..

    Du hast:

    SQL-Abfrage

    1. SELECT id, [1] as Besuch1, [2] as Besuch2, [3] as Besuch3 FROM ms_hotline (SELECT *


    Ich schrieb:

    SQL-Abfrage

    1. Select Id, [1] as Besuch1, [2] as Besuch2, [3] as Besuch3 from
    2. (select
    Das ist meine Signatur und sie wird wunderbar sein!
    @Mono
    hab es nochmal geändert..

    SQL-Abfrage

    1. SELECT id, [1] as Besuch1, [2] as Besuch2, [3] as Besuch3 FROM (SELECT * FROM (SELECT id, datum, rn = row_number() over (PARTITION BY id ORDER BY datum DESC) FROM ms_hotline) C WHERE rn < 4) C pivot (max(date) for rn IN ([1], [2], [3])) AS P


    geht trotzdem nicht... Hab auch nochmal nebenher gegoogelt, ohne Erfolg, ich find kein vergleichbares Beispiel...
    Nein! Doch! OHH!
    Probier es mal so:

    SQL-Abfrage

    1. SET @rn = 0;
    2. SET @id = 0;
    3. SELECT Id,
    4. MAX(IF(rn=1,date,NULL)) AS besuch1,
    5. MAX(IF(rn=2,date,NULL)) AS besuch2,
    6. MAX(IF(rn=3,date,NULL)) AS besuch3
    7. FROM
    8. (select @rn:=CASE
    9. WHEN @id = Id THEN @rn + 1
    10. ELSE 1
    11. END AS rn,
    12. @id:=Id as Id,
    13. date from Customer Order by Id asc, date desc) C
    14. GROUP BY Id


    Wenn du damit auch nicht hinkommst schau ich mir das später vll noch mal an.
    Das ist meine Signatur und sie wird wunderbar sein!
    Bezug auf Post #4
    Kannst du "das Script hängt sich dann auf, direkt in phpMyAdmin legt es den kompletten Server lahm" mal etwas näher definieren?
    (Kommt es zu einem Tiemout? Wie viel Datensätze hast du in der Tabelle? Was sagt das Log? Was passiert, wenn du es in der Konsole ausführst? etc.)

    Mit MySQL 5.6 (Konsole) läuft das bei mir problemlos und bei SQLFiddle bekomme ich auch das gewünschte Resultat: sqlfiddle.com/#!9/5f864/1
    Das gwünschte Resultat ist aber, dass die Spalten Besuch1, Besuch2, Besuch3 sind gruppiert über die USerID. Das ist normalerweise eine Pivot Operation (Zeilen nach Spalten drehen)
    Das ist meine Signatur und sie wird wunderbar sein!
    @Mono
    Mit "gewünschte Resultat" bezog ich mich mehr auf Post #2, also dass die Abfrage wie beschrieben (unabhängig von der Darstellung) zunächst die korrekten Daten liefern sollte.
    Auch wenn ich gerne viel von der DB erledigen lasse, so würde ich die "Zeilen nach Spalten drehen"-Operation persönlich in PHP programmieren. So bliebe die SQL-Abfrage etwas einfacher und unabhängiger vom DBMS. Hat natürlich auch wieder Nachteile und ist letztlich ja dem TE überlassen.
    @Mono
    Danke für den Schnipsel

    SQL-Abfrage

    1. SELECT id,
    2. MAX(IF(rn=1,datum,NULL)) AS besuch1,
    3. MAX(IF(rn=2,datum,NULL)) AS besuch2,
    4. MAX(IF(rn=3,datum,NULL)) AS besuch3
    5. FROM
    6. (select @rn:=CASE
    7. WHEN @id = id THEN @rn + 1
    8. ELSE 1
    9. END AS rn,
    10. @id:=id as id,
    11. datum from ms_hotline Order by id asc, datum desc) C
    12. GROUP BY id

    so geht es,... jedoch listet er mir in phpMyAdmin alle 17.000 Datensätze auf,...

    Habe es nun so eingegrenzt das er mir nur die sucht die in einer bestimmten Spalte (verhotsys) den Wert 7 haben, klappt auch wunderbar,...
    Laut phpMyAdmin, Abfrage dauert ~ 0,0210 Sekunden

    was mir jetzt noch fehlt um glücklich zu werden ist der Wert kdnr aus der Tabelle, aber ich bekomm es ums verrecken nicht hin, das er mir die kdnr noch aus der Spalte mitnimmt.

    Hast du da ggf. noch nenn tipp für mich?

    Danke schon mal :)

    ok, funktioniert doch nicht... hab nun 2 weitere Datensätze bei dem gleichen Kunden reingesetzt... ohne Erfolg. Er listet mir alles einzeln auf.
    Nein! Doch! OHH!
    @Mono
    Habs nun so...

    SQL-Abfrage

    1. SELECT id, kdnr,
    2. MAX(IF(rn=1,datum,NULL)) AS besuch1,
    3. MAX(IF(rn=2,datum,NULL)) AS besuch2,
    4. MAX(IF(rn=3,datum,NULL)) AS besuch3
    5. FROM
    6. (select @rn:=CASE
    7. WHEN @id = kdnr THEN @rn + 1
    8. ELSE 1
    9. END AS rn,
    10. @id:=kdnr as kdnr,
    11. datum, id from ms_hotline WHERE verhotsys='7' Order by datum desc) C
    12. GROUP BY kdnr


    Ausgabe sieht so aus...


    Aber er Gruppiert es mir nicht richtig...
    ich habe 1 kdnr und 4 Einträge, er bringt mir warum auch immer nur Besuch1 als Wert raus...
    Was mach ich falsch?
    Nein! Doch! OHH!
    Also erstens.. du gruppierst ja über kdnr, da kannst normal id nicht mehr im select verwenden. Also in mysql kannst es, aber es gibt dir nicht wirklich einen sinnvollen wert.
    Zweitens ist das Problem das du das innere Select offenbar nicht so ganz verstehst.
    Es wird quasi eine Rank Over Partition simuliert. Damit wird über ein Spalte (kdnr) hochgezählt, dass funktioniert aber nur richtig, wenn die Daten in der richtigen Order sind (also alle Kdnr auch in korrekter Reihenfolge). Daher muss ins Order By zuerst die Kdnr rein
    Das ist meine Signatur und sie wird wunderbar sein!
    @Mono
    ok, soweit komm ich hinterher...
    Ich hab es nun auch schon soweit das er mir die Daten ausgibt,... in phpMyAdmin funktioniert das mit dem SET @rn soweit super Daten sind korrekt, jedoch bekomm ich es nicht in meinen php Sql query rein,... Da bricht er mir ab... Wie muss ich das setzen um das er den Set Wert mitnimmt?

    mach ich es so nimmt er mir nur den 1. Datumswert mit (obwohl 3 Vorhanden)

    PHP-Quellcode

    1. $sql = "SELECT id, kdnr, MAX(IF(rn=1,datum,NULL)) AS besuch1, MAX(IF(rn=2,datum,NULL)) AS besuch2, MAX(IF(rn=3,datum,NULL)) AS besuch3 FROM (select @rn:=CASE WHEN @id = kdnr THEN @rn + 1 ELSE 1 END AS rn, @id:=kdnr as kdnr, datum, id from ms_hotline WHERE verhotsys='7' Order by kdnr asc, datum desc) C GROUP BY kdnr";


    versuch ich es so, bricht er mir ab

    PHP-Quellcode

    1. $sql = "SET @rn = 0; SET @id = 0; SELECT id, kdnr, MAX(IF(rn=1,datum,NULL)) AS besuch1, MAX(IF(rn=2,datum,NULL)) AS besuch2, MAX(IF(rn=3,datum,NULL)) AS besuch3 FROM (select @rn:=CASE WHEN @id = kdnr THEN @rn + 1 ELSE 1 END AS rn, @id:=kdnr as kdnr, datum, id from ms_hotline WHERE verhotsys='7' Order by kdnr asc, datum desc) C GROUP BY kdnr";


    Lasse ich das SET @rn weg, bringt er mir in phpMyAdmin ebenfalls ein falsches ergebnis. (Nur ein Datum) aber ich denke, ich muss dies doch übergeben, sonst bringt mir der Befehl ja nichts?!

    Fehlermeldung aus PHP:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @id = 0; SELECT id, kdnr, MAX(IF(rn=1,datum,NULL)) AS besuch1, MAX(IF(rn=2,d' at line 1
    Nein! Doch! OHH!
    Das sind 3 Queries quasi. Ich vermute du verwendest das alte MySQL_query,das unterstütz nur ein query pro "query." Also mach einfach erst
    SET @rn = 0;
    query absenden
    SET @id = 0;
    query absenden
    und danach das eigentlich select

    ODER vewende MySQLI und mysqli_multi_query()


    Das ist meine Signatur und sie wird wunderbar sein!