none
Per RAND-Funktion zufällige Namen generieren in einer TSQL-Abfrage RRS feed

  • Frage

  • Hallo,

    um Datenbankinhalte zu Testzwecken zu anonymisieren will ich über die RAND-Funktion aus 100 Vornamen (ggf. auch mehr und später dann auch Nachnamen) je Datensatz einen auswählen. (Siehe nachfolgend mein script dazu.)

    Mein Problem ist nun, dass der Wert eigentlich >= 0 und <= 100 sein sollte. Demnach sollte der Name 'XXXXX     Randolf', der im ELSE-Teil der Case-Struktur steht niemals zu Stande kommen. Das Auftreten dieses Wertes liegt aber bei ca. 40 %, wodurch eine Menge Randolfs erzeugt werden.

    Lasse ich einfach nur den Zufallswert ermitteln, also "Select rount((rand() * 100),0) liegen die erzeugten Zufallszahlen jedoch immer in diesem Wertebereich. Der ELSE-Teil würde nicht eintreffen.

    Jemand eine Idee woran das liegen könnte, bzw. wie man dies verhindern könnte?

    Gruß Chris

    select
    case round((rand() * 100),0)
    when 0 then 'Karl'
    when 1 then 'Gustav'
    when 2 then 'Egon'
    when 3 then 'Horst'
    when 4 then 'Guido'
    when 5 then 'Helmut'
    when 6 then 'Andreas'
    when 7 then 'Ludwig'
    when 8 then 'Sascha'
    when 9 then 'Felix'
    when 10 then 'Anton'
    when 11 then 'Bianca'
    when 12 then 'Christian'
    when 13 then 'Nils'
    when 14 then 'Mike'
    when 15 then 'Alfred'
    when 16 then 'Manfred'
    when 17 then 'Torsten'
    when 18 then 'Klaus'
    when 19 then 'Werner'
    when 20 then 'Markus'
    when 21 then 'Detlev'
    when 22 then 'Martin'
    when 23 then 'Daniel'
    when 24 then 'Wolfgang'
    when 25 then 'Diethelm'
    when 26 then 'Martina'
    when 27 then 'Manuel'
    when 28 then 'Dominik'
    when 29 then 'Franz'
    when 30 then 'Mandy'
    when 31 then 'Heinz'
    when 32 then 'Hannelore'
    when 33 then 'Fred'
    when 34 then 'Fabian'
    when 35 then 'Lorenz'
    when 36 then 'Stefan'
    when 37 then 'Theo'
    when 38 then 'Timo'
    when 39 then 'Jörg'
    when 40 then 'Christine'
    when 41 then 'Alexander'
    when 42 then 'Michelle'
    when 43 then 'Anja'
    when 44 then 'Anne'
    when 45 then 'Axel'
    when 46 then 'Björn'
    when 47 then 'Knut'
    when 48 then 'Johann'
    when 49 then 'Bernhard'
    when 50 then 'Robert'
    when 51 then 'Ronald'
    when 52 then 'Roland'
    when 53 then 'Richard'
    when 54 then 'Josef'
    when 55 then 'Ralf'
    when 56 then 'Thomas'
    when 57 then 'Tanja'
    when 58 then 'Torben'
    when 59 then 'Renate'
    when 60 then 'Rudolf'
    when 61 then 'Joachim'
    when 62 then 'Hans'
    when 63 then 'Willhelm'
    when 64 then 'Kurt'
    when 65 then 'Georg'
    when 66 then 'Waldemar'
    when 67 then 'Willfried'
    when 68 then 'Christof'
    when 69 then 'Jens'
    when 70 then 'Carsten'
    when 71 then 'Julia'
    when 72 then 'Josephine'
    when 73 then 'Elmar'
    when 74 then 'Uwe'
    when 75 then 'Kai'
    when 76 then 'Erika'
    when 77 then 'Florian'
    when 78 then 'Rolf'
    when 79 then 'Gerhard'
    when 80 then 'Holger'
    when 81 then 'Justin'
    when 82 then 'Kevin'
    when 83 then 'Dustin'
    when 84 then 'Dirk'
    when 85 then 'Ewald'
    when 86 then 'Niklas'
    when 87 then 'Elisabeth'
    when 88 then 'Rikardo'
    when 89 then 'Hubert'
    when 90 then 'Ignaz'
    when 91 then 'Karsten'
    when 92 then 'Kerstin'
    when 93 then 'Konrad'
    when 94 then 'Kristin'
    when 95 then 'Lars'
    when 96 then 'Laura'
    when 97 then 'Maik'
    when 98 then 'Mario'
    when 99 then 'Nadja'
    when 100 then 'Zacharias'
    else 'XXXXX     Randolf'
    end

    Dienstag, 26. März 2013 13:08

