MySQL 5.7 Letzter Wert eines Tages der letzten 7 Tage anzeigen lassen

[codo]

Hallo ich kämpfe mich als begeisterungsfähiger Laie gerade durch ein Problem.
Ich habe eine Datenbank in die 6 verschiedene Solarpanele ihre Werte mehrmals täglich einmelden. Unter anderem auch die max Wattstunden des Vortages.Um nun eine schöne grafische Auswertung zu bekommen würde ich gerne nur den jeweils letzen Wert eines Tages für die jeweilige Station anzeigen lassen und das, für die letzten 7 Tage. Den Wert den ich brauche lautet h22

CREATE TABLE `analog_data` (
  `id` int(11) NOT NULL,
  `datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `imei` text NOT NULL COMMENT 'IMEI',
  `imsi` text NOT NULL COMMENT 'IMSI',
  `temp` int(11) NOT NULL COMMENT 'TEMP',
  `h` int(11) NOT NULL COMMENT 'H',
  `sta` int(11) NOT NULL COMMENT 'STA',
  `fw` int(11) NOT NULL COMMENT 'FW',
  `v` int(11) NOT NULL COMMENT 'V',
  `i` int(11) NOT NULL COMMENT 'I',
  `vpv` int(11) NOT NULL COMMENT 'VPV',
  `ppv` int(11) NOT NULL COMMENT 'PPV',
  `err` int(11) NOT NULL COMMENT 'ERR',
  `last` int(11) NOT NULL COMMENT 'LAST',
  `il` int(11) NOT NULL COMMENT 'IL',
  `h19` int(11) NOT NULL COMMENT 'H19',
  `h20` int(11) NOT NULL COMMENT 'H20',
  `h21` int(11) NOT NULL COMMENT 'H21',
  `h22` int(11) NOT NULL COMMENT 'H22',
  `h23` int(11) NOT NULL COMMENT 'H23',
  `swv` int(11) NOT NULL COMMENT 'SWV',
  `ts` datetime NOT NULL COMMENT 'TS'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Meine Lösungsansatz

SELECT datum AS letzter_tag, h22 AS endbetrag
FROM analog_data JOIN ( SELECT DATE_FORMAT(datum,'%Y-%m')
AS d, MAX(DATE_FORMAT(datum,'%d %H:%i:s'))
AS dmax FROM analog_data GROUP BY DATE_FORMAT(datum,'%Y-%m') ORDER BY NULL ) m
ON DATE_FORMAT(datum,'%Y-%m') = m.d AND DATE_FORMAT(datum,'%d %H:%i:s') = m.dmax
WHERE imei='12207001080743'
ORDER BY datum

liefert aber nur den letzten Eintrag eines Tages, da stehe ich leider an.
Wie kann ich das um die Daten der letzten Tage erweitern?
Danke

 

[codo]

Danke an den Moderator für das Formatiern

 

[Yaslaw]

Du solltest dir angewöhnen dein Code zu formatieren. Sowas kann man nicht lesen.

Hier mal dein Code in lesbarer Form

SELECT 
    datum AS letzter_tag, 
    h22 AS endbetrag
FROM 
    analog_data 
    JOIN ( 
        SELECT DATE_FORMAT(datum,'%Y-%m') AS d, 
            MAX(DATE_FORMAT(datum,'%d %H:%i:s')) AS dmax 
        FROM analog_data 
        GROUP BY 
            DATE_FORMAT(datum,'%Y-%m') 
        ORDER BY NULL 
    ) m
    ON DATE_FORMAT(datum,'%Y-%m') = m.d 
    AND DATE_FORMAT(datum,'%d %H:%i:s') = m.dmax
WHERE 
    imei='12207001080743'
ORDER BY datum

Diese ganze Formatiererei ist doch ziemlich wirr.
Grundsätzlich: die letzen 7 Tage filtern und das Datum extrahieren.
Danach geht es mit
Aktuelle Einträge pro Gruppe auslesen weiter. Ich verwende mal "Einfaches Subquery mit IN()"

Dann kommt man etwa auf sowas um pro imei und Tag den letzten Eintrag auszulesen

select 
	date(d.datum) as Tag,
	d.datum,  
	d.imei, 
	d.h22
from analog_data d
where 
	(d.imei, date(d.datum), d.datum) IN (
		select 
			m.imei, 
			date(m.datum) as Tag, 
			max(m.datum) as max_datum
		from analog_data m
		where date(m.datum) >= DATE_SUB(DATE(SYSDATE()), INTERVAL 7 day) 
		group by 
			m.imei,
			date(m.datum)
	)

 

[codo]

Vielen Dank für die Antwort,
Ein Problem habe ich noch.
Ursprünglich habe ich über die MYSQL Abfrage in php

WHERE
    imei='12207001328381'

die ID selektiert, damit ich über

$id=$_GET['id'];
if($id=='12207001328381')

für die jeweilige imei eine eigene Seite erstellt habe.
wie könnte ich das mit dein Abfrage realisieren?

 

[Yaslaw]

Einfach im inneren SELECT im WHERE-Teil ergänzen

--alt
		where date(m.datum) >= DATE_SUB(DATE(SYSDATE()), INTERVAL 7 day) 
--neu
		where 
			date(m.datum) >= DATE_SUB(DATE(SYSDATE()), INTERVAL 7 day) 
			and m.imei='12207001080743'

 

[codo]

Danke Vielmals

 

[codo]

Hallo
Noch eine Frage zu dem Thema. Mittlerweile dauert die Abfrage Elends lange. Gibt es eine Methode das zu beschleunigen. Ich lasse ja auchGrafiken dazu anzeigen? Könnte man diese Abfragen eventuel 3x täglich vorab laden lassen?
Danke
Scio ut nesciso....

 

[Zvoni]

Als Faustregel: Ein SELECT....FROM.... WHERE Irgendwas IN lässt sich in der Regel durch ein LEFT JOIN mit ISNULL auf der rechten Seite optimieren.
Nimm mal deine (bzw. Yaslaw's) Abfrage, und schick sie mal (in Workbench?) durch den Query-Analyzer um den Execution-Plan zu sehen. Dort siehst du auch den Zeitaufwand für jede einzelne "Komponente" der Abfrage.
UNd dann bau das ganze mal auf einen LEFT JOIN mit ISNULL um, teste nochmal.

EDIT: Ich sehe gerade, dass deine Tabelle "analog_data" keinen Primär-Schlüssel hat?!?!?!?.
Soll wohl Spalte "id" sein. Setz das mal auf Primär-Schlüssel, weil dann bekommst du automatisch auch einen Index dafür.
Falls das nicht geht, erzeuge manuell einen Index darauf.
KORREKTUR: Index auf imei (sofern "imei" keine Duplikate hat)

EDIT2: Sehe ich das richtig in Post 4, dass du ID mit IMEI vergleichst?

EDIT3: Korrektur zu dem LEFT JOIN: Es muss natürlich NOT ISNULL heissen