从开始时间获取每小时数据
从开始时间获取每小时数据
提问于 2018-07-14 01:44:26
例如，我有这样的数据
min max time batch sensor
-------------------------------------------------------------------
10 20 2018-07-20 10:15:00:00 x abc
15 30 2018-07-20 10:14:00:00 x abc
15 30 2018-07-20 10:16:00:00 x abc
| | | | | |
| | | | | |
20 30 2018-07-20 11:15:00:00 x abc现在我需要每小时的数据
min max
------------------------------------------------------------
2018-07-20 10:15:00:00 2018-07-20 11:15:00:00 10 10回答 1
Stack Overflow用户
发布于 2018-07-14 03:03:55
这里的复杂性在于，您每分钟都要检查一个小时范围的窗口，因此，如果是全天，则您的报告有1440行。下面是一些可以做到这一点的代码：
SET NOCOUNT ON
-- Create a temporary numbers table to demonstrate its use.
-- We need numbers up to 24*60-1 (24 hours * 60 minutes -1 for based on zero)
Declare @DailyIntervals int,
SET @DailyIntervals = 24 * 60 - 1
--SELECT @DailyIntervals
-- This calculates the number of recursive inserts needed to reach at least your maximum value
-- NOTE: This is based off of manually inserting your first four values into the table first!
DECLARE @loop int
SET @loop = CEILING(Log(@DailyIntervals,2))-2
Declare @m int
CREATE TABLE ##Nums (i int primary key clustered);
INSERT INTO ##Nums VALUES (1), (2), (3), (4); -- Seed the table
SET @m = 4
WHILE @loop > 0
BEGIN
INSERT INTO ##nums SELECT I + @m FROM ##Nums
SET @m = @@ROWCOUNT * 2 -- max value in table now
SET @loop = @loop - 1
END
INSERT INTO ##nums Values (0) -- add the zero value
--SELECT MAX(I) FROM ##Nums
-- Look at July 20th
DECLARE @BeginDt datetime = '2018-07-20'
-- Build a working table of one minute intervals for a full day
WITH hhrs as (
SELECT Dateadd(MINUTE, i, @BeginDt) as StartTm,
Dateadd(MINUTE, i+59, @BeginDt) as EndTm
FROM ##Nums
WHERE i BETWEEN 0 and @DailyIntervals
),
SELECT
StartTm,
EndTm,
min([Min]) as [Min],
max([max]) as [Max]
FROM hhrs h
INNER JOIN MyData m
ON m.[Time] Between @StartTm and @EndTm
ORDER BY StartTm
DROP TABLE ##Nums页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51330363
复制相关文章
相似问题