Keerukamad andmepäringud ilmamõõtmiste kohta.

Kas kaste on vihma karjapoiss?

Uue (abi)tabeli loomine, kus iga päeva kohta on üks rida. 
Üks tulp näitab sademete hulka kell 4 (GMT), teine sademete hulka kell 12(GMT)
ainult juulikuus kuu=7

kuupäev sademed4 sademed12


SELECT DISTINCT paevanumber FROM ilmharkus WHERE kuunumber=7;

SELECT DISTINCT paevanumber, 
   (SELECT sademed FROM ilmharkus AS t2
     WHERE kuunumber=7 AND kellaaeg="04:00:00" AND t1.paevanumber=t2.paevanumber ) 
   FROM ilmharkus AS t1 WHERE kuunumber=7;

SELECT sademed, paevanumber, kellaaeg FROM ilmharkus WHERE kuunumber=7 AND sademed>0;
+---------+-------------+----------+
| sademed | paevanumber | kellaaeg |
+---------+-------------+----------+
|     0.6 |           1 | 11:00:00 |
|     1.3 |           1 | 12:00:00 |
|     1.7 |           1 | 13:00:00 |
|     0.6 |           1 | 14:00:00 |
|     0.6 |           1 | 15:00:00 |
|     2.5 |           3 | 21:00:00 |
|       3 |           3 | 22:00:00 |
|     3.6 |           3 | 23:00:00 |
|     0.4 |           4 | 02:00:00 |
|     0.9 |           4 | 03:00:00 |
|    11.3 |           7 | 18:00:00 |
|     0.1 |           7 | 19:00:00 |
|     0.2 |          10 | 05:00:00 |
|     0.1 |          10 | 06:00:00 |
|     0.3 |          13 | 19:00:00 |
|     0.6 |          13 | 20:00:00 |
|     1.1 |          20 | 09:00:00 |
|      12 |          29 | 11:00:00 |
|     0.1 |          29 | 12:00:00 |
|     6.7 |          31 | 12:00:00 |
|     0.2 |          31 | 14:00:00 |
|       1 |          31 | 16:00:00 |
+---------+-------------+----------+

Andmeid järeldamiseks liialt vähe - ka teiste suvekuude juures


Uus hüpotees: kas külmemad päevad on vihmasemad

SELECT AVG(tempkesk) FROM ilmharkus WHERE kuunumber=7;

Tabel, juulikuu 2014:

paevanumber, kesktemp, vihmatundide_arv

SELECT DISTINCT paevanumber FROM ilmharkus WHERE kuunumber=7;

SELECT DISTINCT paevanumber, AVG(tempkesk), SUM(sademed) FROM ilmharkus 
   WHERE kuunumber=7
   GROUP BY paevanumber;
+-------------+--------------------+---------------------+
| paevanumber | AVG(tempkesk)      | SUM(sademed)        |
+-------------+--------------------+---------------------+
|           1 | 12.383333325386047 |   4.800000071525574 |
|           2 | 14.237500111262003 |                   0 |
|           3 | 15.570833365122477 |   9.099999904632568 |
|           4 | 15.633333325386047 |  1.2999999821186066 |
|           5 |  15.25000011920929 |                   0 |
|           6 |  19.99583335717519 |                   0 |
|           7 | 21.787500023841858 |   11.40000019222498 |
|           8 | 22.458333412806194 |                   0 |
|           9 |             21.125 |                   0 |
|          10 | 16.487499992052715 | 0.30000000447034836 |
|          11 | 14.916666626930237 |                   0 |
|          12 |  16.59999978542328 |                   0 |
|          13 | 21.641666690508526 |  0.9000000357627869 |
|          14 | 18.262500206629436 |                   0 |
|          15 |  18.84166673819224 |                   0 |
|          16 | 17.641666650772095 |                   0 |
|          17 | 18.395833492279053 |                   0 |
|          18 | 18.229166626930237 |                   0 |
|          19 | 18.170833388964336 |                   0 |
|          20 | 17.916666626930237 |   1.100000023841858 |
|          21 | 19.766666690508526 |                   0 |
|          22 | 20.133333444595337 |                   0 |
|          23 |  22.22083322207133 |                   0 |
|          24 | 21.508333325386047 |                   0 |
|          25 | 22.958333333333332 |                   0 |
|          26 | 22.887499968210857 |                   0 |
|          27 |  24.49583347638448 |                   0 |
|          28 |   24.5583332379659 |                   0 |
|          29 |  22.25000015894572 |  12.100000001490116 |
|          30 | 23.454166650772095 |                   0 |
|          31 | 21.670833349227905 |   7.899999812245369 |
+-------------+--------------------+---------------------+

