Page 1 of 2
Consecutive wet & dry days
Posted: Tue 23 Jul 2013 4:44 pm
by AndyH
Hi all,
I have been using mySQL to process the dayfile for my website for quite a while and would like to add another statistic to my website - but I cannot get my head around it and wondered whether anyone out there had done a similar job.
Basically, I want to be able to produce the top x consecutive wet days and the same for dry days. I would like to do this server side using mySQL. Has anyone got some help/advice.
If it makes things clearer, I have done similar with highest temperatures, wind gusts, average temperature etc etc on this page:
http://www.sompt.com/weather/history/to ... php?top=10
Thank you!
Re: Consecutive wet & dry days
Posted: Tue 23 Jul 2013 8:16 pm
by tobyspond
Re: Consecutive wet & dry days
Posted: Wed 24 Jul 2013 5:16 pm
by AndyH
Thanks Kerry - that has given me some good pointers. The result I now get is the max consecutive days with/without rain but not the top 10 etc. If I change the LIMIT from 1 then it gives me more data but I want the unique top 10 days rather than including those on the run up to number 1 (if that makes sense)
I did write and try the code below which I wrote which works, EXCEPT that it does not carry on the count past the end of the month. i.e when the month rolls over it starts the count again:
set @count=0;
select
dayfile.logdate, dayfile.totrainfall, t2.totrainfall,
@count := if(dayfile.totrainfall > 0 AND t2.totrainfall > 0, @count + 1, 1)
from dayfile
left join dayfile t2 on dayfile.logdate = t2.logdate + 1
where dayfile.totrainfall > 0
It also takes a while to run but that's another issue I will deal with later.
Any ideas how I can get around the month roll over issue?
Re: Consecutive wet & dry days
Posted: Wed 24 Jul 2013 7:23 pm
by mcrossley
The best I can come up with quickly is below. Separate queries for wet and dry days, but they return the top 10, and take into account 0.1mm of rain being counted as a 'dry' day - you may want to tweak that.
Processes 1300 rows in my dayfile table in around 70ms on my old Pentium M laptop.
DRY DAYS
** DELETED INCORRECT CODE **
Code: Select all
SELECT MAX(LogDate) AS date, COUNT(*) cnt
FROM (
SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0.1),
df.LogDate
FROM (
SELECT @r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10
[/s]
WET DAYS
** DELETED INCORRECT CODE **
Code: Select all
SELECT MAX(LogDate) AS date, COUNT(*) cnt
FROM (
SELECT @r := @r + (@rain != (TotRainFall < 0.2) OR @rain IS NULL) AS gr,
@rain := (TotRainFall < 0.2),
df.LogDate
FROM (
SELECT @r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10
[/s]
Re: Consecutive wet & dry days
Posted: Wed 24 Jul 2013 7:43 pm
by AndyH
Oh my word - I think that does it!
I have just tried the dry days code and it returns pretty much what I expected (and wanted). I shall check it against my data to make sure!
Thank you very much - I knew I could rely on this excellent forum to help me out with a problem I have been struggling with over the last few weeks.
I shall try and implement this into my website over the next week or so (partner and kids back from holiday tomorrow so time will be limited) and let you know the outcome.
Thank you again!
Re: Consecutive wet & dry days
Posted: Wed 24 Jul 2013 9:03 pm
by mcrossley
Let us know if it checks out, I only checked the top value. I may be using this one myself sometime
Re: Consecutive wet & dry days
Posted: Wed 24 Jul 2013 10:56 pm
by tobyspond
Hi Mark,
Thanks for this. I could not figure out how to create the grouping variable. However, I'm getting a mixture of wet and dry days. So I modified your code a bit to get it to work for me. Here it is:
for Wet Days
SELECT max(LogDate) AS date, COUNT(*) cnt, wd
FROM (
SELECT @r := @r + (@rain != (TotRainFall > 0) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0), if( totrainfall <0.01, "D", "W") as wd,
df.LogDate
FROM (
SELECT
@r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x where wd="W"
GROUP BY gr
ORDER BY cnt DESC
limit 10
Dry Days
SELECT max(LogDate) AS date, COUNT(*) cnt, wd
FROM (
SELECT @r := @r + (@rain != (TotRainFall > 0) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0), if( totrainfall <0.01, "D", "W") as wd,
df.LogDate
FROM (
SELECT
@r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x where wd="D"
GROUP BY gr
ORDER BY cnt DESC
limit 10
Kerry
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 1:18 am
by mcrossley
Hi Kerry, I just tried your version (changing the dry day threshold) but the top outout does not match my records?
A refinement of my version would be to pull back both the from and to dates. Using dry days as an example...
DELETED - INCORRECT CODE
Code: Select all
SELECT MIN(LogDate) AS date_from,
MAX(LogDate) AS date_to,
COUNT(*) cnt
FROM
(
SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0.1),
df.LogDate
FROM
(
SELECT @r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10
[/s]
Gives...
Code: Select all
date_from date_to cnt
2013-02-15 2013-03-06 20
2013-07-04 2013-07-22 19
2010-04-08 2010-04-24 17
2012-06-26 2012-07-11 16
2011-02-01 2011-02-16 16
2013-03-27 2013-04-10 15
2012-03-19 2012-04-01 14
2011-05-05 2011-05-18 14
2011-12-01 2011-12-13 13
2011-04-23 2011-05-04 12
Its 2:18 am now - time to try and sleep! I'll look at your query again tomorrow...
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 8:01 am
by mcrossley
Kerry, your code works better when I edit it correctly! It also has the advantage you can use a single query and get either dry or wet days back by changing a single variable. Here is your code reformatted and taking my 0.1mm threshold into account. I also added the start date...
Code: Select all
SET @wetOrDry = "D";
SELECT MIN(LogDate) AS date_from,
MAX(LogDate) AS date_to,
COUNT(*) cnt
FROM
(
SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr,
@rain := (TotRainFall > 0.1),
IF (TotRainFall < 0.2, "D", "W") AS wd,
df.LogDate
FROM
(
SELECT @r := 0,
@rain := NULL
) vars,
dayfile df
ORDER BY LogDate
) x
WHERE wd = @wetOrDry
GROUP BY gr
ORDER BY cnt DESC
LIMIT 10
The two Dry day outputs compared...
Code: Select all
My version Kerry's version
========== ===============
date_from date_to cnt date_from date_to cnt
---------- ---------- --- ---------- ---------- ---
2013-02-15 2013-03-06 20 2013-02-15 2013-03-06 20
2013-07-04 2013-07-22 19 2013-07-04 2013-07-22 19
2010-04-08 2010-04-24 17 2010-04-08 2010-04-24 17
2012-06-26 2012-07-11 16 2013-03-27 2013-04-10 15
2011-02-01 2011-02-16 16 2012-03-19 2012-04-01 14
2013-03-27 2013-04-10 15 2011-04-23 2011-05-04 12
2012-03-19 2012-04-01 14 2013-05-31 2013-06-11 12
2011-05-05 2011-05-18 14 2010-12-17 2010-12-27 11
2011-12-01 2011-12-13 13 2012-05-20 2012-05-30 11
2011-04-23 2011-05-04 12 2013-04-29 2013-05-08 10
As you can see there are differences further down the list, I'm going to have to get the dayfile into Excel to check this - I suspect Kerry's is correct
EDIT: Kerry's is of course correct - I'll delete details of my query above to avoid confusion.
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 12:26 pm
by tobyspond
Mark,
Like your reformatted version!
Users should note that the TotRainFall can be in mm or inches depending on their set up and should change those values accordingly.
Kerry
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 4:26 pm
by AndyH
Thank you again for all your efforts. Just implemented this code (with a few very minor changes to customise it for my webpages) and it works brilliantly. Hopefully others will find this useful for their pages too.
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 4:28 pm
by steve
I'm not following the SQL discussion, but presumably this would all be much easier if Cumulus logged the current wet and dry spell figures to dayfile.txt?
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 4:31 pm
by mcrossley
Yes, but where would the fun be in that!
Re: Consecutive wet & dry days
Posted: Thu 25 Jul 2013 4:50 pm
by AndyH
steve wrote:I'm not following the SQL discussion, but presumably this would all be much easier if Cumulus logged the current wet and dry spell figures to dayfile.txt?
Yes Steve, it probably would. If it's possible then I suppose see whether there is anyone else who thinks it would be useful and go with the majority. Personally, I prefer to get this sort of stuff my a database it's just that my MySQL skills are rather rusty having left the IT world a number of years back.
Re: Consecutive wet & dry days
Posted: Sun 06 Apr 2014 7:02 pm
by mm23
Hi,
i am looking for data for longest dry and wet spell. As I understand the wabtags is consecutive rain and wet days from ini pages.
Which is the way to put that data on my month and year table....or should I simply use the carry code for database from above?
Thanks everybody!