Блог

Главная - Вопросы по программированию - Найти предыдущий номер в столбце, который не отсутствует в последовательности

Найти предыдущий номер в столбце, который не отсутствует в последовательности

#sql #oracle #datetime #window-functions #gaps-and-islands

#sql #Oracle #дата-время #окно-функции #пробелы и острова

Вопрос:

У меня есть таблица со столбцом, который должен содержать числа в полной последовательности, для простоты скажем, от 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>

Но без столбца notes, это просто для наглядности.

Я могу получить ответ, но sql настолько огромен и громоздок, что практически бесполезен. Спасибо.

Комментарии:

1. это похоже на вопрос о html и css, почему это помечено sql и oracle?

2. Поскольку вопрос касается sql в базе данных Oracle, чтобы получить требуемый набор результатов. html используется только потому, что это единственный видимый мной способ отображения таблицы для удобства чтения. Если я могу сделать это по-другому, дайте мне знать, и я отредактирую вопрос.

3. Вы можете скопировать и вставить результаты в код, который доступен для чтения. Но это намного проще, если вы предоставите ddl и некоторые dml и ожидаемые результаты (могут быть просто вставлены)

Ответ №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

С другой стороны, если у вас есть последовательные пропущенные числа, и вам нужна одна строка для каждого, тогда вам нужна какая-то рекурсия:

 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