시퀀스에서 누락되지 않은 열에서 이전 번호 찾기
Aug 18 2020
완전한 순서로 숫자를 포함해야하는 열이있는 테이블이 있습니다. 단순화를 위해 101에서 110까지 말하겠습니다. 그러나이 테이블은 신뢰할 수없는 정보를 수동으로 입력해야하므로 순서의 숫자가 누락됩니다. 참조해야 할 동일한 테이블에 날짜 열도 있습니다. 이전에 입력 한 시퀀스 번호, 입력 한 날짜, 입력 한 날짜가있는 다음 시퀀스 번호와 함께 누락 된 시퀀스 번호를 모두 찾아야했습니다. 누락 된 시퀀스 번호를 찾는 것은 간단하며, 제가 고민하고있는 관련 이전 및 다음 레코드를 가져 오는 것입니다. 내 데이터가 다음과 같으면
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}<html>
<body>
<table>
<tr>
<th>Seq No</th>
<th>Date Input</th>
</tr>
<tr>
<td>101</td>
<td>01-JAN-20</td>
</tr>
<tr>
<td>102</td>
<td>05-JAN-20</td>
</tr>
<tr>
<td>104</td>
<td>07-JAN-20</td>
</tr>
<tr>
<td>105</td>
<td>08-JAN-20</td>
</tr>
<tr>
<td>106</td>
<td>09-JAN-20</td>
</tr>
<tr>
<td>108</td>
<td>10-JAN-20</td>
</tr>
<tr>
<td>109</td>
<td>11-JAN-20</td>
</tr>
<tr>
<td>110</td>
<td>12-JAN-20</td>
</tr>
</table>
</body>
</html>내 결과 세트는 다음과 같습니다.
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}<html>
<body>
<table>
<tr>
<th>Missing Seq No</th>
<th>Previous Date</th>
<th>Next Date</th>
<th>Notes</th>
</tr>
<tr>
<td>103</td>
<td>05-JAN-20</td>
<td>07-JAN-20</td>
<td>Dates from found seq nos 102 and 104</td>
</tr>
<tr>
<td>107</td>
<td>09-JAN-20</td>
<td>10-JAN-20</td>
<td>Dates from found seq nos 106 and 108</td>
</tr>
</table>
</body>
</html>그러나 메모 열이 없으면 명확성을 위해 거기에 있습니다.
나는 대답을 얻을 수 있지만 SQL은 너무 거대하고 다루기 힘들 기 때문에 쓸모없는 것만 큼 좋습니다. 감사.
답변
2 GMB Aug 18 2020 at 21:01
간격 당 하나의 행을 원하는 경우 창 함수를 사용할 수 있습니다.
select
lag_seq_no last_sequence_number,
lag_date_input last_date_input,
seq_no next_sequence_number,
date_input next_date_input
from (
select
t.*,
lag(seq_no) over(order by date_input) lag_seq_no,
lag(date_input) over(order by date_input) lag_date_input
from mytable t
) t
where seq_no > lag_seq_no + 1
반면에 연속적으로 누락 된 숫자가 있고 각각에 대해 하나의 행을 원하는 경우 일종의 재귀가 필요합니다.
with
data(seq_no, date_input, lag_seq_no, lag_date_input) as (
select
t.*,
lag(seq_no) over(order by date_input) lag_seq_no,
lag(date_input) over(order by date_input) lag_date_input
from mytable t
),
cte (seq_no, date_input, lag_seq_no, lag_date_input) as (
select seq_no, date_input, lag_seq_no + 1, lag_date_input
from data
where seq_no > lag_seq_no + 1
union all
select seq_no, date_input, lag_seq_no + 1, lag_date_input
from cte
where seq_no > lag_seq_no + 1
)
select
lag_seq_no missing_seq_no,
lag_date_input last_date_input,
date_input next_date_input
from cte