SQL Abfrage

Hallo Gemeinde

Ich kämpfe gerade mit einem Projekt, welches zwar nicht direkt mit ips zu tun hat, aber evtl. dennoch in diesem Forum thematisch richtig ist…daher gleich mal vorab meine Entschuldigung, falls ich hier im falschen Forum sein sollte…

Ich versuche mal die Sache als Laie zu erklären:
Ich habe SQL Datenbank in einem Datenlogger zur Verfügung, auf die ich mit HeidiSQL draufkomm…in dieser gibt es eine Tabelle „measurement“ mit den Spalten „ID“, „SensorID“, „Value“, „AnswerDate“ und „RequestDate“.
In dieser Tabelle werden alle 5 Minuten die Werte der jeweiligen Zähler abgelegt, wobei „SensorID“ eben aussagt, welcher Zähler es ist…Value ist der Wert des jeweiligen Zählers und AnswerDate und RequestDate ist die Zeit des jeweiligen Zählerstandes…siehe Bilder measurement1 + measurement2 im Anhang

In einer weiteren Tabelle „sensor“ erfolgt die Zuweisung der „SensorID“ zu dem jeweils angeschlossenen Gerät.

Wo ich hin möchte:
Will eine Darstellung haben über die Anzahl an Zählungen innerhalb eines Zeitbereiches von einer Stunde, also etwa so wie in dem Bild „Wunschvorstellung“ …im idealfall dann unten noch einen „Vor“ und „Zurück“ Button, mit dem ich die Tage wechseln kann…

Meine Vorgehensweise als Laie:
Ich habe mir erstmal was gebastelt, um die aktuelle Zeit + Datum zu ermitteln…dann bastel ich mir eine „time2“, die das aktuelle Datum und als Uhrzeit die zurückliegende volle Stunde hat…also heute um 12:24 Uhr wäre time2 dann heute um 12:00…

Dann versuche ich zu ermitteln, wie der Zählerstand einer bestimmten SensorID um 12:00 war und wie er aktuell ist…das mache ich mit „time2+3600“, was ja dann 13:00 Uhr heute wäre…da es aber noch nicht 13 Uhr ist sondern erst 12:24, suche ich nach allen Werten die vor 13 Uhr abgelegt wurden, sortiere diese und suche mir den, der die „höchste“ Zeit hat…wäre dann in meinem Beispiel der von 12:20 Uhr, weil ja alle 5 Minuten geloggt wird…

Dann bilde ich die Differenz aus den beiden Werten und habe damit die Anzahl an Takten zwischen 12:00 Uhr und 12:20 Uhr…

$sql = 
"SELECT 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=86 Order By AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=86) AS Differenz86, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=7 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=7) AS Differenz7, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=8 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=8) AS Differenz8, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=9 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=9) AS Differenz9, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=10 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=10) AS Differenz10, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=11 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=11) AS Differenz11, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=12 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=12) AS Differenz12, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=82 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=82) AS Differenz82, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=83 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=83) AS Differenz83, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=84 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=84) AS Differenz84, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=85 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=85) AS Differenz85, 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=86 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=86) AS Differenz86,  
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$time2+3600) And SensorID=87 ORDER BY AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$time2) And SensorID=87) AS Differenz87";
 
