Oracle - Connect By Prior

Question

我知道我必须在这个查询中使用CONNECT BY PRIOR，但我不确定如何实现它。

我们有客户每月购买订阅，这些客户每月都会自动续订。我们有一个日志表，可以显示您当前的订单ID和以前的订单ID。因此，表记录可能如下所示：

CUSTOMER ID: 1     ORDER ID: 123   PREV_ORDER_ID:       STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 456   PREV_ORDER_ID: 123   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 789   PREV_ORDER_ID: 456   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 888   PREV_ORDER_ID: 789   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 999   PREV_ORDER_ID: 888   STATUS: Active

我想统计有多少客户已经连续订阅了至少13个月，与最近的订阅没有差距；"活动"；地位如果订阅中断，PREV_ORDER_ID将为NULL。

我希望在查询中做到这一点，而不必为其编写匿名块

非常感谢！

Answer 1

您可以这样做(使用实际的表和列名，而不是查询中的with子句，您应该删除它(。注意，分层递归从末尾开始(从'Active'状态开始(并向后进行；在我的查询中，我在第4级停止了它，因为我不想写足够的行来达到第13级。当然，在where子句中，您必须将4替换为13。

with
test_data (customer_id, order_id, prev_order_id, status) as (
select 1, 123, null, 'Complete' from dual union all
select 1, 456,  123, 'Complete' from dual union all
select 1, 789,  456, 'Complete' from dual union all
select 1, 888,  789, 'Complete' from dual union all
select 1, 999,  888, 'Active'   from dual union all
select 2, 100, null, 'Complete' from dual union all
select 2, 200,  100, 'Active'   from dual union all
select 5, 105, null, 'Complete' from dual union all
select 5, 106,  105, 'Complete' from dual union all
select 5, 205, null, 'Complete' from dual union all
select 5, 206,  205, 'Active'   from dual
)
select  customer_id
from    test_data
where   level = 4
start   with status = 'Active'
connect by customer_id = prior customer_id and order_id = prior prev_order_id
;
CUSTOMER_ID
-----------
1

Answer 2

需要更多的数据和测试。

也许它会帮助你

CREATE TABLE CUSTOMER_LOG
(
CUSTOMER_ID number(5),
ORDER_ID number(5),
PREV_ORDER_ID number(5),
STATUS VARCHAR(50)
);
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,123,NULL, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,456,123, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,789,456, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,888,789, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,999,888, 'Active');
Select
l.*,
(
select count(*)
from CUSTOMER_LOG s
where s.customer_id=1
start with s.ORDER_ID=l.ORDER_ID
connect by s.ORDER_ID= prior s.PREV_ORDER_ID   
) QTDE
from CUSTOMER_LOG l
where l.status='Active'