使用 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'