我正在尝试以 15 分钟为间隔返回数据.我想到的第一件事是:
I am trying to return data in fifteen minute intervals. The first thing I thought to do was this:
select * from myTable where DATEPART(minute, Timestamp) % 15 = 0
但是这种方法有两个问题.第一个是在给定的分钟不一定总是有时间戳的数据，另一个是有时在给定的分钟有多个数据点具有不同的秒值.我希望每 15 分钟组正好有一排，在 :00、:15、:30 等.
But there are two problems with this approach. The first is that there will not necessarily always be data with a timestamp at a given minute, the other is that sometimes there are multiple data points at a given minute with different second values. I want to have exactly one row for each fifteen minute group, at :00, :15, :30, etc.
只有在发生变化时才会记录此数据，因此，例如，如果我在 12:30 没有数据点，我可以在此之前获取最近的数据点并使用该值作为 12:30，它会正确.
This data is only recorded when something changes, so if I don't have a data point at 12:30, for example, I could take the closest data point before that and use that value for 12:30 and it would be correct.
所以基本上我需要能够返回恰好在 :00、:30 等的时间戳以及来自最接近该时间的记录的数据.
So basically I need to be able to return timestamps at exactly :00, :30, etc along with the data from the record closest to that time.
数据可能跨越数年，但更有可能是较短的时间、数天或数周.这是预期的输出:
The data could span years but is more likely to be a shorter amount of time, days or weeks. This is what the expected output would look like:
Timestamp Value 1/1/2015 12:30:00 25 1/1/2015 12:45:00 41 1/1/2015 1:00:00 45我在想一种在 SQL 中执行此操作的方法时遇到了麻烦.可能吗?
I'm having trouble thinking of a way to do this in SQL. Is it possible?
推荐答案给定一个固定的开始时间，您只需要一个数字表来添加您的间隔.如果您还没有数字表(这很有用)，那么快速生成一个数字表的方法是
Given a fixed start time, all you would need is a table of numbers to add your intervals to. If you don't already have a table of numbers (which are useful) then a quick way to generate one on the fly is
WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)), N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2), Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2) SELECT * FROM Numbers;这只是生成一个从 1 到 10,000 的序列.有关更多阅读，请参阅以下系列:
This simply generates a sequence from 1 to 10,000. For more reading on this see the following series:
- 生成一个没有循环的集合或序列——第 1 部分
- 生成一个没有循环的集合或序列——第 2 部分
- 生成一个没有循环的集合或序列——第 3 部分
然后一旦你有了你的数字，你就可以生成你的间隔:
Then once you have your numbers you can generate your intervals:
DECLARE @StartDateTime SMALLDATETIME = '20150714 14:00', @EndDateTime SMALLDATETIME = '20150715 15:00'; WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)), N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2), Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2) SELECT Interval = DATEADD(MINUTE, 15 * (N - 1), @StartDateTime) FROM Numbers WHERE DATEADD(MINUTE, 15 * (N - 1), @StartDateTime) <= @EndDateTime这给出了类似的东西:
Interval ---------------------- 2015-07-14 14:00:00 2015-07-14 14:15:00 2015-07-14 14:30:00 2015-07-14 14:45:00 2015-07-14 15:00:00 2015-07-14 15:15:00 2015-07-14 15:30:00然后您只需要使用 APPLY 和 TOP:'
Then you just need to find the closest value on or before each interval using APPLY and TOP:'
/***************************************************************** SAMPLE DATA *****************************************************************/ DECLARE @T TABLE (1701035015 DATETIME, Value INT); INSERT @T (1701035015, Value) SELECT DATEADD(SECOND, RAND(CHECKSUM(NEWID())) * -100000, GETDATE()), CEILING(RAND(CHECKSUM(NEWID())) * 100) FROM sys.all_objects; /***************************************************************** QUERY *****************************************************************/ DECLARE @StartDateTime SMALLDATETIME = '20150714 14:00', @EndDateTime SMALLDATETIME = '20150715 15:00'; WITH N1 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t (N)), N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2), Numbers (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N2 AS N1 CROSS JOIN N2 AS N2), Intervals AS ( SELECT Interval = DATEADD(MINUTE, 15 * (N - 1), @StartDateTime) FROM Numbers WHERE DATEADD(MINUTE, 15 * (N - 1), @StartDateTime) <= @EndDateTime ) SELECT i.Interval, t.1701035015, t.Value FROM Intervals AS i OUTER APPLY ( SELECT TOP 1 t.1701035015, t.Value FROM @T AS t WHERE t.1701035015 <= i.Interval ORDER BY t.1701035015 DESC, t.Value ) AS t ORDER BY i.Interval;编辑
需要注意的一点是，如果有两个相等的时间戳，它们都在或最接近一个间隔，我已经应用了 Value 的二级排序:
One point to note is that in the case of having two equal timestamps that are both on or closest to an interval, I have applied a secondary level of ordering by Value:
SELECT i.Interval, t.1701035015, t.Value FROM Intervals AS i OUTER APPLY ( SELECT TOP 1 t.1701035015, t.Value FROM @T AS t WHERE t.1701035015 <= i.Interval ORDER BY t.1701035015 DESC, t.Value --- ORDERING HERE ) AS t ORDER BY i.Interval;这是任意的，可以是你选择的任何东西，最好确保你按足够多的项目排序以确保结果是确定性的，也就是说，如果你对相同的数据多次运行相同的查询结果将被返回，因为只有一行满足条件.如果你有这样的两行:
This is arbitrary and could be anything you chose, it would be advisable to ensure that you order by enough items to ensure the results are deterministic, that is to say, if you ran the query on the same data many times the same results would be returned because there is only one row that satisfies the criteria. If you had two rows like this:
Timestamp | Value | Field1 -----------------+---------+-------- 2015-07-14 14:00 | 100 | 1 2015-07-14 14:00 | 100 | 2 2015-07-14 14:00 | 50 | 2如果你只是按时间戳排序，对于区间2015-07-14 14:00，你不知道你会得到50还是100的值，而且可能会有所不同执行之间取决于统计信息和执行计划.同样，如果您按 Timestamp 和 Value 排序，那么您不知道 Field1 是 1 还是 2.
If you just order by timestamp, for the interval 2015-07-14 14:00, you don't know whether you will get a value of 50 or 100, and it could be different between executions depending on statistics and the execution plan. Similarly if you order by Timestamp and Value, then you don't know whether Field1 will be 1 or 2.
更多推荐
如果确切时间不可用,如何根据最后一个可用时间戳返回值?
发布评论