SELECT AVG(tk), sadesum>0
FROM
 (SELECT DISTINCT paevanumber, AVG(tempkesk) AS tk, SUM(sademed) AS sadesum 
   FROM ilmharkus 
   WHERE kuunumber=7
   GROUP BY paevanumber) AS vahetabel
  GROUP BY sadesum>0;
 
+--------------------+-----------+
| AVG(tk)            | sadesum>0 |
+--------------------+-----------+
| 19.823106084809158 |         0 |
|  18.37129631748906 |         1 |
+--------------------+-----------+

SELECT AVG(tk), sadesum>0
FROM
 (SELECT DISTINCT paevanumber, AVG(tempkesk) AS tk, SUM(sademed) AS sadesum 
   FROM ilmharkus 
   WHERE kuunumber=8
   GROUP BY paevanumber) AS vahetabel
  GROUP BY sadesum>0;
 

SELECT AVG(tk), sadesum>0
FROM
 (SELECT DISTINCT paevanumber, AVG(tempkesk) AS tk, SUM(sademed) AS sadesum 
   FROM ilmharkus 
   WHERE kuunumber=6
   GROUP BY paevanumber) AS vahetabel
  GROUP BY sadesum>0;
 
 
-- Pange sama päring tööle kolme suvekuu peale kokku
  kuunumber IN (6, 7, 8)
-- tuli vastus, kus pole näha, et vihmane ilm oleks jahedam.
-- Põhjus aga tõenäoliselt vigases grupeerimises
   
SELECT AVG(tk), STD(tk), sadesum>0, COUNT(*), STD(tk)/SQRT(COUNT(*))
FROM
 (SELECT DISTINCT CONCAT(kuunumber, '-', paevanumber) AS kuupaev, 
     AVG(tempkesk) AS tk, SUM(sademed) AS sadesum 
   FROM ilmharkus 
   WHERE kuunumber IN (6, 7, 8)
   GROUP BY kuupaev) AS vahetabel
  GROUP BY sadesum>0;
  
+--------------------+-------------------+-----------+----------+------------------------+
| AVG(tk)            | STD(tk)           | sadesum>0 | COUNT(*) | STD(tk)/SQRT(COUNT(*)) |
+--------------------+-------------------+-----------+----------+------------------------+
| 17.942795144394044 | 4.529730799551138 |         0 |       48 |     0.6538103241193471 |
|  14.94744319536469 | 3.779346801961821 |         1 |       44 |     0.5697579679425898 |
+--------------------+-------------------+-----------+----------+------------------------+

______

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus;


mysql> SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus;
+-------------------+------------------+
| AVG(tempkesk)     | STD(tempkesk)    |
+-------------------+------------------+
| 6.791632423605175 | 8.94809803983727 |
+-------------------+------------------+

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus
  WHERE kuunumber IN (6, 7, 8);
  
+--------------------+-------------------+
| AVG(tempkesk)      | STD(tempkesk)     |
+--------------------+-------------------+
| 16.510235516597394 | 5.274613999458991 |
+--------------------+-------------------+

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus
  WHERE kuunumber IN (6, 7, 8) AND 
   kellaaeg>'00:00' AND kellaaeg<'03:00';
   
+--------------------+--------------------+
| AVG(tempkesk)      | STD(tempkesk)      |
+--------------------+--------------------+
| 13.247282629427703 | 3.8969321288299175 |
+--------------------+--------------------+

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus
  WHERE kuunumber IN (6, 7, 8) AND 
   kellaaeg>'12:00' AND kellaaeg<'15:00';

