如何根据上一行和下一行删除记录并根据某些条件分配日期 [英] How to Delete the records based upon Prev and Next rows and assign the date based upon certain conditions

查看：78
本文介绍了如何根据上一行和下一行删除记录并根据某些条件分配日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是我对源数据的插入语句.

This is my insert Statements for the Source data.

REM INSERTING into EXPORT_TABLE  
SET DEFINE OFF;  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDFREE','UPSELL',to_date('11-MAR-14 17:05:35','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-JUN-14 23:59:00','DD-MON-YY HH24:MI:SS'),92,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('11-MAR-14 17:05:35','DD-MON-YY HH24:MI:SS'),to_date('12-JUN-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-15 23:59:00','DD-MON-YY HH24:MI:SS'),271,73.78);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDFREE','EXPIRATION',to_date('12-JUN-14 01:26:26','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-14 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-JUN-14 23:59:00','DD-MON-YY HH24:MI:SS'),92,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','RENEWAL',to_date('11-MAR-15 01:23:01','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),365,99);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('11-MAR-15 03:11:09','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-15 23:59:00','DD-MON-YY HH24:MI:SS'),0,-99);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('16-MAR-15 10:49:34','DD-MON-YY HH24:MI:SS'),to_date('16-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('10-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),360,97.92);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('22-FEB-16 18:19:00','DD-MON-YY HH24:MI:SS'),to_date('16-MAR-15 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-FEB-16 23:59:00','DD-MON-YY HH24:MI:SS'),343,-4.61);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','NEW SUBSCRIPTION',to_date('23-FEB-16 13:08:05','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-FEB-18 23:59:00','DD-MON-YY HH24:MI:SS'),730,178);    
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','CANCELLATION',to_date('23-FEB-16 15:16:44','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 23:59:00','DD-MON-YY HH24:MI:SS'),0,-178);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDGWA','UPSELL',to_date('23-FEB-16 15:22:42','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('22-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),28,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDGWA','CANCELLATION',to_date('11-MAR-16 04:25:50','DD-MON-YY HH24:MI:SS'),to_date('23-FEB-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('11-MAR-16 23:59:00','DD-MON-YY HH24:MI:SS'),17,0);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('14-MAR-16 10:02:05','DD-MON-YY HH24:MI:SS'),to_date('14-MAR-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('13-APR-16 23:59:00','DD-MON-YY HH24:MI:SS'),30,8.41);  
Insert into EXPORT_TABLE   values ('4VKMH','GUIDPAID','UPSELL',to_date('11-APR-16 09:33:06','DD-MON-YY HH24:MI:SS'),to_date('14-APR-16 00:00:00','DD-MON-YY HH24:MI:SS'),to_date('13-MAR-17 23:59:00','DD-MON-YY HH24:MI:SS'),333,90.59);

我的源数据为

REG_ID  | PRODUCT_CD | EVENT_TYPE      | EVENT_DATE         | TERM_START_DATE    | TERM_END_DATE      | DAYS | AMT
--------+------------+-----------------+--------------------+--------------------+--------------------+------+--------
4VKMH   | GUIDFREE   | UPSELL          | 11-MAR-14 17:05:35 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 11-MAR-14 17:05:35 | 12-JUN-14 00:00:00 | 10-MAR-15 23:59:00 | 271  |   73.78  
4VKMH   | GUIDFREE   | EXPIRATION      | 12-JUN-14 01:26:26 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | RENEWAL         | 11-MAR-15 01:23:01 | 11-MAR-15 00:00:00 | 10-MAR-16 23:59:00 | 365  |   99     *
4VKMH   | GUIDPAID   | CANCELLATION    | 11-MAR-15 03:11:09 | 11-MAR-15 00:00:00 | 11-MAR-15 23:59:00 |   0  |  -99  
4VKMH   | GUIDPAID   | UPSELL          | 16-MAR-15 10:49:34 | 16-MAR-15 00:00:00 | 10-MAR-16 23:59:00 | 360  |   97.92  
4VKMH   | GUIDPAID   | CANCELLATION    | 22-FEB-16 18:19:00 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 343  |   -4.61   
4VKMH   | GUIDPAID   | NEW SUBSCRIPTION| 23-FEB-16 13:08:05 | 23-FEB-16 00:00:00 | 22-FEB-18 23:59:00 | 730  |  178  
4VKMH   | GUIDPAID   | CANCELLATION    | 23-FEB-16 15:16:44 | 23-FEB-16 00:00:00 | 23-FEB-16 23:59:00 |   0  | -178  
4VKMH   | GUIDGWA    | UPSELL          | 23-FEB-16 15:22:42 | 23-FEB-16 00:00:00 | 22-MAR-16 23:59:00 |  28  |    0  
4VKMH   | GUIDGWA    | CANCELLATION    | 11-MAR-16 04:25:50 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  17  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 14-MAR-16 10:02:05 | 14-MAR-16 00:00:00 | 13-APR-16 23:59:00 |  30  |    8.41  
4VKMH   | GUIDPAID   | UPSELL          | 11-APR-16 09:33:06 | 14-APR-16 00:00:00 | 13-MAR-17 23:59:00 | 333  |   90.59

此数据已按REG_ID，EVENT_DATE和TERM_START_DATE排序.

我正在尝试从中生成此输出:

I am trying to generate this output from that:

REG_ID  | PRODUCT_CD | EVENT_TYPE      | EVENT_DATE         | TERM_START_DATE    | TERM_END_DATE      | DAYS | AMT
--------+------------+-----------------+--------------------+--------------------+--------------------+------+--------
4VKMH   | GUIDFREE   | UPSELL          | 11-MAR-14 17:05:35 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 11-MAR-14 17:05:35 | 12-JUN-14 00:00:00 | 10-MAR-15 23:59:00 | 271  |   73.78  
4VKMH   | GUIDFREE   | EXPIRATION      | 12-JUN-14 01:26:26 | 11-MAR-14 00:00:00 | 11-JUN-14 23:59:00 |  92  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 16-MAR-15 10:49:34 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 360  |   97.92  
4VKMH   | GUIDPAID   | CANCELLATION    | 22-FEB-16 18:19:00 | 16-MAR-15 00:00:00 | 22-FEB-16 23:59:00 | 343  |   -4.61  
4VKMH   | GUIDGWA    | UPSELL          | 23-FEB-16 15:22:42 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  28  |    0  
4VKMH   | GUIDGWA    | CANCELLATION    | 11-MAR-16 04:25:50 | 23-FEB-16 00:00:00 | 11-MAR-16 23:59:00 |  17  |    0  
4VKMH   | GUIDPAID   | UPSELL          | 14-MAR-16 10:02:05 | 14-MAR-16 00:00:00 | 13-APR-16 23:59:00 |  30  |    8.41  
4VKMH   | GUIDPAID   | UPSELL          | 11-APR-16 09:33:06 | 14-APR-16 00:00:00 | 13-MAR-17 23:59:00 | 333  |   90.59

这是从原始数据得出结果的逻辑:

This is the logic by which the result is derived from the original data:

对于具有EVENT_TYPE 'RENEWAL'，'UPSELL'或'NEW SUBSCRIPTION'的每个记录 A :如果以下记录 B 具有EVENT_TYPE 'CANCELLATION' ，然后:

For each record A with EVENT_TYPE 'RENEWAL', 'UPSELL', or 'NEW SUBSCRIPTION': if the following record B has EVENT_TYPE 'CANCELLATION', then:

  1. 如果记录 B 与 A 具有相同的EVENT_DATE日期部分(忽略时间)，则同时删除记录 A 和 B .这就是为什么要删除记录4、5、8和9的原因；
  2. 如果记录 B 的TERM_END_DATE值早于记录 A ，则将 A 的TERM_END_DATE更新为 B .这就是为什么记录10具有更新的TERM_END_DATE
  1. if record B has the same EVENT_DATE date part as A (ignore time), eliminate both record A and B from the result. So this is why records 4, 5, 8 and 9 are eliminated;
  2. else if record B has an earlier TERM_END_DATE value than record A, update A's TERM_END_DATE to that of B. So this is why record 10 has an updated TERM_END_DATE

我尝试使用以下SQL处理我的第一种情况并遇到问题ORA-00933:SQL命令未正确结束

I have tried to handle my 1st condition using the Following SQL and getting a issue ORA-00933: SQL command not properly ended

         (SELECT REG_ID, 
            EVENT_TYPE,
            EVENT_DATE,
            PRODUCT_CD,
            TERM_START_DATE,
            TERM_END_DATE,
            LAG(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_TYPE,
            LAG(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_DATE,
            LEAD(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_TYPE,
            LEAD(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_DATE  
            from    mytable)TEMP
            WHERE NOT (TEMP.event_type = 'CANCELLATION' AND (TEMP.PREV_EVENT_TYPE = 'NEW SUBSCRIPTION' OR TEMP.PREV_EVENT_TYPE  = 'RENEWAL' OR 
            TEMP.PREV_EVENT_TYPE = 'UPSELL') and TEMP.EVENT_DATE <> TEMP.PREV_EVENT_DATE)
            AND 
            NOT ((TEMP.PREV_EVENT_TYPE = 'NEW SUBSCRIPTION' OR TEMP.PREV_EVENT_TYPE  = 'RENEWAL' OR 
            TEMP.PREV_EVENT_TYPE = 'UPSELL') AND TEMP.EVENT_DATE <> TEMP.NEXT_EVENT_DATE AND TEMP.NEXT_EVENT_TYPE = 'CANCELLATION')

推荐答案

查询出错的原因是，在定义子查询之前，必须指明要从中选择什么.因此，如果您在select * from之前添加了前缀，那将是一个有效的查询.

The reason why you got an error on your query is that before the sub-query is defined, you must indicate what you want to select from it. So if you had prefixed that with select * from it would have been a valid query.

请注意，您不必执行这些or操作，因为您可以使用in运算符来缩短操作时间.

Note that you don't have to do those or operations, as you can do that shorter with an in operator.

您还应该取消一些比较(因为您已经有NOT)，并用TRUNC截断日期.

You should also negate some comparisons (as you already have NOT) and truncate dates with TRUNC.

这是我建议的查询:

SELECT      TEMP.REG_ID, 
            TEMP.EVENT_TYPE,
            TEMP.EVENT_DATE,
            TEMP.PRODUCT_CD,
            TEMP.TERM_START_DATE,
            CASE WHEN TEMP.EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
                  AND TEMP.NEXT_EVENT_TYPE = 'CANCELLATION' THEN
                        LEAST(TEMP.TERM_END_DATE, TEMP.NEXT_TERM_END_DATE)
                 ELSE TEMP.TERM_END_DATE
            END AS TERM_END_DATE,
            TEMP.DAYS,
            TEMP.AMT
FROM    (SELECT     REG_ID, 
                    EVENT_TYPE,
                    EVENT_DATE,
                    PRODUCT_CD,
                    TERM_START_DATE,
                    TERM_END_DATE,
                    DAYS,
                    AMT,
                    LAG(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_TYPE,
                    LAG(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as PREV_EVENT_DATE,
                    LEAD(EVENT_TYPE, 1, '-') over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_TYPE,
                    LEAD(EVENT_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_EVENT_DATE,  
                    LEAD(TERM_END_DATE, 1) over (
                        PARTITION BY REG_ID, PRODUCT_CD
                        ORDER BY EVENT_DATE, TERM_START_DATE) as NEXT_TERM_END_DATE
            FROM    export_table) TEMP
WHERE   NOT (TEMP.EVENT_TYPE = 'CANCELLATION' 
             AND TEMP.PREV_EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
             AND TRUNC(TEMP.EVENT_DATE) = TRUNC(TEMP.PREV_EVENT_DATE))
AND     NOT (TEMP.NEXT_EVENT_TYPE = 'CANCELLATION'
             AND TEMP.EVENT_TYPE IN ('NEW SUBSCRIPTION', 'RENEWAL', 'UPSELL') 
             AND TRUNC(TEMP.NEXT_EVENT_DATE) = TRUNC(TEMP.EVENT_DATE))

请注意，记录6的 term_end_date 也已修改，因为规则2适用于此.

Note that the term_end_date of record 6 is also modified, as rule 2 is applicable to it.

这篇关于如何根据上一行和下一行删除记录并根据某些条件分配日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