CREATE TABLE ilmharkus(
   kuunumber INT,
   paevanumber INT,
   kellaaeg TIME,
   sademed FLOAT,
   niiskus INT,
   tempkesk FLOAT,
   tempmin FLOAT,
   tempmax FLOAT,
   tuulesuund INT,
   tuulekiirus FLOAT,
   tuulemaxkiirus FLOAT
);

LOAD DATA INFILE '/home/jaagup/public_html/2016/abproj/harkuilm.txt'
INTO TABLE ilmharkus
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Leia, mitmel tunnil aastas sadas
SELECT COUNT(*) FROM ilmharkus WHERE sademed>0;
      mitu tundi on aastas kokku
SELECT COUNT(*) FROM ilmharkus;
      mitmel protsendil tundidest aastas sadas
SELECT COUNT(*)/(SELECT COUNT(*) FROM ilmharkus) FROM ilmharkus WHERE sademed>0;
SELECT 3+2;
SELECT 
  ROUND((SELECT COUNT(*) FROM ilmharkus WHERE sademed>0) /
  (SELECT COUNT(*) FROM ilmharkus) * 100, 1) AS protsent;

* Sademete tundide arv millimeetri kaupa
SELECT COUNT(*), ROUND(sademed) FROM ilmharkus GROUP BY ROUND(sademed);

+----------+----------------+
| COUNT(*) | ROUND(sademed) |
+----------+----------------+
|     8477 |              0 |
|      197 |              1 |
|       52 |              2 |
|       13 |              3 |
|       10 |              4 |
|        2 |              5 |
|        1 |              6 |
|        3 |              7 |
|        2 |              8 |
|        1 |             10 |
|        1 |             11 |
|        1 |             12 |
+----------+----------------+

SELECT COUNT(*), CEILING(sademed) FROM ilmharkus GROUP BY CEILING(sademed);
  --CEILING ümardab üles

-- kokkuvõttev tabel, kus on näha sademeteta tunnid, alla 3 mm sademetega ja muud

SELECT if(sademed>3, "sajab", "ei saja") FROM ilmharkus LIMIT 5; 
SELECT sademed, if(sademed>3, "sajab", if(sademed>0, "tibutab", "ei saja")) FROM ilmharkus;

SELECT COUNT(*), 
  if(sademed>3, "sajab", if(sademed>0, "tibutab", "ei saja")) AS tugevus 
  FROM ilmharkus
  GROUP BY tugevus;


mysql> SELECT COUNT(*),
    ->   if(sademed>3, "sajab", if(sademed>0, "tibutab", "ei saja")) AS tugevus
    ->   FROM ilmharkus
    ->   GROUP BY tugevus;
+----------+---------+
| COUNT(*) | tugevus |
+----------+---------+
|     7998 | ei saja |
|       22 | sajab   |
|      740 | tibutab |
+----------+---------+

* Koostage sarnane analüüs tuule keskmise tugevuse kohta
-- Jaotage 1m/s kaupa tugevused automaatselt gruppidesse ja kuvage sagedused
SELECT ROUND(tuulekiirus), COUNT(*) FROM ilmharkus GROUP BY ROUND(tuulekiirus);

+--------------------+----------+
| ROUND(tuulekiirus) | COUNT(*) |
+--------------------+----------+
|                  0 |      244 |
|                  1 |     1286 |
|                  2 |     2360 |
|                  3 |     1793 |
|                  4 |     1504 |
|                  5 |      808 |
|                  6 |      562 |
|                  7 |      148 |
|                  8 |       46 |
|                  9 |        7 |
|                 10 |        2 |
+--------------------+----------+


-- Koostage tuule maksimumtugevuse kohta oma määratud piiridega grupid
-- kuvage nende sagedusi (tuulevaikus, nõrk tuul, tugev tuul, torm)

SELECT COUNT(*), if(tuulemaxkiirus<2, "tuulevaikus", 
    if(tuulemaxkiirus<5, "nõrk tuul",
    if(tuulemaxkiirus<15, "tugev tuul", "torm"))) AS tuul
     FROM ilmharkus GROUP BY tuul;


mysql> SELECT COUNT(*), if(tuulemaxkiirus<2, "tuulevaikus",
    ->     if(tuulemaxkiirus<5, "nõrk tuul",
    ->     if(tuulemaxkiirus<15, "tugev tuul", "torm"))) AS tuul
    ->      FROM ilmharkus GROUP BY tuul;
+----------+-------------+
| COUNT(*) | tuul        |
+----------+-------------+
|     3043 | nõrk tuul   |
|       43 | torm        |
|     5076 | tugev tuul  |
|      598 | tuulevaikus |
+----------+-------------+


SELECT COUNT(*), if(tuulemaxkiirus<2, "tuulevaikus", 
    if(tuulemaxkiirus<5, "nõrk tuul",
    if(tuulemaxkiirus<15, "tugev tuul", "torm"))) AS tuul
     FROM ilmharkus GROUP BY tuul
	 ORDER BY FIELD(tuul, 'tuulevaikus', 'nõrk tuul', 'tugev tuul', 'torm');
	  

