使用SQL检测异常间隔 [英] Detect Anomaly Intervals with SQL

问题描述

我的问题很简单:我有一个带有一系列状态和时间戳的表(出于好奇，这些状态表示警报级别)，我想查询该表以获取两个状态之间的持续时间./p>

似乎很简单，但是棘手的部分出现了:我无法创建查找表，过程，并且它应该尽可能快，因为该表是一个拥有10亿条记录的小怪物(没有在开玩笑！). ..

该架构非常简单:

[pk]时间 值

(准确地说，还有第二个pk，但这是没有用的)

在一个真实的例子下面:

Timestamp          Status
2013-1-1 00:00:00    1
2013-1-1 00:00:05    2
2013-1-1 00:00:10    2
2013-1-1 00:00:15    2
2013-1-1 00:00:20    0
2013-1-1 00:00:25    1
2013-1-1 00:00:30    2
2013-1-1 00:00:35    2
2013-1-1 00:00:40    0

仅考虑2级警报的输出应如下所示，应报告2级警报的开始及其结束(达到0时):

StartTime          EndTime            Interval
2013-1-1 00:00:05  2013-1-1 00:00:20     15
2013-1-1 00:00:30  2013-1-1 00:00:40     10

我一直在尝试各种内部联接，但是所有这些使我引向了惊人的笛卡尔爆炸.你们可以帮我找出实现此目标的方法吗?

谢谢！

解决方案

这必须是我今天看到的最困难的问题之一-谢谢！我认为您可以使用CTE?如果是这样，请尝试以下操作:

;WITH Filtered
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY dateField) RN, dateField, Status
    FROM Test    
)
SELECT F1.RN, F3.MinRN,
    F1.dateField StartDate,
    F2.dateField Enddate
FROM Filtered      F1, Filtered F2, (
SELECT F1a.RN, MIN(F3a.RN) as MinRN
FROM Filtered      F1a
   JOIN Filtered F2a ON F1a.RN = F2a.RN+1 AND F1a.Status = 2 AND F2a.Status <> 2
   JOIN Filtered F3a ON F1a.RN < F3a.RN AND F3a.Status <> 2
GROUP BY F1a.RN ) F3 
WHERE F1.RN = F3.RN AND F2.RN = F3.MinRN

小提琴.我没有添加间隔，但我想您可以从这里处理该部分.

祝你好运.

My problem is simple: I have a table with a series of statuses and timestamps (for the sake of curiosity, these statuses indicate alarm levels) and I would like to query this table in order to get duration between two statuses.

Seems simple, but here comes the tricky part: I can´t create look-up tables, procedures and it should be as fast as possible as this table is a little monster holding over 1 billion records (no kidding!)...

The schema is drop dead simple:

[pk] Time Value

(actualy, there is a second pk but it is useless for this)

And below a real world example:

Timestamp          Status
2013-1-1 00:00:00    1
2013-1-1 00:00:05    2
2013-1-1 00:00:10    2
2013-1-1 00:00:15    2
2013-1-1 00:00:20    0
2013-1-1 00:00:25    1
2013-1-1 00:00:30    2
2013-1-1 00:00:35    2
2013-1-1 00:00:40    0

The output, considering only a level 2 alarm, should be as follow should report the begin of a level 2 alarm an its end (when reach 0):

StartTime          EndTime            Interval
2013-1-1 00:00:05  2013-1-1 00:00:20     15
2013-1-1 00:00:30  2013-1-1 00:00:40     10

I have been trying all sorts of inner joins, but all of them lead me to an amazing Cartesian explosion. Can you guys help me figure out a way to accomplish this?

Thanks!

解决方案

This has to be one of the harder questions I've seen today - thanks! I assume you can use CTEs? If so, try something like this:

;WITH Filtered
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY dateField) RN, dateField, Status
    FROM Test    
)
SELECT F1.RN, F3.MinRN,
    F1.dateField StartDate,
    F2.dateField Enddate
FROM Filtered      F1, Filtered F2, (
SELECT F1a.RN, MIN(F3a.RN) as MinRN
FROM Filtered      F1a
   JOIN Filtered F2a ON F1a.RN = F2a.RN+1 AND F1a.Status = 2 AND F2a.Status <> 2
   JOIN Filtered F3a ON F1a.RN < F3a.RN AND F3a.Status <> 2
GROUP BY F1a.RN ) F3 
WHERE F1.RN = F3.RN AND F2.RN = F3.MinRN

And the Fiddle. I didn't add the intervals, but I imagine you can handle that part from here.

Good luck.

这篇关于使用SQL检测异常间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！