Kas kaste on vihma karjapoiss?

* Sademete järgi arvutamine ei toiminud, sest kaste puhul piisavat vedelikukogust ei registreeritud

Esmased katsed niiskuse ja sademete hindamise alal
SELECT * FROM `ilmharkus` WHERE niiskus>95 AND kuunumber=7

SELECT * FROM `ilmharkus` WHERE sademed>0 AND kuunumber=7

SELECT DISTINCT paevanumber FROM ilmharkus WHERE kuunumber=7

SELECT paevanumber, 
    (SELECT niiskus FROM ilmharkus AS t2 
	   WHERE kellaaeg='04:00:00' AND t1.kuunumber=t2.kuunumber AND t2.paevanumber=t1.paevanumber)>95 AS kell7
   FROM ilmharkus AS t1 WHERE kuunumber=6
   GROUP BY paevanumber
   

SELECT paevanumber, 
    (SELECT niiskus FROM ilmharkus AS t2 
	   WHERE kellaaeg='04:00:00' AND t1.kuunumber=t2.kuunumber AND 
	          t2.paevanumber=t1.paevanumber)>95 AS kell7,
	(SELECT ROUND(SUM(sademed), 1) FROM ilmharkus AS t3
	  WHERE t1.kuunumber=t3.kuunumber AND t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'08:00:00' AND t3.kellaaeg<'14:00:00') AS sademed
   FROM ilmharkus AS t1 WHERE kuunumber=7
   GROUP BY paevanumber
   

CREATE TABLE ilmjuulis AS 
SELECT * FROM ilmharkus WHERE kuunumber=7;

CREATE TABLE ilmjuulis_niiskus AS
SELECT paevanumber, 
    (SELECT niiskus FROM ilmjuulis AS t2 
	   WHERE kellaaeg='04:00:00' AND 
	          t2.paevanumber=t1.paevanumber)>95 AS kell7,
	(SELECT ROUND(SUM(sademed), 1) FROM ilmjuulis AS t3
	  WHERE t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'08:00:00' AND t3.kellaaeg<'14:00:00') AS sademed
   FROM ilmjuulis AS t1 
   GROUP BY paevanumber
   
SELECT kell7, sademed>0, COUNT(*) FROM ilmjuulis_niiskus 
  GROUP BY kell7, sademed>0;
  

DROP TABLE IF EXISTS ilmkuus; 
CREATE TABLE ilmkuus AS 
SELECT * FROM ilmharkus WHERE kuunumber=8;

DROP TABLE IF EXISTS ilmkuus_niiskus;  
CREATE TABLE ilmkuus_niiskus AS
SELECT paevanumber, 
    (SELECT niiskus FROM ilmkuus AS t2 
	   WHERE kellaaeg='04:00:00' AND 
	          t2.paevanumber=t1.paevanumber)>95 AS kell7,
	(SELECT ROUND(SUM(sademed), 1) FROM ilmkuus AS t3
	  WHERE t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'08:00:00' AND t3.kellaaeg<'14:00:00') AS sademed
   FROM ilmkuus AS t1 
   GROUP BY paevanumber;
   
SELECT kell7, sademed>0, COUNT(*) FROM ilmkuus_niiskus 
  GROUP BY kell7, sademed>0;
   
  #Pärast vahetundi lisame juurde samade päevade sademete summa vahemikus kell 11-17

#Püüdke näidata seost või seose puudmist (juuli)kuu 
  öise keskmise niiskuse ja sellele järgneva päeva
  keskmise niiskusega
 Nt kõrvutage iga päeva kohta niiskus kell 00:00-04:00 ning 12:00-16:00
 
kuupaev, ookesk, paevakesk

SELECT paevanumber, 
    (SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t2 
	   WHERE t2.kellaaeg>='00:00:00' AND t2.kellaaeg<='04:00:00' AND 
	          t2.paevanumber=t1.paevanumber) AS ooniiskus,
	(SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t3
	  WHERE t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'12:00:00' AND t3.kellaaeg<'16:00:00') AS paevaniiskus
   FROM ilmkuus AS t1 
   GROUP BY paevanumber;
   

