SQL Recherche d'enregistrements "hors séquence" à l'aide de la fonction LAG ()


Exemple

Compte tenu de ces exemples de données:

ID STATUT STATUS_TIME STATUS_BY
1 UN 2016-09-28-19.47.52.501398 USER_1
3 UN 2016-09-28-19.47.52.501511 USER_2
1 TROIS 2016-09-28-19.47.52.501517 USER_3
3 DEUX 2016-09-28-19.47.52.501521 USER_2
3 TROIS 2016-09-28-19.47.52.501524 USER_4

Les éléments identifiés par ID valeurs ID doivent passer de STATUS 'ONE' à 'TWO' à 'THREE' dans l'ordre, sans ignorer les statuts. Le problème est de trouver des utilisateurs ( STATUS_BY ) qui violent la règle et passent de «UN» immédiatement à «TROIS».

La fonction analytique LAG() aide à résoudre le problème en renvoyant pour chaque ligne la valeur de la ligne précédente:

SELECT * FROM (
 SELECT 
  t.*, 
  LAG(status) OVER (PARTITION BY id ORDER BY status_time) AS prev_status 
  FROM test t
) t1 WHERE status = 'THREE' AND prev_status != 'TWO'

Si votre base de données n’a pas de LAG (), vous pouvez l’utiliser pour produire le même résultat:

SELECT A.id, A.status, B.status as prev_status, A.status_time, B.status_time as prev_status_time
FROM Data A, Data B
WHERE A.id = B.id
AND   B.status_time = (SELECT MAX(status_time) FROM Data where status_time < A.status_time and id = A.id)
AND   A.status = 'THREE' AND NOT B.status = 'TWO'