Ilmaandmed

http://www.tlu.ee/~jaagup/andmed/ilm/harkutund.txt

Mitmel tunnil üldse sadas?

SELECT PR1H FROM harkutund WHERE PR1H>0;

SELECT COUNT(*) FROM harkutund WHERE PR1H>0;

+----------+
| COUNT(*) |
+----------+
|      377 |
+----------+

-- Leidke, mitmel tunnil sadas rohkem kui 2 mm

SELECT COUNT(*) FROM harkutund WHERE PR1H>2;

SELECT kuu, paev, kell,  pr1h FROM harkutund WHERE PR1H>2;

SELECT kuu, paev, kell,  pr1h FROM harkutund WHERE PR1H>1.5;

SELECT ROUND(PR1H), COUNT(*) FROM harkutund GROUP BY ROUND(PR1H);

+-------------+----------+
| ROUND(PR1H) | COUNT(*) |
+-------------+----------+
|           0 |     4188 |
|           1 |      121 |
|           2 |       22 |
|           3 |        5 |
|           4 |        5 |
|           5 |        1 |
|           6 |        1 |
|           7 |        1 |
+-------------+----------+

SELECT ROUND(PR1H) AS sademm, COUNT(*) FROM harkutund GROUP BY sademm;

SELECT ROUND(PR1H*2)/2 AS sade05mm, COUNT(*) FROM harkutund GROUP BY sade05mm;

SELECT kuu, paev, kell, PR1H, ROUND(PR1H*2)/2 AS sade05mm FROM harkutund
  WHERE PR1H>0;

|    6 |   29 | 5:00  |  1.4 |   1.5000 |
|    6 |   29 | 6:00  |  1.7 |   1.5000 |
|    6 |   29 | 7:00  |  0.6 |   0.5000 |
|    6 |   29 | 8:00  |  3.8 |   4.0000 |
|    6 |   29 | 9:00  |  1.8 |   2.0000 |
|    6 |   29 | 16:00 |  0.4 |   0.5000 |
|    6 |   29 | 17:00 |  1.3 |   1.5000 |
|    6 |   29 | 18:00 |  1.1 |   1.0000 |
|    6 |   29 | 19:00 |  0.8 |   1.0000 |
|    6 |   29 | 20:00 |  0.3 |   0.5000 |
|    6 |   29 | 23:00 |  0.5 |   0.5000 |
+------+------+-------+------+----------+


SELECT ROUND(WD1H, -1) as tuulesuund FROM harkutund;

SELECT COUNT(*), ROUND(WD1H, -1) as tuulesuund FROM harkutund GROUP BY tuulesuund;

SELECT COUNT(*), ROUND(WD1H/20)*20 as tuulesuund FROM harkutund GROUP BY tuulesuund;


+----------+------------+
| COUNT(*) | tuulesuund |
+----------+------------+
|        1 |       NULL |
|       80 |          0 |
|      333 |         20 |
|      153 |         40 |
|       92 |         60 |
|      116 |         80 |
|      159 |        100 |
|      192 |        120 |
|      358 |        140 |
|      404 |        160 |
|      370 |        180 |
|      485 |        200 |
|      362 |        220 |
|      321 |        240 |
|      282 |        260 |
|      148 |        280 |
|      119 |        300 |
|      135 |        320 |
|      160 |        340 |
|       74 |        360 |
+----------+------------+

SELECT COUNT(*), ROUND(WD1H/45)*45 as tuulesuund FROM harkutund GROUP BY tuulesuund;
+----------+------------+
| COUNT(*) | tuulesuund |
+----------+------------+
|        1 |       NULL |
|      306 |          0 |
|      345 |         45 |
|      312 |         90 |
|      717 |        135 |
|      888 |        180 |
|      811 |        225 |
|      489 |        270 |
|      300 |        315 |
|      175 |        360 |
+----------+------------+

SELECT COUNT(*), ROUND(WD1H/45)*45 as tuulesuund, AVG(WS1H)
  FROM harkutund WHERE ROUND(WD1H/45)*45 IS NOT NULL GROUP BY tuulesuund;

SELECT COUNT(*), ROUND(WD1H/45)*45 as tuulesuund, SUM(WS1H)
  FROM harkutund WHERE ROUND(WD1H/45)*45 IS NOT NULL GROUP BY tuulesuund;
  
SELECT COUNT(*), ROUND(WD1H/45)*45 as tuulesuund, SUM(WS1H*WS1H)
  FROM harkutund WHERE ROUND(WD1H/45)*45 IS NOT NULL GROUP BY tuulesuund;

+----------+------------+----------------+
| COUNT(*) | tuulesuund | SUM(WS1H*WS1H) |
+----------+------------+----------------+
|      306 |          0 |        3310.12 |
|      345 |         45 |        5964.35 |
|      312 |         90 |        3213.32 |
|      717 |        135 |        7600.00 |
|      888 |        180 |       11705.77 |
|      811 |        225 |        9099.59 |
|      489 |        270 |        6731.10 |
|      300 |        315 |        2224.93 |
|      175 |        360 |         906.76 |
+----------+------------+----------------+

-- Leidke kellaaegade kaupa, kui suur on sademete summa
 SELECT HOUR(kell), SUM(PR1H) FROM harkutund GROUP BY HOUR(kell) ORDER BY HOUR(kell);
-- Leidke kellaaegade kaupa, kui suur on sademetega tundide arv
 SELECT HOUR(kell), COUNT(PR1H) FROM harkutund WHERE PR1H>0
   GROUP BY HOUR(kell) ORDER BY HOUR(kell);

 SELECT HOUR(kell), COUNT(PR1H) FROM harkutund WHERE PR1H>0.5
   GROUP BY HOUR(kell) ORDER BY HOUR(kell);

 SELECT kell, PR1H, IF(HOUR(kell)<8, "öö", "päev") FROM harkutund WHERE PR1H>0.5;
 
| 4:00  |  2.7 | öö                                |
| 5:00  |  1.4 | öö                                |
| 6:00  |  1.7 | öö                                |
| 7:00  |  0.6 | öö                                |
| 8:00  |  3.8 | päev                              |
| 9:00  |  1.8 | päev                              |
| 17:00 |  1.3 | päev                              |
| 18:00 |  1.1 | päev                              |
| 19:00 |  0.8 | päev                              |
+-------+------+-----------------------------------+


 SELECT COUNT(*),  IF(HOUR(kell)<8, "öö", "päev") AS aeg 
   FROM harkutund WHERE PR1H>0.5 GROUP BY aeg;

+----------+-------+
| COUNT(*) | aeg   |
+----------+-------+
|       41 | öö    |
|       91 | päev  |
+----------+-------+