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