selfhtml.de - Alles für den Webmaster!

Anzeige: united-domains - Die ganze Welt der Domains

Kombination der Aggregatfunktionen max und sum

Autor: mk
Datum: 07.12.2011 18:56:12

Hallo zusammen,

folgende Frage an Euch:

nehmen wir an, wir haben die folgende Tabelle mit Beispieldaten zur Speicherung von Arbeitszeiten (FK_employees ist quasi die MitarbeiterID):

CREATE TABLE IF NOT EXISTS workingHours (
  FK_employees int(11) NOT NULL,
  startOfWork datetime DEFAULT NULL,
  endOfWork datetime DEFAULT NULL,
  PRIMARY KEY(FK_employees, startOfWork)
);


INSERT INTO workingHours (FK_employees, startOfWork, endOfWork) VALUES
(1, '2011-11-02 07:15:00', '2011-11-02 18:00:00'),
(2, '2011-11-02 07:25:00', '2011-11-02 15:30:00'),
(3, '2011-11-02 08:15:00', '2011-11-02 19:14:00'),
(2, '2011-11-02 17:10:00', '2011-11-02 19:10:00'),
(1, '2011-11-03 07:05:00', '2011-11-03 18:00:00'),
(2, '2011-11-03 08:00:00', '2011-11-03 15:30:00'),
(3, '2011-11-03 09:15:00', '2011-11-03 19:14:00'),
(1, '2011-11-04 07:05:00', '2011-11-04 18:00:00');

Mitarbeiter können sich mehrfach pro Tage an- und abmelden.

Wenn ich jetzt wissen will, wie lange der Mitarbeiter 2 (FK_employees = 2) am 02.11.2011 gearbeitet hat, so lautet der Befehl zum Beispiel:

SELECT sec_to_time(SUM(UNIX_TIMESTAMP(workingHours.endOfWork)- UNIX_TIMESTAMP(workingHours.startOfWork)))  AS 'Arbeitszeit von Peter Müller'
FROM workingHours, employees
WHERE employees.ID = workingHours.FK_employees
AND employees.firstName = 'Peter'
AND employees.surname = 'Müller'
AND workingHours.startOfWork LIKE '2011-11-02%';


Nicht klar ist mir aber:
Wie lautet der MySQL-Befehl, um die MitarbeiterID (Feld FK_employees) des Mitarbeiters auszugeben, der am 02.11.2011 am längsten gearbeitet hat?

Ja, es soll MySQL-seitig und nicht PHP-seitig ausgewertet werden. Letzteres wäre mir klar. Falls möglich, will ich keine Stored Procedures verwenden.

Irgendwie habe ich mit der Kombination der Aggregatfunktionen max und sum so mein Problem.

Korrektur: Kombination der Aggregatfunktionen max und sum

Autor: mk
Datum: 07.12.2011 19:00:59

Sorry, der richtige MySQL-Befehl lautet:


SELECT sec_to_time(SUM(UNIX_TIMESTAMP(workingHours.endOfWork)- UNIX_TIMESTAMP(workingHours.startOfWork)))  AS Arbeitszeit
FROM workingHours
WHERE workingHours.FK_employees = 2
AND workingHours.startOfWork LIKE '2011-11-02%';


Der andere Befehl stammt noch aus der kompletten DB.

Re: Kombination der Aggregatfunktionen max und sum

Autor: gaby
Datum: 08.12.2011 05:25:10

> Wenn ich jetzt wissen will, wie lange der
> Mitarbeiter 2 (FK_employees = 2) am 02.11.2011
> gearbeitet hat, so lautet der Befehl zum
> Beispiel:
>
> SELECT
> sec_to_time(SUM(UNIX_TIMESTAMP(workingHours.endOfWork)-
> UNIX_TIMESTAMP(workingHours.startOfWork)))  AS
> ''Arbeitszeit von Peter Müller''
> FROM workingHours, employees
> WHERE employees.ID = workingHours.FK_employees
> AND employees.firstName = ''Peter''
> AND employees.surname = ''Müller''
> AND workingHours.startOfWork LIKE ''2011-11-02%'';
>
>
> Nicht klar ist mir aber:
> Wie lautet der MySQL-Befehl, um die MitarbeiterID
> (Feld FK_employees) des Mitarbeiters auszugeben,
> der am 02.11.2011 am längsten gearbeitet hat?


Moin moin :-)

Die Kombination von SUM und MAX ist mir leider auch nicht geläufig. Würde es denn genügen, wenn du die Mitarbeiter, sortiert nach ihren Gesamtarbeitszeiten, aufgelistet bekämst?

