Pergunta sobre oracle, gaps-and-islands, plsql, sql – Como verificar qualquer número ausente de uma série de números?

22

Eu estou fazendo um projeto criando um sistema de admissão para uma faculdade; as tecnologias são Java e Oracle.

Em uma das tabelas, os números de série pré-gerados são armazenados. Mais tarde, contra esses números de série, os dados do formulário do candidato serão inseridos. Minha exigência é que, quando o processo de inscrição for concluído, terei que gerar um relatório muito sábio. Se durante a alimentação de números de série pré-gerados, qualquer número de sequência desapareceu.

Por exemplo, digamos em uma tabela, os números de seqüência são 7001, 7002, 7004, 7005, 7006, 7010. A partir da série acima é claro que de 7001 a 7010 os números faltantes são 7003, 7007, 7008 e 7009

Existe alguma função do SGBD disponível no Oracle para descobrir esses números ou se qualquer procedimento armazenado pode cumprir o meu propósito, por favor sugira um algoritmo.

Eu posso encontrar algumas técnicas em Java, mas para a velocidade eu quero encontrar a solução no Oracle.

Eu adicionei a tag lacunas e ilhas. pesquisar por ele provavelmente renderá uma quantidade suficiente de arte anterior, incluindo consultas recursivas. wildplasser

Sua resposta

9   a resposta
1

Depende da função LAG da OracleNão requer conhecimento da sequência completa (mas, portanto, não detecta se os primeiros ou últimos números em sequência foram perdidos)Lista os valores em torno das listas de números ausentesLista as listas de números ausentes como grupos contíguos (talvez conveniente para relatórios)Tragicamente falha para listas muito grandes de números ausentes, devido a limitações 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*/
;

Saída:

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

mas seleciona a primeira seqüência (valor inicial), pois não possui predecessor. Testado no SQL Server, mas deve funcionar no Oracle

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

Aqui está um resultado de teste

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

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

Para obter uma sequência não atribuída, apenas façavalue[i] - 1 onde i é maior primeira linha, e.(7004 - 1 = 7003 and 7007 - 1 = 7006) quais são seqüências disponíveis

Eu acho que você pode melhorar esta simples consulta

Isso pressupõe uma tabela com todos os números de seqüências armazenados. Não há necessidade de fazer isso no Oracle. Ben
seria da Oracleconnect by executar melhor que isso? codingbiz
a resposta com os maiores votos aqui usa 2 funções agregadas - e o desempenho? codingbiz
Eu gosto desta solução simples, mas se você tem vários números perdidos em sequência (7,8,9), só detectará um deles Troglo
40

Uma solução sem codificar o 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.
Isso faz minha resposta parecer ridiculamente complicada demais! +1 Ben
Eu estava explorando a lógica por um tempo e decidi que era desnecessariamente perder tempo assim. Eu acho que deveria ter feito do Google uma boa prática. Portanto, +1 para esta resposta. 4 Leave Cover
2

= 8.4. Com algumas pequenas modificações na sintaxe CTE, o Oracle e o Microsoft também podem funcionar.

-- 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;
Bem, eu tenho dito que o CTE é implementado em oracle ver:stackoverflow.com/questions/6064970/oracle-cte-merge. É claro que a conexão / construção prévia existe há alguns anos, mas a sintaxe CTE é pelo menos parte de um padrão, e há sempre uma razão para alguma diversidade, mesmo que seja padrão. Como eu disse na minha resposta: pequenas diferenças na sintaxe (como omitir a palavra-chave RECURSIVE) podem existir. E finalmente: pelo menos a consulta funciona para mim (mebe com algumas mudanças para os outros também). Houve outras respostas postadas aqui quenão faça trabalhos. wildplasser
Não fui eu quem menosprezou, mas para ser justo com eles também é marcado Oracle e esta sintaxe está incorreta. Ben
Para o downvoter: por favor explique. Esta questão está etiquetada com 'sql' que é (deveria ser) sql padrão. Os CTEs são parte disso. wildplasser
0
 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;
Não downvoting porquefaz trabalhar se você alterar os valores dea para 1, 2, 3 ... 10, em vez dos 7001-7010 indicados por OP, mas ... bem, não funciona se os números em questão forem 7001-7010, conforme indicado na pergunta.dbfiddle aqui Bob Jarvis
13

Tente isto:

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)

Aqui está o resultado para a seqüência 7001, 7002, 7004, 7005, 7006, 7010:

From  To
7003  7003
7007  7009
1

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;

O select lhe dará a resposta do seu exemplo. Isso só faz sentido, se você souber com antecedência em que intervalo os números estão e o intervalo não deve ser muito grande. O primeiro número deve ser o deslocamento noROWNUM parte e o comprimento da seqüência é o limite para o nível noconnect by parte.

Você precisaconhecer que o valor é 9. Como você sabe disso? Ben
Foi o que escrevi: você precisa conhecer o alcance de sua sequência. Se eu entendi a tarefa corretamente, isso é provavelmente conhecido. Ou eu te entendi mal? Stefan
-1
 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
Por favor, edite sua postagem para contextualizar sua resposta. As respostas somente de código são apenas parcialmente úteis:stackoverflow.com/help/how-to-answer Uwe Allner
Números de sequência em falta. Harish H.N
1

connect by level ComoStefan fezNo entanto, você não pode usar uma subconsulta nesta declaração, o que significa que ela não é realmente adequada para você, pois você precisa saber quais são os valores máximo e mínimo de sua sequência.

Eu sugeriria umfunção de mesa forrada de tubos pode ser a melhor maneira de gerar os números que você precisa para fazer a junção. Para que isso funcione, você precisará de um objeto em seu banco de dados para retornar os valores para:

create or replace type t_num_array as table of number;

Então a função:

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 função, o seguinte retornaria uma lista de números de série, entre o mínimo e o máximo.

select * from table(generate_serial_nos);

O que significa que sua consulta para descobrir quais números de série estão faltando se torna:

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

Perguntas relacionadas