+-------------+-----------+--------------+
| paevanumber | ooniiskus | paevaniiskus |
+-------------+-----------+--------------+
|           1 |        98 |           51 |
|           2 |        94 |           58 |
|           3 |        74 |           45 |
|           4 |        79 |           41 |
|           5 |        72 |           30 |
|           6 |        67 |           32 |
|           7 |        72 |           72 |
|           8 |        96 |           52 |
|           9 |        98 |           57 |
|          10 |        99 |           54 |
|          11 |        86 |           53 |
|          12 |       100 |           56 |
|          13 |        90 |           50 |
|          14 |        92 |           59 |
|          15 |        98 |           60 |
|          16 |        99 |           83 |
|          17 |        98 |           55 |
|          18 |        96 |           74 |
|          19 |        99 |           64 |
|          20 |        88 |           81 |
|          21 |        99 |           61 |
|          22 |        96 |           73 |
|          23 |       100 |           83 |
|          24 |        99 |           63 |
|          25 |       100 |           99 |
|          26 |        96 |           99 |
|          27 |       100 |           83 |
|          28 |        93 |           80 |
|          29 |       100 |           71 |
|          30 |       100 |           48 |
|          31 |        95 |           68 |
+-------------+-----------+--------------+


SELECT AVG(ooniiskus), STD(ooniiskus),  AVG(paevaniiskus), STD(paevaniiskus) FROM 
(SELECT paevanumber, 
    (SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t2 
	   WHERE t2.kellaaeg>='00:00:00' AND t2.kellaaeg<='04:00:00' AND 
	          t2.paevanumber=t1.paevanumber) AS ooniiskus,
	(SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t3
	  WHERE t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'12:00:00' AND t3.kellaaeg<'16:00:00') AS paevaniiskus
   FROM ilmkuus AS t1 
   GROUP BY paevanumber) AS vahetabel;


CREATE TABLE ilm_niiskused AS   
SELECT paevanumber, 
    (SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t2 
	   WHERE t2.kellaaeg>='00:00:00' AND t2.kellaaeg<='04:00:00' AND 
	          t2.paevanumber=t1.paevanumber) AS ooniiskus,
	(SELECT ROUND(AVG(niiskus)) FROM ilmkuus AS t3
	  WHERE t3.paevanumber=t1.paevanumber
	    AND t3.kellaaeg>'12:00:00' AND t3.kellaaeg<'16:00:00') AS paevaniiskus
   FROM ilmkuus AS t1 
   GROUP BY paevanumber;

SELECT * FROM ilm_niiskused
UNION ALL
SELECT 'keskmine', AVG(ooniiskus), AVG(paevaniiskus) FROM ilm_niiskused;

+-------------+-----------+--------------+
| paevanumber | ooniiskus | paevaniiskus |
+-------------+-----------+--------------+
| 1           |   98.0000 |      51.0000 |
| 2           |   94.0000 |      58.0000 |
| 3           |   74.0000 |      45.0000 |
| 4           |   79.0000 |      41.0000 |
| 5           |   72.0000 |      30.0000 |
| 6           |   67.0000 |      32.0000 |
| 7           |   72.0000 |      72.0000 |
| 8           |   96.0000 |      52.0000 |
| 9           |   98.0000 |      57.0000 |
| 10          |   99.0000 |      54.0000 |
| 11          |   86.0000 |      53.0000 |
| 12          |  100.0000 |      56.0000 |
| 13          |   90.0000 |      50.0000 |
| 14          |   92.0000 |      59.0000 |
| 15          |   98.0000 |      60.0000 |
| 16          |   99.0000 |      83.0000 |
| 17          |   98.0000 |      55.0000 |
| 18          |   96.0000 |      74.0000 |
| 19          |   99.0000 |      64.0000 |
| 20          |   88.0000 |      81.0000 |
| 21          |   99.0000 |      61.0000 |
| 22          |   96.0000 |      73.0000 |
| 23          |  100.0000 |      83.0000 |
| 24          |   99.0000 |      63.0000 |
| 25          |  100.0000 |      99.0000 |
| 26          |   96.0000 |      99.0000 |
| 27          |  100.0000 |      83.0000 |
| 28          |   93.0000 |      80.0000 |
| 29          |  100.0000 |      71.0000 |
| 30          |  100.0000 |      48.0000 |
| 31          |   95.0000 |      68.0000 |
| keskmine    |   92.6774 |      63.0645 |
+-------------+-----------+--------------+