SELECT FK_employees, sec_to_time( SUM( UNIX_TIMESTAMP( workingHours.endOfWork ) - UNIX_TIMESTAMP( workingHours.startOfWork ) ) ) AS Arbeitszeit
FROM workingHours
WHERE workingHours.startOfWork LIKE ''2011-11-02%''
GROUP BY fk_employees
ORDER BY `Arbeitszeit` DESC


Grüße
gaby

Edit:

Wenn du dann noch mit "LIMIT 1 " die Ausgabe der Mitarbeiter auf 1 begrenzt, sollte es das gewünschte Ergebnis sein.

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 08.12.2011 13:51:49

> Die Kombination von SUM und MAX ist mir leider
> auch nicht geläufig.

Es ist tatsächlich nicht möglich, zu sagen: »Selektiere die ID (hier: FK_employees) des Datensatzes mit dem per MAX() ermittelten höchsten Wert« -- zumindest nicht in _einem_einfachen_ Query. Möglich ist das aber z.B. über einen Subquery, wie er hier gezeigt wird:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html
(direkt das erste Beispiel -- und Achtung, englische Seite)

Daher würde ich auch zunächst Deine Variante wählen, mit ORDER BY und LIMIT 1.
Zu beachten ist aber: Es ist durchaus möglich, dass _zwei_ (oder mehrere) Datensätze genau die gleiche Arbeitszeit aufweisen. Und das ist nicht einmal sooo unwahrscheinlich, da (wenn ich mir die Beispieldatensätze so ansehe) die Anfangs- und Endzeiten ja nicht sekundengenau erfasst werden -- mir ist auch noch kein Arbeitgeber begegnet, der es mit der Arbeitszeit seiner Angestellten _so_ genau nähme. ;-)

In diesem Fall wird dann per "LIMIT 1" nur _ein_ Datensatz selektiert; die Version mit dem Subquery würde hingegen korrekterweise beide/alle zurückliefern.


Und zwei weitere Vorschläge möchte ich noch anbringen:

1. Anstelle des komplizierten und kaum lesbaren Konstrukts

"SELECT sec_to_time( SUM (
UNIX_TIMESTAMP( workingHours.endOfWork ) - UNIX_TIMESTAMP( workingHours.startOfWork )
) ) AS 'arbeitszeit'"

würde ich schreiben:

SELECT TIMEDIFF(endOfWork, startOfWork) AS 'arbeitszeit' FROM workingHours

... das macht das gleiche, nur lesbarer und der für Zeitberechnungen vorgesehenen Funktion. ;-)



2. Wenn man Datumsfelder hat, sollte man auch Datumsfunktionen nutzen -- und nicht Stringfunktionen wie LIKE.
Also nicht
WHERE startOfWork LIKE '2011-11-02%'
sondern:
WHERE DATE(startOfWork) = '2011-11-02'


HTH,
Tobias Jung

Re: Kombination der Aggregatfunktionen max und sum

Autor: gaby
Datum: 08.12.2011 18:16:05

@ mk

SELECT FK_employees, sec_to_time( SUM( UNIX_TIMESTAMP( workingHours.endOfWork ) - UNIX_TIMESTAMP( workingHours.startOfWork ) ) ) AS Arbeitszeit
FROM workingHours
WHERE workingHours.startOfWork LIKE ''2011-11-02%''
GROUP BY fk_employees
ORDER BY `Arbeitszeit` DESC
LIMIT 1

Man kann diesen Query auch noch verkürzen:
Da hier nur _eine_ Tabelle verarbeitet wird, ist es überflüssig, den Tabellennamen "workingHours" vor den Feldnamen zu hängen.

Das braucht man nur dann zur Unterscheidung, wenn in dem Query mehrere Tabellen verarbeitet werden, in denen die gleichen Feldnamen (hier: startOfWork und endOfWork) vorkommen.

Es genügt also

...
( SUM( UNIX_TIMESTAMP( endOfWork ) - UNIX_TIMESTAMP( startOfWork ) ) )
AS Arbeitszeit
FROM workingHours
WHERE startOfWork LIKE "2011-11-02%"
...

oder optimalerweise der Vorschlag von Tobias aus dem Vorposting.

------------------------------------------


> Zu beachten ist aber: Es ist durchaus möglich,
> dass _zwei_ (oder mehrere) Datensätze genau die
> gleiche Arbeitszeit aufweisen. Und das ist nicht
> einmal sooo unwahrscheinlich, da (wenn ich mir
> die Beispieldatensätze so ansehe) die Anfangs-
> und Endzeiten ja nicht sekundengenau erfasst
> werden

Mit Deinem Einwand hast Du natürlich recht.

