如何返回满足特定事件序列的行？

我正在尝试提取满足特定事件序列的 UserID 的记录。如果用户有一个 JOIN，然后是一个后续的 CANCEL，然后是一个后续的 JOIN，我想在结果集中返回它们。我需要根据需要一次运行此查询一天，或一次运行几天。

下表显示了满足和不满足该顺序的 UserID 的示例。

+--------+--------+---------------------+------------+------------------+
| rownum | UserID |      Timestamp      | ActionType | Return in query? |
+--------+--------+---------------------+------------+------------------+
|      1 |  12345 | 2016-11-01 08:25:39 | JOIN       | yes              |
|      2 |  12345 | 2016-11-01 08:27:00 | NULL       | yes              |
|      3 |  12345 | 2016-11-01 08:28:20 | DOWNGRADE  | yes              |
|      4 |  12345 | 2016-11-01 08:31:34 | NULL       | yes              |
|      5 |  12345 | 2016-11-01 08:32:44 | CANCEL     | yes              |
|      6 |  12345 | 2016-11-01 08:45:51 | NULL       | yes              |
|      7 |  12345 | 2016-11-01 08:50:57 | JOIN       | yes              |
|      1 |   9876 | 2016-11-01 16:05:42 | JOIN       | yes              |
|      2 |   9876 | 2016-11-01 16:07:33 | CANCEL     | yes              |
|      3 |   9876 | 2016-11-01 16:09:09 | JOIN       | yes              |
|      1 |  56565 | 2016-11-01 18:15:16 | JOIN       | no               |
|      2 |  56565 | 2016-11-01 19:22:25 | CANCEL     | no               |
|      3 |  56565 | 2016-11-01 20:05:05 | CANCEL     | no               |
|      1 |  34343 | 2016-11-01 05:32:56 | JOIN       | no               |
+--------+--------+---------------------+------------+------------------+

我阅读了有关差距和岛屿的内容，并查看了各种复杂的论坛帖子，这些帖子围绕着我想要实现的目标跳舞。

目前，我所能做的就是查看一天的记录，对我需要的序列逻辑没有限制：

SELECT
    ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY tmsmp) rownum
    ,UserID
    ,tmstmp
    ,ActionType
FROM
    t
    INNER JOIN  (
                SELECT UserID
                FROM t
                WHERE tmstmp BETWEEN '2016-11-20 00:00:01' AND '2016-11-20 11:59:59'
                GROUP BY UserID
                HAVING COUNT(*) >= 2
                ) AS sub ON t1.UserID = sub.UserID

谢谢您的意见！

您可以使用LEAD() :

SELECT * FROM (
    SELECT t.* ,
           LAG(t.ActionType,1) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS LAST_ACTION,
           LAG(t.ActionType,2) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS LAST_ACTION2,
           LEAD(t.ActionType,1) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS NEXT_Action,
           LEAD(t.ActionType,2) OVER(PARTITION BY t.userid ORDER BY t.timestamp) AS NEXT_Action2
    FROM YourTable t
    WHERE tmstmp BETWEEN <Start> AND <End>)
WHERE (t.actionType = 'JOIN' AND
      t.NEXT_Action = 'Cancel' AND
      t.NEXT_Action2 = 'JOIN')
  OR (t.LAST_ACTION= 'JOIN' AND
      t.actionType= 'Cancel' AND
      t.NEXT_Action = 'JOIN')
  OR (t.LAST_ACTION2= 'JOIN' AND
      t.LAST_ACTION = 'Cancel' AND
      t.actionType= 'JOIN')