从同一表添加多个"changes in time"

Question

每当用户状态发生变化时，我的表都会使用时间戳。每天从"a"开始，一直进行a->b->c->d->e->…

所以我试着找出a和b之间的总时间加上c和d之间的时间加上e和f等等。

用来获取两个状态之间的时间:

SELECT cast(us2.status_timestamp as date) - cast(us1.status_timestamp as date)
FROM user_status US1, user_status US2
WHERE US1.statID = 'a' and us2.statID = 'b'
AND US1.user_ID = US2.user_ID
AND US1.work_date = US2.work_date

是否有一种方法可以做到这一点，而不只是多次执行此选择语句，然后添加结果?我觉得有更快的方法。

注意:Oracle使用Toad

Answer 1

如果我正确理解您的要求，下面的查询将工作:

SELECT SUM (CAST (us2.status_timestamp AS date) - CAST (us1.status_timestamp AS date))
FROM   user_status us1, user_status us2
WHERE  CHR (ASCII (us1.statid) + 1) = us2.statid
   AND MOD (ASCII (us1.statid), 2) <> 0
   AND us1.user_id = us2.user_id
   AND us1.work_date = us2.work_date;

MOD (ASCII (us1.statid), 2)对于具有偶数ASCII值的字符将返回0，对于所有其他值将返回其他值。a是97，所以我们总是想从这个值不为0的地方开始。我们可以使用非常相似的逻辑来获得序列中的下一个值:将字母转换为它的ASCII值，加1，然后转换回来。在连接中使用该值可以让我们找到每个开始时间对应的结束时间。最后，我们把所有的差值加起来。

Answer 2

您可以使用LEAD和LAG分析函数

SELECT cast(status_timestamp as date) - cast(prior_status_timestamp as date) diff,
       user_id,
       work_date,
       statID
  FROM (
    SELECT status_timestamp,
           lag(status_timestamp) over 
              (partition by user_id, 
                            work_date 
               order by status_timestamp) prior_status_timestamp,
           user_id,
           statID,
           work_date
      FROM user_status )
 WHERE statID in ('b','d','f')      

对于表中的每一行，这将获得相同user_id和work_date的前一行的状态时间戳(因此每天每个用户的'a'行将具有NULL的prior_status_时间戳)。然后显示当前行与前一行之间的差值。然后可以适当地进行聚合。

Answer 3

SELECT US1.user_ID, SUM(difference)
FROM (
SELECT US1.user_ID, cast(us2.status_timestamp as date) - cast(us1.status_timestamp as date) AS difference
FROM user_status US1, user_status US2
WHERE 
   ((US1.statID = 'a' and us2.statID = 'b') OR
    (US1.statID = 'C' AND us2.statID = 'D') OR
    (US1.statID = 'E' AND us2.statID = 'F'))
AND US1.user_ID = US2.user_ID
AND US1.work_date = US2.work_date
) GROUP BY US1.user_id