问题：

如何根据“上一行”和“下一行”删除记录，并根据特定条件分配日期

劳华灿

2023-03-14

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

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_dates进行排序。

我试图从中生成以下输出：

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

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

对于每个EVENT_TYPE“续订”、“追加销售”或“新订阅”的记录 A：如果以下记录 B EVENT_TYPE“取消”，则：

如果记录 B 具有与 A 相同的EVENT_DATE日期部分（忽略时间），请从结果中删除记录 A 和 B。这就是为什么记录4，5，8和9被删除的原因;
否则，如果记录 B 的TERM_END_DATE值早于记录 A，请将 A 的TERM_END_DATE更新为 B 的TERM_END_DATE。这就是为什么记录10具有更新TERM_END_DATE

我尝试使用以下SQL处理我的第一个条件，并得到一个问题ORA-00933: SQL命令未正确结束

         (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')

共有2个答案

杨君之

2023-03-14

让我先声明一下，我没有在Oracle中测试这一点，因为我手边没有Oracle数据库。

我将其缩减为单个连接，将性能与公认的答案进行比较可能会很有用。

select  
    e1.reg_id,
    e1.product_cd,
    e1.event_type, 
    e1.event_date, 
    e1.term_start_date,
    case e1.event_type when 'CANCELLATION' then e1.term_end_date else coalesce(e2.term_end_date, e1.term_end_date) end as term_end_date,
    e1.days, 
    e1.amt
from event e1 
    left outer join event e2 on 
        e1.reg_id = e2.reg_id and 
        e1.product_cd = e2.product_cd and 
        e1.term_start_date = e2.term_start_date and 
        (e1.event_type = 'CANCELLATION' or e2.event_type = 'CANCELLATION') and 
        e1.event_date <> e2.event_date  
where trunc(e1.event_date) <> trunc(e2.event_date) or e2.reg_id is null

洪国兴

2023-03-14

查询出错的原因是，在定义子查询之前，必须指明要从中选择的内容。因此，如果您将其作为前缀，则从中选择*将是一个有效的查询。

请注意，您不必执行这些< code >或操作，因为您可以使用< code>in操作符来缩短执行时间。

您还应该否定一些比较（因为您已经有Not）并使用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的术语_ end_。

类似资料：

如何根据上一行和下一行删除记录并根据某些条件分配日期

问题内容：这是我对源数据的插入语句。我有我的源数据该数据已经排序，和。我正在尝试从中生成此输出：这是从原始数据得出结果的逻辑：对于带有，或的每个记录 A* ：如果以下记录 B 具有，则： * 如果记录乙具有相同的日期部分如甲（忽略时间），同时消除记录甲和乙从结果。这就是为什么删除记录4、5、8和9的原因；否则，如果记录 B 的值早于记录 A ，则将 A 的值更新为 B
Firestore根据条件执行删除

问题内容：有没有一种方法可以对 field1 = x* 和 field2 = y的文档执行删除操作？ *** 我看到了删除功能，但没有附带。如果使用事务，则存在get和delete，但get似乎不接受“ where”子句。我希望我在文档中缺少一些内容。谢谢问题答案：为此，您需要先创建所需的查询，然后才使用如下方法：
根据最大值删除一行

问题内容：如何构造一个mySQL查询以基于最大值删除行。我试过了但是有错误？问题答案：采用：请注意，如果存在重复，则将删除所有具有该值的行。关于1093错误的愚蠢之处在于，您可以通过在自引用之间放置一个子查询来解决它：说明 MySQL仅在使用＆语句时检查是否存在正在更新的同一表的一级子查询。这就是为什么将其放在第二级（或更深层）的子查询替代项中的原因。但这只是检查子查询- JO
根据熊猫中的条件删除行

问题内容：我有以下数据框基本上我可以如下过滤行我可以如下所示删除/删除一行但是我想根据条件删除一定数量的行，我该怎么做？问题答案：最好的是但需要反转条件-使所有值相等且更高，如下所示：与功能相同：另一种可能的解决方案是通过以下方法反转掩码：
根据匹配条件对SQL记录进行排序

问题内容：我有这个查询：是否可以根据首先匹配的条件对从该查询返回的记录进行排序。我想先获取所有匹配的记录，然后再获取其他匹配的记录。例如，如果我有以下记录：我希望对它们进行如下排序：有可能吗？问题答案：
根据多个`或`条件删除行[重复]

让我们假设我的数据frame如下所示：我有一个的列表，我想从Dataframe中删除它们。该列表超过200个长度，因此多个筛选器太麻烦了。是否可以通过传递的列表来进行筛选？

如何根据“上一行”和“下一行”删除记录，并根据特定条件分配日期

共有2个答案

相关问答

相关文章

相关阅读

相关工具

相关文档