巧用分析函式循序漸進解決實際問題

今天同事問我一個問題，他說問題的邏輯很清晰，但是感覺無從開始。問題的邏輯大體是這樣的。
有一個表，存在著大量的資料，比如account_id為1代表account的編號，可以把這個account做暫停操作，相當於把賬戶凍結，然後在一定的時候後做恢復操作，相當於把賬戶解凍。就對應ACTIVITY_CODE的 SUSPEND,RESUME
ACCOUNT_ID ACTIVITY_CODE      EFFECTIVE_DATE
---------- ------------------------------ ------------------
         1 SUSPEND                        28-DEC-14
         1 RESUME                         01-JAN-15
         1 SUSPEND                        03-JAN-15
         1 RESUME                         06-JAN-15
         1 SUSPEND                        07-JAN-15
         1 RESUME                         08-JAN-15
現在想統計這些操作的累計時間，比如對於ACCOUNT_ID為1的賬戶來說，存在3組操作。
SUSPEND--RESUME  28-DEC-14 ~  01-JAN-15   耗時4天
SUSPEND--RESUME  03-JAN-15 ~  06-JAN-15    耗時3天
SUSPEND--RESUME  07-JAN-15 ~  08-JAN-15    耗時1天
其中SUSPEND和RESUME操作是一組操作，RESUME操作依賴於SUSPEND，這一點很容易理解，要解凍賬號，首先賬號得已經凍結。
所以對於上面的操作來說我們期望得到對於ACCOUNT_ID為1的賬戶來說，耗時8天。
如果單純得到最大值減去最小值得到的時間差是不準確的，畢竟每組操作的時間是不連續的。

我使用瞭如下的語句進行了簡單模擬。
首先建立表test
create table test(account_id number,activity_code varchar2(30),effective_date date);

插入一些資料，為了儘量使資料看起來不是很規整，把資料的插入順序進行了微調。
insert into test values(1,'SUSPEND',sysdate-12);
insert into test values(1,'RESUME',sysdate-8);
insert into test values(1,'SUSPEND',sysdate -6);
insert into test values(1,'RESUME',sysdate-3);

insert into test values(2,'SUSPEND',sysdate-10);
insert into test values(2,'RESUME',sysdate-7);
insert into test values(2,'SUSPEND',sysdate -5);
insert into test values(2,'RESUME',sysdate-4);
insert into test values(2,'SUSPEND',sysdate-2);
insert into test values(2,'RESUME',sysdate-1);

insert into test values(1,'SUSPEND',sysdate-2);
insert into test values(1,'RESUME',sysdate-1);

這樣我們得到了一個基本的列表。代表某個賬戶在某個時間段進行了某些操作。
ACCOUNT_ID ACTIVITY_CODE                  EFFECTIVE_DATE
---------- ------------------------------ ------------------
         1 SUSPEND                        28-DEC-14
         1 RESUME                         01-JAN-15
         1 SUSPEND                        03-JAN-15
         1 RESUME                         06-JAN-15
         2 SUSPEND                        30-DEC-14
         2 RESUME                         02-JAN-15
         2 SUSPEND                        04-JAN-15
         2 RESUME                         05-JAN-15
         2 SUSPEND                        07-JAN-15
         2 RESUME                         08-JAN-15
         1 SUSPEND                        07-JAN-15
         1 RESUME                         08-JAN-15

這個時候我們對資料進行基本的排序，就能夠清晰的看到這些操作的時間。
select *from test order by account_id,effective_date;
ACCOUNT_ID ACTIVITY_CODE                  EFFECTIVE_DATE
---------- ------------------------------ ------------------
         1 SUSPEND                        28-DEC-14
         1 RESUME                         01-JAN-15
         1 SUSPEND                        03-JAN-15
         1 RESUME                         06-JAN-15
         1 SUSPEND                        07-JAN-15
         1 RESUME                         08-JAN-15
         2 SUSPEND                        30-DEC-14
         2 RESUME                         02-JAN-15
         2 SUSPEND                        04-JAN-15
         2 RESUME                         05-JAN-15
         2 SUSPEND                        07-JAN-15
         2 RESUME                         08-JAN-15

我們進行重要的一步操作，把操作進行分組，即SUSPEND-RESUME的操作合成一些，可以使用分析函式lead來完成。
比如對於
select t.account_id,effective_date,lead(effective_date,1,effective_date) over(partition by account_id order by account_id,effective_date) next_date  from test t
  2  /

ACCOUNT_ID EFFECTIVE_DATE     NEXT_DATE
---------- ------------------ ------------------
         1 28-DEC-14          01-JAN-15
         1 01-JAN-15          03-JAN-15
         1 03-JAN-15          06-JAN-15
         1 06-JAN-15          07-JAN-15
         1 07-JAN-15          08-JAN-15
         1 08-JAN-15          08-JAN-15
         2 30-DEC-14          02-JAN-15
         2 02-JAN-15          04-JAN-15
         2 04-JAN-15          05-JAN-15
         2 05-JAN-15          07-JAN-15
         2 07-JAN-15          08-JAN-15
         2 08-JAN-15          08-JAN-15

這樣就把操作進行了一個初步的分組，但是標黃列的資料是錯誤的，它統計的是RESUME-SUSPEND的時間差，這個和邏輯不符，我們需要得到的是SUSPEND-RESUME的時間差。我們可以進行簡單過濾。

SQL> select rownum,mod(rownum,2)chk_num,t.account_id,next_date-effective_date duration from 
  2  (
  3  select t.account_id,effective_date,lead(effective_date,1,effective_date) over(partition by account_id order by account_id,effective_date) next_date  from test t
  4  )t 
  5  /

    ROWNUM    CHK_NUM ACCOUNT_ID   DURATION
---------- ---------- ---------- ----------
         1          1          1          4
         2          0          1          2
         3          1          1          3
         4          0          1          1
         5          1          1          1
         6          0          1          0
         7          1          2          3
         8          0          2          2
         9          1          2          1
        10          0          2          2
        11          1          2          1
        12          0          2          0

對於mod(ronum,2)=0的資料行來說，這些資料可以排除，這些時間差是不需要考慮的。
最後的完整sql如下：
select t.account_id,sum(duration)
from
(
select rownum,mod(rownum,2)chk_num,t.account_id,next_date-effective_date duration from 
(
select t.account_id,effective_date,lead(effective_date,1,effective_date) over(partition by account_id order by account_id,effective_date) next_date  from test t
)t 
)t where chk_num=1
group by account_id;

ACCOUNT_ID SUM(DURATION)
---------- -------------
         1             8
         2             5
可以看到account_id為1的賬戶耗時8天，account_id為2的賬戶耗時5天。
在表中含有大量的資料前提下，這個操作的資源消耗也不高。如果採用pl/sql也可以解決，不過可能程式碼要更多。