如何在 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&nbsp;`value`,`time`&nbsp;from&nbsp;`device`&nbsp;where&nbsp;MINUTE(`time`)&nbsp;IN&nbsp;(0,&nbsp;10,&nbsp;20,&nbsp;30,&nbsp;40,&nbsp;50)&nbsp;order&nbsp;by&nbsp;`time`";如果您有 10 分钟的多个值并且只需要一个，请使用 group by MINUTE(&nbsp;time)