Application of Oracle hierarchical Query and analysis function in number segment selection

Transferred from: http://www.itpub.net/thread-719692-1-1.html

Summary
A set of consecutive numbers, minus some of the middle number, how to find the remaining number of intervals (i.e., number of segments)? Knowing how to start and end of a segment, how to find out all the numbers in that number? Know a large number segment range and already taken number segment, how to find out the number of available segments? With the powerful query capabilities and analytic functions provided by Oracle, we can easily solve these problems.

Keywords:
Number segment selection, continuous number, breakpoint, hierarchical query, analytic function, connect by, rownum, level, lead, lag

1. The question was raised
In the actual work, we often encounter the problem of the number segment selection, for example:
? A set of consecutive numbers, removing the middle number, requiring the interval of the remaining number (that is, segment)
For example: A string of numbers is 1,2,3,4,7,9,10, then the number segment is 1-4,7-7,9-10
? Know the beginning and end of the segment, and ask for all the numbers in that number
For example, the number segment is 1-3, 15-15, and all the numbers in the number are 1,2,3,15
? A set of numbers, which may have breakpoints in the middle, requiring a missing number
For example: A string of numbers is 1,2,3,4,7,9,10, then the missing number is 5,6,8
? Known large range and used number segment range, to find the available number segment range
For example: A large segment range of 0-999, the used number segment range 0-200, 399-599, the available number segment range is 201-398,600-999
2. Basic knowledge
Do a warm-up exercise first, review the use of hierarchical queries and Lead/lag functions.
2.1 Pseudo-column rownum and level
A pseudo-column is a column that is not actually present in the table. There is a lot of information about the pseudo-columns rownum and level. Just to emphasize that the pseudo-column is only for the result set.
2.2 Constructing successive numbers using hierarchical queries
? Generate 5~8 These 4 consecutive numbers
[PHP]
SELECT * FROM (select Rownum+4 to dual connect by rownum<5);
SELECT * FROM (select Level+4 to dual connect by level<5);
........
[/php]
? In August, for example, August 1, 2005, before the school year for students enrolled in the 2001~2004, followed by the 2002~2005. For the current date of admission of students to the school year:
[PHP]
SELECT * FROM (select To_char (add_months (Sysdate, 4), ' yyyy ')-rownum from dual connect by rownum<5);
........
[/php]
2.3 Using the analysis function lead and lag to get the field values of adjacent rows
[PHP]
Select RN, Lag (RN) over (order by RN) Previos, leads (RN) over (order by RN) next
From (select Rownum+4 rn from dual connect by rownum<5);

RN Previos NEXT
---------- ---------- ----------
5 6
6 5 7
7 6 8
8 7
........
[/php]
To put it simply, the lag is the content of the previous line, and the lead is the content of the following line.
[PHP]
Select RN, Lag (rn,2,-1) over (order by RN) Previos, leads (rn,2,-1) over (order by RN) next
From (select Rownum+4 rn from dual connect by rownum<5);

RN Previos NEXT
---------- ---------- ----------
5-1 7
6-1 8
7 5-1
8 6-1
........
[/php]
Here, the value is automatically set to null if offset is out of range and the default value of 1 is not set by specifying the offset parameter to get the contents before two lines and after two lines.
3. Resolution of the problem
With the accumulation of basic knowledge, we can solve the problems mentioned above.
3.1 Known numbers number segment
Examples of 3.1.1 Problems
I have a table structure,
Fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

(The second field may be contiguous and there may be a breakpoint.) )

How can query out such results, query out a continuous record.
Just like the following?
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

3.1.2 Solutions
Idea: Use lag to get the kshm of the previous line, and then compare with the bank's KSHM, if the difference is 1, the line and the previous row are continuous. Because of the specificity of the end and end, it is necessary to first use Max and Min to obtain the end point.
[PHP]
Select Fphm, NVL (Lag (e) over (partition by FPHM order by S), Minn) ST, NVL (S,MAXN) EN from
(select Fphm, Lag (kshm,1) over (partition by FPHM ORDER by Kshm) S, Kshm E,
Min (KSHM) over (partition by FPHM) Minn, Max (KSHM) through (partition by FPHM) MAXN from T)
where NVL (e-s-1,1) <>0;

FPHM ST EN
---------- ---------- ----------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

........
[/php]
3.2 According to the number of paragraphs to find the number contained
Examples of 3.2.1 problems
There are table and test data as follows:
CREATE TABLE T20
(
ID Number (2),
S Number (5),
E Number (5)
);

