客户有一个判断号码连续的问题，这种问题用分析函数解决是比较有代表性的。
表结构和数据为：
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. |