$db_erg = mysqli_query( $conn, $sql );
if ( ! $db_erg )
{
  die('Ungültige Abfrage: ' . mysqli_error());
}


 
echo '<table border="1">';
while ($zeile = mysqli_fetch_array( $db_erg, MYSQLI_ASSOC))
{

echo "<thead>";

echo "<tr>";
echo "<th class=>Zeit von bis</th>";
echo "<th class=>SGM 1</th>";
echo "<th class=>SGM 2</th>";
echo "<th class=>SGM 5</th>";
echo "<th class=>SGM 6</th>";
echo "<th class=>SGM 7</th>";
echo "<th class=>SGM 8</th>";        
echo "<th class=>SGM 9</th>";
echo "<th class=>SGM 10</th>";
echo "<th class=>SGM 11</th>";
echo "<th class=>SGM 12</th>";
echo "<th class=>SGM 13</th>";
echo "<th class=>SGM 14</th>";

echo "<tr>";

echo "<td>". $Stund; 
echo " bis ";
echo $Stund1;"</td>";

echo "<td align=center>". $zeile['Differenz85'] . "</td>";
echo "<td align=center>". $zeile['Differenz84'] . "</td>";
echo "<td align=center>". $zeile['Differenz83'] . "</td>";
echo "<td align=center>". $zeile['Differenz82'] . "</td>";
echo "<td align=center>". $zeile['Differenz7'] . "</td>";
echo "<td align=center>". $zeile['Differenz8'] . "</td>";
echo "<td align=center>". $zeile['Differenz9'] . "</td>";
echo "<td align=center>". $zeile['Differenz10'] . "</td>";
echo "<td align=center>". $zeile['Differenz11'] . "</td>";
echo "<td align=center>". $zeile['Differenz12'] . "</td>";
echo "<td align=center>". $zeile['Differenz86'] . "</td>";
echo "<td align=center>". $zeile['Differenz87'] . "</td>";
 
echo "</tr>";

Soweit meine Laienhafte Logik…

Ich hoffe, ich konnte das halbwegs verständlich ausdrücken und bitte um Nachsicht, falls mein Ansatz zu kompliziert oder gar völlig falsch ist :wink:
Bin leider absolut befreit von Vorkenntnissen und hab mal versucht, mit viel gegoogel mir die Sache zusammenzureimen…

Ich habe mit obigem code nun die Daten aus der zurückliegenden vollen Stunde, möchte ich die Daten aus der Stunde davor, würde ich jetzt $time2-3600 und $time2-7200 verwenden…für die Stunde davor dann $time2-7200 und $time2-10800…das Ganze insgesamt 24 mal wenn ich nen ganzen Tag wie in dem Beispiel darstellen möchte…:confused:

Ich denke, das das zwar mit viel Geschreibe irgendwie funktionieren könnte, aber sicherlich mehr als unsauber und unschön ist…

Daher an dieser Stelle die Frage nach hilfreichen Tipps oder Suchbegriffen, die mir bei dem Projekt weiterhelfen…

Würde mich sehr über jede Hilfe freuen :slight_smile:

Da hast Du Dir auch ein nicht ganz triviales Thema vorgenommen. Du versuchst aus zeilen basierten Datensätzen eine Spaltenmatrix mit einem Window aufzubauen und kannst dabei nichtmal auf exakt gleiche Zeitstempel zurück greifen.

Ich würde gar nicht erst versuchen das alles in ein einziges SQL zu packen sondern die Zieldatenstruktur nach und nach durch verschactelte Abfragen im PHP aufzubauen. Dann versteht man auch selbst besser, wie es funktioniert.

in etwa so schematisch (ist kein gültiger code) ,syntax kann anders sein


$all_sensors=array(sensor1,sensor2,sensor3);
$time1=time() ; //start
$time2=time()+3600; //ende
for ($sensor in $all_sensors){
   $sql="select max(sensorwert) as start,'' as ende  from mesurements where Sensorid=$sensor and  answerdate<=$time1 union select '' as start, max(sensorwert) from mesurements as ende where Sensorid=$sensor and  answerdate<=$time2";
   ..execute..
   $mysqldata=..fetch array.. //1. Zeile hat start wert
.  $startwert=§mysldata['start']
      $mysqldata=..fetch array.. //2. Zeile hat Ende wert
   $endewert=$mysqldata['ende'];
   $diff=$endewert-$startwert;
   $data[$sensor]=$diff;
}


in $data hast Du dann die Differenz pro Sensor

Mit steigender Datensatzanzahl wird das aber durch die Max-Funktionen inperformant, da muss man mit Indexen und anderen Zugriffen arbeiten.

Eine Timeseries-Datenbank wie <InfluxDB>kann das z.B. viel besse von Hause aus und macht es deutlich einfacher zu programmieren.

Was spricht dagegen das in EXCEL zu machen? Stichwort Aggregatfunktion und Pivottabelle. Da sollte das möglich sein. Ich hab sowas ähnliches zur Betriebsdatenauswertung gemacht. War aber auch nicht ganz so einfach! Dabei ist das Intervall alle 8 Stunden, das sollte sich aber von der Logik auch auf eine Stunde umschreiben lassen.

Gruss Tommy

Zunächst einmal vielen Dank für eure Beiträge

@elektroniktommy: In Excel hab ich das bereits am Laufen, allerdings exportiere ich dazu die Differenz der Zähler stündlich per FTP und nutze das dann wiederum in Excel…klappt an sich hervorragend…wollte das Ganze aber aus verschiedenen Gründen noch als html…

@tommi
Vielen Dank für deine Hilfe, ich werde mir das mal in Ruhe anschauen…wenn ich dich richtig verstehe, wäre eine SQL Tabelle mit den Spalten AnswerDate, SensorID7, SensorID86, SensorIDxy usw. besser geeignet? Leuchtet mir irgendwie ein…werde ich dem Datenlogger aber vermutlich nicht beibringen können schätze ich :frowning:
Eine (evtl. dumme) Idee meinerseits: Ich kann ja per FTP in regelmäßigen Abständen eine Textdatei generieren, bei der ich vorgeben kann, was da drin steht (wie ich es für excel nutze)…wenn ich hier nun als Spalte AnswerDate, SensorID7, Sensor ID86, usw…generiere, lässt sich das dann evtl. wieder in eine SQL Tabelle einlesen und nutzen?
Sorry, falls die Idee doof ist, aber würde doch evtl. Sinn machen, oder?

Die Sensoren in einer Zeile als Spalten unterzubringen macht Sinn, wenn das das Answerdatum für alle gleich ist. Dann hat man alle schon mal Werte, die zu einem Zeitpunkt zusammen gehören mit eine Abfrage. Wenn die nicht exakt zur kleichen Zeit kommen, könnte man den Zeitstempell evtl. auf volle Minuten oder gar 1h runden, wenn nur ein Wert pro Stunde kommt. Das macht die Abfrage deuitlich einfacher…

Wenn Text-Datei dann empfehle ich im CSV Format CSV-Dateien lassen sich ganz gut direkt laden.

Was ist das für ein Datenlogger?

Du kannst die Datenbank auch mit Hilfe von Powerquerry direkt in Excel einlesen! Damit sparst du dir den Umweg per FTP.

Gruss Tommy

Es handelt sich um diese Zähler: https://www.wut.de/e-57652-ww-dade-000.php
Dann gibt es noch diese motherbox:WuT Datenblatt: Motherbox 3

Die Zähler selber können per FTP in regeläßigen Abständen per FTP die gewünschten Parameter irgendwo ablegen, die motherbox hingegen sammelt die Daten aus allen Zählern und speichert die Daten im internen SQL Server…

Werd mir das Ganze nochmal anschauen, soweit ich bisher sehe, kann ich der motherbox nicht sagen, wie die SQL Tabelle aussehen soll :frowning:

Ich kann zwar auf der motherbox eine eigenen Datenbank und Tabellen anlegen, blick da aber noch nicht ganz durch…evtl. kann man ja irgendwie automatisiert die Daten aus der nativen SQL DB in eine von mir angelegte migrieren und dann dort den Aufbau der Tabellen so wählen, wie ich es brauche…

Hätte da noch eine Verständnisfrage :

Zum Testen und zum Kapieren hab ich folgendes:

$all_sensors=86;

$sql = 
"SELECT 
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$Abfragezeit+3600) And SensorID=$all_sensors Order By AnswerDate DESC Limit 0,1) - (SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate=$Abfragezeit) And SensorID=