+------------------+-------------------+
| AVG(tempkesk)    | STD(tempkesk)     |
+------------------+-------------------+
| 19.3929347836453 | 5.435981593320462 |
+------------------+-------------------+

-- Leidke märtsikuu keskmine temperatuur ja standardhälve
SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus WHERE kuunumber = 3;

+--------------------+--------------------+
| AVG(tempkesk)      | STD(tempkesk)      |
+--------------------+--------------------+
| 1.9599462331182533 | 3.4003965670122267 |
+--------------------+--------------------+


-- Leidke märtsikuu keskmine temperatuur ja standardhälve kella 10 ja 13 (GMT) vahel 

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus WHERE kuunumber = 3
  AND kellaaeg>='10:00' AND kellaaeg<='13:00';

+-------------------+-------------------+
| AVG(tempkesk)     | STD(tempkesk)     |
+-------------------+-------------------+
| 4.495967734184477 | 3.068134849626719 |
+-------------------+-------------------+

  
  
-- Leidke märtsikuu keskmine temperatuur ja standardhälve kella 0 ja 3 (GMT) vahel 

SELECT AVG(tempkesk), STD(tempkesk) FROM ilmharkus WHERE kuunumber = 3
  AND kellaaeg>='0:00' AND kellaaeg<='3:00';

+----------------------+------------------+
| AVG(tempkesk)        | STD(tempkesk)    |
+----------------------+------------------+
| -0.09274194005035585 | 2.90322885783221 |
+----------------------+------------------  
 
-- Kuva märtsikuu iga kuupäeva kohta temperatuur kell 1 öösel (GMT)

SELECT paevanumber, tempkesk FROM ilmharkus WHERE kuunumber=3 AND kellaaeg='01:00';
+-------------+----------+
| paevanumber | tempkesk |
+-------------+----------+
|           1 |     -1.7 |
|           2 |      0.5 |
|           3 |      0.5 |
|           4 |      0.2 |
|           5 |     -0.1 |
|           6 |       -1 |
|           7 |      0.3 |
|           8 |      3.2 |
|           9 |      0.2 |
|          10 |      4.1 |
|          11 |      2.2 |
|          12 |     -0.1 |
|          13 |        0 |
|          14 |      3.4 |
|          15 |      1.1 |
|          16 |     -0.2 |
|          17 |     -7.1 |
|          18 |     -6.5 |
|          19 |     -1.3 |
|          20 |     -6.4 |
|          21 |        4 |
|          22 |      3.3 |
|          23 |      0.8 |
|          24 |      0.4 |
|          25 |     -1.6 |
|          26 |      3.9 |
|          27 |     -0.8 |
|          28 |     -2.2 |
|          29 |     -2.6 |
|          30 |     -0.6 |
|          31 |      1.6 |
+-------------+----------+
31 rows in set (0.01 sec)

SELECT paevanumber, tempkesk AS oosel,
  (SELECT tempkesk FROM ilmharkus AS t2 
    WHERE t1.kuunumber=t2.kuunumber AND t1.paevanumber=t2.paevanumber
	  AND kellaaeg='13:00') AS paeval 
 FROM ilmharkus AS t1 WHERE kuunumber=3 AND kellaaeg='01:00';

-- Lisage juurde temperatuur samade päevade õhtul kell 18 (GMT)
 
