Zufallszahl mit Ausnahmen

[Ecke]

Hi,

hab da ein kleines Problem.
Ich habe eine Tabelle die mit Datensätzen gefüllt werden soll.
Jeder Datensatz hat eine ID Spalte. Die IDs sollen zwischen 100.000 und 399.999 liegen und zufällig vergeben werden.

Bisher habe ich:

SELECT 100000 + CONVERT(INT, (399999 - 100000 + 1) * RAND() ) as Zufall


Das gibt mir eine Zufallszahl aus.
Allerdings sind in der Tabelle ja bereits Datensätze enthalten. Wie kann ich das Statement jetzt erweitern damit ich eine Zufallszahlbekomme die nicht eine der bereits vergebenen ist?


MfG
Ecke

[gfoidl]

Hallo,

mit Zufallszahlen (des SQL Server) geht das nicht, da nicht ausgeschlossen werden kann dass keine doppelten Werte erzeugt werden.

Desweiteren wird bei deiner Implementierung mit RAND() (immer) die selbe Zufallszahl erzeugt da keine Seed angegeben ist.

Eine Möglichkeit die Tabelle mit IDs zu füllen wäre wie in diesem Beispiel:
[highlight=sql]
-- Temporäre Tabelle mit den ID in
-- sequentieller Folge von 10..39:
CREATE TABLE #tmp
(
ID int
);

DECLARE @i int;
SET @i = 10;
WHILE @i < 40
BEGIN
INSERT INTO #tmp VALUES (@i)
SET @i += 1;
END;

-- Tabelle (hier auch temporär) mit den IDs in zufälliger Reihenfolge:
CREATE TABLE #ZufallsIDs
(
ID int
);

INSERT INTO #ZufallsIDs (ID)
SELECT ID
FROM #tmp
ORDER BY NEWID()

SELECT * FROM #ZufallsIDs

-- Aufräumen:
DROP TABLE #tmp;
DROP TABLE #ZufallsIDs;
[/highlight]

mfG Gü

PS: Der Code kann mit [highligh=sql]dein Code[/highlight] formatiert werden.

[Ecke]

Danke für die schnelle Antwort.
Aber irgendiwe kapier ich das nicht, der Code von dir scheint mir nur den Inhalt der Tabelle in Zufälliger Reihenfolge auszugeben.

Ich habe jetzt folgenden Code:

[highlight=sql]
CREATE TABLE #ZufallsLfdNr
(
LfdNr int
);

INSERT INTO #ZufallsLfdNr (LfdNr)
SELECT LfdNr
FROM Belege
ORDER BY NEWID()

SELECT * FROM #ZufallsLfdNr

DROP TABLE #ZufallsLfdNr;
[/highlight]


Damit bekomme ich die Zeilen aus der Tabelle Belege in Zufälliger Reihenfolge ausgegeben.



Was ich aber brauche ist die nächste Nummer als Zufall.
Also in einer Tabelle sind 3 Datensätze mit Nummern 15, 44 und 67.

Und ich möchte jetzt die nächste Nummer generieren zwischen 1 und 100, per Zufall natürlich, sie darf aber keine der bereits enthaltenden sein.

1 Ansatz wäre in einer While Schleife eine Zufallsnummer zu generieren, zu schauen ob sie vergeben ist, wenn ja weiter, wenn nein diesen Wert zurückgeben.
Wenn man damit aber ganz großes Pech hat dauert das ewig, besonders je voller die Datenbank wird.

Ein 2. Ansatz wäre ich habe eine extra Tabelle die vorher mit Zufallszahlen gefüllt wurde.
Jetzt schaue ich wieviele Datensätze in der Belege Tabelle enthalten sind und nehme dann die Zufalls zahl an der (Count(*)+1)-ten Stelle
Also bei 3 Datensätzen die Zahl von der 4, usw.


Aber ich hatte gehofft das es eine Lösung gibt die Eleganter und komplett im SQL zu realisieren ist.

[gfoidl]

Aber irgendiwe kapier ich das nicht, der Code von dir scheint mir nur den Inhalt der Tabelle in Zufälliger Reihenfolge auszugeben.

Mehr geht eben nicht (so einfach).

Ein 2. Ansatz wäre ich habe eine extra Tabelle die vorher mit Zufallszahlen gefüllt wurde.

Das entspricht dann dem was in obigen Beispiel ist.


Zufallszahlen sind zufällig verteile Zahlen. Es ist daher aus statistischer Sicht nicht zielführend einen Vektor mit 100 Elementen so zu füllen dass keine Zahl doppelt auftritt. Dieses Problem wird in der Mathematik mit Permutationen beschrieben. Allerdings bekommst du mit Permutationen auch das "Gesamtergebnis" und musst die Werte aus einer "Look-Up-Tabelle" holen -> da ist obige Umsetzung einfacher.

Rein in SQL kann das (außer wie oben gezeigt) AFAIK nicht gelöst werden.

Was spricht dagegen die ID in sequentieller Folge zu vergeben?

mfG Gü

[Ecke]

Naja die untere Grenze liegt bei 1 und die obere bei 99.999.999
Das dauert

[gfoidl]

