我有下表：
NO ACTION_DT REQUEST_TYPE STATUS_CD
34 12/5/2007 6:57:04 PM CREATE APPROVED
34 1/10/2012 2:53:49 PM DELETE PENDING
34 1/10/2012 3:00:00 PM DELETE DENIED
34 1/7/2013 9:42:33 AM DELETE APPROVED
我想根据条件创建一个start_dt。
我需要状态代码为“已批准”或“已拒绝”后的request_start_date的第一个值
“已批准”或“已拒绝”表示请求结束。接下来的行应具有第一行action_dt，直到满足批准或拒绝的条件。
预期产量：
NO ACTION_DT REQUEST_TYPE STATUS_CD REQUEST_START_DT
34 12/5/2007 6:57:04 PM CREATE APPROVED 12/5/2007 6:57:04 PM
34 1/10/2012 2:53:49 PM DELETE PENDING 1/10/2012 2:53:49 PM
34 1/10/2012 3:00:00 PM DELETE DENIED 1/10/2012 2:53:49
34 1/7/2013 9:42:33 AM DELETE APPROVED 1/7/2013 9:42:33 AM
码：
select *,
CASE
WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE','RELEASE','DELETE','HOLD') AND W.STATUS_CD IN ('NEW') THEN W.ACTION_DT
WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE','RELEASE','DELETE','HOLD') AND W.STATUS_CD IN ('PENDING','NEW','DENIED','APPROVED') AND LAG(W.ACTION_USER) OVER (PARTITION BY W.NO ORDER BY W.ACTION_DT) IS NULL THEN W.ACTION_DT
--WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE','RELEASE','DELETE','HOLD') AND W.STATUS_CD NOT IN ('NEW') THEN (CASE WHEN W.STATUS_CD IN ('APPROVED','DENIED') THEN LAG(W.ACTION_DT)
--OVER (PARTITION BY W.NO,W.REQUEST_TYPE ORDER BY W.ACTION_DT)END)
WHEN LAG(W.STATUS_CD) OVER (PARTITION BY W.NO ORDER BY W.ACTION_DT)
/*WHEN W.REQUEST_TYPE IN ('CREATE','UPDATE','RELEASE','DELETE','HOLD') AND W.STATUS_CD NOT IN ('NEW') THEN LAG(CASE WHEN W.STATUS_CD IN ('APPROVED','DENIED') THEN W.ACTION_DT END )
--,ROWS BETWEEN UNBOUNDED PRECEDING AND PRECEDING )
OVER (PARTITION BY W.NO ORDER BY W.ACTION_DT) */
ELSE NULL
--ELSE W.ACTION_DT
END REQUEST_START_DT
from w
Oracle安装程序：
CREATE TABLE W ( NO, ACTION_DT, REQUEST_TYPE, STATUS_CD ) AS
SELECT 34, DATE '2007-12-05' + INTERVAL '18:57:04' HOUR TO SECOND, 'CREATE', 'APPROVED' FROM DUAL UNION ALL
SELECT 34, DATE '2012-01-10' + INTERVAL '14:53:49' HOUR TO SECOND, 'DELETE', 'PENDING' FROM DUAL UNION ALL
SELECT 34, DATE '2012-01-10' + INTERVAL '15:00:00' HOUR TO SECOND, 'DELETE', 'DENIED' FROM DUAL UNION ALL
SELECT 34, DATE '2013-01-07' + INTERVAL '09:42:33' HOUR TO SECOND, 'DELETE', 'APPROVED' FROM DUAL
查询：
使用COUNT分析函数可获取行APPROVED或DENIED行数的累计计数，从而为您提供一些按行分组的方式：
SELECT W.*,
COUNT( CASE WHEN STATUS_CD 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
AS grp
FROM w
哪个输出：
否| ACTION_DT | REQUEST_TYPE | STATUS_CD | GRP- ：| ：------------------ | ：----------- | ：-------- | -： 34 | 2007-12-05 18:57:04 | 创建| 批准| 1 34 | 2012-01-10 14:53:49 | 删除 待处理| 2 34 | 2012-01-10 15:00:00 | 删除 拒绝| 2 34 | 2013-01-07 09:42:33 | 删除 批准| 3
然后，您可以使用它来查找ACTION_DT每个组的最小值：
SELECT no,
action_dt,
request_type,
status_cd,
MIN( action_dt ) OVER ( PARTITION BY no, grp ) AS request_start_dt
FROM (
SELECT W.*,
COUNT( CASE WHEN STATUS_CD 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
AS grp
FROM w
)
ORDER BY action_dt
输出：
否| ACTION_DT | REQUEST_TYPE | STATUS_CD | REQUEST_START_DT- ：| | ：------------------ | ：----------- | ：-------- | ：------------------ 34 | 2007-12-05 18:57:04 | 创建| 批准| 2007-12-05 18:57:04 34 | 2012-01-10 14:53:49 | 删除 待处理| 2012-01-10 14:53:49 34 | 2012-01-10 15:00:00 | 删除 拒绝| 2012-01-10 14:53:49 34 | 2013-01-07 09:42:33 | 删除 批准| 2013-01-07 09:42:33
db <>在这里拨弄
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句