> mir ist auch noch kein Arbeitgeber
> begegnet, der es mit der Arbeitszeit seiner
> Angestellten _so_ genau nähme. ;-)

Dann hast Du noch nicht viele Arbeitgeber gehabt.
Naja, Du bist ja auch noch jung. ;-)
Bei uns wird sekundengenau erfasst, und erst bei der Berechnung auf volle Min kaufmännisch gerundet.


Grüße
gaby

Re: Kombination der Aggregatfunktionen max und sum

Autor: mk
Datum: 08.12.2011 18:34:08

Hallo Gaby, hallo Tobias,

danke für Euer Feedback. Ihr hattet beide die richtige Idee:

Sortieren und dann nur den ersten ausgeben (mit dem Risiko, dass man weitere mit gleichen Zeiten vernachlässigt). Hätte ich auch selber drauf kommen können.

An Subselects hatte ich auch schon gedacht, werde ich testen und das Ergebnis posten.

Allerdings glaube ich, dass der Einwand von Tobias bzgl. der Nutzung von timediff so nicht richtig ist. Hiermit kann ich zwar die Zeitdifferenz zwischen zwei DATETIME's ermitteln, die Ergebnisse kann ich dann aber nicht aufsummieren. Deshalb der Umweg über UNIX-TIMESTAMP.

MfG

mk

Re: Kombination der Aggregatfunktionen max und sum

Autor: gaby
Datum: 08.12.2011 19:05:31

> Hallo Gaby, hallo Tobias,
>
> danke für Euer Feedback. Ihr hattet beide die
> richtige Idee:
>
> Sortieren und dann nur den ersten ausgeben (mit
> dem Risiko, dass man weitere mit gleichen Zeiten
> vernachlässigt). Hätte ich auch selber drauf
> kommen können.
>
> An Subselects hatte ich auch schon gedacht, werde
> ich testen und das Ergebnis posten.

Ja, bitte mach das.
Das würde mich auch interessieren.


> Allerdings glaube ich, dass der Einwand von
> Tobias bzgl. der Nutzung von timediff so nicht
> richtig ist. Hiermit kann ich zwar die
> Zeitdifferenz zwischen zwei DATETIME's ermitteln,
> die Ergebnisse kann ich dann aber nicht
> aufsummieren.

Doch, kannst du:

SELECT FK_employees, sec_to_time(
SUM( TIMEDIFF( endOfWork, startOfWork ) )
) AS arbeitszeit
FROM workingHours
WHERE DATE( startOfWork ) = '2011-11-02'
GROUP BY FK_employees


Grüße
gaby

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 08.12.2011 19:09:20

> Doch, kannst du:
>
> SELECT FK_employees, sec_to_time(
> SUM( TIMEDIFF( endOfWork, startOfWork ) )
> ) AS arbeitszeit
> FROM workingHours
> WHERE DATE( startOfWork ) = '2011-11-02'
> GROUP BY FK_employees

Richtig und Sorry an mk, dass ich da ein durcheinander gekommen und diesen wichtigen Part weggelassen habe. :-/

Gruß,
Tobias

Re: Kombination der Aggregatfunktionen max und sum

Autor: mk
Datum: 08.12.2011 20:10:43

Hallo,

ich glaube weiterhin, dass dies so  n i c h t  funktioniert:
> >
> > SELECT FK_employees, sec_to_time(
> > SUM( TIMEDIFF( endOfWork, startOfWork ) )
> > ) AS arbeitszeit
> > FROM workingHours
> > WHERE DATE( startOfWork ) = '2011-11-02'
> > GROUP BY FK_employees
>

Teste es doch einmal, indem du die Beispieleinträge einmal händisch aufsummierst. Dazu muss natürlich ein Mitarbeiter an einem Tag zweimal angemeldet sein. Für FK_employees = 2 ist dies in den Beispieldatensätzen der Fall. Er hat insgesamt 08:05:00 + 02:00:00, also 10 Stunden und 5 Minuten gearbeitet. Die Ergebnisse der obigen Abfrage (27 Stunden und 55 Minuten für Mitarbeiter 2) stimmen aber nicht mit der obigen Rechnung überein.

Dies liegt daran, dass man Zeichenketten (letztlich sind Datumsangaben ja nichts anderes in MySQL) eben nicht mittels sum so einfach addieren kann.

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 08.12.2011 20:56:14

> Hallo,
>
> ich glaube weiterhin, dass dies so  n i c h t  funktioniert:
> (...)

Herrje, ich glaub ich sollte heute nichts mehr posten, bin offensichlich zu unkonzentriert.
Du hast Recht, korrekt -- aber dann eigentlich auch nicht mehr lesbarer als Dein Ausgangsquery -- wäre natürlich:

