sql - 查询SQL中的行顺序

Question

假设我将与events关联的users如下存储在表中(其中dt代表事件的时间戳):

| dt | user | event |
|  1 |  1   |   A   |
|  2 |  1   |   D   |
|  3 |  1   |   B   |
|  4 |  1   |   C   |
|  5 |  1   |   B   |
|  6 |  2   |   B   |
|  7 |  2   |   B   |
|  8 |  2   |   A   |
|  9 |  2   |   A   |
| 10 |  2   |   C   |

这样我们可以说:

用户1具有ADBCB的事件序列

用户2具有事件序列BBAAC

我想针对这些用户回答的问题类型非常容易表示为事件序列的常规表达式，例如“哪些用户的事件序列匹配A. * B？”或“哪些用户的事件序列与A [^ C] * B [^ C] * D匹配？”等等。

在此表结构上可以用来回答类似查询的SQL技巧或运算符是什么？

有没有一种方法可以有效/动态地生成 user -to- event-sequence表，然后可以使用正则表达式查询该表？

我目前正在使用Postgres，但我很想知道是否有任何较大的DBMS(例如SQLServer或Oracle)也为此配备了专门的运算符。

Answer 1

使用Postgres 9.x，这实际上很容易:

select userid, 
       string_agg(event, '' order by dt) as event_sequence
from events
group by userid;

使用该结果，您现在可以在event_sequence上应用正则表达式:

select * 
from (
  select userid, 
         string_agg(event, '' order by dt) as event_sequence
  from events
  group by userid
) t
where event_sequence ~ 'A.*B'

使用Postgres 8.x，您需要找到string_agg()函数的替代品(仅适用于google，那里有很多示例)，并且需要进行子选择以确保聚合的排序为8.x确实在聚合函数中支持 order by。