Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Select mit Durchschnitts Datum Werten

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Zero-G., 3 März 2014.

  1. Zero-G.

    Zero-G. Benutzer

    Hallo
    Die Überschrift ist nicht unbedingt gelungen, sagt aber worum es geht...
    Folgende Situation
    Ich habe eine Tabelle in der ich die Datums der Verkäufe speichere. Die Spalte nennt sich "BezDat" = date field.
    Die Zuordnung zum Kunden in dieser Tabelle erfolgt über das Feld "KundenId" = BigInt.

    Was ich erzielen möchte ist folgendes:
    Ich habe immer wiederkehrende Kunden in einem Durchschnittsrythmus von z.B. 6 Monaten bei mir etwas kauft.
    Was ich herausfiltern möchte ist folgendes:
    Auf Grund der letzten x Käufe einen Durchschnitt herausfiltern (also z.B. die 6 Monate) & schauen, ob das heutige Datum noch innerhalb dieses Durchschnitts ist, oder schon darüber.
    Kann mir dabei jemand helfen?

    Bsp. Daten:
    KundenID BezDat
    1 01.01.2013
    1 01.06.2013
    1 31.12.2013
    2 01.02.2013
    2 01.08.2013

    Bei der Abfrage müsste jetzt theoretisch der Kunde mit der Id 2 gefiltert werden, weil der Schnitt wäre 6 Monate & somit hätte der Kunde im Februar wieder etwas kaufen sollen.

    Danke für eure Mühen. - Um ehrlich zu sein, habe ich gar keine Ansatzidee...
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Also erst einmal a bissl Statistik und den Durchschnitt berechnen, wie oft Kunde was kauft. Dazu den Abstand zum je letzten Kaufdatum berechnen, dazue dient lag():

    Code:
    test=*# select * from zero ;
    kunde |  d
    -------+------------
      1 | 2013-01-01
      1 | 2013-06-01
      1 | 2013-12-31
      2 | 2013-02-01
      2 | 2013-08-01
      3 | 2013-01-01
      3 | 2013-02-01
      3 | 2013-03-01
    (8 rows)
    
    Time: 0,194 ms
    test=*# select *, d-lag(d) over (partition by kunde order by d) as abstand from zero ; kunde |  d  | abstand
    -------+------------+---------
      1 | 2013-01-01 |
      1 | 2013-06-01 |  151
      1 | 2013-12-31 |  213
      2 | 2013-02-01 |
      2 | 2013-08-01 |  181
      3 | 2013-01-01 |
      3 | 2013-02-01 |  31
      3 | 2013-03-01 |  28
    (8 rows)
    
    Daraus nun den Durchschnitt mit der allseits beliebten avg() - Funktion:

    Code:
    test=*# select kunde, avg(abstand) from (select *, d-lag(d) over (partition by kunde order by d) as abstand from zero) foo group by kunde;
    kunde |  avg
    -------+----------------------
      1 | 182.0000000000000000
      2 | 181.0000000000000000
      3 |  29.5000000000000000
    
    Daraus machen wir einen View:

    Code:
    test=*# create view kunde_kauft_aller_tage as select kunde, avg(abstand) from (select *, d-lag(d) over (partition by kunde order by d) as abstand from zero) foo group by kunde;
    CREATE VIEW
    
    Nun noch a bissl rumjoinen und schon hast eine Übersicht, bis wann der jeweilige Kunde wieder Dir Geld in die Kasse spülen sollte:

    Code:
    test=*# select x.kunde, (x.max + y.avg * '1day'::interval)::date from (select kunde, max(d) from zero group by kunde) x left join kunde_kauft_aller_tage y on x.kunde=y.kunde;
    kunde |  date
    -------+------------
      1 | 2014-07-01
      2 | 2014-01-29
      3 | 2013-03-30
    (3 rows)
    
    So in etwa.... ginge sicher noch anzuhübschen.

    Mit Deinem MySQL wirst aber das so nicht 1:1 übernehmen können - zu den vielen Dingen, die MySQL nicht kann, zählen analytische Funktionen wie lag().
     
    Hony% gefällt das.
  3. Zero-G.

    Zero-G. Benutzer

    Hallo!
    Danke für Deine Hilfe! Hört sich soweit sehr nachvollziehbar an.
    Habe jetzt versucht die lag() nachzubauen.
    Heraus gekommen wäre folgender Code:
    Code:
    SELECT
      KundenId,
      AVG(myInterval)
    FROM (SELECT
        KundenId,
        IF(@KId = KundenId,
        DATEDIFF(BezDat, @bd) + LEAST(0, @bd := BezDat),
        NULL + LEAST(0, @KId := KundenId) + (@bd := NULL)) myInterval
      FROM Auftrag,
           (SELECT
               (@KId := 0)) AS a
      ORDER BY KundenId, NULL) AS b
    GROUP BY KundenId
    Leider bringt mir das aber nur die KundenID + NULL....

    DANKE
     
  4. Hony%

    Hony% Datenbank-Guru

    Eine recht elegante Lösung hast du ja schon von @akretschmer bekommen. Das funktioniert so aber leider nur mit einer richtigen Datenbank.

    Schön ist anders. Dafür funktioniert diese Lösung sogar mit MySQL.

    Hier kommt die Maus der Wurm:
    Code:
    SELECT M.`KundenID` , `last` , DATE_ADD( `last` , INTERVAL `avg` DAY ) AS `next` ,
    CASE WHEN DATE_ADD( `last` , INTERVAL `avg` DAY ) < CURRENT_DATE THEN 'true' ELSE 'false' END AS overdue
    FROM (
        SELECT `KundenID` , MAX( `BezDat` ) AS `last`
        FROM `zero`
        GROUP BY `KundenID`
    )M
    INNER JOIN (
        SELECT `KundenID` , AVG( Date_Diff ) AS `avg`
        FROM (
            SELECT A.`KundenID` AS `KundenID` , DATEDIFF( B.`BezDat` , A.`BezDat` ) AS Date_Diff
            FROM `zero` AS A
            INNER JOIN `zero` AS B
            ON A.`KundenId` = B.`KundenId`
            AND B.`BezDat` = (
                SELECT `BezDat`
                FROM `zero`
                WHERE `KundenID` = A.`KundenID`
                AND `BezDat` > A.`BezDat`
                ORDER BY `BezDat` ASC
                LIMIT 1
            )
        ) AS T
        GROUP BY `KundenID`
    ) AS AT
    ON M.`KundenID` = AT.`KundenID`; 
    Code:
    +----------+------------+------------+---------+
    | KundenID | last       | next       | overdue |
    +----------+------------+------------+---------+
    |        1 | 2013-12-31 | 2014-07-01 | false   |
    |        2 | 2013-08-01 | 2014-01-29 | true    |
    +----------+------------+------------+---------+
    Gruß
    Hony
     
    akretschmer gefällt das.
  5. Hony%

    Hony% Datenbank-Guru

    Auf den äußeren Join kann man sogar verzichte:
    Code:
    SELECT `KundenID` , `last` , DATE_ADD( `last` , INTERVAL `avg` DAY ) AS `next` ,
    CASE WHEN DATE_ADD( `last` , INTERVAL `avg` DAY ) < CURRENT_DATE THEN 'true' ELSE 'false' END AS overdue
    FROM (
        SELECT `KundenID` , AVG( Date_Diff ) AS `avg` , MAX( `BezDat` ) AS `last`
            FROM (
            SELECT A.`KundenID` AS `KundenID` , DATEDIFF( B.`BezDat` , A.`BezDat` ) AS Date_Diff, B.`BezDat`
            FROM `zero` AS A
            INNER JOIN `zero` AS B ON A.`KundenId` = B.`KundenId`
            AND B.`BezDat` = (
                SELECT `BezDat`
                FROM `zero`
                WHERE `KundenID` = A.`KundenID`
                AND `BezDat` > A.`BezDat`
                ORDER BY `BezDat` ASC
                LIMIT 1 
            )
        ) AS T
        GROUP BY `KundenID`
    ) AS AT
     
  6. Zero-G.

    Zero-G. Benutzer

    Hallo Hony%
    Danke für Deine sehr ausführliche Hilfe, aber leider funktioniert das bei mir aus irgendeinem Grund nicht....
    Mein Server läuft seit 30 Minuten ohne Ergebnis. - Das Feld BezDat ist indiziert & KundenID = Primary (nur zur Info)

    Kann das einen Deadlock auslösen?

    DANKE
     
  7. akretschmer

    akretschmer Datenbank-Guru

    • was sagt EXPLAIN?
    • über wie viele Datensätze reden wir?
    • läuft es, oder kommen Fehler? Last?

    Kannst Du es mal mit einer kleinen Teilmenge probieren?
     
  8. Hony%

    Hony% Datenbank-Guru

    Ich wüsste gerade echt nicht wo. Die korrelierende Subanfrage könnte allerdings bei vielen Einträgen bremsen.

    Ich würde bei der Suche mit dem "Kern" anfangen:
    Code:
    SELECT A.`KundenID` AS `KundenID` , DATEDIFF( B.`BezDat` , A.`BezDat` ) AS Date_Diff, B.`BezDat`
            FROM `zero` AS A
            INNER JOIN `zero` AS B ON A.`KundenId` = B.`KundenId`
            AND B.`BezDat` = (
                SELECT `BezDat`
                FROM `zero`
                WHERE `KundenID` = A.`KundenID`
                AND `BezDat` > A.`BezDat`
                ORDER BY `BezDat` ASC
                LIMIT 1 
    Die umschließenden SELECTS sind sowieso eher ein Überbleibsel der vorherigen Version und könnten noch integriert werden. Optimierungspotential ist da noch eine Menge vorhanden.
     
  9. Zero-G.

    Zero-G. Benutzer

    Also: Explain funktioniert leider anscheinend auch nicht. - Erst wenn ich es soweit herunterbreche, dass folgendes Statement überbleibt:

    SELECT
    A.`KundenID` AS `KundenID`,
    DATEDIFF(B.`BezDat`, A.`BezDat`) AS Date_Diff,
    B.`BezDat`
    FROM `Auftrag` AS A
    JOIN `Auftrag` AS B
    ON A.`KundenID` = B.`KundenID`
    AND B.`BezDat` = (SELECT
    `BezDat`
    FROM `Auftrag`
    WHERE `KundenID` = A.`KundenID`
    AND `BezDat` > A.`BezDat`
    and B.auftrag='TypeB'
    ORDER BY `BezDat` ASC
    LIMIT 1)

    1) Dann sieht das Explain so aus:
    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY A ALL rel_kundenstamm_auftrag (null) (null) (null) 82892
    1 PRIMARY B ref rel_kundenstamm_auftrag rel_kundenstamm_auftrag 8 optik.A.KundenID 2 Using where
    2 DEPENDENT SUBQUERY Auftrag index rel_kundenstamm_auftrag,idx_BezDat idx_BezDat 4 (null) 1 Using where

    2) Ich grenze schon ein (TypeB)

    3) Keine Fehler! - Es läuft endlos

    4) Auslastung der CPU liegt bei ca. 15% & RAM bei 1GB
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Wie - Explain zeigt keinen Plan an?
     
  11. Zero-G.

    Zero-G. Benutzer

    Selbes Szenario wie beim Ausführen der normalen Abfrage: Explain läuft & läuft .... Aber ohne Ergebnis
     
  12. Zero-G.

    Zero-G. Benutzer

    Zusatzinfo: Wir sprechen im Moment von knapp 81000 Datensätzen
     
  13. Hony%

    Hony% Datenbank-Guru

    Gib BezDat testweise mal einen eigenen Index. Könnte sein, dass MySQL da einen Seq-Scan macht.

    Nachtrag:
    Die korellierende Subanfrage wird pro Datensatz ausgeführt um das nächstgrößere Datum zu finden. Wenn die Datenbank da pro Datensatz nur 0,1 Sekunde braucht dauert die gesamte Anfrage über 2 Stunden.
     
  14. Zero-G.

    Zero-G. Benutzer

    BezDat hat einen Index
     
  15. Hony%

    Hony% Datenbank-Guru

    Wie sieht es mit dem Attribut auftrag aus? Gibt es da einen Index?
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden