TSQL - COMPTER le nombre de lignes dans un état différent de la ligne actuelle

Question

Il est assez difficile à expliquer, mais à partir de cet exemple, cela devrait être clair.

Table TABLE:

 Nom  État Date
--------------------
A    1    1/4/2012
B    0    1/3/2012
C    0    1/2/2012
D    1    1/1/2012

Aimerait:

 select * from TABLE where state=1 order by Time desc

plus une colonne supplémentaire 'Skipped' contenant le nombre de lignes après une où l'état=1 en état 0, en d'autres termes la sortie devrait ressembler à ceci :

Nom État Date       Skipped
A   1    1/4/2012    2        -- 2 lignes après A où l'état != 1
D   1    1/1/2012    0        -- 0 lignes après D où l'état != 1

0 devrait également être signalé en cas de 2 lignes consécutives où l'état = 1, c'est-à-dire qu'il n'y a rien entre ces lignes dans un état autre que 1.

Il semble que les CTE sont nécessaires ici, mais je ne peux pas trouver comment compter les lignes où l'état != 1. Toute aide sera appréciée.

(MS Sql Server 2008)

Answer 1

J'ai utilisé un CTE pour établir RowNo, de sorte que vous ne dépendiez pas des dates consécutives :

WITH CTE_Rows as 
(
    select name,state,time,
    rowno = ROW_NUMBER() over (order by [time])
    from MyTable
)
select name,state,time,
    gap = isnull(r.rowno - x.rowno - 1,0)
from
    CTE_Rows r
    outer apply (
        select top 1 rowno 
        from CTE_Rows sub 
        where sub.rowno < r.rowno and sub.state = 1
        order by sub.rowno desc) x
where r.state = 1

Si vous souhaitez simplement le faire par date, c'est plus simple - il suffit d'utiliser un outer apply:

select name,state,r.time,
    gap = convert(int,isnull(r.time - x.time - 1,0))
from
    MyTable r
    outer apply (
        select top 1 time 
        from MyTable sub 
        where sub.time < r.time and sub.state = 1
        order by sub.time desc) x
where r.state = 1

Pour information, les données de test utilisées ont été créées comme suit :

create table MyTable
(Name char(1), [state] tinyint, [Time] datetime)

insert MyTable 
values
('E',1,'2012-01-05'),
('A',1,'2012-01-04'),
('B',0,'2012-01-03'),
('C',0,'2012-01-02'),
('D',1,'2012-01-01')

Answer 2

D'accord, voici (ça devient un peu compliqué) :

SELECT U.CurrentTime, 
       (SELECT COUNT(*) 
        FROM StateTable AS T3 
        WHERE T3.State=0 
        AND T3.Time BETWEEN U.LastTime AND U.CurrentTime) AS Skipped       
FROM (SELECT T1.Time AS CurrentTime, 
             (SELECT TOP 1 T2.Time 
              FROM StateTable AS T2 
              WHERE T2.Time < T1.Time AND T2.State=1 
              ORDER BY T2.Time DESC) AS LastTime 
      FROM StateTable AS T1 WHERE T1.State = 1) AS U