Finding discontinuities from a SQL table

Question

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.

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.

If you can put them in a table with an index in the right order, the join should be very simple. — Dennis Jaheruddin, Aug 15 '13 at 13:01
The table is indexed on the timestamp, but I can't really see the simple solution. — Anlo, Aug 15 '13 at 13:02
Add a table where line 1 has number 1 and line 2 has number 2 etc. Then you can just select where `T2.id = T1.id+1` and `T2.num — Dennis Jaheruddin, Aug 15 '13 at 13:05
This question may be of some use http://stackoverflow.com/questions/710212/there-is-a-way-to-access-the-previous-row-value-in-a-select-statement — JsonStatham, Aug 15 '13 at 13:20

podiluska · Answer 1 · 2013-08-15T13:13:31.527

11

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.

;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

edited Aug 15 '13 at 13:13

answered Aug 15 '13 at 13:05

podiluska

50,950
7
98
104

I would actually not compare the ts and instead add count as a second sort argument (decreasing) as one could consider 2 count values at the same ts an anomaly as well. – Dennis Jaheruddin Aug 15 '13 at 13:10

valex · Accepted Answer · 2013-08-15T13:29:57.163

6

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

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

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

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

SQLFiddle demo

edited Aug 15 '13 at 13:29

answered Aug 15 '13 at 13:13

valex

23,966
7
43
60

2

Not in SQL 2008 you can't. – podiluska Aug 15 '13 at 13:18
2

But, nevertheless, a nice feature to have in MSSQL2012! Thanks for showing us anyway. ;-) – Carsten Massmann Aug 15 '13 at 13:22
@podiluska: You're right. Sorry I've missed "sql-server-2008" tag :( – valex Aug 15 '13 at 13:23
Too bad I'm stuck with 2008, since this was a nice clean solution. – Anlo Aug 15 '13 at 13:25
@Anlo I've added the query version for 2008 server. – valex Aug 15 '13 at 13:31
1

Wow... I expected a subquery to be slower than podiluska's and Nenad Zivkovic's suggestions. Checking the execution plans on my 200k row table, the query cost came out as 1:11 in favor of the subquery version. Note to self, NEVER guess which query will run faster... – Anlo Aug 15 '13 at 13:50

score 1 · Answer 3 · answered Aug 15 '13 at 13:09

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

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

Finding discontinuities from a SQL table

3 Answers3