拆分日期范围和操作数据

Question

我现在正在进行机器进度项目。我有一个我为记录机器状态而创建的表。在这张表中，我记录了开始和完成状态。现在，如果我输入某个时期之间的日期，我想根据该表创建报告。这是我想要的表格和预期结果。

来源表：

machineid |      starttime      |      endtime        |  status | 
-----------+---------------------+---------------------+---------+
M1      | 2020-01-01 00:00:00 | 2020-01-04 17:00:00 | running |
M1      | 2020-01-04 17:00:00 | 2020-01-04 18:00:00 | down    |
M1      | 2020-01-04 18:00:00 | 2020-01-05 14:00:00 | idle    |
M2      | 2020-01-01 00:00:00 | 2020-01-05 09:00:00 | running |
M2      | 2020-01-05 09:00:00 | 2020-01-05 18:00:00 | idle    |  

预期结果：

输入开始日期报告：2020-01-01 00:00:00和输入结束日期报告：2020-01-05 23:59:59

machineid |    date    | running | idle | down | total |
----------+------------+---------+------+------+-------+
M1     | 2020-01-01 |    24   |   0  |   0  |   24  |
M1     | 2020-01-02 |    24   |   0  |   0  |   24  |
M1     | 2020-01-03 |    24   |   0  |   0  |   24  |
M1     | 2020-01-04 |    17   |   6  |   1  |   24  |
M1     | 2020-01-05 |     0   |  14  |   0  |   14  |
M2     | 2020-01-01 |    24   |   0  |   0  |   24  |
M2     | 2020-01-02 |    24   |   0  |   0  |   24  |
M2     | 2020-01-03 |    24   |   0  |   0  |   24  |
M2     | 2020-01-04 |    24   |   0  |   0  |   24  |
M2     | 2020-01-05 |     9   |   9  |   0  |   18  |

有什么建议吗？

Answer 1

您可以使用CTE:

DECLARE @tmp TABLE(machineid varchar(3), starttime  datetime, endtime datetime,  [status] varchar(20)) 
INSERT INTO @tmp (machineid, starttime, endtime,  [status])
VALUES('M1' , '2020-01-01 00:00:00' , '2020-01-04 17:00:00' , 'running') ,
('M1' , '2020-01-04 17:00:00' , '2020-01-04 18:00:00' , 'down') ,
('M1' , '2020-01-04 18:00:00' , '2020-01-05 14:00:00' , 'idle') ,
('M2' , '2020-01-01 00:00:00' , '2020-01-05 09:00:00' , 'running') ,
('M2' , '2020-01-05 09:00:00' , '2020-01-05 18:00:00' , 'idle')
;WITH CTE AS
(
--initial data
SELECT machineid, starttime, endtime, [status]
FROM @tmp 
--recursive part
UNION ALL
SELECT machineid, DATEADD(HH, 1, starttime) starttime, endtime, [status]
FROM CTE
WHERE DATEADD(HH, 1, starttime) < endtime 
)
SELECT machineid, [date], COALESCE([running], 0) [running], COALESCE([idle], 0) [idle], COALESCE([down], 0) [down], COALESCE([running], 0)  + COALESCE([idle], 0) + COALESCE([down], 0) total
FROM
(
SELECT machineid, CONVERT(date, starttime) [date], [status], COUNT(starttime) cnt
FROM CTE
WHERE CONVERT(date, starttime) BETWEEN '2020-01-01' AND '2020-01-05'
GROUP BY machineid, CONVERT(date, starttime), [status]
) DT
PIVOT(MAX(cnt) FOR [status] IN ([running], [idle], [down])) PT
ORDER BY machineid, [date]
OPTION (MAXRECURSION 0)

Answer 2

我同意递归CTE来扩展数据(作为最简单的代码解决方案(。然而，我认为条件聚合比pivot:简单得多

WITH cte AS (
SELECT machineid, starttime, endtime, status
FROM t
UNION ALL
SELECT machineid, DATEADD(hour, 1, starttime) as starttime, endtime, [status]
FROM CTE
WHERE DATEADD(hour, 1, starttime) < endtime 
)
SELECT machineid, CONVERT(date, starttime) as dte,
SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) as running,
SUM(CASE WHEN status = 'idle' THEN 1 ELSE 0 END) as idle,
SUM(CASE WHEN status = 'down' THEN 1 ELSE 0 END) as down,
COUNT(*) as total
FROM cte
WHERE starttime >= '2020-01-01' AND 
starttime < '2020-01-06'
GROUP BY machineid, CONVERT(date, starttime)
ORDER BY machineid, CONVERT(date, starttime)
OPTION (MAXRECURSION 0);

两个音符。首先，这不包括没有活动的日子。您的样本数据并非如此。

其次，这可以提高效率。然而，在你的问题中，性能似乎不是一个问题。