查找最长的无雨时间(每小时，超过几天)

kx7yvsdv 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(596)

我有一个气象站，在那里我测量每小时的降雨量，并将数据保存在数据库中。雨表如下所示：

CREATE TABLE `Rain` (
  `id` int(11) NOT NULL,
  `rain` double NOT NULL,
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Rain` (`id`, `rain`, `date_time`) VALUES
(1, 0, '2020-07-12 21:00:01'),
(2, 0, '2020-07-12 22:00:01'),
(3, 0, '2020-07-12 23:00:01'),
(4, 0, '2020-07-13 00:00:01'),
(5, 0, '2020-07-13 01:00:01'),
(6, 0, '2020-07-13 02:00:01'),
(7, 0, '2020-07-13 03:00:01');

现在我想要每月最长的无雨时间。我已经有了一个解决方案，但它只适用于相应的一天。但是，如果连续几天不下雨，我的解决方案将无法正常工作。。。

我现在的陈述是这样的：

SET @row_number:=0;
SELECT rain, date_time, 
        EXTRACT(DAY_HOUR FROM date_time) - @row_number:=@row_number+1 as 'check_sum'
FROM Rain 
WHERE rain=(SELECT MIN(rain) 
            FROM Rain 
            WHERE date_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'
            ) 
AND date_time BETWEEN '2020-07-01 00:00:00' AND '2020-07-31 00:00:00'

此语句的输出如下：

rain date_time           check_sum
   0 2020-07-12 21:00:01      1220
   0 2020-07-12 22:00:02      1220
   0 2020-07-12 23:00:01      1220 
   0 2020-07-13 00:00:01      1296
   0 2020-07-13 01:00:01      1296
   0 2020-07-13 02:00:01      1296
   0 2020-07-13 03:00:01      1296
   0 2020-07-13 04:00:01      1296
   0 2020-07-13 04:00:01      1296

虽然没有下雨，但我得到了不同的答案 checksum 第二天。
sqlfiddle公司
我也知道为什么，但我不知道如何用不同的方法来解决这个问题，以达到我的目的。

day/hour       @row_number+1
(12/21) 1221 -       1       = 1220 same
(12/22) 1222 -       2       = 1220 same
(12/23) 1223 -       3       = 1220 same
(13/00) 1300 -       4       = 1296 not same (day changed)
(13/01) 1301 -       5       = 1296 not same (day changed)
(13/02) 1302 -       6       = 1296 not same (day changed)

有没有人知道如何在日新月异的情况下做到这一点 check sum ? 或者有没有其他方法（全新的方法/新的查询）来解决我的问题？
提前谢谢！

sql DateTime Date gaps-and-islands mariadb-10.1

来源：https://stackoverflow.com/questions/62873018/find-the-longest-rainless-time-every-hour-beyond-several-days

2条答案

按热度按时间

mzaanser1#

在旧版本中，可以使用变量：

SELECT MIN(date_time), MAX(date_time),
       TIMESTAMPDIFF(hour, MIN(date_time), MAX(date_time)) hours
FROM (SELECT r.*, (@rn := @rn + 1) as rn
      FROM (SELECT r.*, FLOOR(UNIX_TIMESTAMP(date_time) / 3600) as hh
            FROM Rain r
            WHERE rain = 0
            ORDER BY date_time
           ) r CROSS JOIN
           (SELECT @rn := 0) params
      ) r
GROUP BY (hh - rn)
ORDER BY hours desc

添加 LIMIT 1 以获得最高小时数。
这个想法是计算每个无雨日的小时数（从时间开始算起）。如果你从小时数中减去一个连续的数字，你就会得到一个连续小时的常数。
请注意，这种方法也适用于窗口函数：

SELECT MIN(date_time), MAX(date_time),
       TIMESTAMPDIFF(hour, MIN(date_time), MAX(date_time)) hours
FROM (SELECT r.*, FLOOR(UNIX_TIMESTAMP(date_time) / 3600) as hh,
             ROW_NUMBER() OVER (ORDER BY date_time) as rn
      FROM Rain r
      WHERE rain = 0
     ) r
GROUP BY (hh - rn)
ORDER BY hours desc

赞(0）回复(0）举报 2021-07-26

0s7z1bwu2#

在我看来，这是一个缺口和孤岛问题。我知道你想要最长的连胜 0 列中的s rain .
如果您运行的是mysql 8.0，一种方法是使用行号之间的差异：

select 
    min(date_time) start_date_time, 
    max(date_time) end_date_time, 
    count(*) no_hours
from (
    select 
        r.*,
        row_number() over(order by date_time) rn1,
        row_number() over(partition by rain order by date_time) rn2
    from rain r
) r
where rain = 0
group by rn1 - rn2
order by no_hours desc

赞(0）回复(0）举报 2021-07-26

我来回答

查找最长的无雨时间(每小时，超过几天)

我现在的陈述是这样的：

此语句的输出如下：

2条答案

相关问题

热门标签

最新问答