SQL小时日志，显示n小时内值低于阈值的所有匹配行

Question

我有一个简单的美国市场SQL日志表（在SQLite中命名为market_history），它看起来像这样：

样本表（市场历史）

id  datetime        market    percent 
1   9/5/2014 7:50   ARIZONA      50.0 
2   9/5/2014 7:50   ATLANTA      97.4 
3   9/5/2014 7:50   AUSTIN       78.8 
4   9/5/2014 7:50   BOSTON       90.9 
6   9/5/2014 7:50   CHARLOTTE    100.0 
7   9/5/2014 7:50   CHICAGO      90.3 

此表是每个市场中各种系统的网络容量的每小时快照。我想做的是建立一个警报系统，如果任何一个特定市场连续2个小时（每小时记录一行）低于阈值百分比（比如50），就会触发一封警报电子邮件。。因此，查询应该向我显示一个市场名称的唯一列表，其中百分比为<50.0对于超过最后2个连续条目

以下是我正在尝试的SQL，但它不起作用：

示例SQL（不工作）：

SELECT 
mh.datetime, mh.market, mh.percent
 FROM  markets_history mh
WHERE
 (SELECT mh1.precent FROM markets_history mh1 WHERE  mh1.datetime BETWEEN "2015-03-23 00:00:00" AND "2015-03-23 00:59:59" AND mh.market=mh1.market ) < 50 AND (SELECT mh2.precent FROM markets_history mh2 WHERE  mh2.datetime BETWEEN "2015-03-23 01:00:00" AND "2015-03-23 01:59:59" AND mh.market=mh2.market ) < 50
ORDER by mh.datetime

我知道我错过了什么。。任何建议

Answer 1

如果时间窗口是固定和可靠的，只需确保最大的时间窗口不超过阈值。如果你需要将其扩展到两个以上，那么你看起来有多远也不重要。

select market
from markets_history mh
where mh.datetime between <last_two_hours> and <now>
group by mh.market
having max(percent) < 50.0
    -- and count(*) = 2 /* if you need to be sure of two ... */

Answer 2

这里有一种应该在SQLite中工作的方法。查找每个市场中的最后一个有效id（如果有的话）。然后计算大于id的行数。

select lastgood.market,
       sum(case when lastgood.market is null then 1
                when lastgood.id < mh.id then 1
                else 0
           end) as NumInRow
from market_history mh left join
     (select market, max(id) as maxid
      from market_history mh
      where percent < 50.0
      group by market
     ) as lastgood
     on lastgood.market = mh.market and lastgood.id < mh.id;

这个查询有点复杂，因为它需要考虑没有任何好的id的可能性。如果是这样，那么市场的所有行都会计数。