Frage an plsql, gaps-and-islands, sql, oracle – Wie überprüfe ich eine fehlende Zahl aus einer Reihe von Zahlen?

22

Ich mache ein Projekt zur Schaffung eines Zulassungssystems für ein College; Die Technologien sind Java und Oracle.

In einer der Tabellen sind vorgenerierte Seriennummern gespeichert. Gegen diese Seriennummern werden später die Formulardaten des Antragstellers eingegeben. Meine Anforderung ist, dass ich nach Abschluss des Anmeldevorgangs einen lotweisen Bericht erstellen muss. Wenn beim Zuführen von vorgenerierten Seriennummern Sequenznummern fehlen.

Beispielsweise lauten in einer Tabelle die Folgenummern 7001, 7002, 7004, 7005, 7006, 7010. Aus der obigen Reihe geht hervor, dass von 7001 bis 7010 die fehlenden Nummern 7003, 7007, 7008 und 7009 sind

Steht in Oracle eine DBMS-Funktion zur Verfügung, um diese Zahlen zu ermitteln, oder schlägt eine gespeicherte Prozedur einen Algorithmus vor, um meinen Zweck zu erfüllen.

Ich kann einige Techniken in Java finden, aber aus Gründen der Geschwindigkeit möchte ich die Lösung in Oracle finden.

Ich habe das Gaps-and-Islands-Tag hinzugefügt. Wenn Sie danach suchen, erhalten Sie wahrscheinlich eine ausreichende Menge des Standes der Technik, einschließlich rekursiver Abfragen. wildplasser

Deine Antwort

9   die antwort
0
 SELECT ROWNUM "Missing_Numbers" FROM dual CONNECT BY LEVEL <= (SELECT MAX(a) FROM test1)
 MINUS
 SELECT a FROM test1 ;
Nicht weil es downvotingtut Arbeit, wenn Sie die Werte von änderna zu 1, 2, 3 ... 10, anstatt der von OP notierten 7001-7010, aber ... nun, es funktioniert nicht, wenn die fraglichen Zahlen 7001-7010 lauten, wie in der Frage notiert.dbfiddle hier Bob Jarvis
2

= 8.4. Mit einigen geringfügigen Änderungen an der CTE-Syntax könnte sie auch für Oracle und Microsoft geeignet sein.

-- 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;
Zum Downvoter: bitte erläutern. Diese Frage ist mit "sql" markiert, was Standard-sql ist (sein sollte). CTEs sind ein Teil davon. wildplasser
Ich habe nicht herabgestimmt, aber um fair zu sein, hat es auch Oracle-Tags und diese Syntax ist falsch. Ben
Nun, mir wurde gesagt, dass der CTE in Oracle implementiert ist, siehe:stackoverflow.com/questions/6064970/oracle-cte-merge. Natürlich gibt es das connect-by / prior-Konstrukt schon seit einigen Jahren, aber die CTE-Syntax ist zumindest Teil eines Standards, und es gibt immer einen Grund für eine gewisse Vielfalt, auch wenn es Standard ist. Wie ich in meiner Antwort sagte: Es können geringfügige Unterschiede in der Syntax (wie das Weglassen des Schlüsselworts RECURSIVE) bestehen. Und schließlich: Zumindest funktioniert die Abfrage für mich (mit ein paar Änderungen auch für andere). Hier wurden andere Antworten gepostetnicht Arbeit. wildplasser
1

Eine einfache Möglichkeit, eine Antwort auf Ihr Szenario zu erhalten, ist folgende:

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;

Die Auswahl gibt Ihnen die Antwort aus Ihrem Beispiel. Dies ist nur dann sinnvoll, wenn Sie im Voraus wissen, in welchem ​​Bereich Ihre Zahlen liegen und der Bereich nicht zu groß sein sollte. Die erste Zahl muss der Versatz in der seinROWNUM Teil und die Länge der Sequenz ist die Grenze für das Niveau in derconnect by Teil.