+-------------+-------+----------------------+
| paevanumber | oosel | paeval               |
+-------------+-------+----------------------+
|           1 |  -1.7 |    2.799999952316284 |
|           2 |   0.5 |   1.2000000476837158 |
|           3 |   0.5 |                  0.5 |
|           4 |   0.2 |    4.699999809265137 |
|           5 |  -0.1 |    6.099999904632568 |
|           6 |    -1 |    5.599999904632568 |
|           7 |   0.3 |    5.400000095367432 |
|           8 |   3.2 |    5.599999904632568 |
|           9 |   0.2 |                    9 |
|          10 |   4.1 |    8.800000190734863 |
|          11 |   2.2 |    6.199999809265137 |
|          12 |  -0.1 |    6.400000095367432 |
|          13 |     0 |    9.399999618530273 |
|          14 |   3.4 |    5.800000190734863 |
|          15 |   1.1 |  0.10000000149011612 |
|          16 |  -0.2 | -0.30000001192092896 |
|          17 |  -7.1 | -0.10000000149011612 |
|          18 |  -6.5 |    2.799999952316284 |
|          19 |  -1.3 | -0.10000000149011612 |
|          20 |  -6.4 |    1.399999976158142 |
|          21 |     4 |    9.199999809265137 |
|          22 |   3.3 |    7.599999904632568 |
|          23 |   0.8 |                    7 |
|          24 |   0.4 |    8.100000381469727 |
|          25 |  -1.6 |   3.5999999046325684 |
|          26 |   3.9 |    6.099999904632568 |
|          27 |  -0.8 |    6.900000095367432 |
|          28 |  -2.2 |    6.599999904632568 |
|          29 |  -2.6 |                    8 |
|          30 |  -0.6 |    7.599999904632568 |
|          31 |   1.6 |    1.899999976158142 |
+-------------+-------+----------------------+


SELECT paevanumber, tempkesk AS oosel,
  ROUND((SELECT tempkesk FROM ilmharkus AS t2 
    WHERE t1.kuunumber=t2.kuunumber AND t1.paevanumber=t2.paevanumber
	  AND kellaaeg='13:00'), 1) AS paeval,
  ROUND((SELECT tempkesk FROM ilmharkus AS t3 
    WHERE t1.kuunumber=t3.kuunumber AND t1.paevanumber=t3.paevanumber
	  AND kellaaeg='18:00'), 1) AS ohtul 
 FROM ilmharkus AS t1 WHERE kuunumber=3 AND kellaaeg='01:00';

-- Lisage juurde temperatuur samade päevade õhtul kell 18 (GMT)


+-------------+-------+--------+-------+
| paevanumber | oosel | paeval | ohtul |
+-------------+-------+--------+-------+
|           1 |  -1.7 |    2.8 |   1.0 |
|           2 |   0.5 |    1.2 |   0.9 |
|           3 |   0.5 |    0.5 |   0.4 |
|           4 |   0.2 |    4.7 |   3.2 |
|           5 |  -0.1 |    6.1 |   2.3 |
|           6 |    -1 |    5.6 |   2.3 |
|           7 |   0.3 |    5.4 |   1.4 |
|           8 |   3.2 |    5.6 |   2.7 |
|           9 |   0.2 |    9.0 |   6.4 |
|          10 |   4.1 |    8.8 |   4.9 |
|          11 |   2.2 |    6.2 |   2.1 |
|          12 |  -0.1 |    6.4 |   2.2 |
|          13 |     0 |    9.4 |   5.2 |
|          14 |   3.4 |    5.8 |   2.5 |
|          15 |   1.1 |    0.1 |   0.4 |
|          16 |  -0.2 |   -0.3 |  -1.0 |
|          17 |  -7.1 |   -0.1 |  -2.9 |
|          18 |  -6.5 |    2.8 |  -0.3 |
|          19 |  -1.3 |   -0.1 |  -1.8 |
|          20 |  -6.4 |    1.4 |  -1.6 |
|          21 |     4 |    9.2 |   6.3 |
|          22 |   3.3 |    7.6 |   4.4 |
|          23 |   0.8 |    7.0 |   2.8 |
|          24 |   0.4 |    8.1 |   1.9 |
|          25 |  -1.6 |    3.6 |   5.6 |
|          26 |   3.9 |    6.1 |   2.8 |
|          27 |  -0.8 |    6.9 |   2.3 |
|          28 |  -2.2 |    6.6 |   3.6 |
|          29 |  -2.6 |    8.0 |   2.6 |
|          30 |  -0.6 |    7.6 |   2.7 |
|          31 |   1.6 |    1.9 |  -1.0 |
+-------------+-------+--------+-------+

