TSQL 查询返回过去 24 小时内每小时的值 [英] TSQL Query returns values for each hour for the past 24 hours
问题描述
我有一个问题，我真的不知道如何开始.我希望有人能帮我解决这个问题.我先解释一下表格
I have a query that I don't really know how to begin with. I'm hoping someone can help me out with it. I will start by explaining the table
我有一个包含四列的设备表:Device_Id、Device_Status、Begin_dt、End_dt有 6 种不同的状态，其中 3(为简单起见，可以说状态 1、4 和 5)表示设备在线"
I have a device table with four columns: Device_Id, Device_Status, Begin_dt, End_dt there are 6 different statuses where 3 (for simplicity lets say status 1, 4 and 5) mean the device is 'online'
这个表格的一个例子可能是
an example of this table could be
Id | Status| Begin | End
001| 1 | 2012-09-01 00:00:00.000 | 2012-09-01 01:00:00.000
001| 2 | 2012-09-01 01:00:00.000 | 2012-09-01 01:35:00.000
001| 1 | 2012-09-01 01:35:00.000 | 2012-09-01 02:05:00.000
003| 1 | 2012-09-01 05:00:00.000 | 2012-09-01 07:02:00.000
004| 1 | 2012-09-01 01:00:00.000 | 2012-09-01 01:35:00.000
003| 2 | 2012-09-01 07:02:00.000 | NULL
我的查询需要返回 TIME 的总和，所有设备的状态都指示 24 小时内每小时的在线".
My query needs to return the Sum of the TIME all the devices have a status that indicates 'online' for each hour in a 24 hour period.
所以我的回报应该是这样的
so my return should look like
Hour| Online_Time
0 | 5:30:12.11
1 | 3:30:12.11
2 | 4:30:12.11
3 | 5:30:12.11
4 | 6:30:12.11
5 | 4:00:00.00
6 | 1:30:12.11
7 | 3:30:12.11
8 | 4:30:12.11
etc |
因此，对于一天中的每个小时，我可以有超过 1 小时的在线时间(显然)，因为例如，如果我有 5 台设备在整个小时内都在线，那么那一小时我将有 5 小时的在线时间.
So for each hour of the day I can have more than 1 hour of online time (obviously) because for example if I have 5 devices all online for the whole hour I would have 5 hours of online time for that hour.
这有点复杂，我希望我能很好地解释它，非常感谢任何可以提供帮助或提出建议的人.
This is kind of complex and I hope I did a good job of explaining it, anyone that can help or give a suggestion is greatly appreciated.
-J
with const as (
select dateadd(hour, 1, cast(cast(getdate() -1 as date) as datetime)) as midnnight
),
allhours as (
select 0 as hour, midnight as timestart, dateadd(hour, 1, timestart) as timeend from const union all
select 1 as hour, dateadd(hour, 1, midnight), dateadd(hour, 2, midnight) from const union all
select 2 as hour, dateadd(hour, 2, midnight), dateadd(hour, 3, midnight) from const union all
select 3 as hour, dateadd(hour, 3, midnight), dateadd(hour, 4, midnight) from const union all
select 4 as hour, dateadd(hour, 4, midnight), dateadd(hour, 5, midnight) from const union all
select 5 as hour, dateadd(hour, 5, midnight), dateadd(hour, 6, midnight) from const union all
select 6 as hour, dateadd(hour, 6, midnight), dateadd(hour, 7, midnight) from const union all
select 7 as hour, dateadd(hour, 7, midnight), dateadd(hour, 8, midnight) from const union all
select 8 as hour, dateadd(hour, 8, midnight), dateadd(hour, 9, midnight) from const union all
select 9 as hour, dateadd(hour, 9, midnight), dateadd(hour, 10, midnight) from const union all
select 10 as hour, dateadd(hour, 10, midnight), dateadd(hour, 11, midnight) from const union all
select 11 as hour, dateadd(hour, 11, midnight), dateadd(hour, 12, midnight) from const union all
select 12 as hour, dateadd(hour, 12, midnight), dateadd(hour, 13, midnight) from const union all
select 13 as hour, dateadd(hour, 13, midnight), dateadd(hour, 14, midnight) from const union all
select 14 as hour, dateadd(hour, 14, midnight), dateadd(hour, 15, midnight) from const union all
select 15 as hour, dateadd(hour, 15, midnight), dateadd(hour, 16, midnight) from const union all
select 16 as hour, dateadd(hour, 16, midnight), dateadd(hour, 17, midnight) from const union all
select 17 as hour, dateadd(hour, 17, midnight), dateadd(hour, 18, midnight) from const union all
select 18 as hour, dateadd(hour, 18, midnight), dateadd(hour, 19, midnight) from const union all
select 19 as hour, dateadd(hour, 19, midnight), dateadd(hour, 20, midnight) from const union all
select 20 as hour, dateadd(hour, 20, midnight), dateadd(hour, 21, midnight) from const union all
select 21 as hour, dateadd(hour, 21, midnight), dateadd(hour, 22, midnight) from const union all
select 22 as hour, dateadd(hour, 22, midnight), dateadd(hour, 23, midnight) from const union all
select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const union all
)
select ah.hour,
sum(datediff(ms, (case when ah.timestart >= dt.Begin_Dt then timestart else dt.Begin_Dt end),
(case when ah.timeend <= dt.End_Dt then ah.timeend else dt.End_Dt end))) as totalms,
cast(dateadd(ms, sum(datediff(ms, (case when ah.timestart >= dt.Begin_Dt then timestart else dt.Begin_Dt end),
(case when ah.timeend <= dt.End_Dt then ah.timeend else dt.End_Dt end))),0) as time
) as totalTime
from allhours as ah left outer join
dataTable as dt
on ah.timestart< coalesce(dt.End_dt, getdate()) and
ah.timeend >= dt.Begin_Dt
group by ah.hour
order by ah.hour
<小时>
这就是我现在所拥有的，我在)"上遇到错误
This is what I have right now, I'm getting an error on the ')'
select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const union all
) <----- Incorrect syntax near ')'. Expecting SELECT, or '('.
select ah.hour,
推荐答案
您的问题似乎是时间跨度需要以小时为单位细分.因此，您需要从一天中的所有时间开始.然后计算重叠，总结差异(以毫秒为单位)并将所有内容转换回输出时间.
Your problem seems to be that the time span needs to be broken up in hours. So, you need to start with all hours in the day. Then you calculate the overlap, sum up the differences (below in milliseconds) and convert everything back to a time for the output.
with const as (
select dateadd(hour, 1, cast(cast(getdate() -1 as date) as datetime)) as midnight
),
allhours as (
select 0 as hour, midnight as timestart, dateadd(hour, 1, midnight) as timeend from const union all
select 1 as hour, dateadd(hour, 1, midnight), dateadd(hour, 2, midnight) from const union all
select 2 as hour, dateadd(hour, 2, midnight), dateadd(hour, 3, midnight) from const union all
. . .
select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const
)
select ah.hour,
sum(datediff(ms, (case when ah.timestart >= dt.begin then timestart else dt.begin end),
(case when ah.timeend <= dt.end then ah.timeend else dt.end end)
)
) as totalms,
cast(dateadd(ms, sum(datediff(ms, (case when ah.timestart >= dt.begin then timestart else dt.begin end),
(case when ah.timeend <= dt.end then ah.timeend else dt.end end)
)
),
0) as time
) as totalTime
from allhours ah left outer join
DeviceTable dt
on ah.timestart< coalesce(dt.end, getdate()) and
ah.timeend >= dt.begin
group by ah.hour
order by ah.hour
此外，要使这项工作正常进行，您需要将begin"和end"括在双引号或方括号中.这些是 T-SQL 中的保留字.你需要替换......"3 到 22 小时有额外的线路.
Also, to make this work, you need to wrap "begin" and "end" in double quotes or square brackets. These are reserved words in T-SQL. And you need to replace the ". . ." with additional lines for hours from 3 to 22.
这篇关于TSQL 查询返回过去 24 小时内每小时的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
