在老杨的博客上看到一个“号码分段的问题”，
原文地址：
需求是找到一组连续ID的最小值和最大值，要求除了数值连续外，另外两个字段也必须相同，否则认为是新的号段。
简单的说，最终需要的结果类似：
100-101 11 S
102-104 11 N
105-107 00 S
108-110 00 N
... ...
200-201 11 S
202-204 11 N
205-207 00 S
208-210 00 N
老杨用的是LAG分析函数。
然后自己也写了一个，用的是ROW_NUMBER分析函数及其GROUP BY：
SQL> DESC T
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
CA NUMBER(38)
REG CHAR(1)
SQL> SELECT * FROM T;
ID CA RE
---------- ---------- --
100 11 S
101 11 S
102 11 N
103 11 N
104 11 N
105 0 S
106 0 S
107 0 S
108 0 N
109 0 N
110 0 N
ID CA RE
---------- ---------- --
111 11 S
112 11 S
113 11 N
114 11 N
115 11 N
116 0 S
117 0 S
118 0 N
119 0 N
120 0 N
121 11 S
ID CA RE
---------- ---------- --
122 11 S
123 11 S
124 11 S
125 11 S
126 11 S
127 11 S
128 11 S
129 11 S
130 11 S
200 11 S
201 11 S
ID CA RE
---------- ---------- --
202 11 N
203 11 N
204 11 N
205 0 S
206 0 S
207 0 S
208 0 N
209 0 N
210 0 N
211 11 S
212 11 S
ID CA RE
---------- ---------- --
213 11 N
214 11 N
215 11 N
300 11 S
301 11 S
302 11 N
303 11 N
304 11 N
305 0 S
306 0 S
307 0 S
ID CA RE
---------- ---------- --
308 0 N
309 0 N
310 0 N
311 11 S
312 11 S
313 11 N
314 11 N
315 11 N
已选择63行。
SQL> COL ID_RANGE FORMAT A20
SQL> SELECT TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
2 FROM
3 (
4 SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) RN
5 FROM T
6 )
7 GROUP BY CA,REG,RN
8 ORDER BY 1
9 /
ID_RANGE CA RE
-------------------- ---------- --
100-101 11 S
102-104 11 N
105-107 0 S
108-110 0 N
111-112 11 S
113-115 11 N
116-117 0 S
118-120 0 N
121-130 11 S
200-201 11 S
202-204 11 N
ID_RANGE CA RE
-------------------- ---------- --
205-207 0 S
208-210 0 N
211-212 11 S
213-215 11 N
300-301 11 S
302-304 11 N
305-307 0 S
308-310 0 N
311-312 11 S
313-315 11 N
已选择21行。
如果上面的方法需要表中的ID,CA,REG不能有重复的行，否则可能会得到不同的结果。
SQL> insert into t values (100, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (101, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (103, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (103, 11, 'S');
已创建 1 行。
SQL>
SQL> insert into t values (104, 11, 'N');
已创建 1 行。
SQL> SELECT TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
2 FROM
3 (
4 SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) R
5 FROM T
6 )
7 GROUP BY CA,REG,RN
8 ORDER BY 1
9 /
ID_RANGE CA RE
-------------------- ---------- --
100-100 11 S
100-103 11 S
101-103 11 S
102-104 11 N
104-104 11 N
105-107 0 S
108-110 0 N
111-112 11 S
113-115 11 N
116-117 0 S
118-120 0 N
ID_RANGE CA RE
-------------------- ---------- --
121-130 11 S
200-201 11 S
202-204 11 N
205-207 0 S
208-210 0 N
211-212 11 S
213-215 11 N
300-301 11 S
302-304 11 N
305-307 0 S
308-310 0 N
ID_RANGE CA RE
-------------------- ---------- --
311-312 11 S
313-315 11 N
已选择24行。
SQL> SELECT TO_CHAR(MIN(ID))||'-'||TO_CHAR(MAX(ID)) ID_RANGE,CA,REG
2 FROM
3 (
4 SELECT CA,REG,ID,ID-ROW_NUMBER() OVER (PARTITION BY CA,REG ORDER BY ID) R
5 FROM (SELECT DISTINCT ID,CA,REG FROM T)
6 )
7 GROUP BY CA,REG,RN
8 ORDER BY 1;
ID_RANGE CA RE
-------------------- ---------- --
100-101 11 S
102-104 11 N
103-103 11 S
105-107 0 S
108-110 0 N
111-112 11 S
113-115 11 N
116-117 0 S
118-120 0 N
121-130 11 S
200-201 11 S
ID_RANGE CA RE
-------------------- ---------- --
202-204 11 N
205-207 0 S
208-210 0 N
211-212 11 S
213-215 11 N
300-301 11 S
302-304 11 N
305-307 0 S
308-310 0 N
311-312 11 S
313-315 11 N
已选择22行。
SQL>
通过对DISTINCT去重复 就可以得到正确的结果。
阅读(1792) | 评论(0) | 转发(0) |