$all_sensors) AS Differenz$all_sensors";

Kreige damit die Werte für den Zähler mit der ID 86…ersetze ich die 86 z.Bsp. durch die 7, kriege ich logischerweise die Werte für den Zähler mit der ID 7…

Könnte ich nun z.Bsp. $all_senors=array(7, 86, 87, 21); nutzen um in einer Art Schleife die 4 Zähler 7,86,87 und 21 abzufragen?:confused:

Nur damit ich es kapiere…weil so häte ich mir das jetzt zusamemngereimt, klappt aber leider nicht…

Ich weiß nicht, wie die PHP SQL Bibliotheken funktionieren, aber von SQL her kannst du mehrere IDs mit


SELECT  
(SELECT Round(VALUE,0) FROM measurement WHERE (AnswerDate<=$Abfragezeit+3600) And SensorID IN (7, 86, 87, 21) Order By AnswerDate DESC Limit 0,1)"

abfragen. Da arbeitest denke ich am besten mit dem PHP-Befehl join.

Danke Dr. Niels

Frage noch dazu: Ich gehe ja am Ende her, und will aus der Differenz die „Variablen“ Differenz7, Diferenz86, Differenz87, Differenz21 usw. generieren…das klappt dann aber mit dem „IN“ dann nicht mehr, oder?

In den Rechenoperationen hänge ich leider nicht so drin, daher kann ich das nicht beantworten. Da musst du einfach mal rumprobieren. Vielleicht kannst du da was mit JOIN auf Basis der ID machen?