+----------+-------------+
| COUNT(*) | tuul        |
+----------+-------------+
|      598 | tuulevaikus |
|     3043 | nõrk tuul   |
|     5076 | tugev tuul  |
|       43 | torm        |
+----------+-------------+

-- Leia iga ööpäeva suurim ja vähim tunni keskmine temperatuur
-- Loenda, kui palju iga täisarvulist temperatuurierinevust päevas on

SELECT kuunumber, paevanumber, MIN(tempkesk), MAX(tempkesk) FROM ilmharkus GROUP BY kuunumber, paevanumber;
+-----------+-------------+---------------+---------------+
| kuunumber | paevanumber | MIN(tempkesk) | MAX(tempkesk) |
+-----------+-------------+---------------+---------------+
|         1 |           1 |          -1.2 |           4.4 |
|         1 |           2 |          -1.2 |           0.4 |
|         1 |           3 |          -0.2 |           0.4 |
|         1 |           4 |           0.4 |           4.7 |
|         1 |           5 |           0.2 |           4.7 |
|         1 |           6 |           0.2 |           3.1 |
|         1 |           7 |           2.5 |           5.8 |
|         1 |           8 |           4.9 |           6.3 |
|         1 |           9 |           4.2 |           5.4 |
|         1 |          10 |          -0.3 |           4.1 |
|         1 |          11 |          -1.5 |          -0.3 |

SELECT kuunumber, paevanumber, ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe 
  FROM ilmharkus GROUP BY kuunumber, paevanumber;
  

mysql> SELECT kuunumber, paevanumber, ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe
    ->   FROM ilmharkus GROUP BY kuunumber, paevanumber LIMIT 5;
+-----------+-------------+------+
| kuunumber | paevanumber | vahe |
+-----------+-------------+------+
|         1 |           1 |    6 |
|         1 |           2 |    2 |
|         1 |           3 |    1 |
|         1 |           4 |    4 |
|         1 |           5 |    4 |
+-----------+-------------+------+
 

 
SELECT vahe, COUNT(*) FROM 
(SELECT ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe 
  FROM ilmharkus GROUP BY kuunumber, paevanumber) AS abitabel
 GROUP BY vahe;
 
+------+----------+
| vahe | COUNT(*) |
+------+----------+
|    1 |       20 |
|    2 |       29 |
|    3 |       35 |
|    4 |       38 |
|    5 |       35 |
|    6 |       33 |
|    7 |       32 |
|    8 |       33 |
|    9 |       22 |
|   10 |       32 |
|   11 |       21 |
|   12 |       17 |
|   13 |        8 |
|   14 |        4 |
|   15 |        2 |
|   16 |        2 |
|   18 |        2 |
+------+----------+

-- Proovige sama päringu tulemusi eraldi konkreetsel kuul

SELECT vahe, COUNT(*) FROM 
(SELECT ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe 
  FROM ilmharkus WHERE kuunumber = 2 GROUP BY kuunumber, paevanumber) AS abitabel
 GROUP BY vahe;
 
SELECT vahe, COUNT(*) FROM 
(SELECT ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe 
  FROM ilmharkus WHERE kuunumber IN (1, 2, 3) GROUP BY kuunumber, paevanumber) AS abitabel
 GROUP BY vahe;
mysql> SELECT vahe, COUNT(*) FROM
    -> (SELECT ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe
    ->   FROM ilmharkus WHERE kuunumber IN (1, 2, 3) GROUP BY kuunumber, paevanumber) AS abitabel
    ->  GROUP BY vahe;
+------+----------+
| vahe | COUNT(*) |
+------+----------+
|    1 |       12 |
|    2 |       10 |
|    3 |        8 |
|    4 |       14 |
|    5 |       13 |
|    6 |        6 |
|    7 |        9 |
|    8 |        6 |
|    9 |        7 |
|   10 |        3 |
|   11 |        1 |
|   12 |        1 |
+------+----------+


SELECT vahe, COUNT(*), GROUP_CONCAT(kuunumber) FROM 
(SELECT kuunumber, ROUND(MAX(tempkesk)-MIN(tempkesk)) AS vahe 
  FROM ilmharkus WHERE kuunumber IN (1, 2, 3) GROUP BY kuunumber, paevanumber) AS abitabel
 GROUP BY vahe;
 
+------+----------+-----------------------------+
| vahe | COUNT(*) | GROUP_CONCAT(kuunumber)     |
+------+----------+-----------------------------+
|    1 |       12 | 2,1,1,2,3,1,1,2,3,2,3,1     |
|    2 |       10 | 2,2,2,2,2,1,2,1,2,2         |
|    3 |        8 | 1,2,1,3,2,2,2,2             |
|    4 |       14 | 1,2,1,2,2,2,1,3,2,1,1,3,3,2 |
|    5 |       13 | 2,2,1,1,3,1,1,2,1,1,3,1,3   |
|    6 |        6 | 3,3,3,1,3,1                 |
|    7 |        9 | 3,2,3,1,1,1,3,3,3           |
|    8 |        6 | 1,1,3,2,1,3                 |
|    9 |        7 | 3,3,3,3,1,1,3               |
|   10 |        3 | 3,3,3                       |
|   11 |        1 | 3                           |
|   12 |        1 | 3                           |
+------+----------+-----------------------------+