Поиск разрывов из таблицы SQL

Вероятно, есть довольно простое решение моей проблемы, но у меня есть отличная возможность сформулировать для него хорошую поисковую фразу. У меня есть таблица с отметками времени и счетчиками:

2013-08-15 14:43:58.447    5
2013-08-15 14:44:58.307    12
2013-08-15 14:45:58.383    14
2013-08-15 14:46:58.180    0
2013-08-15 14:47:58.210    4
2013-08-15 14:48:58.287    6
2013-08-15 14:49:58.550    12
2013-08-15 14:50:58.440    2
2013-08-15 14:51:58.390    5

Как видите, счетчик увеличивается, а затем время от времени опорожняется. Поиск строк, в которых count = 0, прост, но иногда счет увеличивается до того, как будет зарегистрирован нулевой счетчик. В 14:49 счетчик равен 12, затем он сбрасывается до 0 и увеличивается до 2 перед следующей записью в 14:50.

Мне нужно перечислить временные метки, где счетчик меньше, чем счетчик раньше:

2013-08-15 14:46:58.180    0
2013-08-15 14:50:58.440    2

Я начал объединять саму таблицу, чтобы сравнить две строки, но вскоре SQL стал очень запутанным.


sql sql-server tsql sql-server-2008
person Anlo    schedule 15.08.2013    source источник
comment
Если вы можете поместить их в таблицу с индексом в правильном порядке, соединение должно быть очень простым.   -  person Dennis Jaheruddin    schedule 15.08.2013
comment
Таблица проиндексирована по метке времени, но я не вижу простого решения.   -  person Anlo    schedule 15.08.2013
comment
Добавьте таблицу, в которой строка 1 имеет номер 1, а строка 2 - номер 2 и т. Д. Затем вы можете просто выбрать, где T2.id = T1.id+1 и T2.num<T1.num   -  person Dennis Jaheruddin    schedule 15.08.2013
comment
Этот вопрос может быть полезен stackoverflow.com/questions/710212/   -  person JsonStatham    schedule 15.08.2013

Ответы (3)


arrow_upward
6
arrow_downward

Также в этом случае вы можете использовать функцию LEAD ():

with CTE as
(
select t.*, LEAD(ct) OVER (ORDER BY dt DESC) as LEAD_CT from t
)  
select dt,ct from CTE where LEAD_CT>CT

демонстрация SQLFiddle

UPD: LEAD () доступен с версии SQLServer 2012. В 2008 году вы можно заменить его подзапросом:

select *
      FROM T as T1
      where (SELECT TOP 1 ct FROM T 
                             WHERE T.dt<T1.DT
                             ORDER BY dt DESC) >CT

демонстрация SQLFiddle

person valex    schedule 15.08.2013
comment
Не в SQL 2008. - person podiluska; 15.08.2013
comment
Но, тем не менее, это хорошая возможность в MSSQL2012! В любом случае спасибо, что показали нам. ;-) - person Carsten Massmann; 15.08.2013
comment
@podiluska: Ты прав. Извините, я пропустил тег sql-server-2008 :( - person valex; 15.08.2013
comment
Жаль, что я застрял в 2008 году, так как это было хорошее чистое решение. - person Anlo; 15.08.2013
comment
@Anlo Я добавил версию запроса для сервера 2008 года. - person valex; 15.08.2013
comment
Вау ... Я ожидал, что подзапрос будет медленнее, чем предложения Подилуски и Ненада Живковича. При проверке планов выполнения в моей таблице из 200 тыс. Строк, стоимость запроса оказалась 1:11 в пользу версии подзапроса. Примечание для себя, НИКОГДА не угадайте, какой запрос будет выполняться быстрее ... - person Anlo; 15.08.2013

arrow_upward
10
arrow_downward

Это создает номер строки на основе столбца ts (datetime), тогда легче присоединиться к предыдущей записи. Затем он сравнивает время и счетчики, чтобы найти исключения.

;with cte as 
(
    select * ,
      ROW_NUMBER() over (order by ts) rn    
    from yourtable
)
    select c1.* from cte c1
        inner join cte c2
            on c1.rn=c2.rn+1
            and c1.c < c2.c
person podiluska    schedule 15.08.2013
comment
На самом деле я бы не стал сравнивать ts и вместо этого добавил бы count в качестве второго аргумента сортировки (по убыванию), поскольку можно было бы рассматривать 2 значения count при одном и том же ts как аномалию. - person Dennis Jaheruddin; 15.08.2013

arrow_upward
1
arrow_downward

Используя функцию ROW_NUMBER(), вы можете назначать номера в своем заказе и использовать их для присоединения :

WITH CTE_RN AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY [TimeStamp]) RN
    FROM Table1
)
SELECT r2.* 
FROM CTE_RN r1
    INNER JOIN CTE_RN r2 ON r1.RN +1 = r2.RN
WHERE r1.count > r2.count

SQLFiddle DEMO

person Nenad Zivkovic    schedule 15.08.2013