Erstellen einer Partition basierend auf der Differenz zwischen nachfolgenden Zeilenindizes in SQL server 2012

Ich verwende SQL server 2012.

Ich möchte eine row_number erstellen, je nachdem, ob der Index in nachfolgenden Zeilen um 1 oder mehr ansteigt. Zum Beispiel, sagen, ich habe einen Tisch, der aussieht:

event row_index 1 24 2 25 3 26 4 30 5 31 6 42 7 43 8 44 9 45 

Dann, was ich tun möchte, ist eine Spalte am Ende zu erstellen, genannt seq_ID:

 event row_index seq_id 1 24 1 2 25 1 3 26 1 4 30 2 5 31 2 6 42 3 7 43 3 8 44 3 9 45 3 

Grundsätzlich ist die seq_id nur Chancen, wenn der Unterschied zwischen nachfolgenden Zeilenindizes> 1. Ich habe versucht zu verwenden:

rank() over (partition by 'not sure what to put here' )

   

Versuche dies:

  ;with cte as (select *, coalesce(row_index - (lag(row_index) over (order by event)),1) diff from tbl ), cte2 as (select *, (select max(diff) from cte c where c.row_index <= d.row_index ) minri from cte d ) select event, row_index, minri, dense_rank() over (order by minri) rn from cte2 
  • Der erste CTE erhält die Unterschiede mit der lag function (ab SQL server 2012).
  • Der nächste CTE berechnet, wenn die Differenz 1 überschreitet und alle datasätze nach diesem Punkt einer 'Gruppe' zuweist, bis die nächste Differenz <> 1 gefunden wird. Dies ist der wichtigste Schritt in der Gruppierung.
  • Der letzte Schritt besteht darin, den dense_rank über den im vorherigen Schritt berechneten Indikator zu verwenden, um die Zeilennummern nach Bedarf zu erhalten.

Diese Lösung hat eine Einschränkung, dass es scheitert, wenn die Unterschiede nicht in aufsteigender Reihenfolge sind, dh wenn Sie zwei weitere Werte in den Beispieldaten wie 52 und 53 haben, wird sie sie in Gruppe 3 klassifizieren, anstatt eine neue Gruppe zu erstellen.

Demo

Update : Der folgende Ansatz kann die oben genannte Einschränkung überwinden:

  ;with cte as (select *, coalesce(row_index - (lag(row_index) over (order by event)),1) diff from tbl) ,cte2 as (select *, diff - coalesce(diff - (lag(diff) over (order by event)),0) tmp from cte d) select event,row_index, 1 + sum(case when tmp >= diff then 0 else 1 end) over (order by event) risum from cte2 

Wieder ist der erste Schritt derselbe. Aber in Schritt 2 prüfen wir nur den Übergang zu einem anderen Wert der Differenz zwischen aufeinanderfolgenden Werten, anstatt eine Min / Max-function zu verwenden. Das Ranking verwendet dann eine bedingte Summe, um eine Gruppe für jeden Wert in den Originaldaten zuzuordnen.

Demo

Dies kann weiter vereinfacht werden bei:

 select event, row_index, sum(case when diff <= 1 then 0 else 1 end) over (order by event) as rb from (select *, row_index - (lag(row_index) over (order by event)) diff from tbl ) s 

Mit einer riesigen Anzahl von Aufzeichnungen kann es schlecht laufen, aber probieren Sie es aus:

 ;with T as ( select event, rowindex, rowindex-row_number() over (order by event) as rn from YourTableName ) select event, (select count(distinct rn)+1 from T where rn<TT.rn) as seq_id from T TT order by event