SELECT FK_employees,
sec_to_time( SUM( time_to_sec( TIMEDIFF( endOfWork, startOfWork ) ) ) )
AS arbeitszeit
FROM workingHours
WHERE DATE( startOfWork ) = '2011-11-02'
GROUP BY FK_employees

Gruß,
Tobias

Re: Kombination der Aggregatfunktionen max und sum

Autor: mk
Datum: 09.12.2011 08:48:08

> SELECT FK_employees,
> sec_to_time( SUM( time_to_sec( TIMEDIFF(
> endOfWork, startOfWork ) ) ) )
> AS arbeitszeit
> FROM workingHours
> WHERE DATE( startOfWork ) = '2011-11-02'
> GROUP BY FK_employees

ja, das würde auch funktionieren.

Ich habe einmal versucht, ein Subselect zu erzeugen. ich habe unterschiedliche Ansätze probiert, klappt aber nicht.

Deshalb gibt es meiner Meinung nach doch nur die Lösungen:
a) mittels ORDER und LIMIT wie vorab besprochen mit dem Nachteil, gleich hohe Arbeitszeite nicht zu erkennen
b) über eine temporäre Tabelle
c) über eine stored procedure

Da b) und c) aber den dazugehörigen PHP-Code auch nicht vereinfacht, werde ich dann wohl doch eher das Maximum der Summe PHP-seitig bestimmen wollen.

Re: Kombination der Aggregatfunktionen max und sum

Autor: manya
Datum: 13.12.2011 12:00:14

