登录
当前位置： 首页 > 面试题库 >

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

潘秦斩
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_DATE。

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

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

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

对于带有，或的每个记录 A* ：如果以下记录 B 具有，则：EVENT_TYPE 'RENEWAL'``'UPSELL'``'NEW SUBSCRIPTION' *EVENT_TYPE 'CANCELLATION'

  1. 如果记录 乙 具有相同的EVENT_DATE日期部分如 甲 （忽略时间），同时消除记录 甲 和 乙 从结果。这就是为什么删除记录4、5、8和9的原因；
  2. 否则，如果记录 B 的TERM_END_DATE值早于记录 A ，则将 A 的值更新TERM_END_DATE为 B的值 。这就是为什么记录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')

问题答案：

您的查询出现错误的原因是，在定义子查询之前，您必须指出要从中选择的内容。因此，如果在该前缀之前添加select * from了一个有效的查询。

请注意，您不必执行这些or操作，因为您可以使用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的 term_end_date 也已修改，因为规则2适用于它。



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

    这是我对源数据的插入语句。 我有我的源数据 此数据已按、和进行排序。 我试图从中生成以下输出： 这是从原始数据得出结果的逻辑： 对于的记录 A：如果以下记录 B ，则： 如果记录 B 具有与 A 相同的日期部分（忽略时间），请从结果中删除记录 A 和 B。这就是为什么记录4，5，8和9被删除的原因; 否则，如果记录 B 值早于记录 A，请将 A 的更新为 B 的TERM_END_DATE。这就是为

  • Firestore根据条件执行删除

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

  • 如何根据阈值删除除某些记录以外的所有记录？

    问题内容： 我有一个这样的表： 我正在尝试删除具有3个以上具有相同ID的名称的记录，但要删除所有记录。因此，我试图得到这样的东西： 我不了解如何编写此查询。我已经达到了保留一个记录但没有记录阈值的程度： 给我： 有什么建议？哦，对了，我不在乎合并时会保留哪些记录。 问题答案： 您可以使用CTE做到这一点

  • 根据最大值删除一行

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

  • 根据熊猫中的条件删除行

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

  • 根据匹配条件对SQL记录进行排序

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

相关阅读

根据匹配条件对SQL记录进行排序根据列值删除行根据一列值删除重复的行根据“未进入”条件从数据框中删除行根据条件从另一个列表中删除一个列表中的某些元素

相关文章

某小厂10.24JAVA一面记录如何设计一个分布式系统去分析上亿条数据？记录一下美团一面记录一下秋招［汇川］记录一下

相关问答

根据日期和值聚合行，并添加分数根据条件获取行IDPython：如何根据每个条目中的行数删除dict条目熊猫：根据阈值条件删除列Python DataFrame：根据条件删除重复项？

相关工具

根据开发者帐号查看App物体根据重力感应运动一天日报财付通打印票据和拖动银行卡效果录音并AMR和WAV互转

相关文档

利用 Python 进行数据分析 · 第 2 版Python 和 Pandas 数据分析教程那些年我们一起学 XSS下一代 Web 框架 Koa数据结构和算法
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.