sql - Postgres 9.1 - 行编号组

Question

我有一些代表不同“ Action ”的数据。这些“ Action ”共同构成一个“事件”。

数据如下所示:

    EventID   |   UserID   |   Action   |        TimeStamp
--------------+------------+------------+-------------------------
       1      |    111     |   Start    |   2012-01-01 08:00:00
       1      |    111     |   Stop     |   2012-01-01 08:59:59
       1      |    999     |   Start    |   2012-01-01 09:00:00
       1      |    999     |   Stop     |   2012-01-01 09:59:59
       1      |    111     |   Start    |   2012-01-01 10:00:00
       1      |    111     |   Stop     |   2012-01-01 10:30:00

如您所见，每个“事件”都由一个或多个“操作”(或者我认为是“子事件”)组成。

我需要识别每个“子事件”并给它一个标识符。这就是我要找的:

    EventID   |   SubeventID   |   UserID   |   Action   |        TimeStamp
--------------+----------------+------------+------------+-------------------------
       1      |       1        |    111     |   Start    |   2012-01-01 08:00:00
       1      |       1        |    111     |   Stop     |   2012-01-01 08:59:59
       1      |       2        |    999     |   Start    |   2012-01-01 09:00:00
       1      |       2        |    999     |   Stop     |   2012-01-01 09:59:59
       1      |       3        |    111     |   Start    |   2012-01-01 10:00:00
       1      |       3        |    111     |   Stop     |   2012-01-01 10:30:00

我需要一些可以开始计数的东西，但仅在某些列具有特定值(例如“Action”=“开始”)时才递增。

我一直在尝试为此使用窗口函数，但收效甚微。我似乎找不到我认为可行的解决方案……有什么想法吗？

Answer 1

如果您有一些可以排序的字段，您可以使用以下查询(未经测试):

SELECT 
    sum(("Action" = 'Start')::int) OVER (PARTITION BY "EventID" ORDER BY "Timestamp" ROWS UNBOUNDED PRECEDING)
FROM 
    events

请注意，如果第一个 SubEvent 不是以 Start 开头，它的事件 ID 将为 0，这可能不是您想要的。

---

您还可以使用 COUNT() 代替 SUM():

SELECT 
    EventID 
  , COUNT(CASE WHEN Action = 'Start' THEN 1 END) 
        OVER ( PARTITION BY EventID 
               ORDER BY TimeStamp 
               ROWS UNBOUNDED PRECEDING ) 
      AS SubeventID 
  , UserID 
  , Action 
FROM 
    tableX AS t ; 

SQL-Fiddle 测试: test