如何在 PHP MySQL 的单个查询中获取相隔 10 分钟的所有行？

Question

我有一个表设备，其中包含记录的温度值及其测量时间。我需要通过仅获取至少相隔 10 分钟的值来对这些数据进行采样。（例如，如果第一行读取 (30k,8.40.00)，则下一行包含8.50.00 之后或时的值（以接下来 10 分钟的值为准）。我的时间列存储为 sting 格式 (Hms)。我当前在 PHP 中的选择查询是$sql="select value ,time from device where time >= '$interval' order by time limit 1";使用 strtotime 方法， $interval 值增加 10 min 。每次只选择一行问题是我需要使用循环来获取所有可能的行。我的问题是如何在不使用循环的情况下获取所有行，就像在单个查询中一样？为了使用 for 循环，我不需要提前知道行数。

Answer 1

这比你想象的更复杂。您不能只查看连续行之间的间隔，您需要跟踪选定的最后一行以识别下一行。这意味着一个迭代过程；在 SQL 中，这通常使用递归查询来实现 - MySQL 仅支持从 8.0 版本开始。

考虑：

with recursive cte as (

    select value, time 

    from device 

    where time = (select min(time) from device)

    union all

    select d.value, d.time 

    from cte c

    inner join device d on d.time = (

        select min(d1.time) from device d1 where d1.time >= c.time + interval 10 minute

    )

)

select * from cte

Answer 2

我建议首先使用窗口函数获取（至少）10 分钟后每行的值，然后应用递归子查询：

with recursive d(value, time, next_time) as (

      select value, time,

             min(time) over (order by time

                             range between interval 10 minute following and unbounded following

                            ) as next_time

      from device

     )

     recursive cte(value, time, next_time) as (

      (select value, time, next_time

       from d 

       order by time

       limit 1

      ) union all

      select d.value, d.time, d.next_time

      from cte join

           d 

           on device.time = cte.next_time

      )

select *

from cte;

这是一个 db<>fiddle。

Answer 3

我认为这会起作用：

$sql="select `value`,`time` from `device` where MINUTE(`time`) IN (0, 10, 20, 30, 40, 50) order by `time`";

如果您有 10 分钟的多个值并且只需要一个，请使用 group by MINUTE( time)