Mysql:间隙检测查询未检测到间隙 [英] Mysql: Gap detection query not detecting gaps

问题描述

似乎我的差距检测只是选择我设置为时间差的任何内容，并基于此在每个时间间隔执行.

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 <= 16 时，它不会计算 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.

SQLFIDDLE

这篇关于Mysql:间隙检测查询未检测到间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！