Stats

Contributors: 2 Friday, June 23, 2017
Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Rip Tutorial: riptutorial@gmail.com
Roadmap: roadmap

Finding "out-of-sequence" records using the LAG() function

Example

Given these sample data:

IDSTATUSSTATUS_TIMESTATUS_BY
1ONE2016-09-28-19.47.52.501398USER_1
3ONE2016-09-28-19.47.52.501511USER_2
1THREE2016-09-28-19.47.52.501517USER_3
3TWO2016-09-28-19.47.52.501521USER_2
3THREE2016-09-28-19.47.52.501524USER_4

Items identified by ID values must move from STATUS 'ONE' to 'TWO' to 'THREE' in sequence, without skipping statuses. The problem is to find users (STATUS_BY) values who violate the rule and move from 'ONE' immediately to 'THREE'.

The LAG() analytical function helps to solve the problem by returning for each row the value in the preceding row:

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'

In case your database doesn't have LAG() you can use this to produce the same result:

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'