Bir dizide eksik olmayan sütundaki önceki sayıyı bul
Tam bir sırayla sayılar içermesi gereken bir sütuna sahip bir tablom var, basit olması için 101 ila 110 diyeceğiz. Bununla birlikte, bu tablo manuel olarak girilecek güvenilir olmayan bilgilere dayanıyor, bu nedenle dizideki sayılar gözden kaçıyor. Aynı tabloda, birazdan buna daha çok atıfta bulunmam gereken bir tarih sütunu da var. Girilen önceki sıra numarası, girildiği tarih ve girildiği tarihle birlikte bir sonraki sıra numarasıyla birlikte eksik sıra numaralarının tümünü bulmam için zorlandım. Eksik sıra numaralarını bulmak basittir, uğraştığım önceki ve sonraki kayıtları almaktır. Yani verilerim böyle görünseydi;
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>Sonuç kümem şöyle görünecektir;
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>Ancak notlar sütunu olmadan, bu sadece açıklık sağlamak içindir.
Bir cevaba ulaşabilirim, ama sql o kadar büyük ve hantal, yararsız olduğu kadar iyi. Teşekkürler.
Yanıtlar
Aralık başına bir satır istiyorsanız, pencere işlevlerini kullanabilirsiniz:
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
Öte yandan, ardışık eksik numaralarınız varsa ve her biri için bir satır istiyorsanız, bir tür özyinelemeye ihtiyacınız var:
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