查询以识别时间数据中的差距 [英] Query to identify gaps in time data

问题描述

我对数据库编程还很陌生，我需要一些帮助来编写看似复杂的 t-sql 查询.

I'm pretty new to database programming, and I'm in need some help writing what seems to be a complicated t-sql query.

我们的数据库存储时间数据以及当时记录的各个级别.典型的测量将持续数周或数月.我需要在 SqlServer2005 中编写一个查询来识别时间数据中的差距，以了解仪器何时没有进行通信.我们的采样间隔可以是 1 秒或 0.5 秒，但在给定的测量中始终相同(即:测量中的所有样本将为 1 或 0.5 个样本/秒).

Our database stores time data along with various levels recorded at that time. A typical measurement will last for weeks or months. I need to write a query in SqlServer2005 to identify gaps in the time data to know when the instrument wasn't communicating. Our sample interval can be either 1 sec or .5 sec, but it will always be the same in a given measurement (ie: all samples in a measurement will be either 1 or .5 samples/sec).

理想情况下，我想得到一个列表[block1-start block1-end][block2-start block2-end]等

Ideally I would like to get a list of [block1-start block1-end] [block2-start block2-end] etc

其中每个块是整体测量中连续时间的单位.

where each block is a unit of consecutive times within the overall measurement.

TSQL 中是否有任何命令可以使此查询更容易?

Are there any commands in TSQL that would make this query easier?

推荐答案

看看这个.由于您没有重叠的间隔，您可以编写一个简单的 SQL 查询来返回这些结果.下面的 SQL 创建了一个名为 @Events 的虚拟表变量来模拟您的测量表.最终查询输出大于 1 秒的间隙(可通过变量 @MaxIntervalAllowedBetweenEvents 配置).

Check this out. Since you don't have overlapping intervals you can write a simple SQL query to return these results. The SQL below creates a dummy table variable called @Events to simulate your measurements table. The final query outputs the gaps greater than 1 second (configurable via variable @MaxIntervalAllowedBetweenEvents).

-- table with dummy data
declare @Events table (
    ID          int IDENTITY NOT NULL,
    StartDate   datetime NOT NULL,
    EndDate     datetime NOT NULL
)
INSERT @Events VALUES ('1/1/2011 1:00am', '1/1/2011 2:00am')
INSERT @Events VALUES ('1/1/2011 2:00am', '1/1/2011 3:00am')  -- no gap after previous event
INSERT @Events VALUES ('1/1/2011 3:01am', '1/1/2011 4:00am')  -- 1 minute gap
INSERT @Events VALUES ('1/1/2011 4:30am', '1/1/2011 5:00am')  -- 30 minute gap


-- this variable defines the maximum interval allowed between events
declare @MaxIntervalAllowedBetweenEvents int
set @MaxIntervalAllowedBetweenEvents = 1    -- # seconds


-- select the gaps between events
SELECT
    e1.EndDate,
    Min(e2.StartDate) as NextEventStartDate,
    DateDiff(s, e1.EndDate, Min(e2.StartDate)) as SecondsBetweenEvents
FROM
    @Events as e1
join
    -- for each event in e1, get the event that immediately follows it
    @Events as e2
        on  (e1.EndDate <= e2.StartDate)
GROUP BY
    e1.EndDate
HAVING
    -- filter out events that are too close to each other
    (DateDiff(s, e1.EndDate, Min(e2.StartDate)) > @MaxIntervalAllowedBetweenEvents)
ORDER BY
    e1.EndDate

这篇关于查询以识别时间数据中的差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！