DROP TABLE IF EXISTS kuuilm;
CREATE TABLE kuuilm AS
SELECT paevanumber, tempkesk AS oosel,
  ROUND((SELECT tempkesk FROM ilmharkus AS t2 
    WHERE t1.kuunumber=t2.kuunumber AND t1.paevanumber=t2.paevanumber
	  AND kellaaeg='13:00'), 1) AS paeval,
  ROUND((SELECT tempkesk FROM ilmharkus AS t3 
    WHERE t1.kuunumber=t3.kuunumber AND t1.paevanumber=t3.paevanumber
	  AND kellaaeg='18:00'), 1) AS ohtul 
 FROM ilmharkus AS t1 WHERE kuunumber=3 AND kellaaeg='01:00'; 

SELECT 'keskmine', AVG(oosel), AVG(paeval), AVG(ohtul) FROM kuuilm;
+----------+----------------------+-------------+------------+
| keskmine | AVG(oosel)           | AVG(paeval) | AVG(ohtul) |
+----------+----------------------+-------------+------------+
| keskmine | -0.08064515456076592 |     4.96452 |    2.07419 |
+----------+----------------------+-------------+------------+

SELECT * FROM kuuilm 
UNION ALL
SELECT 'keskmine', AVG(oosel), AVG(paeval), AVG(ohtul) FROM kuuilm;

-- Lisage vastuse juurde eraldi reana ka standardhälve

+-------------+----------------------+----------------------+----------------------+
| paevanumber | oosel                | paeval               | ohtul                |
+-------------+----------------------+----------------------+----------------------+
| 1           |  -1.7000000476837158 |                  2.8 |                    1 |
| 2           |                  0.5 |                  1.2 |                  0.9 |
| 3           |                  0.5 |                  0.5 |                  0.4 |
| 4           |  0.20000000298023224 |                  4.7 |                  3.2 |
| 5           | -0.10000000149011612 |                  6.1 |                  2.3 |
| 6           |                   -1 |                  5.6 |                  2.3 |
| 7           |  0.30000001192092896 |                  5.4 |                  1.4 |
| 8           |    3.200000047683716 |                  5.6 |                  2.7 |
| 9           |  0.20000000298023224 |                    9 |                  6.4 |
| 10          |    4.099999904632568 |                  8.8 |                  4.9 |
| 11          |    2.200000047683716 |                  6.2 |                  2.1 |
| 12          | -0.10000000149011612 |                  6.4 |                  2.2 |
| 13          |                    0 |                  9.4 |                  5.2 |
| 14          |   3.4000000953674316 |                  5.8 |                  2.5 |
| 15          |    1.100000023841858 |                  0.1 |                  0.4 |
| 16          | -0.20000000298023224 | -0.30000000000000004 |                   -1 |
| 17          |   -7.099999904632568 | -0.09999999999999998 |                 -2.9 |
| 18          |                 -6.5 |                  2.8 | -0.30000000000000004 |
| 19          |  -1.2999999523162842 | -0.09999999999999998 |                 -1.8 |
| 20          |   -6.400000095367432 |                  1.4 |                 -1.6 |
| 21          |                    4 |                  9.2 |                  6.3 |
| 22          |    3.299999952316284 |                  7.6 |                  4.4 |
| 23          |    0.800000011920929 |                    7 |                  2.8 |
| 24          |   0.4000000059604645 |                  8.1 |                  1.9 |
| 25          |   -1.600000023841858 |                  3.6 |                  5.6 |
| 26          |   3.9000000953674316 |                  6.1 |                  2.8 |
| 27          |   -0.800000011920929 |                  6.9 |                  2.3 |
| 28          |   -2.200000047683716 |                  6.6 |                  3.6 |
| 29          |  -2.5999999046325684 |                    8 |                  2.6 |
| 30          |  -0.6000000238418579 |                  7.6 |                  2.7 |
| 31          |    1.600000023841858 |                  1.9 |                   -1 |
| keskmine    | -0.08064515456076592 |   4.9645161290322575 |    2.074193548387097 |
+-------------+----------------------+----------------------+----------------------+

