MySQL to select a daterange where sum is less than a value

Question

I have MySQL database with weather info:

Date       | Rain
2001-01-01 | 0.0
2001-01-02 | 0.2
2001-01-03 | 0.0

This table has almost 20 years of data. One record per day.

I want to get all drought periods from the database. Definition: less than 0.5 mm of rain in a period of at least 20 days.

I could make a loop in PHP and iterate to all data and count the sum of the last 20 days. But I was wondering if this could be done in a MySQL statement.

To make it even more complex, I also want the durations of the drought period in days.

So if a drought period starts om 2020-04-01 and ends on 2020-04-25, then I want the sum of the rain (which is always less than 0.5mm) and the duration of 25 days. Theoretically in this case there is a drought period from 2020-04-02 to 2020-04-21, but that is inside another period. So I only want the longest period.

What is the best way to get this accomplished in MySQL?

MySQL version: 5.5.31

Query to create table with data:

CREATE TABLE `dayRecords` (
  `Date` date NOT NULL,
  `Rain` decimal(10,1) DEFAULT NULL
) ENGINE=MyISAM;

INSERT INTO `dayRecords` (`Date`, `Rain`) VALUES
('2020-03-10', '11.0'),
('2020-03-11', '10.8'),
('2020-03-12', '2.2'),
('2020-03-13', '1.8'),
('2020-03-14', '0.0'),
('2020-03-15', '0.0'),
('2020-03-16', '0.0'),
('2020-03-17', '0.0'),
('2020-03-18', '0.0'),
('2020-03-19', '0.0'),
('2020-03-20', '0.0'),
('2020-03-21', '0.2'),
('2020-03-22', '0.0'),
('2020-03-23', '0.0'),
('2020-03-24', '0.0'),
('2020-03-25', '0.0'),
('2020-03-26', '0.0'),
('2020-03-27', '0.0'),
('2020-03-28', '0.0'),
('2020-03-29', '0.0'),
('2020-03-30', '0.0'),
('2020-03-31', '0.0'),
('2020-04-01', '0.2'),
('2020-04-02', '0.0'),
('2020-04-03', '0.0'),
('2020-04-04', '0.0'),
('2020-04-05', '0.0'),
('2020-04-06', '0.0'),
('2020-04-07', '0.0'),
('2020-04-08', '0.0'),
('2020-04-09', '0.2');

ALTER TABLE `dayRecords`
  ADD PRIMARY KEY (`Date`);

Desired result:

start      | end        | total | duration
2020-03-14 | 2020-04-08 | 0.4   | 26

user_5193536 · Answer

My end result is a little different from yours. Because i have also the 09.04 but i also have the resuklt 26 days in a row.

SELECT 
    MIN(`Date`) start,
    MAX(`Date`) end,
    ROUND(MAX(total_rain),1) total,
    SUM(isdRoud=1) duration
FROM
    (SELECT 
        `Date`,

            IF( `Rain` > 0.5, IF(@droud = 3,@droud := 3,@droud := 2), @droud := 1) isdRoud,
            IF (`Rain` > 0.5 AND @droud  = 2, @cat:= @cat + 1, @cat:= @cat) categorie,
            IF(@droud = 2,@droud := 3,@droud := @droud) nextrain,
            IF (@droud  = 3 OR @droud  = 2, @rain:= 0, @rain:= @rain) resetrain,
            IF(@droud = 1,@rain:=@rain + `Rain`,@rain:=@rain) total_rain
    FROM
        (SELECT 
        *
    FROM
        `dayRecords`
    ORDER BY `Date` DESC) t1
     , (SELECT @rain:=0.0) t2 
     , (SELECT @droud:=1) t3
     , (SELECT @cat:=0) t4) t5
WHERE
    isdroud = 1
GROUP BY categorie
HAVING duration >= 20;

start      | end        | total | duration
:--------- | :--------- | ----: | -------:
2020-03-21 | 2020-04-09 |   0.4 |       20
2019-04-04 | 2019-04-23 |   0.2 |       20

db<>fiddle here

MySQL to select a daterange where sum is less than a value

1 Answers