使用 LAG() 函数查找失序记录

给出这些样本数据:

ID 状态 STATUS_TIME STATUS_BY
1 2016-09-28-19.47.52.501398 USER_1
3 2016-09-28-19.47.52.501511 USER_2
1 2016-09-28-19.47.52.501517 USER_3
3 2016-09-28-19.47.52.501521 USER_2
3 2016-09-28-19.47.52.501524 USER_4

ID 值标识的项目必须按顺序从 STATUS‘ONE’移动到’TWO’到’THREE’,而不跳过状态。问题是找到违反规则的用户(STATUS_BY)值并立即从 ONE 移动到 THREE

LAG() 分析函数通过为每一行返回前一行中的值来帮助解决问题:

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'

如果你的数据库没有 LAG(),你可以使用它来产生相同的结果:

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'