MySQL 选择总和小于某个值的日期范围

Question

我有带有天气信息的 MySQL 数据库:

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

这张表有将近 20 年的数据。每天一个记录。

我想从数据库中获取所有干旱期。定义:至少 20 天的降雨量小于 0.5 毫米。

我可以在 PHP 中创建一个循环并迭代所有数据并计算过去 20 天的总和。但我想知道这是否可以在 MySQL 语句中完成。

为了使其更加复杂，我还需要以天为单位的干旱期持续时间。

因此，如果干旱期从 2020-04-01 开始并在 2020-04-25 结束，那么我需要降雨(始终小于 0.5 毫米)和持续 25 天的总和。从理论上讲，在这种情况下，从 2020-04-02 到 2020-04-21 有一个干旱期，但那是在另一个时期内。所以我只想要最长的时间。

在 MySQL 中实现这一目标的最佳方法是什么？

MySQL版本:5.5.31

用数据创建表的查询:

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`);

期望的结果:

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

Answer 1

我的最终结果与您的略有不同。因为我也有 09.04，但我也有连续 26 天的结果。

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 |       202019-04-04 | 2019-04-23 |   0.2 |       20

db<> fiddle here