Verwenden von SQL (oder T-SQL), Wie bekomme ich die Länge der time ein Wert war 0 oder 1 pro Stunde aus einer Tabelle, die das date und die Uhrzeit ein Wert gesetzt wurde?

Ich habe einen Tisch mit folgenden data:

StartTime Value ---------------------------------------------- 2016-12-20 08:09:00 1 2016-12-20 09:53:00 0 2016-12-20 11:10:21 1 2016-12-20 11:30:00 1 2016-12-20 12:20:30 0 .... .... .... .... 2016-12-20 23:44:10 1 2016-12-21 00:33:00 0 2016-12-21 01:05:05 0 

Also, was ich versuche zu tun ist zu finden, wie lange ein Wert war 0 oder 1 während jeder Stunde, wo die Stunden von 00:00:00 – 01:00:00, dann von 01:00:00 – 02: 00:00 … 23:00:00 – 00:00:00. Was ich erreichen möchte, sieht so aus:

  HourStart Period Value Was 0 Period Value Was 1 -------------------------------------------------------------------------- 2016-12-20 00:00:00 50 min 10 min 2016-12-20 01:00:00 30 30 2016-12-20 02:00:00 32 28 2016-12-20 03:00:00 60 00 2016-12-20 04:00:00 21 39 .... .... .... .... .... .... 2016-12-20 21:00:00 30 30 2016-12-20 22:00:00 20 40 2016-12-20 23:00:00 10 50 2016-12-21 00:00:00 01 59 2016-12-21 01:00:00 30 30 .... .... 

Übrigens verwende ich Microsoft Transact-SQL. Vielen Dank.

Hier ist ein path, dies zu tun:

Zuerst erstellen und befüllen Probe Tabelle ( Bitte speichern Sie uns diesen Schritt in Ihre zukünftigen Fragen)

 DECLARE @T as TABLE ( startTime datetime, value int ) INSERT INTO @T VALUES ('2016-12-20 08:09:00', 1), ('2016-12-20 09:53:00', 0), ('2016-12-20 11:10:21', 1), ('2016-12-20 11:30:00', 1), ('2016-12-20 12:20:30', 0), ('2016-12-20 23:44:10', 1), ('2016-12-21 00:33:00', 0), ('2016-12-21 01:05:05', 0) 

Dann verwenden Sie ein paar gemeinsame Tabellenausdrücke – eine, um die Minuten und Sekunden der Startzeit loszuwerden, und die zweite, die Startzeit der vorherigen Zeile zu erhalten:

 ;WITH cte1 as ( SELECT StartTime, value, DATEADD(SECOND, -DATEPART(SECOND, startTime), DATEADD(MINUTE, -DATEPART(MINUTE, startTime), startTime)) As startHour FROM @T ), cte2 as ( SELECT StartTime, value, StartHour, DATEDIFF(MINUTE, ISNULL(LAG(startTime) OVER(Partition by startHour ORDER BY startTime), startHour), startTime) As minutes FROM cte1 ) 

Dann wähle aus dem zweiten cte aus:

 SELECT startHour, value, CASE WHEN value = 1 THEN minutes ELSE 60 - minutes END as minutes_1, CASE WHEN value = 0 THEN minutes ELSE 60 - minutes END as minutes_0 FROM cte2 

Ergebnisse:

 startHour value minutes_1 minutes_0 20.12.2016 08:00:00 1 9 51 20.12.2016 09:00:00 0 7 53 20.12.2016 11:00:00 1 10 50 20.12.2016 11:00:00 1 20 40 20.12.2016 12:00:00 0 40 20 20.12.2016 23:00:00 1 44 16 21.12.2016 00:00:00 0 27 33 21.12.2016 01:00:00 0 55 5