分析函数之SUM,LAG实战

今天在论坛里看到一个问题，看了牛人的解答，忍不住学习整理了下。

现有记录车辆状态的表TEST_TABLE创建如下，并插入一定量数据：

[sql]   view plain copy print ?

1. create table test_table(  
2.  can_use number(1),  
3.  c_date date,  
4.  des varchar2(4 char),  
5.  car_use_mode varchar2(20),  
6.  use_no varchar2(20)  
7. );  
8.   
9. insert into test_table values(1,sysdate-1/1440,'繁忙','租赁','HT12011600401');  
10. insert into test_table values(1,sysdate-2/1440,'繁忙','租赁','HT12011600401');  
11. insert into test_table values(1,sysdate-3/1440,'繁忙','租赁','HT12011600401');  
12. insert into test_table values(1,sysdate-4/1440,'繁忙','租赁','HT12011600401');  
13. insert into test_table values(1,sysdate-4/1440,'繁忙','租赁','HT12011600401');  
14. insert into test_table values(1,sysdate-5/1440,'繁忙','租赁','HT12011600401');  
15. insert into test_table values(1,sysdate-6/1440,'繁忙','租赁','HT12011600401');  
16. insert into test_table values(0,sysdate-7/1440,'空闲','','');  
17. insert into test_table values(0,sysdate-8/1440,'空闲','','');  
18. insert into test_table values(0,sysdate-9/1440,'空闲','','');  
19. insert into test_table values(0,sysdate-10/1440,'空闲','','');  
20. insert into test_table values(0,sysdate-11/1440,'空闲','','');  
21. insert into test_table values(1,sysdate-12/1440,'繁忙','租赁','HT12011600402');  
22. insert into test_table values(1,sysdate-13/1440,'繁忙','租赁','HT12011600402');  
23. insert into test_table values(1,sysdate-14/1440,'繁忙','保养','BY12011600401');  
24. insert into test_table values(1,sysdate-15/1440,'繁忙','保养','BY12011600401');  
25. insert into test_table values(1,sysdate-16/1440,'繁忙','保养','BY12011600401');  
26. insert into test_table values(1,sysdate-17/1440,'繁忙','保养','BY12011600401');  
27. insert into test_table values(0,sysdate-18/1440,'空闲','','');  
28. insert into test_table values(0,sysdate-19/1440,'空闲','','');  
29. insert into test_table values(0,sysdate-20/1440,'空闲','','');  
30. insert into test_table values(0,sysdate-21/1440,'空闲','','');  
31. insert into test_table values(0,sysdate-22/1440,'空闲','','');  
32. insert into test_table values(0,sysdate-23/1440,'空闲','','');  

上表中的数据有这样的特点： 车辆在一个连续的时间内空闲或是繁忙，但该表中每个空闲段或繁忙段都有多条记录。现在需要提取繁忙或空闲的时间段该如何做呢？

例如:2012-07-10 19:57  2012-07-10 20:20 繁忙 租赁 HT12011600402

显然解决这个问题的关键是如何对上述数据进行分组，但原表的数据是无法直接分组的，所以要通过一定的方法使这些数据可以实现分组。这时候该分析函数登场了。首先是LAG，该函数可以讲当前行与前一行进行比较，然后按一定的规则对当前行进行处理。

[sql]   view plain copy print ?

1. select decode(lag(tt.can_use) over(order by tt.c_date),  
2.                               tt.can_use,  
3.                               0,  
4.                               null,  
5.                               1,  
6.                               1) as rn,  
7.                        tt.*  
8.                   from (select * from test_table t order by t.c_date) tt  

上述语句的含义是：将表的数据按照时间进行排序，如果当前行的CAN_USE值和前一行相同，则改行的RN值为0。RN列的结果形式如下：

1

0

0

0

1

0

0

如果看到这里有点迷糊，那您该考虑将前面出现过的脚本放到自己的开发环境里运行下，这将非常有助于理解。

接下来SUM可以出场了。

[sql]   view plain copy print ?

1. select sum(t1.rn) over(order by t1.c_date) seq, t1.*  
2.           from (select decode(lag(tt.can_use) over(order by tt.c_date),  
3.                               tt.can_use,  
4.                               0,  
5.                               null,  
6.                               1,  
7.                               1) as rn,  
8.                        tt.*  
9.                   from (select * from test_table t order by t.c_date) tt)   

这样查询结果又增加了1列SEQ，SEQ列将原表的数据进行了十二分明确的分组。目标近在眼前了，下面就是问题的答案。

[sql]   view plain copy print ?

1. select mod(t2.seq,2),min(t2.c_date),max(t2.c_date),min(t2.des),t2.car_use_mode,t2.use_no from (  
2. select sum(t1.rn) over(order by t1.c_date) seq, t1.*  
3.           from (select decode(lag(tt.can_use) over(order by tt.c_date),  
4.                               tt.can_use,  
5.                               0,  
6.                               null,  
7.                               1,  
8.                               1) as rn,  
9.                        tt.*  
10.                   from (select * from test_table t order by t.c_date) tt) t1  
11.                   ) t2 group by t2.seq,t2.car_use_mode,t2.use_no  
12.                   order by min(t2.c_date)  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867611/viewspace-735350/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867611/viewspace-735350/