{"id":1837,"date":"2013-09-08T15:46:03","date_gmt":"2013-09-08T14:46:03","guid":{"rendered":"http:\/\/roboblog.fatal-fury.de\/?page_id=1837"},"modified":"2013-09-17T14:35:43","modified_gmt":"2013-09-17T13:35:43","slug":"sql","status":"publish","type":"page","link":"http:\/\/roboblog.fatal-fury.de\/?page_id=1837","title":{"rendered":"SQL"},"content":{"rendered":"

Testen ob alle 3 sec ein Messwert aufgenommen wurde<\/p>\n

SELECT t1.uhrzeit, t2.uhrzeit FROM ( huxi as t1 LEFT JOIN huxi AS t2 ON( t2.messwert=t1.messwert+1 ) ) WHERE t2.messwert IS NOT NULL AND TIMESTAMPDIFF( SECOND , t2.uhrzeit, t1.uhrzeit) <> 3<\/code><\/p>\n

Starke Temperatur\u00e4nderungen finden f\u00fcr gegebenen Zeitraum
\n
\nSELECT t1.uhrzeit, t1.temperatur, t2.temperatur, (t2.temperatur - t1.temperatur) AS diff FROM ( huxi as t1 LEFT JOIN huxi AS t2 ON( t2.messwert=t1.messwert+1 ) ) WHERE t2.messwert IS NOT NULL AND abs(t2.temperatur - t1.temperatur) > 0.2 AND t1.datum = \"2013:08:25\" <\/code><\/p>\n

csv importieren wo das Datumsformat falsch ist. Floatingpoint Zahlen mit Komma nach Punkt convertieren
\nLOAD DATA LOCAL INFILE 'messwerte.csv' INTO TABLE `xxx` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (@var1, Zeit,@var2,Luftdruck,@var3,Windrichtung,`rel Luftfeuchtigkeit`) set `Datum` = STR_TO_DATE(@var1, '%d.%m.%Y'), `Temperatur` = REPLACE(@var2, ',', '.'), `Windgeschwindigkeit` = REPLACE(@var3, ',', '.'); <\/code><\/p>\n

import Tarrenz. Mit den Himmelsrichtungen gibts Probleme. Im Kompass steht WNW im Datensatz NWW
\nLOAD DATA LOCAL INFILE 'tarrenz.txt' INTO TABLE `August2013` FIELDS TERMINATED BY ' ' IGNORE 6 LINES (@dumm2, @var1, @zeit,@dummy, `rel Luftfeuchtigkeit`, Temperatur, `abs Luftdruck`, Windgeschwindigkeit, Windboeen, @windrichtung, `rel Luftdruck`, Taupunkt, Windauskuehlung, `Wind Level`, `Windboeen Level`) set `Datum` = STR_TO_DATE(@var1, '%d-%m-%Y'), `Zeit` = STR_TO_DATE(@zeit, '%H:%i'), `Windrichtung` = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@windrichtung, 'NNE', 22.5), 'NEE', 67.5), 'SEE', 112.5), 'SSE', 157.5), 'SSW', 202.5), 'SWW', 247.5), 'NWW', 292.5), 'NNW', 337.5), 'NE', 45), 'SE', 135), 'SW', 225), 'NW', 315), 'N', 360), 'E', 90), 'S', 180), 'W' , 270);
\n<\/code><\/p>\n

Vier in der ersten Reihe
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1`<= 7 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2`<= 7 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3`<= 7 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4`<= 7 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5`<= 7 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6`<= 7 THEN 1 ELSE 0 END)\n \n) >= 4<\/code><\/p>\n

Zweite Zeile
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`, `ZZ`, `SZ`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 8 AND`GZ1`<= 14 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 8 AND`GZ2`<= 14 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 8 AND`GZ3`<= 14 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 8 AND`GZ4`<= 14 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 8 AND`GZ5`<= 14 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 8 AND`GZ6`<= 14 THEN 1 ELSE 0 END)\n \n) >= 4<\/code><\/p>\n

Dritte Zeile
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 15 AND`GZ1`<= 21 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 15 AND`GZ2`<= 21 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 15 AND`GZ3`<= 21 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 15 AND`GZ4`<= 21 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 15 AND`GZ5`<= 21 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 15 AND`GZ6`<= 21 THEN 1 ELSE 0 END)\n \n) >= 4<\/code><\/p>\n

4 Reihe4
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 22 AND`GZ1`<= 28 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 22 AND`GZ2`<= 28 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 22 AND`GZ3`<= 28 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 22 AND`GZ4`<= 28 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 22 AND`GZ5`<= 28 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 22 AND`GZ6`<= 28 THEN 1 ELSE 0 END)\n \n) >= 4<\/code><\/p>\n

4 Reihe5
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 29 AND`GZ1`<= 35 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 29 AND`GZ2`<= 35 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 29 AND`GZ3`<= 35 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 29 AND`GZ4`<= 35 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 29 AND`GZ5`<= 35 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 29 AND`GZ6`<= 35 THEN 1 ELSE 0 END)\n \n) >= 4
\n<\/code><\/p>\n

4Reihe6
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 36 AND`GZ1`<= 42 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 36 AND`GZ2`<= 42 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 36 AND`GZ3`<= 42 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 36 AND`GZ4`<= 42 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 36 AND`GZ5`<= 42 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 36 AND`GZ6`<= 42 THEN 1 ELSE 0 END)\n \n) >= 4<\/code><\/p>\n

4Reihe7
\nSELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
\n FROM `Ziehungen`
\nWHERE
\n(
\n (CASE WHEN `GZ1` >= 43 AND`GZ1`<= 49 THEN 1 ELSE 0 END) + \n (CASE WHEN `GZ2` >= 43 AND`GZ2`<= 49 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ3` >= 43 AND`GZ3`<= 49 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ4` >= 43 AND`GZ4`<= 49 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ5` >= 43 AND`GZ5`<= 49 THEN 1 ELSE 0 END) +\n (CASE WHEN `GZ6` >= 43 AND`GZ6`<= 49 THEN 1 ELSE 0 END)\n \n) >= 4
\n<\/code><\/p>\n

Mittelwert & Abweichung der Anzahl der Tage zwischen den Ziehungen
\nSELECT
\navg(datediff(T2.Datum, T1.DAtum)),
\nstd(datediff(T2.Datum, T1.DAtum))
\nFROM `test` AS T1 LEFT JOIN `test` AS T2
\nON T2.Id = T1.ID+1<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

Testen ob alle 3 sec ein Messwert aufgenommen wurde SELECT t1.uhrzeit, t2.uhrzeit FROM ( huxi as t1 LEFT JOIN huxi AS t2 ON( t2.messwert=t1.messwert+1 ) ) WHERE t2.messwert IS NOT NULL AND TIMESTAMPDIFF( SECOND , t2.uhrzeit, t1.uhrzeit) 3 Starke Temperatur\u00e4nderungen finden f\u00fcr gegebenen Zeitraum SELECT t1.uhrzeit, t1.temperatur, t2.temperatur, (t2.temperatur - t1.temperatur) AS diff FROM ( […]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1837","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/pages\/1837","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1837"}],"version-history":[{"count":16,"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/pages\/1837\/revisions"}],"predecessor-version":[{"id":1858,"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=\/wp\/v2\/pages\/1837\/revisions\/1858"}],"wp:attachment":[{"href":"http:\/\/roboblog.fatal-fury.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}