シーケンスから欠落していない列の前の番号を検索します
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
ギャップごとに1行が必要な場合は、ウィンドウ関数を使用できます。
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
一方、連続した欠落番号があり、それぞれに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