> CREATE TABLE IF NOT EXISTS workingHours (
>   FK_employees int(11) NOT NULL,
>   startOfWork datetime DEFAULT NULL,
>   endOfWork datetime DEFAULT NULL,
>   PRIMARY KEY(FK_employees,
> startOfWork)
> );
>
>
> INSERT INTO workingHours (FK_employees,
> startOfWork, endOfWork) VALUES
> (1, '2011-11-02 07:15:00', '2011-11-02
> 18:00:00'),
> (2, '2011-11-02 07:25:00', '2011-11-02
> 15:30:00'),
> (3, '2011-11-02 08:15:00', '2011-11-02
> 19:14:00'),
> (2, '2011-11-02 17:10:00', '2011-11-02
> 19:10:00'),
> (1, '2011-11-03 07:05:00', '2011-11-03
> 18:00:00'),
> (2, '2011-11-03 08:00:00', '2011-11-03
> 15:30:00'),
> (3, '2011-11-03 09:15:00', '2011-11-03
> 19:14:00'),
> (1, '2011-11-04 07:05:00', '2011-11-04
> 18:00:00');


Guten Morgen!

[off-topic]

Kannst du, oder jemand anderer mir sagen, wie du das gemacht hast, die Struktur und den Inhalt einer Tabelle in so einer Befehlsabfolge zu erstellen? Ich nehme jetzt nicht an, daß du alles von Hand getippt hast? ;-)

[/off-topic]

Gruß
manya

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 13.12.2011 12:20:26

> [off-topic]
>
> Kannst du, oder jemand anderer mir sagen, wie du
> das gemacht hast, die Struktur und den Inhalt
> einer Tabelle in so einer Befehlsabfolge zu
> erstellen? Ich nehme jetzt nicht an, daß du alles
> von Hand getippt hast? ;-)
>
> [/off-topic]

Das geht z.B. mit der Export-Funktion von phpMyAdmin.
Wenn Du die Default-Einstellungen belässt (Export im SQL-Format, Struktur _und_ Daten exportieren), wird Dir eine Datei mit der Endung .sql erzeugt -- dabei handelt es sich um eine Text-Datei, die Du mit jedem Editor öffnen kannst und dann genau so aussieht wie der Code von mk.

HTH,
Tobias

Re: Kombination der Aggregatfunktionen max und sum

Autor: manya
Datum: 13.12.2011 13:25:44

> Das geht z.B. mit der Export-Funktion von
> phpMyAdmin.
> Wenn Du die Default-Einstellungen belässt (Export
> im SQL-Format, Struktur _und_ Daten exportieren),
> wird Dir eine Datei mit der Endung .sql erzeugt
> -- dabei handelt es sich um eine Text-Datei, die
> Du mit jedem Editor öffnen kannst und dann genau
> so aussieht wie der Code von mk.

Vielen Dank, das hat geklappt!  :-))

Eine Kleinigkeit nur noch:
Die Umlaute in der erzeugten sql-Datei sind jetzt nicht mehr so, wie sie sein sollen.
statt "Jürgen" steht da "Jürgen".
Ich habe aber in der export-Oberfläche keine Möglichkeit gefunden, einen Zeichensatz auszuwählen.

Soviel ich weiß, hast Du einen Mac, da sind die Auswahlmöglichkeiten vielleicht ein bißchen anders. Deshalb habe ich einen Screenshot von xampp/Windows mitgeliefert.

Alle Einstellungen bei "export" sind auf "Default" gelassen.
Siehst Du in dem Screenshot vielleicht etwwas, das nicht so ist, wie sie sein soll?

Gruß
manya

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 13.12.2011 14:05:06

> Eine Kleinigkeit nur noch:
> Die Umlaute in der erzeugten sql-Datei sind jetzt
> nicht mehr so, wie sie sein sollen.
> statt "Jürgen" steht da "Jürgen".

Wahrscheinlich wurde die Datei im UTF-8-encoding erzeugt.
Das solltest Du in einem guten Texteditor (bei entsprechenden Einstellungen) auch mit korrekter Darstellung der Umlaute öffnen und ggf. konvertieren können.


> Ich habe aber in der export-Oberfläche keine
> Möglichkeit gefunden, einen Zeichensatz
> auszuwählen.
>
> Soviel ich weiß, hast Du einen Mac, da sind die
> Auswahlmöglichkeiten vielleicht ein bißchen
> anders.

Nein; wenn, dann hängen diese Auswahlmöglichkeiten mit der Version von phpMyAdmin zusammen.
Ich habe Version 3.4.7, da kann man die Zeichencodierung der Export-Datei einstellen -- und zwar genau auf der Seite, die Du als Screenshot gepostet hast (obgleich die in »meiner« Version ein bisschen anders aussieht).
Kann mich leider nicht erinnern, ab welcher phpMyAdmin-Version diese Option zur Verfügung steht.

Wenn Du es gar nicht hinbekommst, kannst Du mir die exportierte Datei auch schicken (meine Mailadresse bekommst Du per PN) und mir sagen, in welchem Encoding Du sie brauchst... ich denke, ich werde es hinbekommen, das zu konvertieren.
In diesem Fall die Datei bitte unbedingt vorher als ZIP packen!
Nicht wegen der Größe, sondern weil die ungepackte Textdatei für den Mailtransport ja abermals codiert würde, und eine weitere Problemursache können wir nicht brauchen. ;-)

Gruß,
Tobias

Re: Kombination der Aggregatfunktionen max und sum

Autor: manya
Datum: 13.12.2011 17:49:51

> Wahrscheinlich wurde die Datei im UTF-8-encoding
> erzeugt.
> Das solltest Du in einem guten Texteditor (bei
> entsprechenden Einstellungen) auch mit korrekter
> Darstellung der Umlaute öffnen und ggf.
> konvertieren können.

Ein "guter" Editor ist meistens nicht kostenlos. ;-)
Meiner ist alles andere als perfekt, dafür ist er aber gratis.
Man kann nicht alles haben. ;-)

> Nein; wenn, dann hängen diese
> Auswahlmöglichkeiten mit der Version von
> phpMyAdmin zusammen.
> Ich habe Version 3.4.7, da kann man die
> Zeichencodierung der Export-Datei einstellen --

Ich habe die Versionsnummer
Versionsinformationen: http://3.2.0.1
Das muß ich bei Gelegenheit auch mal wieder updaten.

> Wenn Du es gar nicht hinbekommst, kannst Du mir
> die exportierte Datei auch schicken (meine
> Mailadresse bekommst Du per PN) und mir sagen, in
> welchem Encoding Du sie brauchst...

Danke für Dein nettes Angebot!  :-)
Wenn ich es nicht schaffe, werde ich sicher darauf zurückkommen.

Gruß
manya

Re: Kombination der Aggregatfunktionen max und sum

Autor: T.Jung
Datum: 13.12.2011 18:54:42

> Ein "guter" Editor ist meistens nicht kostenlos.
> ;-)
> Meiner ist alles andere als perfekt, dafür ist er
> aber gratis.
> Man kann nicht alles haben. ;-)

Da mir für den Mac auf Anhieb zwei Gratis-Editoren einfallen, die UTF-8 beherrschen und ggf. in andere encodings konvertieren können (und einer davon gehört sogar zum Lieferumfang eines jeden Macs), bin ich sicher, dass es für Windows mindestens ein bis zwei Hände voll entsprechender Programme gibt. :-)


> Wenn ich es nicht schaffe, werde ich sicher
> darauf zurückkommen.

Tu das!
Gruß,
Tobias