如何找到数据空白并插入NULL数据点而不是间隙 [英] How to find gaps of data and insert NULL data points instead having gap
问题描述
每列可以有多个点(datetime和value)。
每个列集都具有预定义的时间步长。以下示例是:600秒。
columnA
dateTime - '2013-10-05 00:10:00'
价值 - 0.51231
dateTime - '2013-10-05 00:20:00'
价值 - 0.54123
dateTime - '2013-10-05 00:30:00'
价值 - 0.53312
...
dateTime - '2013-11-14 13:30:00'
价值 - 0.53312
等
columnB
dateTime - '2013-10-05 00:10:00'
价值 - 0.51231
dateTime - '2013-10-05 00:20:00'
价值 - 0.54123
dateTime - '2013-10-05 00:50:00'
价值 - 0.54123
...
dateTime - '2013-11-14 13:20:00'
价值 - 0.53312
dateTime - '2013-11-14 13:30:00'
价值 - 0.53312
dateTime - '2013-11-14 13:40:00'
价值 - 0.53312
我需要为每个列找到缺少的数据点,并插入NULL分,所以所有的列都将具有对于上述例子,缺少的数据点是:
columnA - 2013-11-14 13:40:00
columnB - 2013-10-05 00:30:00,2013-10-05 00:40:00
我知道我可以使用T-SQL或PHP(客户端)对给定列集的每一列进行迭代,并检查每个点,但是会我觉得很慢特别是PHP解决方案。
可能有智能SQL可以选择缺少的dateTimes(差距)。
任何想法?
更新:
因为DB结构不清楚。
列是表中的一个字段,所以我们有一个表:tblValues。
有3个字段:columnName,datetime和value。
在我们的例子中,columnName将是columnA或columnB。
这不是太糟糕。这里的关键是保留数字表。它只是一个表,一列,数字1到尽可能多的你想要的。它们非常有用。这是我最喜欢的创建方法(由Itzik Ben-Gan提供) :
; WITH
Pass0 as(select 1 as C union all select 1),--2 rows
Pass1为(选择1作为C从Pass0作为A,Pass0为B), - 4行
Pass2为(选择1为C,从Pass1为A,Pass1为B), - 16行
Pass3为(从Pass2中选择1作为A,Pass2为B), - 256行
Pass4为(从C中选择1作为A,Pass3为B), - 65536行
Pass5为(选择1作为C从Pass4作为A,Pass4为B), - 4,294,967,296行
计数为(select row_number()((C))作为Number从Pass5)
选择编号INTO dbo.来自Tally的数字号码< = 1000000
现在你已经有了,这只是使用数字表来生成数据应该的位置,然后将其连接到您实际拥有的位置。这样做:
WITH [data] AS(SELECT * FROM(VALUES
('2013-10-05 00:10:00',0.51231),
('2013-10-05 00:20:00',0.54123),
('2013-10-05 00:30:00',0.53312 )
('2013-10-05 00:50:00',0.54123)
)AS x(d,v)),
[times] AS(
SELECT TOP 10 DATEADD(MINUTE,10 * [Number],'2013-10-05')AS [d]
FROM dba.dbo。[Numbers] AS n
)
SELECT * FROM [times]
LEFT JOIN [data]
ON [times] .d = [data] .d
WHERE [data] .d IS NULL
I have a column set (let's say column A and column B). Each column can have multiple points (datetime and value). Each column set have predefined timestep. For below example it's: 600sec.
columnA
dateTime - '2013-10-05 00:10:00'
Value - 0.51231
dateTime - '2013-10-05 00:20:00'
Value - 0.54123
dateTime - '2013-10-05 00:30:00'
Value - 0.53312
...
dateTime - '2013-11-14 13:30:00'
Value - 0.53312
etc.
columnB
dateTime - '2013-10-05 00:10:00'
Value - 0.51231
dateTime - '2013-10-05 00:20:00'
Value - 0.54123
dateTime - '2013-10-05 00:50:00'
Value - 0.54123
...
dateTime - '2013-11-14 13:20:00'
Value - 0.53312
dateTime - '2013-11-14 13:30:00'
Value - 0.53312
dateTime - '2013-11-14 13:40:00'
Value - 0.53312
I need to find missing data points for each column and insert NULL points so all columns would have exact amount of points.
For above examples missing data points are:
columnA - 2013-11-14 13:40:00
columnB - 2013-10-05 00:30:00, 2013-10-05 00:40:00
I know I can iterate using T-SQL or PHP (client side) for each column for given column set and check each point, but it would be very slow I think. Especially PHP solution.
There is possibly smart SQL that would be able to select missing dateTimes (gaps).
Any ideas?
UPDATE: Because it's not clear about the DB structure. Column is a field within the table, so, let's have a table: tblValues. There are 3 fields: columnName, datetime and value. In our examples columnName would be the columnA or columnB.
It's not too bad. The key here is to keep a numbers table around. It's just a table with one column and the numbers 1 through as many as you want in it. They're incredibly useful. Here's my favorite method to create one (courtesy of Itzik Ben-Gan):
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
select Number INTO dbo.Numbers from Tally where Number <= 1000000
Now that you've got that, it's just a matter of using the numbers table to generate where the data should be and then left join it to what you actually have. Something like this:
WITH [data] AS (SELECT * FROM (VALUES
('2013-10-05 00:10:00', 0.51231),
('2013-10-05 00:20:00', 0.54123),
('2013-10-05 00:30:00', 0.53312),
('2013-10-05 00:50:00', 0.54123)
) AS x(d, v)),
[times] AS (
SELECT TOP 10 DATEADD(MINUTE, 10*[Number], '2013-10-05') AS [d]
FROM dba.dbo.[Numbers] AS n
)
SELECT * FROM [times]
LEFT JOIN [data]
ON [times].d = [data].d
WHERE [data].d IS NULL
这篇关于如何找到数据空白并插入NULL数据点而不是间隙的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!