从SQL表中查找不连续性

There is probably a quite simple solution to my problem, but I'm having great touble formulating a good search phrase for it. I have a table containing timestamps and counts:

对我的问题可能有一个非常简单的解决方案，但我正在为它制定一个好的搜索短语。我有一个包含时间戳和计数的表：

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

As you can see, the count increases and then gets emptied once in a while. Searching for the rows where count = 0 is easy, but sometimes the count is increased before the zero count has been logged. At 14:49 the count is 12, it is then reset to 0 and incremented to 2 before the next log at 14:50.

如您所见，计数增加，然后偶尔清空。搜索count = 0的行很容易，但有时计数会在记录零计数之前增加。在14:49，计数为12，然后重置为0并在下一个14:50的日志之前增加到2。

I need to list the timestamps where the count is less than the count before:

我需要列出计数小于之前的计数的时间戳：

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

I started to make a join on the table itself, to compare two rows but the SQL soon got very messy.

我开始在表本身上进行连接，比较两行，但SQL很快就变得非常混乱了。

3 个解决方案

#1

Also in this case you can use LEAD() function:

在这种情况下，您也可以使用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 demo

SQLFiddle演示

UPD: LEAD() is available from version SQLServer 2012. In 2008 you can replace it with a subquery:

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 demo

SQLFiddle演示

#2

What this does is it creates a row number based on the ts (datetime) column, then it is easier to join to the previous entry. It then compares the times and the counts to find the exceptions.

这样做是基于ts（日期时间）列创建行号，然后更容易加入到上一个条目。然后，它会比较时间和计数以查找异常。

;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

#3

Using ROW_NUMBER() function you can assign numbers on your order and use them for join:

使用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

SQLFiddle DEMO