INSERT into T20 (ID, S, E) VALUES (1, 10, 11);
INSERT into T20 (ID, S, E) VALUES (2, 1, 5);
INSERT into T20 (ID, S, E) VALUES (3, 88, 92);
COMMIT;

S is the beginning of the number segment, and E is the end of the number segment, and the number between the start and end points (including start and end points) is calculated.
3.2.2 Solutions
Obviously, this needs to construct a sequence to solve the problem.
[PHP]
Select a.ID, A.S, A.e,b.dis, a.s+b.dis-1 h from
T20 A,
(Select RowNum dis from
(select Max (e-s) +1 Gap from T20)
Connect by Rownum<=gap) b
where a.e>=a.s+b.dis-1
Order BY a.ID, 4

Operation Result:
ID S E DIS H
---------- ---------- ---------- ---------- ----------
1 10 11) 1 10
1 10 11) 2 11
2 1 5) 1 1
2 1 5) 2 2
2 1 5) 3 3
2 1 5) 4 4
2 1 5) 5 5
3 88 92) 1 88
3 88 92) 2 89
3 88 92) 3 90
3 88 92) 4 91
3 88 92) 5 92

........
[/php]
Let's look at the following approach:

Select a.ID, A.S, A.e,rownum, a.s+rownum-1 h from
T20 A,
(select ID, e-s+1 gap from T20 where id=2) b
where a.id=b.id
Connect by Rownum<=gap

[PHP]
ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
2 1 5) 1 1
2 1 5) 2 2
2 1 5) 3 3
2 1 5) 4 4
2 1 5) 5 5

........
[/php]
Well, the result is also correct, if we remove the bold italic part, see what the result looks like:
[PHP]
ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
1 10 11) 1 10
1 10 11) 2 11
2 1 5) 3 3
2 1 5) 4 4
2 1 5) 5 5
2 1 5) 6 6
3 88 92) 7 94
........
[/php]
The result is obviously not what we need, let alone this is wrong. In this way, it is more deeply understood that pseudo-columns are only for the result set.
3.3 To find the missing number
Examples of 3.3.1 problems
Table T, column: Serial_no
I would like to be able to query the discontinuous value of the Serial_no field.
For example:
Serial_no
1
2
3
4
6
8
9
10
I want a SQL statement to find out the missing number,
The results shown are:
5
7
3.3.2 Solutions
Idea: Find the number B and the number A in front of it to compare (the number is sorted from large to small), if b-a=1, then the description is continuous, there is no breakpoint in the middle.
[PHP]
Select distinct S+level-1 rlt from (select Lags (serial_no,1) over (order by Serial_no) +1 s,
Serial_no-1 E from T) where e-s<>0 connect by level<=e-s
........
[/php]
3.4 To find unused number segments
Examples of 3.4.1 problems
Table A structure:
bill_type_id varchar2 (1),
Bill_start number,
Bill_end number,
Office_level VARCHAR2 (4)
The data are as follows:
A 0 999 1
A 0 199 2
A 300 499 2
A 700 799 2
The purpose of SQL is to take out a segment that is included in the Level1 level and has not entered the level2 level.
3.4.2 Solutions
This seems to be the inverse of the two problems of 3.1 and 3.3.
To create a table and test data:
CREATE TABLE T8
(
A Number (4),
B Number (4),
C Number (4),
Q VARCHAR2 (1 bytes)
);

Insert into T8 (A, B, C, Q) Values (555, 666, 2, ' A ');
Insert into T8 (A, B, C, Q) Values (+, 199, 2, ' A ');
Insert into T8 (A, B, C, Q) Values (0, 999, 1, ' A ');
Insert into T8 (A, B, C, Q) Values (499, 2, ' A ');
COMMIT;

Train of thought: compare the boundary of the large section to the boundary of the small section, "Dig" the small section from the large section, so that the rest is the usable number segment.
[PHP]
Select S,e from
(
SELECT NVL2 (LAG (a) over (PARTITION by Q ORDER by A), B+1, MIN (a) over (PARTITION by Q)) S,
NVL (Lead (a) up (PARTITION by Q ORDER by A)-1, MAX (B) over (PARTITION by Q)) E
From T8 START with C=1 CONNECT by C-1 = PRIOR C and q= PRIOR Q
)
where s<=e
Operation Result:
S E
---------- ----------
0 99
200 299
500 554
667 999
........