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

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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏疯狂的小程序

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

今天打算开始实战一个微信小程序项目,一开始就踩坑了,正确设置了TabBar,但是TabBar就是不能显示出来,后面才找到原因,这里记录下:

22750
来自专栏黑白安全

来做个Google Hack吗?

storemanager/contents/item.php?page_code=

71160
来自专栏ascii0x03的安全笔记

IE的BHO通过IHTMLDocument2接口获得网页源代码

参考了凤之焚的专栏:http://blog.csdn.net/lion_wing/article/details/769742 但是他的源码有些问题,即IHTM...

34950
来自专栏ImportSource

读懂一行Full GC日志(回复JVM内存分配担保机制一文中 Mr/Mrs Xxx 在留言区提出的问题)

回复JVM内存分配担保机制一文中 Mr/Mrs Xxx 在留言区提出的问题: “请问分配3M的时候,怎么还发生了full gc?” 回复如下: 发生Full ...

3.1K70
来自专栏Jerry的SAP技术分享

使用SAPGUI画图

国内80后上的编程课应该都学过Logo(https://en.wikipedia.org/wiki/Logo_(programming_language)这门编...

15240
来自专栏码匠的流水账

JDK11的ZGC小试牛刀

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

23720
来自专栏曾大稳的博客

使用SoudTouch实现变速变调

32020
来自专栏xingoo, 一个梦想做发明家的程序员

MFC 随机矩形

问题描述:   简单地使用随即的尺寸和颜色不停的绘制一系列的图像。 一种古老的方式:   设置一个向窗口函数发送WM_TIMER消息的windows计时器。  ...

21650
来自专栏Jed的技术阶梯

图解常见 GC 算法和垃圾收集器

垃圾收集(Garbage Collection) 通常被称为"GC",它诞生于1960年 MIT 的 Lisp 语言,经过半个多世纪,目前已经十分成熟了。 j...

22530
来自专栏游戏杂谈

cocos2d-x 2.x版本接入bugly的总结

最开始项目使用的是自己DIY的很简陋的上报系统,后来改成google breakpad来上报,发现其实都做的不太理想,游戏引擎因为版本历史问题存在一些崩溃问题。...

20400

扫码关注云+社区

领取腾讯云代金券