根据不同的列时间戳差异编写oracle DB视图 [英] Write oracle DB view based on different column time stamp difference

问题描述

我试图写一个视图，其中一列是业务逻辑，就像基于接收到的消息状态的SLA时差一样.

I am trying to write a view where one of the column is a business logic which is like a SLA time difference based on received message state.

下面是示例..对于相同的MSG_GUID，我们有不同的PAYLOAD_TYPE，它们代表消息的不同状态，并且创建时间显示了它插入数据库的时间.

Example is below.. where for same MSG_GUID, we have different PAYLOAD_TYPE which represent the different state of message and created time shows the time it was inserted in DB.

PayloadType = 1将始终在DB中存储所有消息，但 在这里，对于同一个消息GUID，我将拥有Payload_type = 2或3，但不能同时拥有两者

PayloadType=1 will always be in DB for all message but here I will have either Payload_type =2 or 3 for same message GUID but not both at same time

MSG_GUID             PAYLOAD_TYPE       CREATED_TIME                  
------------------------------------- ------------ ----
5dd222f2175e14       1                   02-MAY-14 04:22:11                       
5dd222f2175e14       2                   02-MAY-14 04:28:11                      
5dd222f2175e14       3                   02-MAY-14

或

MSG_GUID             PAYLOAD_TYPE       CREATED_TIME                  
------------------------------------- ------------ ----
5dd222f2175e14       1                   02-MAY-14 04:22:11                       
5dd222f2175e14       2                   02-MAY-14                       
5dd222f2175e14       3                   02-MAY-14 04:28:11

现在，在我看来，我必须显示 SLATime，这将是Payload_type 1和2或1和3的时差. ..在这里，messageGUID对此将保持不变...同样的方式将有100条记录.

now, In my view I have to display SLATime which would be time difference of Payload_type 1 and 2 or 1 and 3. .. here messageGUID will remain same for this... same way I will have 100's of record.

有人可以告诉我如何编写SQL查询来实现这一目标.

Could someone please tell me how I can write SQL query to achieve this .

预期结果-

如果有效载荷类型1和2可用于同一MSG_GUID，则

If Payload_type 1 and 2 available for same MSG_GUID then

SLATime
-----------
0 HOURS 6 MINUTES 0 SECS (created time difference of Payload_type 1 and 2 row)

但是如果有效载荷类型1和3可用于相同的MSG_GUID，则

but if Payload_type 1 and 3 available for same MSG_GUID then

SLATime
-----------
0 HOURS 6 MINUTES 0 SECS (created time difference of Payload_type 1 and 3 row)

推荐答案

如果您希望与没有时间成分的created_time有所不同，那么您需要这样的逻辑:

If you want the difference with the created_time that has no time component, then you want logic like this:

select msg_guid,
       (max(case when payload_type = 1 then created_time end) -
        (case when max(case when payload_type = 2 then created_time end) <>
                   trunc(max(case when payload_type = 2 then created_time end))
              then max(case when payload_type = 2 then created_time end) 
              when max(case when payload_type = 3 then created_time end) <>
                   trunc(max(case when payload_type = 3 then created_time end))
              then max(case when payload_type = 3 then created_time end) 
        end)
        ) as diff              
from table t
group by msg_guid;

这将返回一天的结果.

这篇关于根据不同的列时间戳差异编写oracle DB视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！