✔ MySQL: Datenbank-Abfrage optimieren

T

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

SQL: 
SELECT
    YEAR(`Date`) as Jahr
    ,Day
    ,HOUR(`Login`)
    ,COUNT(ID)
FROM
    `Login`
WHERE
    YEAR(`Date`) = 2017
GROUP BY
    YEAR(`Date`) as Jahr
    ,Day
    ,HOUR(`Login`)

gruß Patrick
 
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
 
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.

SQL: 
SELECT
    YEAR(`Date`) AS Jahr
    ,DAY as Wochentag
    ,HOUR(`Login`) as Stunde
    ,COUNT(ID) as Anzahl
FROM
    `Login`
WHERE
    YEAR(`Date`) = 2017
    AND HOUR(`Login`) BETWEEN 08 AND 17
GROUP BY
    YEAR(`Date`)
    ,DAY
    ,HOUR(`Login`)

EDIT: Da war ich doch zu langsam ;)
 
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:

SQL: 
SELECT
  DAY(`Date`) AS Tag,
  Wochentag
FROM `login`
WHERE
  YEAR(`Date`) = 2017 AND
  MONTH(`Date`) = 10
GROUP BY Wochentag, Tag

Ausgabe:
Code: 
| Tag |  Wochentag |
|-----|------------|
|  10 |   Dienstag |
|  17 |   Dienstag |
|  24 |   Dienstag |
|   5 | Donnerstag |
|  12 | Donnerstag |
|  19 | Donnerstag |
|  26 | Donnerstag |
|   6 |    Freitag |
|  13 |    Freitag |
|  20 |    Freitag |
|  27 |    Freitag |
|   4 |   Mittwoch |
|  11 |   Mittwoch |
|  18 |   Mittwoch |
|  25 |   Mittwoch |
|   2 |     Montag |
|   9 |     Montag |
|  16 |     Montag |
|  23 |     Montag |
|  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?
 
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
SQL: 
-- Tag eindeutig zählen
SELECT
  COUNT(DISTINCT DAY(`Date`)) AS anzahl,
  Wochentag
FROM `login`
WHERE
  YEAR(`Date`) = 2017 AND
  MONTH(`Date`) = 10
GROUP BY Wochentag;

-- Version Subquery
SELECT
  COUNT(*) AS anzahl,
  wochentag
FROM
  (
    SELECT
      DAY(`Date`) AS Tag,
      Wochentag
    FROM `login`
    WHERE
      YEAR(`Date`) = 2017 AND
      MONTH(`Date`) = 10
    GROUP BY Wochentag, Tag
   ) t
GROUP BY
  Wochentag
 
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.
SQL: 
SELECT
  COUNT(DISTINCT DAY(`Date`)) AS anzahl,
  wochentag
FROM `login`
WHERE
  YEAR(`Date`) = 2017 AND
  MONTH(`Date`) = 10
GROUP BY Wochentag, weekday(`Date`)
Order by weekday(`Date`)
;

Grüße
Biber
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück