Trova il numero precedente nella colonna che non manca da una sequenza
Ho una tabella con una colonna che dovrebbe contenere numeri in una sequenza completa, per semplicità diremo da 101 a 110. Tuttavia, questa tabella si basa su informazioni inaffidabili da inserire manualmente, quindi i numeri nella sequenza vengono persi. C'è anche una colonna della data nella stessa tabella a cui devo fare riferimento, ne parleremo tra poco. Mi è stato chiesto di trovare tutti i numeri di sequenza mancanti insieme al numero di sequenza precedente immesso, la data in cui è stato inserito e il numero di sequenza successivo con la data in cui è stato inserito. Trovare i numeri di sequenza mancanti è semplice, è ottenere i record precedenti e successivi pertinenti con cui sto lottando. Quindi se i miei dati fossero così;
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>Il mio set di risultati sarebbe simile a;
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>Ma senza la colonna delle note, è lì solo per chiarezza.
Posso arrivare a una risposta, ma lo sql è così enorme e poco maneggevole che è tanto utile quanto inutile. Grazie.
Risposte
Se vuoi una riga per spazio, puoi usare le funzioni della finestra:
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
D'altra parte, se hai numeri mancanti consecutivi e vuoi una riga per ciascuno, allora hai bisogno di una sorta di ricorsione:
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