Menemukan diskontinuitas dari tabel SQL

Mungkin ada solusi yang cukup sederhana untuk masalah saya, tapi saya mengalami kesulitan merumuskan frase pencarian yang baik untuk itu. Saya memiliki tabel yang berisi cap waktu dan jumlah:

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

Seperti yang Anda lihat, hitungannya bertambah dan kemudian dikosongkan sesekali. Mencari baris dengan jumlah = 0 sangatlah mudah, namun terkadang jumlah tersebut bertambah sebelum jumlah nol dicatat. Pada pukul 14:49 hitungannya menjadi 12, kemudian direset menjadi 0 dan ditambah menjadi 2 sebelum log berikutnya pada pukul 14:50.

Saya perlu membuat daftar stempel waktu yang hitungannya kurang dari hitungan sebelumnya:

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

Saya mulai membuat gabungan pada tabel itu sendiri, untuk membandingkan dua baris tetapi SQL segera menjadi sangat berantakan.


sql sql-server tsql sql-server-2008
person Anlo    schedule 15.08.2013    source sumber
comment
Jika Anda bisa meletakkannya di tabel dengan indeks dalam urutan yang benar, penggabungannya akan sangat sederhana.   -  person Dennis Jaheruddin    schedule 15.08.2013
comment
Tabel diindeks pada stempel waktu, tetapi saya tidak dapat melihat solusi sederhananya.   -  person Anlo    schedule 15.08.2013
comment
Tambahkan tabel yang baris 1 bernomor 1 dan baris 2 bernomor 2 dst. Kemudian Anda tinggal memilih di mana T2.id = T1.id+1 dan T2.num<T1.num   -  person Dennis Jaheruddin    schedule 15.08.2013
comment
Pertanyaan ini mungkin berguna stackoverflow.com/questions/710212/   -  person JsonStatham    schedule 15.08.2013

Jawaban (3)


arrow_upward
6
arrow_downward

Juga dalam hal ini Anda dapat menggunakan fungsi 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

Demo SQLFiddle

UPD: LEAD() tersedia dari versi SQLServer 2012. Pada tahun 2008 Anda dapat menggantinya dengan subquery:

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

Demo SQLFiddle

person valex    schedule 15.08.2013
comment
Tidak di SQL 2008 Anda tidak bisa. - person podiluska; 15.08.2013
comment
Namun, bagaimanapun, fitur bagus untuk dimiliki di MSSQL2012! Terima kasih sudah menunjukkannya kepada kami. ;-) - person Carsten Massmann; 15.08.2013
comment
@podiluska: Anda benar. Maaf saya melewatkan tag sql-server-2008 :( - person valex; 15.08.2013
comment
Sayang sekali saya terjebak dengan tahun 2008, karena ini adalah solusi bersih yang bagus. - person Anlo; 15.08.2013
comment
@Anlo Saya telah menambahkan versi kueri untuk server 2008. - person valex; 15.08.2013
comment
Wow... Saya berharap subquery lebih lambat dari saran podiluska dan Nenad Zivkovic. Memeriksa rencana eksekusi pada tabel baris 200k saya, biaya kueri keluar sebagai 1:11 mendukung versi subkueri. Catatan untuk diri sendiri, JANGAN PERNAH menebak kueri mana yang akan berjalan lebih cepat... - person Anlo; 15.08.2013

arrow_upward
10
arrow_downward

Tujuannya adalah membuat nomor baris berdasarkan kolom ts (datetime), sehingga lebih mudah untuk digabungkan dengan entri sebelumnya. Kemudian membandingkan waktu dan jumlah untuk menemukan pengecualian.

;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
Saya sebenarnya tidak akan membandingkan ts dan malah menambahkan hitungan sebagai argumen pengurutan kedua (menurun) karena seseorang dapat menganggap 2 nilai hitungan pada saat yang sama juga merupakan anomali. - person Dennis Jaheruddin; 15.08.2013

arrow_upward
1
arrow_downward

Dengan menggunakan fungsi ROW_NUMBER() Anda dapat menetapkan nomor pada pesanan Anda dan menggunakannya untuk bergabung :

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

DEMO SQLFiddle

person Nenad Zivkovic    schedule 15.08.2013