选择DateTime不在SQL
我有如下表:
oDateTime pvalue
2017-06-01 00:00:00 70
2017-06-01 01:00:00 65
2017-06-01 02:00:00 90
ff.
2017-08-01 08:00:00 98
的oDateTime
字段是每小时的数据是不可能有重复的值。
我的问题是,我怎么知道oDateTime
数据是否正确?我的意思是,我需要确保数据不会跳转?它应该始终是“小时”的基础。
我错过了日期吗?我错过了时间吗?
请指教。谢谢。
基于this answer,你可以得到丢失的时间形成的表MyLogTable
这样的:
DECLARE @StartDate DATETIME = '20170601', @EndDate DATETIME = '20170801'
SELECT DATEADD(hour, nbr - 1, @StartDate)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c.object_id) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(hour, @StartDate, @EndDate) AND
NOT EXISTS (SELECT 1 FROM MyLogTable WHERE DATEADD(hour, nbr - 1, @StartDate)= oDateTime)
如果您需要检查周期较长,你可以添加CROSS JOIN这样
FROM sys.columns c
CROSS JOIN sys.columns c1
它使您能够在一个查询中检查多于cca一千条记录(sysccolumn表的行数)。
这里的危险是你需要在sys.columns中有足够的行来获得所有的时间。对于示例中的日期,即1465年。如果您缺少它(如我的演示服务器),则可以添加另一个交叉连接。 –
也许你正在寻找?这将返回日期为计数< 24 - 这表示“跳”
;WITH datecount
AS (SELECT CAST(oDateTime AS DATE) AS [date] ,
COUNT(CAST(oDateTime AS DATE)) AS [count]
FROM @temp
GROUP BY (CAST(oDateTime AS DATE))
)
SELECT *
FROM datecount
WHERE [count] < 24;
编辑:既然你改变了需求“如何知道是否有缺失”到“什么是失踪”,这里是一个更新查询。
DECLARE @calendar AS TABLE (oDateTime DATETIME)
DECLARE @min DATETIME = (SELECT MIN([oDateTime]) FROM @yourTable)
DECLARE @max DATETIME = (SELECT MAX([oDateTime]) FROM @yourTable)
WHILE (@min <= @max)
BEGIN
INSERT INTO @calendar
VALUES (@min);
SET @min = DATEADD(hh, 1, @min);
END;
SELECT t1.[oDateTime]
FROM @calendar t1
LEFT JOIN @yourTable t2 ON t1.[oDateTime] = t2.[oDateTime]
GROUP BY t1.[oDateTime]
HAVING COUNT(t2.[oDateTime]) = 0;
我第一次创建基于您的MAX和MIN DATETIME一个小时日历,然后与你的实际表,日历,以找出是否有一个“跳”。
谢谢,我怎么知道我没有错过日期? – Haminteu
@Haminteu我刚刚编辑了我的答案。希望这可以帮助! –
由于您的表没有任何唯一的id号,请使用row_number()获取cte中的行号,然后使用行id和next id执行自我内部联接,相应地获取oDateTime的差异,这正好显示出哪一行不为1小时
;with cte(oDateTime,pValue,Rid)
As
(
select *,row_number() over(order by oDateTime) from [YourTableName] t1
)
select *,datediff(HH,c1.oDateTime,c2.oDateTime) as HourDiff from cte c1
inner join cte c2
on c1.Rid=c2.Rid-1 where datediff(HH,c1.oDateTime,c2.oDateTime) >1
谢谢,我如何知道我没有错过日期? – Haminteu
你可以使用DENSE_RANK()的时间差在一天编号的时间从1到24,然后,所有你需要做的是检查一天中最高排名是否为24。如果每小时至少有一个条目,则密集排名的最大值将为24.
使用以下查询可以查找缺少oDateTime的日期。
SELECT [date]
FROM
(
SELECT *
, CAST(oDateTime AS DATE) AS [date]
, DENSE_RANK() OVER(PARTITION BY CAST(oDateTime AS DATE) ORDER BY DATEPART(HOUR, oDateTime)) AS rank_num
FROM Test
) AS t
GROUP BY [date]
HAVING(MAX(rank_num) != 24);
如果你需要验证的oDateTime的每一行,你可以做自联接根据等级和获得的这个小时每个oDateTime。
预期结果是什么?你想显示失踪的时间? –
计算每个“日期”的记录数。每个日期应该有24个。 – Incognito
@FelixPamittan,是的,这就是我的意思。 – Haminteu