Oracle APEX - SQL -产品生命周期表视图和计算阶段长度

Question

这个问题是这个问题的延续：(Oracle APEX - SQL - Creating a Sequential History and Calculating Days Between Each Phase)

一些背景：

因此，对于我的一个应用程序，我决定需要能够捕获比以前更详细的度量标准。我的小组创建文档，特别是我想知道该文档在开发的每个阶段花费了多长时间(以天为单位)。用于捕获此数据的表的结构如下：

 TBL_DOC_TIMELINE
 DOC_ENTRY_ID     DOC_ID     DOC_STATUS     DOC_CATEGORY     DOC_DATE       
 1                123        Planned        OPEN             06-05-2012    
 7                123        Draft          OPEN             06-15-2012
 38               123        Approval       OPEN             06-20-2012
 102              123        Published      CLOSED           06-30-2012

对于这个函数，我们所有的文档都使用相同的表，所以我不能简单地在DOC_ENTRY_ID上键，尽管它可能会有所帮助。我需要找到DOC_ID的最大DOC_ID，然后计算。我这样做，直到到达‘DOC_CATEGORY’的‘关闭’和'0‘点被插入到单元格中，因为这是DOC_ID生命周期的结束。就像这样：

 DOC_ENTRY_ID  DOC_ID  DOC_STATUS  DOC_CATEGORY  DOC_DATE     DOC_DURATION     
 1             123     Planned     OPEN          06-05-2012   10     
 7             123     Draft       OPEN          06-15-2012   5
 38            123     Approval    OPEN          06-20-2012   10
 102           123     Published   CLOSED        06-30-2012   0

由于杰出的托尼·安德鲁斯( thanks )为视图代码提供了粗略的草稿，最终成为：

create or replace  view DOC_TIMELINE as
  select t.DOC_ENTRY_ID, t.DOC_ID, t.DOC_STATUS, t.DOC_CATEGORY, t.DOC_DATE
       , case when DOC_CATEGORY = 'CLOSED' then 0
            else lead(DOC_DATE) over (partition by DOC_ID order by DOC_ENTRY_ID)
                   - DOC_DATE
              end as duration
  from TBL_DOC_TIMELINE t;

我现在需要做的事：

这一切都是完美的，除了在我的第一次通过，我忘记了一个非常重要的部分，我的要求。我的目标是知道每个阶段的文档花费了多长时间，但我完全没有意识到，我需要实时收集这些信息，而不仅仅是在文档关闭之后。使用当前的设置，视图在其生命周期的中间将如下所示：

 DOC_ENTRY_ID  DOC_ID  DOC_STATUS  DOC_CATEGORY  DOC_DATE     DOC_DURATION     
 1             123     Planned     OPEN          06-05-2012   10     
 7             123     Draft       OPEN          06-15-2012   5
 38            123     Approval    OPEN          06-20-2012   -

看到问题了吗？如果我需要知道该文档在审批状态中花费了多长时间，那么我需要等到它离开该状态才能计算持续时间。因此，即使它可能已经处于这种状态20天，我的指标将不会反映这一点。

我需要做的是找到一些方法来调整上面的View代码，如果给定的DOC_ENTRY_ID的DOC_CATEGORY = 'OPEN‘，则根据SYSDATE()计算这个值。

 assuming SYSDATE() = '06-29-2012'
 DOC_ENTRY_ID  DOC_ID  DOC_STATUS  DOC_CATEGORY  DOC_DATE     DOC_DURATION     
 1             123     Planned     OPEN          06-05-2012   10     
 7             123     Draft       OPEN          06-15-2012   5
 38            123     Approval    OPEN          06-20-2012   9

这些都有意义吗？我想象我需要在View代码中添加另一个案例，但是sytanx给我带来了一些麻烦。对你们来说，这可能是一个简单的解决方案，但我对这类病例的熟悉程度和允许的sytanx是有限的，而且我的研究没有发现任何与此相关的东西。

我衷心感谢所有的帮助。谢谢你们！

Answer 1

像这样吗？-

create or replace  view DOC_TIMELINE as
  select t.DOC_ENTRY_ID, t.DOC_ID, t.DOC_STATUS, t.DOC_CATEGORY, t.DOC_DATE
       , case when DOC_CATEGORY = 'CLOSED' then 0
              when lead(DOC_DATE) over (partition by DOC_ID order by DOC_ENTRY_ID)
                   - DOC_DATE is null then trunc(sysdate)-trunc(DOC_DATE)
              else lead(DOC_DATE) over (partition by DOC_ID order by DOC_ENTRY_ID)
                   - DOC_DATE
              end as duration
  from TBL_DOC_TIMELINE t;

或者因为lead(DOC_DATE)总是null for max DOC_ENTRY_ID (对于DOC_ID)-

create or replace  view DOC_TIMELINE as
  select t.DOC_ENTRY_ID, t.DOC_ID, t.DOC_STATUS, t.DOC_CATEGORY, t.DOC_DATE
       , case when DOC_CATEGORY = 'CLOSED' then 0
              when lead(DOC_DATE) over (partition by DOC_ID order by DOC_ENTRY_ID) 
                      is null then trunc(sysdate)-trunc(DOC_DATE)
              else lead(DOC_DATE) over (partition by DOC_ID order by DOC_ENTRY_ID)
                   - DOC_DATE
              end as duration
  from TBL_DOC_TIMELINE t;