Select mit Durchschnitts Datum Werten

Zero-G. · 3 März 2014

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...

akretschmer · 3 März 2014

Zero-G. schrieb:
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
Danke für eure Mühen. - Um ehrlich zu sein, habe ich gar keine Ansatzidee...

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().

Zero-G. · 3 März 2014

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

Hony% · 3 März 2014

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

Hony% · 3 März 2014

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

Zero-G. · 3 März 2014

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

akretschmer · 3 März 2014

Zero-G. schrieb:
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

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?

Hony% · 3 März 2014

Zero-G. schrieb:
Kann das einen Deadlock auslösen?

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.

Zero-G. · 3 März 2014

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

akretschmer · 3 März 2014

Zero-G. schrieb:
Also: Explain funktioniert leider anscheinend auch nicht.

Wie - Explain zeigt keinen Plan an?

Zero-G. · 3 März 2014

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

Zero-G. · 3 März 2014

Zusatzinfo: Wir sprechen im Moment von knapp 81000 Datensätzen

Hony% · 3 März 2014

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.

Zero-G. · 3 März 2014

BezDat hat einen Index

Hony% · 3 März 2014

Wie sieht es mit dem Attribut auftrag aus? Gibt es da einen Index?

Select mit Durchschnitts Datum Werten

Zero-G.

Benutzer

akretschmer

Datenbank-Guru

Zero-G.

Benutzer

Hony%

Datenbank-Guru

Hony%

Datenbank-Guru

Zero-G.

Benutzer

akretschmer

Datenbank-Guru

Hony%

Datenbank-Guru

Zero-G.

Benutzer

akretschmer

Datenbank-Guru

Zero-G.

Benutzer

Zero-G.

Benutzer

Hony%

Datenbank-Guru

Zero-G.

Benutzer

Hony%

Datenbank-Guru