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

MySQL: Datenbank-Abfrage optimieren

Dieses Thema im Forum "Relationale Datenbanksysteme" wurde erstellt von thomatschko, 14. November 2017.

  1. thomatschko

    thomatschko Grünschnabel

    Hallo,

    mein Name ist Thomas und sitze an einer Abfrage bzw. streng genommen 55 Abfragen.

    Ich habe eine umfangreiche Login-Tabelle, die unter anderem die Spalten [Date], [Day] und [Login] enthält.

    Ich möchte nun herausfinden, wie viele Logins in 2017 montags zwischen 7 und 8 Uhr, 8 und 9 Uhr usw. stattfanden. Das dann auch für Dienstag, Mittwoch usw.

    Beispiel für Zeitraum 7-8 Uhr:
    SELECT COUNT(*) FROM `Login` WHERE YEAR(`Date`)='2017' AND `Day`= 1 AND TIME(`Login`) BETWEEN '07:00:00' AND '08:00:00'

    Insgesamt wären es 55 annähernd identische Anfragen, da lediglich der Teil "BETWEEN '07:00:00' AND '08:00:00'" sich verändern.

    Gibt es eine Möglichkeit, wie man solche Anfragen effizienter gestalten kann?

    Danke
    Thomas
     
  2. Kalito

    Kalito Erfahrenes Mitglied

    Hallo Thomas,

    wie kommst du auf 55 Anfragen? Deiner Anfrage würde ich vermuten, dass du für jeden Tag aus dem Jahr 2017 wissen möchtest wie hoch die Anzahl der Logins ist.

    Kurze Frage: Worin unterscheiden sich die Spalten Date, Day und Login. Scheinen ja alles timestamps oder ähnliches zu sein.

    Mit dem Code solltest du eigentlich pro Tag und Stunde die Anzahl der Logins erhalten.

    Code (SQL):
    1. SELECT
    2.     YEAR(`Date`) AS Jahr
    3.     ,DAY
    4.     ,HOUR(`Login`)
    5.     ,COUNT(ID)
    6. FROM
    7.     `Login`
    8. WHERE
    9.     YEAR(`Date`) = 2017
    10. GROUP BY
    11.     YEAR(`Date`) AS Jahr
    12.     ,DAY
    13.     ,HOUR(`Login`)
    gruß Patrick
     
  3. thomatschko

    thomatschko Grünschnabel

    Hi Patrick,

    Danke für deine Zeilen und sorry, dass ich erst jetzt antworte, war unterwegs.

    Wir möchten die Logins während der Arbeitszeit festhalten. Da die Ausgabe-Tabelle 55 Zellen hat

    2017-11-15 08_51_25-ausgabe.xlsx - Excel.png

    habe ich bisher 55 Anfragen:

    B3: SELECT COUNT(*) FROM `Login` WHERE YEAR(`Date`)='2017' AND `Day`= 1 AND TIME(`Login`) BETWEEN '07:00:00' AND '08:00:00'
    B4: SELECT COUNT(*) FROM `Login` WHERE YEAR(`Date`)='2017' AND `Day`= 1 AND TIME(`Login`) BETWEEN '08:00:00' AND '09:00:00'

    usw. Ausgeben möchte ich in den einzelnen Zellen nur die Summe der Logins in den Zeitfenstern, deswegen hab ich es mit COUNT(*) versucht.

    Inhalte der DB-Felder:

    Date: 2017-11-15 (Typ date)
    Day: 1 für Montag, 2 für Dienstag usw. (Typ tinyint(1))
    Login: 08:58:06 (Typ time)

    Ich schaue mir deine Abfrage jetzt erst mal in Ruhe an.

    Gruß
    Thomas
     
  4. thomatschko

    thomatschko Grünschnabel

    Deine Abfrage macht genau das, was ich wollte. Besten Dank!
     
  5. Kalito

    Kalito Erfahrenes Mitglied

    Hallo Thomas,

    ich habe gerade einen kopierfehler bei meinem Script gefunden. Ebenso habe ich die Einschränkung auf die von dir oben genannten Zeiten vorgenommen.

    Code (SQL):
    1. SELECT
    2.     YEAR(`Date`) AS Jahr
    3.     ,DAY AS Wochentag
    4.     ,HOUR(`Login`) AS Stunde
    5.     ,COUNT(ID) AS Anzahl
    6. FROM
    7.     `Login`
    8. WHERE
    9.     YEAR(`Date`) = 2017
    10.     AND HOUR(`Login`) BETWEEN 08 AND 17
    11. GROUP BY
    12.     YEAR(`Date`)
    13.     ,DAY
    14.     ,HOUR(`Login`)
    EDIT: Da war ich doch zu langsam ;)
     
  6. thomatschko

    thomatschko Grünschnabel

    Hi Kalito,

    cool, vielen, vielen Dank.

    Ggf. kannst du mir zu einem anderen Problem einen Lösungsansatz geben. Ich habe das mal hier abgelegt, was ggf. einfacher ist: http://sqlfiddle.com/#!9/606546/6
    Ich möchte nun schauen, wie viele Montage, Dienstage etc. es in einem bestimmten Zeitraum gab. Mit dieser Anfrage (mal der Monat Oktober) erhalte ich eine Liste der eindeutigen Wochentage:

    Code (SQL):
    1. SELECT
    2.   DAY(`Date`) AS Tag,
    3.   Wochentag
    4. FROM `login`
    5. WHERE
    6.   YEAR(`Date`) = 2017 AND
    7.   MONTH(`Date`) = 10
    8. GROUP BY Wochentag, Tag
    Ausgabe:
    Code (Text):
    1. | Tag |  Wochentag |
    2. |-----|------------|
    3. |  10 |   Dienstag |
    4. |  17 |   Dienstag |
    5. |  24 |   Dienstag |
    6. |   5 | Donnerstag |
    7. |  12 | Donnerstag |
    8. |  19 | Donnerstag |
    9. |  26 | Donnerstag |
    10. |   6 |    Freitag |
    11. |  13 |    Freitag |
    12. |  20 |    Freitag |
    13. |  27 |    Freitag |
    14. |   4 |   Mittwoch |
    15. |  11 |   Mittwoch |
    16. |  18 |   Mittwoch |
    17. |  25 |   Mittwoch |
    18. |   2 |     Montag |
    19. |   9 |     Montag |
    20. |  16 |     Montag |
    21. |  23 |     Montag |
    22. |  30 |     Montag |
    Ich zähle dann einfach per PHP, wie oft Montag (5), Dienstag (3) usw. im Array vorhanden sind. Gibt es eine Abfragemöglichkeit, dass quasi eine Tabelle

    Montag 5
    Dienstag 3
    Mittwoch 4
    Donnerstag 4
    Freitag 4

    zurückgegeben wird?
     
  7. Yaslaw

    Yaslaw n/a Moderator

    Entweder ein dein Query als Subquery nehmen und nochmals gruppieren, oder aber gleich den Tag eindeutig zählen.
    Wobei ich die Version mit der eindeutigen Zählung eleganter finde
    Code (SQL):
    1. -- Tag eindeutig zählen
    2. SELECT
    3.   COUNT(DISTINCT DAY(`Date`)) AS anzahl,
    4.   Wochentag
    5. FROM `login`
    6. WHERE
    7.   YEAR(`Date`) = 2017 AND
    8.   MONTH(`Date`) = 10
    9. GROUP BY Wochentag;
    10.  
    11. -- Version Subquery
    12. SELECT
    13.   COUNT(*) AS anzahl,
    14.   wochentag
    15. FROM
    16.   (
    17.     SELECT
    18.       DAY(`Date`) AS Tag,
    19.       Wochentag
    20.     FROM `login`
    21.     WHERE
    22.       YEAR(`Date`) = 2017 AND
    23.       MONTH(`Date`) = 10
    24.     GROUP BY Wochentag, Tag
    25.    ) t
    26. GROUP BY
    27.   Wochentag
     
  8. thomatschko

    thomatschko Grünschnabel

    Hi Yaslaw, leckomio, wie geil ist das denn. Vielen Dank für deine Hilfe!

    Gruß
    Thomas
     
  9. Biber3

    Biber3 Erfahrenes Mitglied

    Moin thomatschko,

    kleine Ergänzung zu Yaslaws Lösung.
    Ich würde den Group By noch erweitern um ein Feld Weekday(date), damit die Wiochentage in der Reihenfolge "Montag, Dienstag, Mittwoch.." sortiert werden und nicht alphabetisch nach Tagesnamen.
    Code (SQL):
    1. SELECT
    2.   COUNT(DISTINCT DAY(`Date`)) AS anzahl,
    3.   wochentag
    4. FROM `login`
    5. WHERE
    6.   YEAR(`Date`) = 2017 AND
    7.   MONTH(`Date`) = 10
    8. GROUP BY Wochentag, weekday(`Date`)
    9. ORDER BY weekday(`Date`)
    10. ;
    Grüße
    Biber
     
Die Seite wird geladen...