SELECT * FROM kuuilm 
UNION ALL
SELECT 'keskmine', AVG(oosel), AVG(paeval), AVG(ohtul) FROM kuuilm
UNION ALL
SELECT 'standardhälve', STD(oosel), STD(paeval), STD(ohtul) FROM kuuilm;


+----------------+----------------------+----------------------+----------------------+
| paevanumber    | oosel                | paeval               | ohtul                |
+----------------+----------------------+----------------------+----------------------+
| 1              |  -1.7000000476837158 |                  2.8 |                    1 |
| 2              |                  0.5 |                  1.2 |                  0.9 |
| 3              |                  0.5 |                  0.5 |                  0.4 |
| 4              |  0.20000000298023224 |                  4.7 |                  3.2 |
| 5              | -0.10000000149011612 |                  6.1 |                  2.3 |
| 6              |                   -1 |                  5.6 |                  2.3 |
| 7              |  0.30000001192092896 |                  5.4 |                  1.4 |
| 8              |    3.200000047683716 |                  5.6 |                  2.7 |
| 9              |  0.20000000298023224 |                    9 |                  6.4 |
| 10             |    4.099999904632568 |                  8.8 |                  4.9 |
| 11             |    2.200000047683716 |                  6.2 |                  2.1 |
| 12             | -0.10000000149011612 |                  6.4 |                  2.2 |
| 13             |                    0 |                  9.4 |                  5.2 |
| 14             |   3.4000000953674316 |                  5.8 |                  2.5 |
| 15             |    1.100000023841858 |                  0.1 |                  0.4 |
| 16             | -0.20000000298023224 | -0.30000000000000004 |                   -1 |
| 17             |   -7.099999904632568 | -0.09999999999999998 |                 -2.9 |
| 18             |                 -6.5 |                  2.8 | -0.30000000000000004 |
| 19             |  -1.2999999523162842 | -0.09999999999999998 |                 -1.8 |
| 20             |   -6.400000095367432 |                  1.4 |                 -1.6 |
| 21             |                    4 |                  9.2 |                  6.3 |
| 22             |    3.299999952316284 |                  7.6 |                  4.4 |
| 23             |    0.800000011920929 |                    7 |                  2.8 |
| 24             |   0.4000000059604645 |                  8.1 |                  1.9 |
| 25             |   -1.600000023841858 |                  3.6 |                  5.6 |
| 26             |   3.9000000953674316 |                  6.1 |                  2.8 |
| 27             |   -0.800000011920929 |                  6.9 |                  2.3 |
| 28             |   -2.200000047683716 |                  6.6 |                  3.6 |
| 29             |  -2.5999999046325684 |                    8 |                  2.6 |
| 30             |  -0.6000000238418579 |                  7.6 |                  2.7 |
| 31             |    1.600000023841858 |                  1.9 |                   -1 |
| keskmine       | -0.08064515456076592 |   4.9645161290322575 |    2.074193548387097 |
| standardhälve  |   2.8113157741840955 |   3.0593640902704973 |   2.3045491815713017 |
+----------------+----------------------+----------------------+----------------------+

-- Lisage vastuse juurde eraldi reana ka standardhälve

SELECT paevanumber, kuunumber, tempkesk FROM ilmharkus 
  WHERE kuunumber=3 AND paevanumber=6 AND kellaaeg='13:00';

-- Kuva kõrvuti märtsikuu iga kuupäeva kohta temperatuurid kell 1 ja 13 (GMT)
-- Leidke nende temperatuuride vahed, võtke vahedest keskmine ja standardhälve

-- Koostage juulikuu kohta tabel, kus temperatuurid iga päev kell 1 ja 13 ning
-- tabeli lõpus read tulpade miinimumi, maksimumi, keskmise ja standardhälbe kohta


