SQL Server: trova i record consecutivi recenti maggiori di 5

Question

Ho bisogno di scrivere una query che mostri il risultato suddiviso per FormIDche ha un valore maggiore di 5 in base al più recente LogDate.

In base al più recente LogDate, se c'era un valore inferiore a 5, dovrebbe visualizzare i valori da quel punto che sono maggiori di 5 poiché i valori sotto 5 sono un "reset", se lo desideri.

Sto essenzialmente guardando i LogDaterecord consecutivi recenti che sono maggiori di 5.

Supponiamo di avere il seguente record di record:

FormID   Value  LogDate    
--------------------------
Form2    6      10/12/19   
Form2    7      10/13/19   
Form1    8      10/12/19
Form1    12     10/12/19
Form1    3      10/14/19
Form1    8      10/15/19
Form1    6      10/21/19  

Quanto segue restituirà quanto segue (tieni presente che mi piace mostrare anche il row_num:

 FormID   Value  LogDate   row_num
 ----------------------------------
 Form2    6      10/12/19  1
 Form2    7      10/13/19  2
 Form1    8      10/15/19  1
 Form1    6      10/21/19  2

Nota nell'esempio sopra, poiché il record seguente aveva un valore recente inferiore a 5 (valore di 3), è necessario ottenere i record superiori a 5.

Un altro esempio:

FormID   Value  LogDate     
Form1    8      10/15/19
Form1    3      10/21/19  

RISULTATO: non verrà visualizzato alcun risultato in quanto nel record recente è maggiore di 5

Un altro esempio:

FormID   Value  LogDate    
Form2    4      10/12/19   
Form2    3      10/13/19   
Form1    16     10/12/19
Form1    3      10/12/19
Form1    3      10/14/19
Form1    8      10/15/19
Form1    12     10/21/19 

Il risultato qui sarebbe:

FormID   Value  LogDate   row_num
Form1    8      10/15/19  1
Form1    12     10/21/19  2

Un altro esempio:

FormID   Value  LogDate    
Form1    12      10/12/19   
Form2    13      10/13/19  

Risultato:

FormID   Value  LogDate    row_num
Form1    12      10/12/19  1 
Form2    13      10/13/19  2

Dalla mia comprensione, questo può essere fatto con la funzione LAG ma non sono sicuro di come metterlo del tutto.

Possiamo fare qualcosa di simile a quanto segue:

   DECLARE @mytable TABLE
   (
     FormID VARCHAR(50), 
     [Value] INT, 
     LogDate DATETIME
    )

    select t.*, 
        lag(value) over(partition by formid order by logdate) lag_value
    from @mytablet

Ma non sono sicuro di come mettere tutto insieme.

Answer 1

Se ti seguo correttamente, puoi farlo con le funzioni della finestra come questa:

select 
from (
    select t.*, 
        row_number() over(partition by formid order by logdate desc) rn,
        sum(case when value > 5 then 1 else 0 end) over(partition by formid order by logdate desc) grp
    from mytable t
) t
where rn = grp

L'idea è confrontare il numero di valori sopra 5con un numero di riga, contando dal valore più recente. Le righe in cui i due valori sono uguali possono essere mantenute.

Answer 2

Trova una risposta indicativa in violino .

Sono reset_calendarle date in cui è avvenuto un ripristino e vengono utilizzate per filtrare i dati.

SELECT temp.*,
       ROW_NUMBER() OVER (PARTITION BY temp.FormID ORDER BY temp.LogDate) AS Sequence
FROM (
  SELECT t.*
  FROM t
  LEFT JOIN (
    SELECT FormID, MAX(LogDate) AS recent_reset 
    FROM t
    WHERE Value<6
    GROUP BY FormID) AS reset_calendar
  ON t.FormID = reset_calendar.FormID
  WHERE t.LogDate > reset_calendar.recent_reset OR reset_calendar.recent_reset IS NULL)temp

Answer 3

Un metodo è:

select t.*,
       row_number() over (partition by formid order by logdate)
from t
where t.logdate > (select coalesce(max(t.logdate), '2000-01-01')
                   from t t2
                   where t2.formid = t.formid and t.value <= 5
                  );

Puoi anche usare le funzioni della finestra:

select t.*,
       row_number() over (partition by formid order by logdate)
from (select t.*,
             max(case when value <= 5 then logdate end) over (partition by formid) as logdate_5
      from t
     ) t
where logdate_5 is null or
      date > logdate_5
order by formid, logdate;