Antworten

  • Hallo Chris,

    RAND() hat einen größeren Schönheitsfehler: Es liefert in einer Abfrage immer den gleichen Wert.
    Willst Du mehrere Zufallswerte in einer Abfrage einfügen wird das deswegen nicht funktionieren.

    Besser ist eine Abfrage über NEWID(), was je Zeile einen neuen Wert liefert.

    Lutz Variante könnte (abgekürzt) lauten:

    WITH rndView(rndValue) AS (
    -- negative Zahlen maskieren und Modulo 100 select (CHECKSUM(NEWID()) & 0x7fffffff) % 100 AS rndValue ) select rndValue, case rndValue when 0 then 'Karl' --- ausgelassen --- end FROM rndView

    Hängt man z. B. ein CROSS JOIN sys.all_objects dran, erhält man eine relativ verteilte Auflistung.

    Wobei ich die Namen eher in eine Tabelle packen würde.

    Gruß Elmar

    Dienstag, 26. März 2013 14:19
  • Hallo,

    Sorry, dass ich mich erst jetzt melde!

    Vielen Dank für die ausführlichen Rückmeldungen!

    Das Problem habe ich gerade über NEWID in der Art gelöst, dass ich zunächst in die Spalten Vor- bzw. Nachname eine neue ID (eben NEWID) eintragen lasse.

    Danach lese ich über ein Updatescript aus einer zweiten Tabelle jeweils den Vor- und den Nachnamen zu der passenden "newid" aus und überschreibe den Wert. Somit habe ich bei jedem Datensatz einen neuen Namen stehen.

    ...

    , ((CHECKSUM(NEWID()) & 0x7fffffff) % 99) + 1 as nachname
    , ((CHECKSUM(NEWID()) & 0x7fffffff) % 99) + 1 as vorname

    ...

    Update Personen SET VORNAME = (SELECT VORNAME FROM Zufallsnamen WHERE nameid = personen.vorname),

    NACHNAME = (SELECT NACHNAME FROM Zufallsnamen WHERE nameid = personen.nachname)

    Also, nocheinmal VIELEN DANK!!!

    Gruß Chris

    P.S. Die Thematik sollte also geklärt sein ;O)

    Mittwoch, 17. April 2013 12:23

