如何计算SQL中表满足条件的每小时时间量?

Question

抱歉，如果我没有给你正确的格式/打破礼仪，我通常发布python问题，它很容易创建问题的虚拟示例，但我的SQL技能还不够好。非常感谢您的反馈。

我在SQL中有一个运行表，它监视正在运行的东西的状态，并记录运行和停止周期的开始和结束，如下所示。真实表从过去的某个日期开始运行，并将持续到将来，状态可以持续几秒、几分钟、几天、几周，特别是停止。

为此，我们可以使用Hour表并将您的表左连接到它，我们可以使用计数表动态生成Hour表。我在这里使用了Itzik Ben-Gan的标准格式:

WITH
L0 AS ( SELECT 1 AS c 
FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L3 ),
Hours AS (
SELECT TOP(DATEDIFF(hour, @startDate, GETDATE()) + 1)
DATEADD(hour, rownum - 1, @startDate) AS startHour,
DATEADD(hour, rownum, @startDate) AS endHour
FROM Nums
ORDER BY rownum
)
SELECT
h.hour,
v.Status,
SUM(DATEDIFF(mi,
CASE WHEN h.hour > t.Start THEN h.hour ELSE t.Start END,
CASE WHEN h.hour < t.[End] THEN h.hour ELSE t.[End] END
))
FROM Hours h
CROSS JOIN (VALUES ('Run'), ('Stop') ) v(Status)
LEFT JOIN myTable t
ON t.Start < h.endHour AND t.[End] > h.startHour
AND t.Status = v.Status
GROUP BY h.startHour, v.Status;

DATEDIFF(min将只给你整分钟每行，所以如果你想计算分数，使用DATEDIFF(ms并除以60000.0