Die IDs sollen zwischen 100.000 und 399.999 liegen und zufällig vergeben werden.

untere Grenze liegt bei 1 und die obere bei 99.999.999

Weißt du überhaupt was du machst/machen sollst?

[Ecke]

Jap, es ist für 2 Sachen nötig.

Brauchen tu ich das ganze für den größeren Bereich.
In diesem einen Fall im Moment, ist der Nummernkreis eingeschränkt auf den zuerst genannten Bereich.

Aber im Normalfall ist er nicht eingeschränkt und dann muss es bis 99.999.999 gehen.

Sorry wenn ich etwas Verwirrung gestiftet habe.
Aber wenn jemand nachträglich den Bereich ändert muss man ja trotzdem sicherstellen das Nummern nicht doppelt vorkommen.


Normalerweise sind die Nummernkreise fortlaufend, aber man soll eben auch zufällig wählen können, dann würde ich (wenn das jemand ausgewählt hat) eine Tabelle zum Nummernkreis anlegen mit den Zufallszahlen. Somit hat dann jeder Kreis seinen eigenen Zufallsbereich



Im Moment habe ich es so, das wenn man zufällig wählt eine Tabelle mit Zahlen angelegt wird von 1 bis 999999 (das geht von der Zeit her noch)

Die Abfrage grenzt den Bereich dann ein:

[highlight=sql]
SELECT TOP(1) ZufallsIDs.LfdNr
FROM ZufallsIDs, Belege
WHERE (ZufallsIDs.LfdNr NOT IN( SELECT LfdNr FROM Belege ))
AND (ZufallsIDs.LfdNr BETWEEN 199999 AND 399999)
ORDER BY NewID()
[/highlight]

Wobei ich jetzt das Problem habe das diese Abfrage ganz schön lange dauert.
Und die Tabelle mit den Zahlen ja nur 999.999 Werte hat und nicht 99.999.999
Wobei das die Abfrage der nächsten Nummer wohl nochmal verlangsamt.

Ach menno, das ist doch doof :P

[gfoidl]

Was spricht dagegen die ID in sequentieller Folge zu vergeben?

Gibt weniger Probleme

[Ecke]

Stimmt,
dann benötige ich auch nur 1 Tabelle mit zahlen die ich beim DB-Steup anlegen kann.

Und den Rest macht die Abfrage.
Das ist irgednwie einfacher, da hast du Recht


Danke für deine Hilfe.


PS: Mal was anderes, muss man wirklich eine Tabelle mit zahlen von 1 bis 99.999.999 anlegen, das ist irgendie komisch. Kann man das nicht mit im SELECT Statement unterbringen?

[gfoidl]

Für Zahlen von 1...MAX(int) kann in der Tabelle
[highlight=sql]
CREATE TABLE MeineTabelle
(
ID int IDENTITY,
...
);
GO
[/highlight]
verwendet werden. Somit wird für jede neue Zeile die ID automatisch inkrementiert.

mfG Gü

[Ecke]

Dann muss ich aber trotzdem noch die 99999999 zeilen einfügen und das ist das was die Zeit frisst.


Dazu kommt, das eine Abfrage der nächsten Nummer ca. 5-15 sec dauert, je nach Bereich.
Da muss Performance mäßig noch was gemacht werden.
Wahrscheinlich werd ich immer die nächsten 20 Nummern zwischenspeichern oder so, anders wird es ned gehen.

[gfoidl]

Dann muss ich aber trotzdem noch die 99999999 zeilen einfügen und das ist das was die Zeit frisst.

Nein - beim Einfügen einer neuen Zeile in die tatsächliche Tabelle wird vom Datenbankserver die ID erstellt, sie müssen nicht vorher gespeichert werden.

Um einen Bereich zu erhalten kann in IDENDITY(startwert) der Startwert angegeben werden. Eine Schrittweite wäre auch möglich.

Schneller gehts nicht

mfG Gü

[hwoess]

Wenn es wirklich eine so weit wie möglich zufällige Zahl sein soll und keine Reihe, wie sie bei einem IDENTITY ensteht, wäre folgendes möglich:

create table #tbl (
  id    int identity(1,1),
  zahl  int)
CREATE UNIQUE NONCLUSTERED INDEX tbl_Zahl ON #tbl(zahl ASC)

declare @x int, @zz int
set @x = 1
while @x < 9000 begin
  select @zz = rand() * 9999
  if exists(select 1 from #tbl where zahl = @zz) begin
    select @zz = min(zahl) + 1 from #tbl mt1 where zahl > @zz
      and not exists(select 1 from #tbl mt2 where mt2.zahl = mt1.zahl + 1)
    if @@rowcount = 0
      select @zz = max(zahl) - 1 from #tbl mt1 where zahl < @zz
        and not exists(select 1 from #tbl mt2 where mt2.zahl = mt1.zahl - 1)
  end
  insert into #tbl values(@zz)
  set @x = @x + 1
end

select * from #tbl

drop table #tbl

Lässt sich direkt im ManagementStudio ausführen und ich denke, man kann erkennt, wie ich das meine.

bye,
Helmut

PS: probiere das mal ohne KEY-Definition