今天在论坛看见一个有意思的帖子：http://www.itpub.net/showthread.php?s=&threadid=675285
大致的需求是使用SQL来判断记录的连续性，并找到每段记录的最小值和最大值。
一时手痒，就写了两种不同的实现方法，一种是通过聚集的方式，另一种是通过分析函数的方法。
SQL> CREATE TABLE TEST (TIME VARCHAR2(6));
表已创建。
SQL> INSERT INTO TEST VALUES ('200101');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200102');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200103');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200105');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200106');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200107');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200108');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200109');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200110');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200111');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200112');
已创建 1 行。
SQL> INSERT INTO TEST VALUES ('200202');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT MIN(TIME) '~' MAX(TIME)
2 FROM
3 (
4 SELECT
5 TIME,
6 MONTHS_BETWEEN(TO_DATE(TIME, 'YYYY-MM'), ADD_MONTHS(TRUNC(SYSDATE, 'MM'), ROWNUM)) INTERVAL
7 FROM (SELECT TIME FROM TEST ORDER BY TIME)
8 )
9 GROUP BY INTERVAL;
MIN(TIME) '~
-------------
200101~200103
200105~200112
200202~200202
这种方法的核心思想是通过和ROWNUM的比较，给不同的时间段分配不同的值，然后根据这个值进行GROUP。需要额外注意的是，首先要保证记录是按照顺序读取的，否则的话，会导致记录错误的分段。其次需要注意的是时间的处理，这里是将年和月的信息存在一个字符串中，但是判断记录是否连续需要根据时间的规则处理。而且，每个月包含的天数是不一致的，因此，需要使用处理月份的函数。
SQL> WITH A AS
2 (
3 SELECT
4 TIME,
5 MONTHS_BETWEEN(TO_DATE(TIME, 'YYYY-MM'), LAG(TO_DATE(TIME, 'YYYY-MM')) OVER(ORDER BY TIME)) LAG,
6 MONTHS_BETWEEN(LEAD(TO_DATE(TIME, 'YYYY-MM')) OVER(ORDER BY TIME), TO_DATE(TIME, 'YYYY-MM')) LEAD
7 FROM TEST
8 ),
9 B AS
10 (
11 SELECT TIME BEGIN, ROWNUM RN
12 FROM
13 A
14 WHERE NVL(LAG, 2) != 1
15 ORDER BY BEGIN
16 ),
17 C AS
18 (
19 SELECT TIME END, ROWNUM RN
20 FROM
21 A
22 WHERE NVL(LEAD, 2) != 1
23 ORDER BY END
24 )
25 SELECT B.BEGIN '~' C.END
26 FROM B, C
27 WHERE B.RN = C.RN
28 ;
B.BEGIN '~'
-------------
200101~200103
200105~200112
200202~200202
第二种方法是利用分析函数的方法，这种方法显得稍微麻烦一些，但是由于使用了WITH来优化语句，从执行记录上和第一种方法相差不大，嵌套层数也和第一种方式是一致的。
这种方法从思路上似乎更好理解一些，需要注意的仍然是上面提到的日期的处理问题。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-11 10:11:29 |
赞(
操作
