如何返回符合特定事件序列的行？ [英] How can I return rows that meet a specific sequence of events?

问题描述

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

I am trying to pull records for UserIDs that meet a certain sequence of events. If a user has a JOIN, then a subsequent CANCEL, and then a subsequent JOIN, I want to return them in the result set. I need to run this query for one day at a time, or several days at a time, as needed.

下表显示了满足和不满足的UserID的示例

The table below shows examples of UserIDs that meet and do not meet the sequence.

+--------+--------+---------------------+------------+------------------+
| 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               |
+--------+--------+---------------------+------------+------------------+

我已经阅读了有关差距和孤岛的内容，并浏览了各种复杂的论坛帖子，这些帖子围绕着我想要实现的目标而变化。

I have read up on gaps and islands, and looked at all sorts of complicated forum posts that dance around what I'm trying to achieve.

当前，我所能做的就是查看一天的记录，而对我需要的顺序逻辑没有任何限制：

Currently, all I'm able to do is look at one day's worth of records, with no constraint on the sequence logic that I need:

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

谢谢您的输入！

推荐答案

在我的示例查询中，我将尽力提供您所提供的信息，但是您不清楚源表的外观。您在上方显示了一个表（没有名称），但随后在示例查询中引用了两个不同的表...有点难以理解发生了什么。

In my sample queries I'll do the best I can with the information you've given, but you're a little unclear about what the source table(s) look like. You show one table above (with no name), but then reference two different tables in your sample query... a little hard to see what's going on.

所以我'假设一个名为 t 的表，您可以根据需要进行调整...

So I'll assume a single table, named t, and you can adjust as needed...

然后可以解决这个问题，首先要确定用户

Then how I would handle this, is first identify the users

select distinct userid
  from            t first_join
       inner join t cancel
               on first_join.tmstmp < cancel.tmstp
              and first_join.userid = cancel.userid
       inner join t.second_join
               on second_join.tmstmp > cancel.tmstp
              and second_join.userid = cancel.userid
 where first_join.actiontype = 'JOIN'
   and cancel.actiontype = 'CANCEL'
   and second_join.actiontype = 'JOIN'

现在您可以获取这些用户的所有记录

So now you can get all records for those users

SELECT *
  FROM T
 WHERE USERID IN (
    select distinct userid
      from            t first_join
           inner join t cancel
                   on first_join.tmstmp < cancel.tmstp
                  and first_join.userid = cancel.userid
           inner join t.second_join
                   on second_join.tmstmp > cancel.tmstp
                  and second_join.userid = cancel.userid
     where first_join.actiontype = 'JOIN'
       and cancel.actiontype = 'CANCEL'
       and second_join.actiontype = 'JOIN'
     )

这篇关于如何返回符合特定事件序列的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！