查找表中缺少的时间间隔 [英] Find missing time intervals in a table
问题描述
我有下表,其中包含每15分钟从几个不同设备读取的值:
I have the following table which contains values read every 15 minutes from several different devices:
ID DeviceID Date Value
----------------------------------------------
1 3 24.08.2011 00:00:00 0.51
2 3 24.08.2011 00:15:00 2.9
3 3 24.08.2011 00:30:00 0
4 3 24.08.2011 00:45:00 7.1
5 3 24.08.2011 01:00:00 1.05
6 3 24.08.2011 03:15:00 3.8
我想在表中找到给定月份内没有条目的每台设备的所有差距.对于上表,结果应该是这样的:
I'd like to find all the gaps in the table for each device, where there are no entries, for a given month. For the table above, the result should be something like this:
DeviceID StartDate EndDate
-------------------------------------------------------
3 24.08.2011 01:00:00 24.08.2011 03:15:00
该表大约有35000台设备和1亿个条目.
The table has roughly 35000 devices and 100 million entries.
这是我尝试过的;它很慢,但是返回我需要的.但是,除了速度之外,还有另一个问题:它只能找到在给定月份设备的最后一个条目之前丢失的时间间隔;之后的所有内容都将被忽略,因此有可能会错过额外的缺失值间隔.
This is what I tried; it's rather slow, but returns what I need. However, besides its speed, there's another problem: it only finds missing intervals up to the last entry for a device in the given month; anything after that will be ignored, so it's possible to miss an extra interval of missing values.
SELECT
t2.Date AS StartDate
, t1.Date AS EndDate
FROM
TestTable t1
INNER JOIN TestTable t2 ON t1.DeviceID = t2.DeviceID
WHERE
(t2.Date = (SELECT MAX(Date) FROM TestTable t3 WHERE t3.Date < t1.Date AND t3.DeviceID = t1.DeviceID)
AND DATEDIFF(MINUTE, t2.Date, t1.Date) > 15)
AND t1.DeviceID = @id
AND DATEPART(YEAR, t1.Date) = @year AND DATEPART(MONTH, t1.Date) = @month
推荐答案
以下操作应该有效,并且不会仅返回一个deviceid的单个记录.
Following should work and doesn't return just a single record for a deviceid.
要点是
- 为每个记录添加一个行号,按
Date
的顺序排列,并为每个DeviceID
重新启动. - 与self一起创建包含两个原始行的组合的行的结果.每行的列之间的关系是行号(+1)和
DeviceID
. - 仅保留相关
Date
超过15分钟的行.
- Add a rownumber to each record, ordered by
Date
and restarting for eachDeviceID
. - Join with self to create a result with rows consisting of the combination of two original rows. The relation between the columns of each row is the rownumber (+1) and the
DeviceID
. - Only retain those rows where the related
Date
is more than 15 minutes.
SQL语句
;WITH t AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY Date)
FROM TestTable
)
SELECT t1.DeviceID, t1.Date, t2.Date
FROM t t1
INNER JOIN t t2 ON t2.DeviceID = t1.DeviceID AND t2.rn = t1.rn + 1
WHERE DATEDIFF(MINUTE, t1.Date, t2.Date) > 15
测试脚本
;WITH TestTable (ID, DeviceID, Date, Value) AS (
SELECT 1, 3, '2011-08-24 00:00:00', 0.51 UNION ALL
SELECT 2, 3, '2011-08-24 00:15:00', 2.9 UNION ALL
SELECT 3, 3, '2011-08-24 00:30:00', 0 UNION ALL
SELECT 4, 3, '2011-08-24 00:45:00', 7.1 UNION ALL
SELECT 5, 3, '2011-08-24 01:00:00', 1.05 UNION ALL
SELECT 6, 3, '2011-08-24 03:15:00', 3.8
)
, t AS (
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY Date)
FROM TestTable
)
SELECT t1.DeviceID, t1.Date, t2.Date
FROM t t1
INNER JOIN t t2 ON t2.DeviceID = t1.DeviceID AND t2.rn = t1.rn + 1
WHERE DATEDIFF(MINUTE, t1.Date, t2.Date) > 15
这篇关于查找表中缺少的时间间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!