客户有一个判断号码连续的问题,这种问题用分析函数解决是比较有代表性的。
表结构和数据为:
SQL> select * from tab1;
ID CA REG
---------- -- ---
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
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
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
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
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
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 rows selected.
需求是找到一组连续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
其实这个问题使用分析函数并不复杂,第一步是通过构造列判断是否连续,然后进一步构造出分组的依据,这里使用了一个小技巧,将1变成0,而从保证一个号段内SUM汇总的结果是相同的,最后GROUP BY就可以得到最终结果:
SQL> select min(id) || '-' || max(id) flag,
2
card_type,
3
region
4
from
5
(
6
select id, card_type, region, sum(flag) over(order by id) flag
7
from (
8 select id, card_type, region,
9 nvl(id-lag(id, 1)
over(order by id)
10 + case when card_type =
lag(card_type, 1) over(order by id) then 0 else 10 end
11 + case when region
= lag(region, 1) over(order by id) then 0 else 10 end
12 , 1) -1 flag
13 from tab1
14
)
15
)
16
group by card_type, region, flag
17 order by 1;
FLAG CA
REG
-------------------- -- ---
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
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 rows selected.
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-722465/ ,如需转载,请注明出处,否则将追究法律责任。