巧用分析函数循序渐进解决实际问题

今天同事问我一个问题，他说问题的逻辑很清晰，但是感觉无从开始。问题的逻辑大体是这样的。

有一个表，存在着大量的数据，比如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

         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

这个时候我们对数据进行基本的排序，就能够清晰的看到这些操作的时间。

select *from test order by account_id,effective_date;

我们进行重要的一步操作，把操作进行分组，即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 

)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也可以解决，不过可能代码要更多。