Alle Antworten

  • Erklären kann ich es nicht. Muss wohl einer der Experten machen.
    Ich kann mir allerdings vorstellen, daß der rand()-Ausdruck bei jedem
    Vergleich neu ausgeführt wird!?!?!?
     
    Aber folgende modifizierte Abfrage funktioniert bei mir anscheinend.
     
    WITH rndView(rndValue)
    AS
    (
        select round((rand() * 100),0) AS rndValue
    )
    select rndValue,
        case rndValue
        when 0 then 'Karl'
        when 1 then 'Gustav'
        when 2 then 'Egon'
        when 3 then 'Horst'
        when 4 then 'Guido'
        when 5 then 'Helmut'
        when 6 then 'Andreas'
        when 7 then 'Ludwig'
        when 8 then 'Sascha'
        when 9 then 'Felix'
        when 10 then 'Anton'
        when 11 then 'Bianca'
        when 12 then 'Christian'
        when 13 then 'Nils'
        when 14 then 'Mike'
        when 15 then 'Alfred'
        when 16 then 'Manfred'
        when 17 then 'Torsten'
        when 18 then 'Klaus'
        when 19 then 'Werner'
        when 20 then 'Markus'
        when 21 then 'Detlev'
        when 22 then 'Martin'
        when 23 then 'Daniel'
        when 24 then 'Wolfgang'
        when 25 then 'Diethelm'
        when 26 then 'Martina'
        when 27 then 'Manuel'
        when 28 then 'Dominik'
        when 29 then 'Franz'
        when 30 then 'Mandy'
        when 31 then 'Heinz'
        when 32 then 'Hannelore'
        when 33 then 'Fred'
        when 34 then 'Fabian'
        when 35 then 'Lorenz'
        when 36 then 'Stefan'
        when 37 then 'Theo'
        when 38 then 'Timo'
        when 39 then 'Jörg'
        when 40 then 'Christine'
        when 41 then 'Alexander'
        when 42 then 'Michelle'
        when 43 then 'Anja'
        when 44 then 'Anne'
        when 45 then 'Axel'
        when 46 then 'Björn'
        when 47 then 'Knut'
        when 48 then 'Johann'
        when 49 then 'Bernhard'
        when 50 then 'Robert'
        when 51 then 'Ronald'
        when 52 then 'Roland'
        when 53 then 'Richard'
        when 54 then 'Josef'
        when 55 then 'Ralf'
        when 56 then 'Thomas'
        when 57 then 'Tanja'
        when 58 then 'Torben'
        when 59 then 'Renate'
        when 60 then 'Rudolf'
        when 61 then 'Joachim'
        when 62 then 'Hans'
        when 63 then 'Willhelm'
        when 64 then 'Kurt'
        when 65 then 'Georg'
        when 66 then 'Waldemar'
        when 67 then 'Willfried'
        when 68 then 'Christof'
        when 69 then 'Jens'
        when 70 then 'Carsten'
        when 71 then 'Julia'
        when 72 then 'Josephine'
        when 73 then 'Elmar'
        when 74 then 'Uwe'
        when 75 then 'Kai'
        when 76 then 'Erika'
        when 77 then 'Florian'
        when 78 then 'Rolf'
        when 79 then 'Gerhard'
        when 80 then 'Holger'
        when 81 then 'Justin'
        when 82 then 'Kevin'
        when 83 then 'Dustin'
        when 84 then 'Dirk'
        when 85 then 'Ewald'
        when 86 then 'Niklas'
        when 87 then 'Elisabeth'
        when 88 then 'Rikardo'
        when 89 then 'Hubert'
        when 90 then 'Ignaz'
        when 91 then 'Karsten'
        when 92 then 'Kerstin'
        when 93 then 'Konrad'
        when 94 then 'Kristin'
        when 95 then 'Lars'
        when 96 then 'Laura'
        when 97 then 'Maik'
        when 98 then 'Mario'
        when 99 then 'Nadja'
        when 100 then 'Zacharias'
        else 'XXXXX     Randolf'
        end
    FROM rndView
     
    Dienstag, 26. März 2013 13:46
  • Prima!!!

    Besten Dank! Das hilft schon mal weiter!

    Jetzt muss ich nur noch schauen, wie ich das in ein Update-Select hinein bekomme...

    Gruß Chris

    Dienstag, 26. März 2013 14:02
  • Hallo Chris,

    kann ich auch nicht nachvollziehen; das Script unten generiert 100 Tsd Zufallszahlen und da war bisher keine bei, die ausserhalb des Bereiches lagen.

    Rand liefert ein Float Wert, das Round dazu ebenfalls und Float ist ein "ungefährer" numerischer Datentyp, wo man schon mit Ungenauigkeiten kämpfen muss; das sollte hier aber kein Problem darstellen.

    Welche SQL Server Version / Patchstand verwendest Du?

    CREATE TABLE #test (ID float);
    GO
    
    INSERT INTO #test (ID) 
    SELECT round((rand() * 100.0), 0) AS ID;
    -- 100000 mal wiederholen
    GO 100000
    
    SELECT *
    FROM #test
    WHERE NOT ID BETWEEN 0.0 AND 100.0;
    GO
    
    DROP TABLE #test;


    Olaf Helper

    Blog Xing

    Dienstag, 26. März 2013 14:07
  • Hallo Chris,

    RAND() hat einen größeren Schönheitsfehler: Es liefert in einer Abfrage immer den gleichen Wert.
    Willst Du mehrere Zufallswerte in einer Abfrage einfügen wird das deswegen nicht funktionieren.

    Besser ist eine Abfrage über NEWID(), was je Zeile einen neuen Wert liefert.

    Lutz Variante könnte (abgekürzt) lauten:

    WITH rndView(rndValue) AS (
    -- negative Zahlen maskieren und Modulo 100 select (CHECKSUM(NEWID()) & 0x7fffffff) % 100 AS rndValue ) select rndValue, case rndValue when 0 then 'Karl' --- ausgelassen --- end FROM rndView

    Hängt man z. B. ein CROSS JOIN sys.all_objects dran, erhält man eine relativ verteilte Auflistung.

    Wobei ich die Namen eher in eine Tabelle packen würde.

    Gruß Elmar

    Dienstag, 26. März 2013 14:19
  • Am 26.03.2013 15:07, schrieb Olaf Helper:
    > Hallo Chris,
    >
    > kann ich auch nicht nachvollziehen; das Script unten generiert 100 Tsd
    > Zufallszahlen und da war bisher keine bei, die ausserhalb des Bereiches
    > lagen.
    >
    > Rand liefert ein Float Wert, das Round dazu ebenfalls und Float ist ein
    > "ungefährer" numerischer Datentyp, wo man schon mit Ungenauigkeiten
    > kämpfen muss; das sollte hier aber kein Problem darstellen.
     
    Ich vermute, das der Rand()-Ausdruck bei jedem WHEN wieder neu
    ausgeführt wird und einen neuen Wert liefert.
     
    case round((rand() * 100),0)
    when 0 then 'Karl'    -- round((rand() * 100),0) =12 -> FALSE
    when 1 then 'Gustav'    -- round((rand() * 100),0) =94 -> FALSE
    when 2 then 'Egon'    -- round((rand() * 100),0) =51 -> FALSE
    when 3 then 'Horst'    -- round((rand() * 100),0) =1 -> FALSE
    when 4 then 'Guido'    -- round((rand() * 100),0) =68 -> FALSE
     
    Wahrscheinlich ist es dann Zufall, daß es mal eine Übereinstimmung gibt
    und deshalb läuft das CASE meistens bis in den ELSE-Zweig.
     
    Aber wie gesagt ist es nur eine Vermutung - keine Ahnung wie man das
    prüfen kann.
     
    Dienstag, 26. März 2013 14:38
  • Hallo Lutz,

    ich hatte den Rest vorhin nicht weiter beachtet.

    Aber was den mehrfachen Aufruf von RAND angeht, hast Du Recht.

    Ein CASE Ausdruck wird intern in IF ELSE Ausdrücke umgewandelt und sequentiell ausgewertet - so sieht es auch ANSI SQL vor.
    Und hier bewirkt es dass der RAND Ausdruck jedes Mal neu ausgewertet wird.

    Eine Übereinstimmung wird so auch zum Zufall ;)

    Schaut mal in den XML Plan sieht man etwas  wie:

      <IF>
          <Condition>
            <ScalarOperator>
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Intrinsic FunctionName="round">
                    <ScalarOperator>
                      <Arithmetic Operation="MULT">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="ConstExpr1003">
                              <ScalarOperator>
                                <Intrinsic FunctionName="rand">
                                  <ScalarOperator>
                                    <Const ConstValue="" />
                                  </ScalarOperator>
                                </Intrinsic>
                              </ScalarOperator>
                            </ColumnReference>
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1.000000000000000e+002)" />
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                    <ScalarOperator>
                      <Const ConstValue="(0)" />
                    </ScalarOperator>
                    <ScalarOperator>
                      <Const ConstValue="" />
                    </ScalarOperator>
                  </Intrinsic>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="(1.000000000000000e+000)" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Condition>
          <Then>
            <ScalarOperator>
              <Const ConstValue="'Gustav'" />
            </ScalarOperator>
          </Then>

    wobei sich das Ganze wiederholt und jedesmal eine neue ConstExpr1nnn gebildet wird.

    Gesünder ist es deswegen eine Unterabfrage zu verwenden:

    SELECT case r.v
    when 0 then '0:' + CAST(r.v AS varchar)
    when 1 then '1:' + CAST(r.v AS varchar)
    when 2 then '2:' + CAST(r.v AS varchar)
    when 3 then '3:' + CAST(r.v AS varchar)
    when 4 then '4:' + CAST(r.v AS varchar)
    else '?:' + CAST(r.v AS varchar)
    end FROM (SELECT cast((rand() * 4) as int) ) AS r(v)
    

    (Ich habe beispielhaft einen cast(int) verwendet, da der fixer als ein float Ausdruck ist, spielt aber für den Rest keine Rolle)

    Hier wird ein Ausdruck für r.v mit ConstExpr1002 gebildet, die für alle Teilausdrücke verwendet wird.
    Bei der von Dir verwendeten CTE kommt ebenfalls ein gemeinsamer Ausdruck heraus.

    Gruß Elmar

    Dienstag, 26. März 2013 17:26
  • Hallo,

    Sorry, dass ich mich erst jetzt melde!

    Vielen Dank für die ausführlichen Rückmeldungen!

    Das Problem habe ich gerade über NEWID in der Art gelöst, dass ich zunächst in die Spalten Vor- bzw. Nachname eine neue ID (eben NEWID) eintragen lasse.

    Danach lese ich über ein Updatescript aus einer zweiten Tabelle jeweils den Vor- und den Nachnamen zu der passenden "newid" aus und überschreibe den Wert. Somit habe ich bei jedem Datensatz einen neuen Namen stehen.

    ...

    , ((CHECKSUM(NEWID()) & 0x7fffffff) % 99) + 1 as nachname
    , ((CHECKSUM(NEWID()) & 0x7fffffff) % 99) + 1 as vorname

    ...

    Update Personen SET VORNAME = (SELECT VORNAME FROM Zufallsnamen WHERE nameid = personen.vorname),

    NACHNAME = (SELECT NACHNAME FROM Zufallsnamen WHERE nameid = personen.nachname)

    Also, nocheinmal VIELEN DANK!!!

    Gruß Chris

    P.S. Die Thematik sollte also geklärt sein ;O)

    Mittwoch, 17. April 2013 12:23