sql - numero - ¿Cómo verificar cualquier número faltante de una serie de números?

faltantes en una lista de números consecutivos excel

Question

Estoy haciendo un proyecto creando un sistema de admisión para una universidad; Las tecnologías son Java y Oracle.

En una de las tablas, se almacenan los números de serie pre-generados. Más tarde, contra esos números de serie, se ingresarán los datos del formulario del solicitante. Mi requisito es que cuando se complete el proceso de entrada, tendré que generar un informe de Lotes. Si durante la alimentación de números de serie pre-generados algún número de secuencia desapareció.

Por ejemplo, digamos en una tabla, los números de secuencia son 7001, 7002, 7004, 7005, 7006, 7010. De la serie anterior está claro que de 7001 a 7010 los números que faltan son 7003, 7007, 7008 y 7009

¿Existe alguna función DBMS disponible en Oracle para averiguar estos números o si algún procedimiento almacenado puede cumplir mi propósito, por favor sugiera un algoritmo?

Puedo encontrar algunas técnicas en Java, pero por velocidad quiero encontrar la solución en Oracle.

Answer 1

Aquí hay una solución que:

• Se basa en la función LAG de Oracle
• No requiere conocimiento de la secuencia completa (pero, por lo tanto, no detecta si se omitieron los primeros o últimos números en secuencia)
• Enumera los valores que rodean las listas de números que faltan
• Enumera las listas de números que faltan como grupos contiguos (quizás convenientes para informar)
• Trágicamente falla para listas muy grandes de números faltantes, debido a las limitaciones de listagg

SQL:

WITH MentionedValues /*this would just be your actual table, only defined here to provide data for this example */
        AS (SELECT *
              FROM (    SELECT LEVEL + 7000 seqnum
                          FROM DUAL
                    CONNECT BY LEVEL <= 10000)
             WHERE seqnum NOT IN (7003,7007,7008,7009)--omit those four per example
                                       ),
     Ranges /*identifies all ranges between adjacent rows*/
        AS (SELECT seqnum AS seqnum_curr,
                   LAG (seqnum, 1) OVER (ORDER BY seqnum) AS seqnum_prev,
                   seqnum - (LAG (seqnum, 1) OVER (ORDER BY seqnum)) AS diff
              FROM MentionedValues)
SELECT Ranges.*,
       (    SELECT LISTAGG (Ranges.seqnum_prev + LEVEL, '','') WITHIN GROUP (ORDER BY 1)
              FROM DUAL
        CONNECT BY LEVEL < Ranges.diff) "MissingValues" /*count from lower seqnum+1 up to lower_seqnum+(diff-1)*/
  FROM Ranges
 WHERE diff != 1 /*ignore when diff=1 because that means the numers are sequential without skipping any*/
;

Salida:

SEQNUM_CURR SEQNUM_PREV DIFF MissingValues
7004        7002        2    "7003" 
7010        7006        4    "7007,7008,7009"                  

Answer 2

Esto funcionó pero selecciona la primera secuencia (valor de inicio) ya que no tiene predecesor. Probado en SQL Server pero debería funcionar en Oracle

SELECT
    s.sequence  FROM seqs s
WHERE
    s.sequence - (SELECT sequence FROM seqs WHERE sequence = s.sequence-1) IS NULL

Aquí hay un resultado de prueba

  Table
  -------------
  7000
  7001
  7004
  7005
  7007
  7008

  Result
  ----------
  7000
  7004
  7007

Para obtener una secuencia no asignada, simplemente haga el value[i] - 1 donde i es mayor en la primera fila, por ejemplo (7004 - 1 = 7003 and 7007 - 1 = 7006) que son secuencias disponibles

Creo que puedes mejorar en esta simple consulta.

Answer 3

Esto funciona en postgres> = 8.4. Con algunas modificaciones leves en la sintaxis de CTE, también podría funcionar para Oracle y Microsoft.

-- EXPLAIN ANALYZE
WITH missing AS (
    WITH RECURSIVE fullhouse AS (
        SELECT MIN(num)+1 as num
        FROM numbers n0
        UNION ALL SELECT 1+ fh0.num AS num
        FROM fullhouse fh0
        WHERE EXISTS (
                SELECT * FROM numbers ex
                WHERE ex.num > fh0.num
                )
        )
        SELECT * FROM fullhouse fh1
        EXCEPT ( SELECT num FROM numbers nx)
        )
SELECT * FROM missing;

Answer 4

Habría sugerido connect by level como lo ha hecho Stefan , sin embargo, no puede usar una consulta secundaria en esta declaración, lo que significa que no es realmente adecuado para usted, ya que necesita saber cuáles son los valores máximo y mínimo de tu secuencia son

Yo sugeriría que una función de tabla alineada podría ser la mejor manera de generar los números que necesita para hacer la unión. Para que esto funcione, necesitaría un objeto en su base de datos para devolver los valores a:

create or replace type t_num_array as table of number;

Entonces la función:

create or replace function generate_serial_nos return t_num_array pipelined is

   l_first number;
   l_last number;

begin

   select min(serial_no), max_serial_no)
     into l_first, l_last 
     from my_table
          ;

   for i in l_first .. l_last loop
      pipe row(i);
   end loop;

   return;

end generate_serial_nos;
/

Usando esta función, lo siguiente devolvería una lista de números de serie, entre el mínimo y el máximo.

select * from table(generate_serial_nos);

Lo que significa que su consulta para averiguar qué números de serie faltan se convierte en:

select serial_no
  from ( select * 
           from table(generate_serial_nos) 
                ) generator 
  left outer join my_table actual
    on generator.column_value = actual.serial_no
 where actual.serial_no is null

Answer 5

Prueba esto:

SELECT t1.SequenceNumber + 1 AS "From",
       MIN(t2.SequenceNumber) - 1 AS "To"
FROM MyTable t1
JOIN MyTable t2 ON t1.SequenceNumber < t2.SequenceNumber 
GROUP BY t1.SequenceNumber
HAVING t1.SequenceNumber + 1 < MIN(t2.SequenceNumber)

Aquí está el resultado para la secuencia 7001, 7002, 7004, 7005, 7006, 7010:

From  To
7003  7003
7007  7009

Answer 6

Una forma sencilla de obtener su respuesta para su escenario es esta:

create table test1 ( a number(9,0));

insert into test1 values (7001);
insert into test1 values (7002);
insert into test1 values (7004);
insert into test1 values (7005);
insert into test1 values (7006);
insert into test1 values (7010);
commit;

select n.n from (select ROWNUM + 7001 as n from dual connect by level <= 9) n 
   left join test1 t on n.n = t.a where t.a is null;

La selección le dará la respuesta de su ejemplo. Esto solo tiene sentido, si sabe de antemano en qué rango están sus números y el rango no debería ser demasiado grande. El primer número debe ser el desplazamiento en la parte ROWNUM y la longitud de la secuencia es el límite al nivel en la connect by parte.

Answer 7

Una solución sin codificar el 9:

select min_a - 1 + level
     from ( select min(a) min_a
                 , max(a) max_a
              from test1
          )
  connect by level <= max_a - min_a + 1
    minus
   select a
     from test1

Resultados:

MIN_A-1+LEVEL
-------------
         7003
         7007
         7008
         7009

4 rows selected.

Answer 8

 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;

Answer 9

 select    A.ID + 1 As ID
From [Missing] As A
Where A.ID + 1 Not IN (Select ID from [Missing])
And A.ID < n

Data: ID
1
2
5
7
Result: ID
3
4
6