Du musstkennt dass der Wert 9 ist. Woher weißt du das? Ben
Das habe ich geschrieben: Sie müssen den Bereich Ihrer Sequenz kennen. Wenn ich die Aufgabe richtig verstehe, ist dies wahrscheinlich bekannt. Oder habe ich dich missverstanden? 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
Bitte bearbeiten Sie Ihren Beitrag, um einen Kontext für Ihre Antwort bereitzustellen. Nur-Code-Antworten sind nur teilweise hilfreich:stackoverflow.com/help/how-to-answer Uwe Allner
Fehlende Sequenznummern. Harish H.N
13

Versuche dies:

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)

Hier ist das Ergebnis für die Sequenz 7001, 7002, 7004, 7005, 7006, 7010:

From  To
7003  7003
7007  7009
1

Verlässt sich auf die LAG-Funktion von OracleBenötigt keine Kenntnis der vollständigen Sequenz (erkennt jedoch nicht, ob die ersten oder letzten Zahlen in der Sequenz übersehen wurden)Listet die Werte auf, die die fehlenden Nummernlisten umgebenListet die fehlenden Nummernlisten als zusammenhängende Gruppen auf (möglicherweise praktisch für die Berichterstellung).Scheitert tragischerweise bei sehr großen Listen mit fehlenden Nummern aufgrund von Listagg-Einschränkungen

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*/
;

Ausgabe:

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

connect by level wieStefan hat es geschafftSie können in dieser Anweisung jedoch keine Unterabfrage verwenden, was bedeutet, dass sie für Sie nicht wirklich geeignet ist, da Sie die maximalen und minimalen Werte Ihrer Sequenz kennen müssen.

Ich würde vorschlagen, aPipe-Lined Table-Funktion Möglicherweise ist dies der beste Weg, um die Zahlen zu generieren, die Sie für den Join benötigen. Damit dies funktioniert, benötigen Sie ein Objekt in Ihrer Datenbank, um die Werte an folgende Adresse zurückzugeben:

create or replace type t_num_array as table of number;

Dann die Funktion:

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;
/

Wenn Sie diese Funktion verwenden, wird im Folgenden eine Liste von Seriennummern zwischen dem Minimum und dem Maximum zurückgegeben.

select * from table(generate_serial_nos);

Das bedeutet, dass Ihre Abfrage, welche Seriennummern fehlen, wie folgt lautet:

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
40

Eine Lösung ohne Hardcodierung der 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

Ergebnisse:

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

4 rows selected.
Das lässt meine Antwort lächerlich überkompliziert aussehen! +1 Ben
Ich habe mich selbst eine Weile mit der Logik befasst und festgestellt, dass es unnötig ist, so viel Zeit zu verschwenden. Ich denke, ich hätte Google zu einer guten Praxis machen sollen. Daher +1 auf diese Antwort. 4 Leave Cover
1

wählt aber die erste Sequenz (Startwert) aus, da sie keinen Vorgänger hat. Getestet in SQL Server, sollte aber in Oracle funktionieren

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

Hier ist ein Testergebnis

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

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

Um eine nicht zugewiesene Sequenz zu erhalten, tun Sie einfach Folgendesvalue[i] - 1 wo i größer ist, erste Reihe, z.B.(7004 - 1 = 7003 and 7007 - 1 = 7006) welche Sequenzen zur Verfügung stehen

Ich denke, Sie können diese einfache Abfrage verbessern

Dies setzt eine Tabelle mit allen in gespeicherten Folgenummern voraus. Dies ist in Oracle nicht erforderlich. Ben
Die Antwort mit den höchsten Stimmen verwendet hier 2 Aggregatfunktionen - was ist mit der Leistung? codingbiz
würde Orakelsconnect by besser abschneiden als das? codingbiz
Ich mag diese einfache Lösung, aber wenn Sie mehrere verpasste Nummern in Folge (7,8,9) haben, wird nur eine davon erkannt Troglo

Verwandte Fragen