Tuule kiiruse uuring mysql> SELECT * FROM ilmharkus LIMIT 5; +-----------+-------------+----------+---------+---------+----------+---------+---------+------------+-------------+----------------+ | kuunumber | paevanumber | kellaaeg | sademed | niiskus | tempkesk | tempmin | tempmax | tuulesuund | tuulekiirus | tuulemaxkiirus | +-----------+-------------+----------+---------+---------+----------+---------+---------+------------+-------------+----------------+ | 1 | 1 | 00:00:00 | 0 | 86 | 4.4 | 4.4 | 4.5 | 221 | 5.6 | 10.2 | | 1 | 1 | 01:00:00 | 0 | 87 | 4.3 | 4.1 | 4.5 | 214 | 4.3 | 9 | | 1 | 1 | 02:00:00 | 0 | 89 | 3.9 | 3.8 | 4.1 | 205 | 3.9 | 9.1 | | 1 | 1 | 03:00:00 | 0 | 89 | 3.6 | 3.4 | 3.8 | 208 | 4.3 | 10.5 | | 1 | 1 | 04:00:00 | 0 | 89 | 3.3 | 3 | 3.4 | 211 | 4.4 | 10.1 | +-----------+-------------+----------+---------+---------+----------+---------+---------+------------+-------------+----------------+ 5 rows in set (0.00 sec) Tuulekiiruse ülevaade SELECT MIN(tuulekiirus), AVG(tuulekiirus), STD(tuulekiirus), MAX(tuulekiirus) FROM ilmharkus; +------------------+--------------------+--------------------+-------------------+ | MIN(tuulekiirus) | AVG(tuulekiirus) | STD(tuulekiirus) | MAX(tuulekiirus) | +------------------+--------------------+--------------------+-------------------+ | 0 | 3.0021118734397643 | 1.6111126295192435 | 9.600000381469727 | +------------------+--------------------+--------------------+-------------------+ SELECT kuunumber, MIN(tuulekiirus), AVG(tuulekiirus), STD(tuulekiirus), MAX(tuulekiirus) FROM ilmharkus GROUP BY kuunumber; +-----------+------------------+--------------------+--------------------+------------------+ | kuunumber | MIN(tuulekiirus) | AVG(tuulekiirus) | STD(tuulekiirus) | MAX(tuulekiirus) | +-----------+------------------+--------------------+--------------------+------------------+ | 1 | 0.1 | 2.936693549807113 | 1.4398179180125492 | 7.3 | | 2 | 0 | 3.340029762625428 | 1.4005808801157227 | 7.3 | | 3 | 0.1 | 3.5052419433730746 | 1.874033651290923 | 9.6 | | 4 | 0.1 | 3.0945833378471432 | 1.6826437928125375 | 8.6 | | 5 | 0.1 | 2.7866935492343 | 1.4863077005221295 | 7.7 | | 6 | 0 | 2.5040277794106967 | 1.3761653277928594 | 7.3 | | 7 | 0 | 2.5446236576604586 | 1.520946270860811 | 7.7 | | 8 | 0 | 2.719892473510837 | 1.3177987475042068 | 7.2 | | 9 | 0 | 2.41763888379145 | 1.5010672020639124 | 7.4 | | 10 | 0.2 | 3.478629036776481 | 1.9022094607275633 | 8.3 | | 11 | 0.1 | 2.95013888662474 | 1.427838289557084 | 7.1 | | 12 | 0.3 | 3.746236558082283 | 1.594803998859687 | 8.9 | +-----------+------------------+--------------------+--------------------+------------------+ SELECT kellaaeg, MIN(tuulekiirus), AVG(tuulekiirus), STD(tuulekiirus), MAX(tuulekiirus) FROM ilmharkus GROUP BY kellaaeg; +----------+------------------+--------------------+--------------------+------------------+ | kellaaeg | MIN(tuulekiirus) | AVG(tuulekiirus) | STD(tuulekiirus) | MAX(tuulekiirus) | +----------+------------------+--------------------+--------------------+------------------+ | 00:00:00 | 0 | 2.640547948197959 | 1.601078318905234 | 8.5 | | 01:00:00 | 0 | 2.617260275189191 | 1.5617703849009017 | 8.9 | | 02:00:00 | 0 | 2.640273967201579 | 1.55565074479315 | 8.4 | | 03:00:00 | 0 | 2.6528767148313457 | 1.5450047472019872 | 8.5 | | 04:00:00 | 0 | 2.7147945254020494 | 1.5703416605261467 | 8.1 | | 05:00:00 | 0.1 | 2.798904109756424 | 1.5182899219225328 | 7.5 | | 06:00:00 | 0.2 | 2.918630136120809 | 1.4905118280758565 | 7 | | 07:00:00 | 0.2 | 3.0419178136407514 | 1.4971833496450473 | 7.9 | | 08:00:00 | 0.3 | 3.2405479452381396 | 1.5153279810075253 | 7.1 | | 09:00:00 | 0.3 | 3.4468493131742086 | 1.5942094491521137 | 7.8 | | 10:00:00 | 0.1 | 3.569315072873684 | 1.6351752689187133 | 8.9 | | 11:00:00 | 0.1 | 3.6630137011000556 | 1.6477221154002588 | 9.4 | | 12:00:00 | 0.2 | 3.654794525487782 | 1.6342432590191396 | 9.6 | | 13:00:00 | 0.1 | 3.6290411054475666 | 1.5778537321711554 | 9.6 | | 14:00:00 | 0.5 | 3.5364383624024587 | 1.5029882359396984 | 8.7 | | 15:00:00 | 0.8 | 3.401369868402612 | 1.4687142087792027 | 7.7 | | 16:00:00 | 0.6 | 3.1753424500765868 | 1.5058589370224569 | 8 | | 17:00:00 | 0.3 | 2.9473972564690736 | 1.5279586413378012 | 6.8 | | 18:00:00 | 0 | 2.760000000200043 | 1.581516531311626 | 7.3 | | 19:00:00 | 0.1 | 2.6515068467961598 | 1.6351210956536486 | 7.3 | | 20:00:00 | 0.1 | 2.599178091814257 | 1.6229600657977274 | 7.8 | | 21:00:00 | 0.1 | 2.5493150678807743 | 1.5848013760742934 | 7.7 | | 22:00:00 | 0.2 | 2.5912328794802706 | 1.5482309423335268 | 7.9 | | 23:00:00 | 0 | 2.6101369853705574 | 1.5113202936668106 | 7.8 | +----------+------------------+--------------------+--------------------+------------------+ SELECT kuunumber, kellaaeg, MIN(tuulekiirus), AVG(tuulekiirus), STD(tuulekiirus), MAX(tuulekiirus) FROM ilmharkus GROUP BY kuunumber, kellaaeg; +-----------+----------+------------------+--------------------+--------------------+------------------+ | kuunumber | kellaaeg | MIN(tuulekiirus) | AVG(tuulekiirus) | STD(tuulekiirus) | MAX(tuulekiirus) | +-----------+----------+------------------+--------------------+--------------------+------------------+ | 1 | 00:00:00 | 0.5 | 2.925806474301123 | 1.4788766073155704 | 5.6 | | 1 | 01:00:00 | 0.6 | 2.977419353300525 | 1.3969343656295172 | 5.3 | | 1 | 02:00:00 | 0.6 | 2.9129032011955016 | 1.3992490598141223 | 5.6 | | 1 | 03:00:00 | 0.5 | 2.8516128947657924 | 1.375328924217796 | 5.4 | | 1 | 04:00:00 | 0.5 | 2.8387096889557375 | 1.3964649923635626 | 5.6 | | 1 | 05:00:00 | 0.3 | 2.8096774297375835 | 1.3852576039628748 | 5.3 | | 1 | 06:00:00 | 0.2 | 2.867741913084061 | 1.4898337266388637 | 5.7 | | 1 | 07:00:00 | 0.6 | 2.9258064127737478 | 1.3737088017839703 | 5.7 | | 1 | 08:00:00 | 0.7 | 2.987096769194449 | 1.3195165185595275 | 5.2 | | 1 | 09:00:00 | 0.6 | 3.0967741993165787 | 1.3808562013841903 | 6.3 | | 1 | 10:00:00 | 0.7 | 3.141935496560989 | 1.3895402471175005 | 6.4 | | 1 | 11:00:00 | 0.4 | 3.041935525594219 | 1.2916042195924586 | 5.1 | | 1 | 12:00:00 | 0.2 | 2.919354827653977 | 1.3702882552262239 | 5.3 | | 1 | 13:00:00 | 0.1 | 2.8903225845386906 | 1.3838596931952047 | 5.3 | | 1 | 14:00:00 | 1 | 2.8838709708183043 | 1.3308005679935977 | 5.5 | | 1 | 15:00:00 | 0.9 | 2.7645161344159033 | 1.5266553026626737 | 6.2 | | 1 | 16:00:00 | 0.8 | 2.82258064323856 | 1.5818954955418534 | 6.7 | | 1 | 17:00:00 | 0.6 | 2.887096760734435 | 1.665928355237854 | 6.7 | | 1 | 18:00:00 | 0.6 | 2.877419352531433 | 1.6233596656686349 | 7.1 | | 1 | 19:00:00 | 0.9 | 3.0548387419792915 | 1.5999675173655197 | 7.3 | | 1 | 20:00:00 | 0.9 | 3.067741936252963 | 1.5147420115348416 | 6.8 | | 1 | 21:00:00 | 0.8 | 3.0161290495626387 | 1.39216629463278 | 6.5 | | 1 | 22:00:00 | 0.5 | 2.9645161244177047 | 1.3477695711741566 | 5.8 | | 1 | 23:00:00 | 0.6 | 2.9548387104465115 | 1.3830398250805767 | 5.5 | | 2 | 00:00:00 | 0.3 | 3.3642857181174413 | 1.4931305414617064 | 5.8 | | 2 | 01:00:00 | 0.1 | 3.2928571373756443 | 1.4596791440225625 | 6.5 | | 2 | 02:00:00 | 0 | 3.3142857274838855 | 1.5486663662537503 | 6.8 | | 2 | 03:00:00 | 0.4 | 3.299999995955399 | 1.540176236470747 | 6.6 | | 2 | 04:00:00 | 0.5 | 3.399999995316778 | 1.6095696052265271 | 7.3 | | 2 | 05:00:00 | 0.8 | 3.324999985950334 | 1.5120055461017061 | 6.3 | | 2 | 06:00:00 | 1.1 | 3.299999999148505 | 1.4565124511444747 | 6 | | 2 | 07:00:00 | 0.9 | 3.2750000059604645 | 1.4110849487665296 | 6.2 | | 2 | 08:00:00 | 1.3 | 3.232142840112959 | 1.3946462757218052 | 6.1 | | 2 | 09:00:00 | 1.3 | 3.3999999931880405 | 1.4114328900653008 | 6.6 | | 2 | 10:00:00 | 1.4 | 3.4178571658475057 | 1.2784068514697384 | 5.8 | | 2 | 11:00:00 | 1.4 | 3.517857130084719 | 1.332037517949057 | 6 | | 2 | 12:00:00 | 1.8 | 3.5464285697255815 | 1.3631586840379908 | 6.3 | | 2 | 13:00:00 | 1.4 | 3.500000021287373 | 1.3559393431532616 | 6.4 | | 2 | 14:00:00 | 1.4 | 3.30357141154153 | 1.3265104774615593 | 6.1 | | 2 | 15:00:00 | 1.4 | 3.2500000255448476 | 1.2222637358848005 | 6 | tunni keskmine tuulekiirus <3m/s >=3m/s päev 1993 2387 öö 2683 1697 SELECT COUNT(*) FROM ilmharkus WHERE kellaaeg >= '07:00' AND kellaaeg < '19:00'; +----------+ | COUNT(*) | +----------+ | 4380 | +----------+ SELECT COUNT(*) FROM ilmharkus WHERE kellaaeg >= '07:00' AND kellaaeg < '19:00' AND tuulekiirus < 3; +----------+ | COUNT(*) | +----------+ | 1993 | +----------+ SELECT COUNT(*) FROM ilmharkus WHERE kellaaeg >= '07:00' AND kellaaeg < '19:00' AND tuulekiirus >= 3; +----------+ | COUNT(*) | +----------+ | 2387 | +----------+ SELECT COUNT(*) FROM ilmharkus WHERE NOT (kellaaeg >= '07:00' AND kellaaeg < '19:00') AND tuulekiirus < 3; SELECT COUNT(*) FROM ilmharkus WHERE NOT (kellaaeg >= '07:00' AND kellaaeg < '19:00') AND tuulekiirus >= 3; SELECT kellaaeg, tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus LIMIT 10; +----------+-------------+----------+----------+ | kellaaeg | tuulekiirus | paevaosa | tuulisus | +----------+-------------+----------+----------+ | 00:00:00 | 5.6 | öö | tuuline | | 01:00:00 | 4.3 | öö | tuuline | | 02:00:00 | 3.9 | öö | tuuline | | 03:00:00 | 4.3 | öö | tuuline | | 04:00:00 | 4.4 | öö | tuuline | | 05:00:00 | 4.4 | öö | tuuline | | 06:00:00 | 4.7 | öö | tuuline | | 07:00:00 | 5.7 | päev | tuuline | | 08:00:00 | 5.1 | päev | tuuline | | 09:00:00 | 4.6 | päev | tuuline | +----------+-------------+----------+----------+ SELECT paevaosa, tuulisus, COUNT(*) FROM (SELECT tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus) AS t1 GROUP BY paevaosa, tuulisus; +----------+----------+----------+ | paevaosa | tuulisus | COUNT(*) | +----------+----------+----------+ | öö | tuuline | 1697 | | öö | vaikne | 2683 | | päev | tuuline | 2387 | | päev | vaikne | 1993 | +----------+----------+----------+ Mitmel protsendil öödest oli vaikne 2683/(2683+1697)=61% Mitmel protsendil päevadest oli vaikne 1993/(2387+1993)=46% Tuulevaikseid öid (<3m/s) on 61/46=1,33 korda rohkem kui tuulevaikseid päevi riskisuhe, relative risk -- Arvutage samad protsendid ja suhe jaanuarikuu kohta ning juulikuu kohta SELECT paevaosa, tuulisus, COUNT(*) FROM (SELECT tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus WHERE kuunumber=1) AS t1 GROUP BY paevaosa, tuulisus; +----------+----------+----------+ | paevaosa | tuulisus | COUNT(*) | +----------+----------+----------+ | öö | tuuline | 185 | | öö | vaikne | 187 | | päev | tuuline | 173 | | päev | vaikne | 199 | +----------+----------+----------+ 187/(185+187) 199/(173+199) SELECT kuunumber, paevaosa, tuulisus, COUNT(*) FROM (SELECT kuunumber, tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus) AS t1 GROUP BY kuunumber, paevaosa, tuulisus; +-----------+----------+----------+----------+ | kuunumber | paevaosa | tuulisus | COUNT(*) | +-----------+----------+----------+----------+ | 1 | öö | tuuline | 185 | | 1 | öö | vaikne | 187 | | 1 | päev | tuuline | 173 | | 1 | päev | vaikne | 199 | | 2 | öö | tuuline | 204 | | 2 | öö | vaikne | 132 | | 2 | päev | tuuline | 178 | DROP TABLE IF EXISTS ilm_tuuled; CREATE TABLE ilm_tuuled SELECT kellaaeg, tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus; SET @vaiksedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='vaikne'); SET @vaiksedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='vaikne'); SET @tuulisedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='tuuline'); SET @tuulisedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='tuuline'); SELECT 'vaiksedpäevalprotsent', @vaiksedpaeval/(@vaiksedpaeval+@tuulisedpaeval); SELECT 'vaiksedööselprotsent', @vaiksedoosel/(@vaiksedoosel+@tuulisedoosel); SELECT 'riskisuhe', (@vaiksedoosel/(@vaiksedoosel+@tuulisedoosel))/(@vaiksedpaeval/(@vaiksedpaeval+@tuulisedpaeval)) AS suhe; DROP TABLE IF EXISTS ilm_tuuled; CREATE TABLE ilm_tuuled SELECT kellaaeg, tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus WHERE kuunumber=7; SET @vaiksedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='vaikne'); SET @vaiksedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='vaikne'); SET @tuulisedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='tuuline'); SET @tuulisedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='tuuline'); SELECT 'vaiksedpäevalprotsent', @vaiksedpaeval/(@vaiksedpaeval+@tuulisedpaeval); SELECT 'vaiksedööselprotsent', @vaiksedoosel/(@vaiksedoosel+@tuulisedoosel); SELECT 'riskisuhe', (@vaiksedoosel/(@vaiksedoosel+@tuulisedoosel))/(@vaiksedpaeval/(@vaiksedpaeval+@tuulisedpaeval)) AS suhe; DROP PROCEDURE IF EXISTS kuudeandmed; DELIMITER // CREATE PROCEDURE kuudeandmed() BEGIN DECLARE loendur INT; SET loendur=1; WHILE loendur<5 DO SELECT loendur; SET loendur=loendur+1; END WHILE; END // DELIMITER ; CALL kuudeandmed() DELIMITER // CREATE PROCEDURE proov() BEGIN SELECT "tere"; END // DELIMITER ; DROP PROCEDURE IF EXISTS proov; DELIMITER // CREATE PROCEDURE proov() BEGIN SET @kogus=1; WHILE @kogus<5 DO SET @kogus=@kogus+1; SELECT @kogus; END WHILE; END // DELIMITER ; CALL proov(); DROP PROCEDURE IF EXISTS kuudeandmed; DELIMITER // CREATE PROCEDURE kuudeandmed() BEGIN DROP TABLE IF EXISTS kuudevastused; CREATE TABLE kuudevastused (kuunr INT, riskisuhe DOUBLE); SET @loendur=1; WHILE @loendur<=12 DO DROP TABLE IF EXISTS ilm_tuuled; CREATE TABLE ilm_tuuled SELECT kellaaeg, tuulekiirus, IF(kellaaeg >= '07:00' AND kellaaeg < '19:00', 'päev', 'öö') AS paevaosa, IF(tuulekiirus<3, 'vaikne', 'tuuline') AS tuulisus FROM ilmharkus WHERE kuunumber=@loendur; SET @vaiksedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='vaikne'); SET @vaiksedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='vaikne'); SET @tuulisedpaeval=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='päev' AND tuulisus='tuuline'); SET @tuulisedoosel=(SELECT COUNT(*) FROM ilm_tuuled WHERE paevaosa='öö' AND tuulisus='tuuline'); SET @suhe=(@vaiksedoosel/(@vaiksedoosel+@tuulisedoosel))/(@vaiksedpaeval/(@vaiksedpaeval+@tuulisedpaeval)); INSERT INTO kuudevastused VALUES (@loendur, @suhe); SET @loendur=@loendur+1; END WHILE; SELECT * FROM kuudevastused; END // DELIMITER ; CALL kuudeandmed();