时间戳

首页 > 解决方案 > 分组计数

sql - 分组计数

问题描述

我有下表：

NO  ACTION_DT              REQUEST_TYPE STATUS_CD   
3   5/6/2008 11:07:28 PM    CREATE      APPROVED    
3   1/3/2013 10:02:04 AM    UPDATE      APPROVED    
3   4/21/2015 2:20:27 PM    UPDATE      APPROVED    
3   8/1/2017 3:25:01 PM     UPDATE      PENDING 
3   8/2/2017 9:34:16 AM     UPDATE      APPROVED    
3   11/13/2018 4:12:36 PM   UPDATE      NEW 
3   11/15/2018 9:02:07 AM   UPDATE      APPROVED    
3   7/15/2019 9:07:32 AM    UPDATE      NEW 
3   7/15/2019 9:07:32 AM    UPDATE      PENDING 
3   7/16/2019 9:23:43 AM    UPDATE      NEW 
3   7/16/2019 2:31:20 PM    UPDATE      PENDING 
3   7/16/2019 2:35:47 PM    UPDATE      APPROVED

我的结果：

    NO      ACTION_DT             REQUEST_TYPE  STATUS_CD REQUEST_START_DT      REQUEST_END_DT        REQUEST RNK_GRP_REQUEST
    803     5/6/2008 11:07:28 PM  CREATE        APPROVED  5/6/2008 11:07:28 PM  5/6/2008 11:07:28 PM  1       1
    803     1/3/2013 10:02:04 AM  UPDATE        APPROVED  1/3/2013 10:02:04 AM  1/3/2013 10:02:04 AM  2       1
    803     4/21/2015 2:20:27 PM  UPDATE        APPROVED  4/21/2015 2:20:27 PM  4/21/2015 2:20:27 PM  3       1
    803     8/1/2017 3:25:01 PM   UPDATE        PENDING   8/1/2017 3:25:01 PM   8/2/2017 9:34:16 AM   4       1
    803     8/2/2017 9:34:16 AM   UPDATE        APPROVED  8/1/2017 3:25:01 PM   8/2/2017 9:34:16 AM   4       2
    803     11/13/2018 4:12:36 PM UPDATE        NEW       11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5       1
    803     11/15/2018 9:02:07 AM UPDATE        APPROVED  11/13/2018 4:12:36 PM 11/15/2018 9:02:07 AM 5       2
    803     7/15/2019 9:07:32 AM  UPDATE        NEW       7/15/2019 9:07:32 AM  7/16/2019 2:35:47 PM  6       1
    803     7/15/2019 9:07:32 AM  UPDATE        PENDING   7/15/2019 9:07:32 AM  7/16/2019 2:35:47 PM  6       2
    803     7/16/2019 9:23:43 AM  UPDATE        NEW       7/15/2019 9:07:32 AM  7/16/2019 2:35:47 PM  6       3
    803     7/16/2019 2:31:20 PM  UPDATE        PENDING   7/15/2019 9:07:32 AM  7/16/2019 2:35:47 PM  6       4
    803     7/16/2019 2:35:47 PM  UPDATE        APPROVED  7/15/2019 9:07:32 AM  7/16/2019 2:35:47 PM  6       5

我遇到的问题是请求#6。用户并不总是遵循流程，因此在新请求之前没有“已批准”或“拒绝”。因此，数据应显示 7 个请求。最后三行是第 7 个请求。

我如何在我的代码中解释这一点？

我已经or在我的声明中尝试过，case expression但我收到了window function not allowed here；也不确定这是否会给我正确的结果。

SQL：

SELECT 
W.NO,
W.ACTION_DT,
W.REQUEST_TYPE,
W.STATUS_CD,
MIN(W.ACTION_DT ) OVER ( PARTITION BY NO, GRP ) AS REQUEST_START_DT,
MAX(ACTION_DT) OVER (PARTITION BY NO,GRP) AS REQUEST_END_DT,
W.GRP AS REQUEST,
RNK_GRP_REQUEST 
FROM (
   SELECT W.*, ROW_NUMBER() OVER (PARTITION BY NO,GRP ORDER BY ACTION_DT,STATUS_CD) RNK_GRP_REQUEST      
   FROM ( SELECT 
          W.NO,
          W.ACTION_DT,
          W.REQUEST_TYPE,
          W.STATUS_CD,
          COUNT(CASE WHEN STATUS_CD IN ('APPROVED','DENIED') 
          --OR (STATUS_CD IN ('NEW') AND LAG(STATUS_CD) OVER (PARTITION BY NO ORDER BY ACTION_DT, STATUS_CD) NOT IN ('APPROVED','DENIED'))
               THEN 1 END )                                                 
               OVER (PARTITION BY NO ORDER BY ACTION_DT)               
               --+ CASE WHEN STATUS_CD IN ('NEW') AND LAG(STATUS_CD) OVER (PARTITION BY NO ORDER BY ACTION_DT, STATUS_CD) NOT IN ('APPROVED','DENIED') THEN 1 ELSE 0 END
               + CASE WHEN STATUS_CD IN ('APPROVED','DENIED') THEN 0 ELSE 1 END

               AS GRP
          FROM  W
          WHERE 1=1
          AND W.ACTION_DT IS NOT NULL
          )W)W

ORDER BY NO, W.ACTION_DT, REQUEST, RNK_GRP_REQUEST

标签： sqloraclegroup-bycasewindow-functions

解决方案

可以使用 aCTE创建lag，然后使用您的滞后列case expression来捕获先前的值。

就像是...

with prev as (select lag(status_cd) over partition by no order by action_dt, status_cd) as prev_status_cd, no, action_dt, W.NO|| TO_CHAR(W.ACTION_DT,'YYYYMMDD') || W.STATUS_CD AS PK
from w
select 
COUNT(CASE WHEN STATUS_CD IN ('APPROVED','DENIED') 
      OR W.STATUS_CD IN ('NEW')AND PREV.PREV_STATUS_CD NOT IN ('APPROVED','DENIED')
           THEN 1 END )                                                 
           OVER (PARTITION BY NO ORDER BY ACTION_DT)               
           + CASE WHEN STATUS_CD IN ('APPROVED','DENIED') THEN 0 ELSE 1 END
from w
join prev ON W.NO|| TO_CHAR(W.ACTION_DT,'YYYYMMDD') || W.STATUS_CD = PREV.PK

推荐阅读