问题描述
似乎我的差距检测只是选择我设置为时间差的任何内容,并基于此在每个时间间隔执行.
It seems as though my gap detection is just picking up whatever I set as the timedifference and doing it every interval based on that.
这里是对我的数据结构和我所追求的内容的解释:
So here's an explanation of my data structure and what I'm after:
我有一个这样设置的数据库:
I have a database that's set up like this:
(Schema Name)
Historical
-CID int UQ AI NN
-ID Int PK
-Location Varchar(255)
-Status Varchar(255)
-Time datetime
我的数据看起来像这样(例如所选 ID 的 5 行)
My data comes in looking something like this (example 5 rows for selected ID)
433275 | 97 | MyLocation | OK | 2013-08-20 13:05:54
433275 | 97 | MyLocation | OK | 2013-08-20 13:00:54
433275 | 97 | MyLocation | OK | 2013-08-20 12:25:54
433275 | 97 | MyLocation | OK | 2013-08-20 12:20:54
433275 | 97 | MyLocation | OK | 2013-08-20 12:15:54
在上面的例子中,您会注意到我丢失了 ID 97 从 12:25:54 -> 13:00 的数据.我正在尝试编写一份报告,告诉我:停机时间开始,持续时间停机时间和停机时间结束(我一直在 php 中通过将 timediff 添加到时间来处理)
In the case above you'll notice that I'm missing data from 12:25:54 -> 13:00 for ID 97. I am trying to write a report that will tell me the: Start of downtime, Duration of DownTime, and End of Downtime (which I've been handling in php by adding timediff to Time)
这是我现在的代码(php -> mysql)(不工作):
Here's my code (php -> mysql) (non-working) as it stands now:
select *from (
SELECT
y.*,
TIMESTAMPDIFF(MINUTE, @prevDT, `Time`) AS timedifference,
@prevDT := `Time`
FROM ddHistorical y,
(SELECT @prevDT:=(SELECT MIN(`Time`) FROM ddHistorical)) vars
Where ID = '". mysql_real_escape_string($ID)."'
HAVING timedifference > 16
) s
order by Time desc
Limit 25";
推荐答案
你需要两级子查询:
SELECT *
FROM (
SELECT y.*,
TIMESTAMPDIFF(MINUTE, @prevDT, `Time`) AS timedifference,
@prevDT := `Time`
FROM (SELECT *
FROM ddHistorical
WHERE ID = '97'
ORDER BY `Time`) y,
(SELECT @prevDT:=(SELECT MIN(`Time`) FROM ddHistorical)) vars) z
WHERE timedifference > 16
LIMIT 25
我实际上不确定为什么需要最外层.我尝试不使用它(即以 SELECT y.*
开头)并使用 HAVING timedifference >16
.出于某种原因,这报告了 timedifference = 45
的单行.但是如果我删除那个 HAVING
子句,它会显示所有行,报告的间隔为 35 分钟.通常,
I'm actually not sure why the outermost level is needed. I tried without it (i.e. starting with SELECT y.*
) and using HAVING timedifference > 16
. For some reason, this reported a single row with timedifference = 45
. But if I removed that HAVING
clause, it showed all the rows, with the gap reported as 35 minutes. Usually, there's no difference between
SELECT ... HAVING <cond>
和
SELECT * FROM (SELECT ...) WHERE <cond>
我不知道为什么这个查询违反了规则——我怀疑它与用户变量有关.
I'm not sure why this query violates the rule -- I suspect it has something to do with the user-variables.
我想我已经弄清楚为什么它不能与 HAVING
一起使用了.MySQL 显然在计算每一行的 timedifference
列后立即评估该条件,并丢弃该行.当 timedifference 时,它不会计算
SELECT
子句中的其余列,因此它从不计算 @prevDT := Time代码>.因此,在超过该条件之前,它始终与
MIN(Time)
进行比较.
I think I've figured out why it didn't work with HAVING
. MySQL is apparently evaluating that condition as soon as it calculates the timedifference
column for each row, and discarding the row. When timedifference <= 16
, it doesn't bother calculating the rest of the columns in the SELECT
clause, so it never does @prevDT := Time
. So until it gets past that condition, it's always comparing with MIN(Time)
.
将 timedifference
检查移到外部查询中会强制它在过滤之前计算所有差异.
Moving the timedifference
check into an outer query forces it to calculate all the differences before filtering.
这篇关于Mysql:间隙检测查询未检测到间隙的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!