这是我对源数据的插入语句。
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
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'
EVENT_TYPE
'RENEWAL'``'UPSELL'``'NEW SUBSCRIPTION'
'CANCELLATION'
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了一个有效的查询。
select * from
请注意，您不必执行这些or操作，因为您可以使用in运算符来缩短操作时间。
or
in
您还应该取消一些比较（如已有NOT），并使用截断日期TRUNC。
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适用于它。
