C++Guns – RoboBlog

08.09.2013

SQL

Filed under: — Thomas @ 15:09

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änderungen finden für gegebenen Zeitraum

SELECT 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"

csv importieren wo das Datumsformat falsch ist. Floatingpoint Zahlen mit Komma nach Punkt convertieren
LOAD 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, ',', '.');

import Tarrenz. Mit den Himmelsrichtungen gibts Probleme. Im Kompass steht WNW im Datensatz NWW
LOAD 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);

Vier in der ersten Reihe
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1`<= 7 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2`<= 7 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3`<= 7 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4`<= 7 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5`<= 7 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6`<= 7 THEN 1 ELSE 0 END) ) >= 4

Zweite Zeile
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`, `ZZ`, `SZ`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 8 AND`GZ1`<= 14 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 8 AND`GZ2`<= 14 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 8 AND`GZ3`<= 14 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 8 AND`GZ4`<= 14 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 8 AND`GZ5`<= 14 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 8 AND`GZ6`<= 14 THEN 1 ELSE 0 END) ) >= 4

Dritte Zeile
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 15 AND`GZ1`<= 21 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 15 AND`GZ2`<= 21 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 15 AND`GZ3`<= 21 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 15 AND`GZ4`<= 21 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 15 AND`GZ5`<= 21 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 15 AND`GZ6`<= 21 THEN 1 ELSE 0 END) ) >= 4

4 Reihe4
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 22 AND`GZ1`<= 28 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 22 AND`GZ2`<= 28 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 22 AND`GZ3`<= 28 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 22 AND`GZ4`<= 28 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 22 AND`GZ5`<= 28 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 22 AND`GZ6`<= 28 THEN 1 ELSE 0 END) ) >= 4

4 Reihe5
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 29 AND`GZ1`<= 35 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 29 AND`GZ2`<= 35 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 29 AND`GZ3`<= 35 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 29 AND`GZ4`<= 35 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 29 AND`GZ5`<= 35 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 29 AND`GZ6`<= 35 THEN 1 ELSE 0 END) ) >= 4

4Reihe6
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 36 AND`GZ1`<= 42 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 36 AND`GZ2`<= 42 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 36 AND`GZ3`<= 42 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 36 AND`GZ4`<= 42 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 36 AND`GZ5`<= 42 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 36 AND`GZ6`<= 42 THEN 1 ELSE 0 END) ) >= 4

4Reihe7
SELECT `Id`,`Datum`,`GZ1`, `GZ2`, `GZ3`, `GZ4`, `GZ5`, `GZ6`
FROM `Ziehungen`
WHERE
(
(CASE WHEN `GZ1` >= 43 AND`GZ1`<= 49 THEN 1 ELSE 0 END) + (CASE WHEN `GZ2` >= 43 AND`GZ2`<= 49 THEN 1 ELSE 0 END) + (CASE WHEN `GZ3` >= 43 AND`GZ3`<= 49 THEN 1 ELSE 0 END) + (CASE WHEN `GZ4` >= 43 AND`GZ4`<= 49 THEN 1 ELSE 0 END) + (CASE WHEN `GZ5` >= 43 AND`GZ5`<= 49 THEN 1 ELSE 0 END) + (CASE WHEN `GZ6` >= 43 AND`GZ6`<= 49 THEN 1 ELSE 0 END) ) >= 4

Mittelwert & Abweichung der Anzahl der Tage zwischen den Ziehungen
SELECT
avg(datediff(T2.Datum, T1.DAtum)),
std(datediff(T2.Datum, T1.DAtum))
FROM `test` AS T1 LEFT JOIN `test` AS T2
ON T2.Id = T1.ID+1

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress