Bestimmen Sie die Zeitspanne zwischen 1 zu 0 Übergängen.

Question

Meine Tabelle zeigt den Pumpen-EIN/AUS-Status wie folgt an

Wert    Zeitstempel
1       2013-09-01 00:05:41.987
0       2013-09-01 00:05:48.987
1       2013-09-01 00:05:59.987
0       2013-09-01 00:06:15.987
1       2013-09-01 00:06:34.987
usw. usw. 

Ich brauche eine MSSQL-Abfrage, die einen Monat davon durchgehen und mir die Anzahl der Minuten EIN (1) und die Anzahl der Minuten AUS (0) mitteilen kann, d.h. die Tastverhältnis.

Answer 1

Mit CTE und der Funktion RowNumber() Fiddle-Demo:

declare @date date = '20130925'

;with cte as (
  select value, timestamp, row_number() over(order by timestamp) rn
  from table1
)
select c1.value, sum(datediff(second, c1.timestamp, c2.timestamp)) diffInSeconds
from cte c1 join cte c2 on c1.rn = c2.rn -1
where month(c1.timestamp) = month(@date) and month(c2.timestamp) = month(@date)
group by c1.value

Answer 2

Für SQL Server 2005 und höher @start und @end deklarieren den Zeitbereich, den Sie überprüfen müssen

mit Daten als
(
    wählen
        Wert,
        Zeitstempel,
        Zeilennummer() über (Zeitstempel) als aa
    von
        Daten
    wo
        Zeitstempel zwischen @start und @end
)
wählen
    onPayload,
    datediff(s,@start,@end)-onPayload als offPayload
von
(   
wählen
    sum(datediff(s,s.zeitstempel, e.zeitstempel)) als onPayload,
von
    Daten als s
innen beitreten
    Daten als e
auf
    s.aa = e.aa-1
wo
    s.Wert=1
und
    e.Wert=0
) als a

Answer 3

Versuchen Sie dies (SQL Server 2012):

SELECT Wert, SUM(sec) AS sec
FROM
(
        SELECT Wert, ISNULL(DATEDIFF(SECOND, Zeitstempel,
                LEAD(Zeitstempel,1) OVER (ORDER BY Zeitstempel)
                ),0) sec
        FROM tbl
) t
GROUP BY Wert;