SQL Abfrage

[SehraGuta]

SELECT user_id, pick, Timestamp FROM sappick order by user_id;

# user_id, pick, Timestamp


'3', '1', '10:06:01'
'3', '1', '10:07:01'
'3', '1', '10:09:01'
'3', '1', '10:17:01'
'3', '1', '10:18:01'
'3', '1', '10:19:01'
'3', '2', '10:17:30'
'4', '1', '10:09:01'
'4', '1', '10:07:01'
'4', '1', '10:10:01'
'4', '1', '10:06:01'
-----------------------------

Die Aufgabe ist die Anzahl der picks pro Lagerarbeiter zu berechnen. Und zwar wenn der Lagerarbeiter länger als 5 Minuten pause macht oder
was anders macht, soll die nächste Berechnung folgen.

Z.B. Lagerarbeiter '3' hat um 10:06:01 angefangen und ist um 10:17:30 fertig. Aber weil er zwischen 10:09:01 und 10:17:01 länger als 5 Minuten pause gemacht
soll es zweimal berechnet werden. Also die erste Berechnung gilt zwischen '10:06:01' und '10:09:01' und die zweite Berechnung gilt zwischen dem nächsten
Zeitstempel 10:17:01' und '10:17:30'. Also falls Timestamp Differenz mehr als 5 Minuten soll es einmnal zum nächsten springen.

Ich habe folgendes versucht.

select user_id, Timestamp, TIMEDIFF(max(Timestamp), min(Timestamp)) as interval, SUM(pick) as picksperuser from sappick group by user_id asc;

# user_id, Timestamp, interval, picksperuser

'3', '10:06:01', '00:13:00', '8'
'4', '10:06:01', '00:04:00', '4'
----------------------------------
ABER das summiert nur einmal pro Lagerarbeiter nicht mehrmals (wegen der 5 Minuten Differenz).

Wie kann man diese Aufgabe lösen?

Danke im Voraus!

[defo]

SQL kann nur Werte gruppieren, die auch als Merkmal vorliegen. In deinem Fall wäre das die 5 Minuten Lücke.
Du musst Dir also überlegen, wie Du an diese Werte kommst.

Gedankenexperiment: Schaffe in Deinen Beispieldaten eine neue Spalte, die diese 5 minuten Lücke als "Datum" liefert. Dies wäre bspw. durch eine eindeutige oder alternierende Zahl möglich. Diese Zahl trägst Du in die neue Spalte ein, also immer eine andere Zahl, wenn die Lücke größer als 5 Minuten ist.

Diese Daten kannst Du ganz leicht gemäß der Vorgaben gruppieren.
Frage ist nun, wie kann man diesen Wert per SQL dynamisch erzeugen?

Du brauchst eine Abfrage, die die Lücken gemäß Grenzwert findet, also alle Wechsel mit mehr als 5 Minuten "erkennt".
Hier ist es leider so, dass die mengenbasierte Arbeitsweise von SQL das nicht besonders einfach macht. Die Datenbankanbieter kochen da idR alle Ihr eigenes Süppchen und nennen es Window Functions oder Analytic Functions. Manche Systeme beherrschen das auch gar nicht mittels direkter Funktionen. Mit diesen Funktionen bekommt man jedenfalls in SQL die Möglichkeit, Operationen duchzuführen wie man sie ganz normal aus Spreadsheet Anwendungen kennt, also z.B. "gib die Differenz zwischen Zelle A5 und B5 aus".

[SehraGuta]

danke für den schnellen Antwort. Ja, genaue ist es. Was ich seit Tagen suche ist ja was Sie als neue Spalte gennant haben.
Im diesem Fall ist das die Spalte 'interval'. Die Spalte ist in dem Beispiel mit dem Wert '00:13:00' gefüllt.
Mit dem Befehl TIMEDIFF(max(Timestamp), min(Timestamp)) as interval hat es geklappt, und zwar für die ganze ZEitspanne des Lagerarbeiters.
Was jetzt fehlt ist, die Lücken oder Zeitabstände die mehr als 5 Minute sind auch zu befüllen.
Ich habe heute gegoogelt und gelesen, dass man mit Selfjoin oder Subquery machen kann.
Habe probiert aber hat nicht geklappt. Haben Sie schon mal mit Selfjoin oder Subqueries gearbeitet?
Die Frage ist nur wie kriege ich die Selfjoin oder Subqueries da rein? Very tricky problem#
Danke im Voraus.

[defo]

Falls es um mySQL geht, könnte man es wie unten machen. Vielleicht hat ein richtiger mySQL Kenner einen besseren Vorschlag. Die Nutzung von Variablen ist mit Vorsicht zu genießen. Das finale Statement sollte jedenfalls gut geprüft werden.
Ich hab das Statement in mehreren Schichten aufgebaut, so kann man besser sehen, was passiert, ist aber unkompakt. Das Subselect für das TimeDiff ist bei großen Datenmenge wahrscheinlich nicht so performant.
[highlight=SQL]
select UserID, min(pTime) as pickStart, sum(pickNr) as UserPicks
from (select z.*,
case -- aus dem Intervall größer 5 Minuten einen eindeutigen Wert pro Gruppe bilden
when pInterval > 300
then @pickSet := @pickSet + 1
else @pickSet := @pickSet
end as pPickSet
from (select y.*,
timediff(y.ptime, y.priorptime) as pInterval -- Intervallberechnung
from (select p.*, -- Kern: alle picks mit subselect vorgängerzeit
(select max(ptime)
from pick x
where x.ptime < p.ptime
and x.userid = p.userid) as priorptime
from pick p
order by userid, ptime) y) z,
(SELECT @pickSet := 0) r -- Variableninitialisierung
) grp
group by UserId,
pPickSet -- eine der seltenen Gelegenheiten, nach einem Wert zu gruppieren, den man nicht ausgibt
[/highlight]

[SehraGuta]

Ich bedanke mich bei Ihnen sehr herzlich für die Info!

[defo]

Es würde mich interessieren, ob das wirklich performant und robust funktioniert, vor allem mit vielen Daten. Ist es wirklich ein mySQL System? Ansonsten wird es nicht funktionieren.
Für analytic functions oder window functions, partition over usw. gibt es ansonsten zumindest unter mySQL nach aktuellem Stand (soweit ich weiß) auch weitere Emulationsmöglichkeiten via Group_Concat und verstärkter Nutzung von sql @ variables.