DROP TABLE IF EXISTS kuuilm;
CREATE TABLE kuuilm AS
SELECT paevanumber, tempkesk AS oosel,
  ROUND((SELECT tempkesk FROM ilmharkus AS t2 
    WHERE t1.kuunumber=t2.kuunumber AND t1.paevanumber=t2.paevanumber
	  AND kellaaeg='13:00'), 1) AS paeval
 FROM ilmharkus AS t1 WHERE kuunumber=7 AND kellaaeg='01:00'; 

SELECT * FROM kuuilm 
UNION ALL
SELECT 'miinimum', MIN(oosel), MIN(paeval) FROM kuuilm
UNION ALL
SELECT 'keskmine', AVG(oosel), AVG(paeval) FROM kuuilm
UNION ALL
SELECT 'maksimum', MAX(oosel), MAX(paeval) FROM kuuilm
UNION ALL
SELECT 'standardhälve', STD(oosel), STD(paeval) FROM kuuilm;


+----------------+--------------------+--------------------+
| paevanumber    | oosel              | paeval             |
+----------------+--------------------+--------------------+
| 1              |               12.5 |               11.9 |
| 2              |               11.5 |               17.2 |
| 3              | 10.800000190734863 |               19.5 |
| 4              | 13.800000190734863 |               18.7 |
| 5              | 10.600000381469727 |               16.9 |
| 6              |               13.5 |                 23 |
| 7              | 16.200000762939453 |               25.4 |
| 8              |                 19 |               26.3 |
| 9              |                 16 |               24.8 |
| 10             |                 19 |               16.2 |
| 11             | 11.199999809265137 |               16.8 |
| 12             | 12.100000381469727 |               19.8 |
| 13             | 16.299999237060547 |               25.2 |
| 14             |               17.5 |               19.9 |
| 15             | 16.399999618530273 |               22.4 |
| 16             |                 15 |               22.3 |
| 17             |                 14 |               21.7 |
| 18             | 14.199999809265137 |               23.1 |
| 19             | 14.399999618530273 |               21.7 |
| 20             |               12.5 |               21.9 |
| 21             | 13.600000381469727 |               23.6 |
| 22             |               13.5 |               23.9 |
| 23             | 17.700000762939453 |               26.3 |
| 24             | 15.699999809265137 |               26.4 |
| 25             | 17.299999237060547 |               27.1 |
| 26             | 15.300000190734863 |               27.6 |
| 27             |               16.5 |               30.2 |
| 28             | 20.200000762939453 |                 26 |
| 29             |               19.5 |               22.2 |
| 30             | 17.799999237060547 |               28.7 |
| 31             | 21.100000381469727 |               20.9 |
| miinimum       | 10.600000381469727 |               11.9 |
| keskmine       | 15.312903250417401 | 22.503225806451617 |
| maksimum       | 21.100000381469727 |               30.2 |
| standardhälve  |  2.802964095268785 |  4.063328039361707 |
+----------------+--------------------+--------------------+

DROP TABLE IF EXISTS abiilm;
CREATE TABLE abiilm AS
 SELECT tuulekiirus, tuulemaxkiirus FROM ilmharkus
   WHERE kuunumber=1 AND kellaaeg>='08:00' AND kellaaeg<='18:00';
   
SET @keskvahe=3;   
SET @puhangvahe=6;

SELECT COUNT(*) FROM abiilm WHERE tuulekiirus<@keskvahe AND tuulemaxkiirus<@puhangvahe;
SELECT COUNT(*) FROM abiilm WHERE tuulekiirus<@keskvahe AND tuulemaxkiirus>=@puhangvahe;
SELECT COUNT(*) FROM abiilm WHERE tuulekiirus>=@keskvahe AND tuulemaxkiirus<@puhangvahe;
SELECT COUNT(*) FROM abiilm WHERE tuulekiirus>=@keskvahe AND tuulemaxkiirus>=@puhangvahe;

174  10
 18 139
 
                                     Maksimumpuhangu kiirus
                                 alla keskmise   vähemalt keskmine
keskmine kiirus   alla keskmise   174               10 
              vähemalt keskmine    18              139
			  
Tuule alla keskmise keskmiste kiiruste puhul keskmisest tugevamaid puhanguid esines
  10/(174+10)=5,4% tundidest