T-SQL计算每小时更改状态之间的时间
大家好，
我有多个停车位，只能发送变化的状态。当有汽车到达停车场时，它会发送“ 1”，然后直到汽车再次离开时它才发送任何东西。在那一刻，停车发送“ 0”。我需要做很长时间的分析，所以看到每小时的时间量不太多（以分钟为单位）会很棒。
数据看起来像这样（根据要求，我将其减少为parking-ID 10，而最后一条记录从19.12。开始，记录从20.12。开始）：
+------------+------------------+--------+-------------+
| Parking-ID | DateTime | Status | Comment |
+------------+------------------+--------+-------------+
| 10 | 20.12.2019 16:35 | 0 | Car left |
+------------+------------------+--------+-------------+
| 10 | 20.12.2019 08:22 | 1 | Car arrived |
+------------+------------------+--------+-------------+
| 10 | 19.12.2019 22:47 | 0 | Car left |
+------------+------------------+--------+-------------+
现在不要对我来说太轻松了，在“免费”和“已采取”状态旁边，还有一个温暖的状态。汽车停放1小时后，停车场应标记为“暖”，因为有些汽车必须在几分钟内快速驶入和驶出，并且该时间范围应显示为“温暖”。
为了避免获得太多的行（例如每分钟），如果能每小时获得摘要，我将不胜感激。对于我的分析，我应该能够看到每天停车了几个小时，温暖了几个小时以及有几个小时是免费的。
因此结果应如下所示（对于Parking-ID 10和对于20.12.2019）：
+------------+------------------+--------+----------+---------+
| Parking-ID | DateTime | Status | Duration | Comment |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 23:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 22:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 21:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 20:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 19:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 18:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 17:00 | 0 | 0.42 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 17:00 | 2 | 0.58 | Warm |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 16:00 | 2 | 0.42 | Warm |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 16:00 | 1 | 0.58 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 15:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 14:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 13:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 12:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 11:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 10:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 09:00 | 1 | 1.00 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 08:00 | 1 | 0.63 | Taken |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 08:00 | 0 | 0.37 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 07:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 06:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 05:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 04:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 03:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 02:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 01:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
| 10 | 20.12.2019 00:00 | 0 | 1.00 | Free |
+------------+------------------+--------+----------+---------+
有人有好的方法吗？我已经搜索并尝试过，但是找不到可行的方法。
展开
收起
1
条回答
写回答
写回答
-
替代和改进：
;WITH CTE AS (SELECT ParkingID, ParkingDateTime, COUNT(*) + 1 SplitCount, ParkingStatus AS InitialStatus FROM #ParkingTemp GROUP BY ParkingID, ParkingDateTime, ParkingStatus), DistinctIDCTE AS (SELECT DISTINCT ParkingID FROM #ParkingTemp), CTE1 AS (SELECT Dates, DATEADD(hour, hrs, Dates) ReportDateTime, ParkingID FROM [CalendarDate], #Number N, DistinctIDCTE WHERE dates >= @From AND Dates <= @To), CTE2 AS (SELECT c.ParkingID, DATEADD(minute, -DATEPART(minute, ParkingDateTime), ParkingDateTime) ParkingDate, ParkingDateTime, hrs AS rownum, InitialStatus FROM CTE C CROSS APPLY ( SELECT hrs FROM #Number N WHERE c.SplitCount > n.Hrs ) ca), CTE5 AS (SELECT c4.ParkingID, c4.ReportDateTime, CASE WHEN rownum = 0 AND InitialStatus = 1 THEN 2 WHEN rownum = 1 AND InitialStatus = 1 THEN 3 WHEN rownum = 0 AND InitialStatus = 0 THEN 4 WHEN rownum = 1 AND InitialStatus = 0 THEN 3 ELSE 2 END AS ParkingStatusid, CASE WHEN rownum = 0 THEN DATEDIFF(minute, ReportDateTime, ParkingDateTime) WHEN rownum = 1 THEN 60 - DATEPART(minute, ParkingDateTime) ELSE 1.00 END Duration, ParkingDateTime, rownum, InitialStatus FROM CTE1 c4 LEFT JOIN CTE2 c2 ON c4.ParkingID = c2.ParkingID AND c2.ParkingDate = c4.ReportDateTime) SELECT c5.ParkingID, c5.ReportDateTime, c5.ParkingStatusid, Duration, PS.StatusName AS Comment FROM CTE5 c5 INNER JOIN #ParkingStatus ps ON c5.ParkingStatusid = ps.ParkingStatus ORDER BY ReportDateTime DESC;注意：清除我的疑惑。在同一小时内，将不同的样本数据扔到一个停车位中，例如一小时内。
2020-01-05 14:19:04赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
