我有一个表,主键(bigint),日期时间,值,foreignKey到配置表,包含100,000行.我希望能够获得一个可变时间间隔的行.例如.
Select Timestamp,value from myTable where configID=3
AND{most recent for 15 min interval}
我有一个CTE查询,它返回间隔时间间隔的多行
WITH Time_Interval(timestamp,value,minutes)
AS
(
Select timestamp,DatePart(Minute,Timestamp) from myTable
Where Timestamp >= '12/01/2012' and Timestamp <= 'Jan 10,2013' and
ConfigID = 435 and (DatePart(Minute,Timestamp) % 15) = 0
)
Select Timestamp,minutes from Time_Interval
group by minutes,timestamp
order by Timestamp
如：
2012-12-19 18:15:22.040 6.98 15 2012-12-19 18:15:29.887 6.98 15 2012-12-19 18:15:33.480 7.02 15 2012-12-19 18:15:49.370 7.01 15 2012-12-19 18:30:41.920 6.95 30 2012-12-19 18:30:52.437 6.93 30 2012-12-19 19:15:18.467 7.13 15 2012-12-19 19:15:34.250 7.11 15 2012-12-19 19:15:49.813 7.12 15
但是可以看出,第一个15分钟间隔有4个,下一个间隔有2个等等……更糟糕的是,
如果没有在15分钟的确切时间戳上获得数据,那么将没有价值.
我想要的是十五分钟间隔的最新值…如果该间隔的唯一数据发生在间隔开始后的1秒内.
我正在考虑Lead / over但是……这些行并没有这样的方式.主键是一个bigInt,是一个聚簇索引. timstamp列和ConfigID列都是Indexed.上面的查询在一秒钟内返回4583行.
谢谢你的帮助.
解决方法
尝试这个尺寸.当给定间隔有多个时间戳时,它甚至会处理返回一行的实例.
注意：这假设您的Bigint PK列命名为：idx.如果不是,请替换“idx”.
注意：这假设您的Bigint PK列命名为：idx.如果不是,请替换“idx”.
;WITH Interval_Helper([minute],minute_group)
AS
(
SELECT 0,1 UNION SELECT 1,1 UNION SELECT 2,1 UNION SELECT 3,1 UNION SELECT 4,1
UNION SELECT 5,1 UNION SELECT 6,1 UNION SELECT 7,1 UNION SELECT 8,1 UNION SELECT 9,1
UNION SELECT 10,1 UNION SELECT 11,1 UNION SELECT 12,1 UNION SELECT 13,1 UNION SELECT 14,1
UNION SELECT 15,2 UNION SELECT 16,2 UNION SELECT 17,2 UNION SELECT 18,2 UNION SELECT 19,2
UNION SELECT 20,2 UNION SELECT 21,2 UNION SELECT 22,2 UNION SELECT 23,2 UNION SELECT 24,2
UNION SELECT 25,2 UNION SELECT 26,2 UNION SELECT 27,2 UNION SELECT 28,2 UNION SELECT 29,2
UNION SELECT 30,3 UNION SELECT 31,3 UNION SELECT 32,3 UNION SELECT 33,3 UNION SELECT 34,3
UNION SELECT 35,3 UNION SELECT 36,3 UNION SELECT 37,3 UNION SELECT 38,3 UNION SELECT 39,3
UNION SELECT 40,3 UNION SELECT 41,3 UNION SELECT 42,3 UNION SELECT 43,3 UNION SELECT 44,3
UNION SELECT 45,4 UNION SELECT 46,4 UNION SELECT 47,4 UNION SELECT 48,4 UNION SELECT 49,4
UNION SELECT 50,4 UNION SELECT 51,4 UNION SELECT 52,4 UNION SELECT 53,4 UNION SELECT 54,4
UNION SELECT 55,4 UNION SELECT 56,4 UNION SELECT 57,4 UNION SELECT 58,4 UNION SELECT 59,4
),Time_Interval([timestamp],[date],[hour],minute_group)
AS
(
SELECT A.[Timestamp],A.value,CONVERT(smalldatetime,CONVERT(char(10),A.[Timestamp],101)),DATEPART(HOUR,A.[Timestamp]),B.minute_group
FROM myTable A
JOIN Interval_Helper B
ON (DATEPART(minute,A.[Timestamp])) = B.[minute]
AND A.[Timestamp] >= '12/01/2012'
AND A.[Timestamp] <= '01/10/2013'
AND A.ConfigID = 435
),Time_Interval_TimeGroup([date],[minute],MaxTimestamp)
AS
(
SELECT [date],minute_group,MAX([Timestamp]) as MaxTimestamp
FROM Time_Interval
GROUP BY [date],minute_group
),Time_Interval_TimeGroup_Latest(MaxTimestamp,MaxIdx)
AS
(
SELECT MaxTimestamp,MAX(idx) as MaxIdx
FROM myTable A
JOIN Time_Interval_TimeGroup B
ON A.[Timestamp] = B.MaxTimestamp
GROUP BY MaxTimestamp
)
SELECT A.*
FROM myTable A
JOIN Time_Interval_TimeGroup_Latest B
ON A.idx = B.MaxIdx
ORDER BY A.[timestamp]
这是另一个来自@MntManChris的聪明时间组功能：
CREATE FUNCTION dbo.fGetTimeGroup (@DatePart tinyint,@Date datetime)
RETURNS int
AS
BEGIN
RETURN CASE @DatePart
WHEN 1 THEN DATEPART(mi,@Date)
WHEN 2 THEN DATEPART(mi,@Date)/5 + 1 -- 5 min
WHEN 3 THEN DATEPART(mi,@Date)/15 + 1 -- 15 min
WHEN 4 THEN DATEPART(mi,@Date)/30 + 1 -- 30 min
WHEN 5 THEN DATEPART(hh,@Date) -- hr
WHEN 6 THEN DATEPART(hh,@Date)/6 + 1 -- 6 hours
WHEN 7 THEN DATEPART(hh,@Date)/12 + 1 -- 12 hours
WHEN 8 THEN DATEPART(d,@Date) -- day
ELSE -1
END
END
