快捷搜索:

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

今天同事问我一个问题,他说问题的逻辑很清晰,但是感觉无从开始。问题的逻辑大体是这样的。
有一个表,存在着大量的数据,比如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也可以解决,不过可能代码要更多。

本文源自: AG环亚集团

您可能还会对下面的文章感兴趣: