# 巧用分析函数循序渐进解决实际问题 (r4笔记第10天)

```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```

```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```

```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```

```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```

1399 篇文章138 人订阅

0 条评论

## 相关文章

### 微信小程序分享：TabBar不出现如何处理

22750

storemanager/contents/item.php?page_code=

71160

34950

3.1K70

15240

### JDK11的ZGC小试牛刀

ZGC全称是Z Garbage Collector，是一款可伸缩(scalable)的低延迟(low latency garbage)、并发(concurren...

23720

32020

21650

22530

20400