Jak wygenerować losową liczbę dla każdego wiersza w TSQL Select?


328

Potrzebuję innej liczby losowej dla każdego wiersza w mojej tabeli. Poniższy pozornie oczywisty kod używa tej samej losowej wartości dla każdego wiersza.

SELECT table_name, RAND() magic_number 
FROM information_schema.tables 

Chciałbym uzyskać z tego INT lub FLOAT. Reszta historii polega na tym, że użyję tej losowej liczby do utworzenia losowego przesunięcia daty od znanej daty, np. 1-14 dni przesunięcia od daty początkowej.

Dotyczy to Microsoft SQL Server 2000.


4
Czy istnieje rozwiązanie tego problemu, które nie korzysta z NEWID ()? Chcę być w stanie wygenerować tę samą sekwencję liczb losowych dla danego ziarna.
Rory MacLeod

@Rory Zadaj to pytanie jako nowe, zyska ono więcej uwagi. (Moja odpowiedź to użycie ustalonych tabel liczb losowych, np. Na przykład ten słynny standardowy zestaw liczb losowych: rand.org/pubs/monograph_reports/MR1418/index.html )
MatthewMartin


RAND został wprowadzony w 2005 r., Pytanie to zadano w 2009 r., Które organizacje nadal używały SQL 2000, ponieważ była to pierwsza wersja wystarczająco dobra, aby używać jej na zawsze.
MatthewMartin

Rory MacLeod zapytał: „Czy istnieje rozwiązanie tego problemu, które nie używa NEWID ()? Chcę być w stanie wygenerować tę samą sekwencję liczb losowych dla danego ziarna”. Odpowiedź brzmi tak, ale jest nieco skomplikowana. 1. Utwórz widok, który zwraca select rand () 2. Utwórz UDF, który wybiera wartość z widoku. 3. Przed wybraniem danych uruchom funkcję rand (). 4. Użyj UDF w instrukcji select.
Podam

Odpowiedzi:


516

Spójrz na SQL Server - Ustaw losowe liczby oparte na bardzo szczegółowym objaśnieniu.

Podsumowując, poniższy kod generuje liczbę losową od 0 do 13 włącznie o jednolitym rozkładzie:

ABS(CHECKSUM(NewId())) % 14

Aby zmienić zakres, wystarczy zmienić liczbę na końcu wyrażenia. Zachowaj szczególną ostrożność, jeśli potrzebujesz zakresu obejmującego zarówno liczby dodatnie, jak i ujemne. Jeśli zrobisz to źle, możliwe jest podwójne policzenie liczby 0.

Małe ostrzeżenie dla orzechów matematycznych w pokoju: w tym kodzie jest bardzo niewielki błąd. CHECKSUM()skutkuje liczbami, które są jednolite w całym zakresie typu danych sql Int lub przynajmniej tak blisko, jak może pokazać moje (edytor) testowanie. Jednak wystąpi pewne odchylenie, gdy CHECKSUM () wygeneruje liczbę na samym górnym końcu tego zakresu. Za każdym razem, gdy pojawi się liczba między maksymalną możliwą liczbą całkowitą a ostatnią dokładną wielokrotnością rozmiaru pożądanego zakresu (w tym przypadku 14) przed tymi liczbami całkowitymi, wyniki te są uprzywilejowane w stosunku do pozostałej części zakresu, z której nie można uzyskać ostatnia wielokrotność 14.

Jako przykład wyobraź sobie, że cały zakres typu Int wynosi tylko 19. 19 to największa możliwa liczba całkowita, jaką możesz pomieścić. Kiedy CHECKSUM () daje wynik 14-19, odpowiadają one wynikom 0-5. Liczby te byłyby znacznie bardziej uprzywilejowane w zakresie 6-13, ponieważ CHECKSUM () ma dwa razy większe szanse na ich wygenerowanie. Łatwiej jest to zademonstrować wizualnie. Poniżej znajduje się cały możliwy zestaw wyników dla naszego wyimaginowanego zakresu liczb całkowitych:

Liczba całkowita sumy kontrolnej: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Wynik zakresu: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 0 1 2 3 4 5

Widać tutaj, że istnieje większa szansa na uzyskanie niektórych liczb niż innych: stronniczości. Na szczęście rzeczywisty zasięg typu Int jest znacznie większy ... tak bardzo, że w większości przypadków odchylenie jest prawie niewykrywalne. Należy jednak pamiętać, jeśli robisz to z poważnym kodem bezpieczeństwa.


28
Ta linkowana strona miała rozwiązanie: ABS (CHECKSUM (NewId ()))% 14
MatthewMartin

7
% 14 zwróci liczby od 0 do 13
CoderDennis

7
@Dennis Palmer, wystarczy dodać 1
KM.

59
Właśnie odkryliśmy genialny błąd z tym. Ponieważ suma kontrolna zwraca liczbę całkowitą, a zakres liczby całkowitej wynosi od -2 ^ 31 (-2 147 483 648) do 2 ^ 31-1 (2 147 483 647), funkcja abs () może zwrócić błąd przepełnienia, jeśli wynik będzie dokładnie -2 147 483 648 ! Szanse są oczywiście bardzo niskie, około 1 na 4 miliardy, jednak codziennie przeprowadzaliśmy je w tabeli rzędu ~ 1,8b, więc zdarzało się to mniej więcej raz w tygodniu! Naprawiono rzutowanie sumy kontrolnej na bigint przed abs.
EvilPuppetMaster

17
Myślę, że powinno to oznaczać „rozkład równomierny”, a nie „rozkład znormalizowany” - każda liczba jest równie prawdopodobna, nie jest to krzywa dzwonowa. „Znormalizowany” ma określone znaczenie matematyczne.
AnotherParker

95

Gdy wywoływany wiele razy w jednej partii, rand () zwraca ten sam numer.

Sugerowałbym użycie argumentu konwersji ( varbinary, newid()) jako argumentu źródłowego:

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

newid() gwarantuje, że zwróci inną wartość za każdym razem, gdy zostanie wywołane, nawet w tej samej partii, więc użycie go jako zarodka spowoduje, że rand () poda za każdym razem inną wartość.

Edytowano, aby uzyskać losową liczbę całkowitą od 1 do 14.


Jak uzyskać liczbę z guid lub varbinary? Zaktualizuję pytanie, aby wskazać, że mam nadzieję na liczbę całkowitą.
MatthewMartin,

1
Mnożymy go przez liczbę i piętro :), więc jeśli chcesz pięć cyfr, pomnóż przez 100000 i przekonwertuj na liczbę całkowitą. Brzydkie, ale wystarczająco proste do zrobienia.
Jeremy Smyth,

1
Jako dodatkowy dodatek - który da ci do pięciu cyfr - jeśli chcesz go zerować, musisz użyć typu danych char i użyć replikacji do zerowania do 5 cyfr.
Jeremy Smyth,

Jeśli użyjesz funkcji sufitu zamiast podłogi, nie musisz dodawać 1.
PopeDarren 24.09.18

Nawet gdy tego używam, czasami RAND () zawsze daje mi ten sam wynik. Co dziwniejsze, zdarza się, że przeskakuje z prawidłowego do nieprawidłowego zachowania w zależności od tego, ile razy go używam. Próbuję wdrożyć RANDOM INNER JOIN i jeśli poproszę o więcej niż 19 (!!!) wierszy, zacznie to dawać mi zawsze ten sam wynik ...
Johannes Wentu

72
RAND(CHECKSUM(NEWID()))

Powyższe wygeneruje (pseudo-) losową liczbę od 0 do 1, z wyłączeniem. Jeśli zostanie użyty w selekcji, ponieważ wartość nasion zmienia się dla każdego wiersza, wygeneruje on nową liczbę losową dla każdego wiersza (nie ma jednak gwarancji, że wygeneruje unikalną liczbę dla wiersza).

Przykład w połączeniu z górną granicą 10 (daje liczby 1–10):

CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1

Dokumentacja Transact-SQL:

  1. CAST(): https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
  2. RAND(): http://msdn.microsoft.com/en-us/library/ms177610.aspx
  3. CHECKSUM(): http://msdn.microsoft.com/en-us/library/ms189788.aspx
  4. NEWID(): https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

39

Generowanie liczb losowych od 1000 do 9999 włącznie:

FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)

„+1” - aby uwzględnić górne wartości graniczne (9999 w poprzednim przykładzie)


Górna granica jest wyłączna dla tej metody, więc jeśli chcesz dołączyć najwyższy numer, musisz zrobićFLOOR(RAND(CHECKSUM(NEWID()))*(10000-1000)+1000)
vaindil

20

Odpowiadając na stare pytanie, ale ta odpowiedź nie została wcześniej podana, i mam nadzieję, że przyda się to komuś, kto znajdzie te wyniki za pośrednictwem wyszukiwarki.

W SQL Server 2008 wprowadzono nową funkcję CRYPT_GEN_RANDOM(8), która wykorzystuje CryptoAPI do stworzenia kryptograficznie silnej liczby losowej zwracanej jako VARBINARY(8000). Oto strona dokumentacji: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

Aby uzyskać losowy numer, możesz po prostu wywołać funkcję i przypisać ją do odpowiedniego typu:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

lub aby uzyskać floatod -1 do +1, możesz zrobić coś takiego:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0

13

Funkcja Rand () wygeneruje tę samą liczbę losową, jeśli zostanie użyta w zapytaniu SELECT tabeli. To samo dotyczy, jeśli użyjesz nasionka do funkcji Rand. Alternatywnym sposobem na to jest użycie tego:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

Mam tutaj informacje , które bardzo dobrze wyjaśniają problem.


5

Czy masz wartość całkowitą w każdym wierszu, którą możesz przekazać jako ziarno do funkcji RAND?

Aby uzyskać liczbę całkowitą od 1 do 14, myślę, że to zadziała:

FLOOR( RAND(<yourseed>) * 14) + 1

Działa to w teorii, ale w praktyce okazało się, RAND(<seed>)że nie wydaje się to zbyt przypadkowe w przypadku drobnych zmian w <seed>. Na przykład zrobiłem szybki test: pozwoliłem <seed>być 184380, 184383, 184386, a odpowiednie RAND(<seed>)wartości to: 0,14912, 0,14917, 0,14923.
ImaginaryHuman072889

Być może, aby uzyskać więcej „pozornie” losowych wyników, spróbuj czegoś takiego:RAND(<seed>)*100000) - FLOOR(RAND(<seed>)*100000)
ImaginaryHuman072889,

5

Jeśli chcesz zachować ziarno, aby za każdym razem generowało „te same” losowe dane, możesz wykonać następujące czynności:

1. Utwórz widok, który zwraca select rand ()

if object_id('cr_sample_randView') is not null
begin
    drop view cr_sample_randView
end
go

create view cr_sample_randView
as
select rand() as random_number
go

2. Utwórz UDF, który wybiera wartość z widoku.

if object_id('cr_sample_fnPerRowRand') is not null
begin
    drop function cr_sample_fnPerRowRand
end
go

create function cr_sample_fnPerRowRand()
returns float
as
begin
    declare @returnValue float
    select @returnValue = random_number from cr_sample_randView
    return @returnValue
end
go

3. Przed wybraniem danych uruchom funkcję rand (), a następnie użyj UDF w instrukcji select.

select rand(200);   -- see the rand() function
with cte(id) as
(select row_number() over(order by object_id) from sys.all_objects)
select 
    id,
    dbo.cr_sample_fnPerRowRand()
from cte
where id <= 1000    -- limit the results to 1000 random numbers

4

spróbuj użyć wartości początkowej w RAND (seedInt). Funkcja RAND () będzie wykonywana tylko raz na instrukcję, dlatego za każdym razem widzisz tę samą liczbę.


Najprostsze! Choć wartości wydają się dużo bardziej rozproszone, używając cyfr od środka, że podobnie jak RIGHT(CONVERT(BIGINT, RAND(RecNo) * 1000000000000), 2) (Uwaga: Widzę RIGHTniejawnie przekonwertować BIGINTdo CHAR, ale być rygorystyczne, że masz inny CONVERTtam).
Doug_Ivison,

4

Jeśli nie potrzebujesz, aby była liczbą całkowitą, ale dowolnym losowym unikalnym identyfikatorem, możesz użyć newid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables

4

Dead link :( Jakieś kopie, które mogłyby zostać uwzględnione w odpowiedzi?
żartuje

Umieszcza RAND()widok, umieszcza SELECTwidok tego widoku w funkcji, a następnie wywołuje funkcję z dowolnego miejsca. Sprytny.
Doug_Ivison,

Opublikowałem rozwiązanie, które rozwiązuje problem dokładnie w taki sam sposób, jak w łączonym artykule, ale tutaj w tym blogu bezpośrednio jako odpowiedź pięć postów temu! Nikt nie nazwał mnie sprytną zazdrością twarz hehe
Mitselplik

4
select round(rand(checksum(newid()))*(10)+20,2)

Tutaj liczba losowa roundbędzie zawierać się między 20 a 30. da maksymalnie dwa miejsca po przecinku.

Jeśli chcesz liczb ujemnych, możesz to zrobić

select round(rand(checksum(newid()))*(10)-60,2)

Wtedy minimalna wartość wyniesie -60, a maksymalna wyniesie -50.


3

To tak proste jak:

DECLARE @rv FLOAT;
SELECT @rv = rand();

A to umieści losową liczbę od 0 do 99 w tabeli:

CREATE TABLE R
(
    Number int
)

DECLARE @rv FLOAT;
SELECT @rv = rand();

INSERT INTO dbo.R
(Number)
    values((@rv * 100));

SELECT * FROM R

2

Problem, który czasami mam z wybraną odpowiedzią, polega na tym, że dystrybucja nie zawsze jest równa. Jeśli potrzebujesz bardzo równomiernego rozkładu losowego 1 - 14 między wiele wierszy, możesz zrobić coś takiego (moja baza danych ma 511 tabel, więc to działa. Jeśli masz mniej wierszy niż przedział liczb losowych, to nie działa dobrze):

SELECT table_name, ntile(14) over(order by newId()) randomNumber 
FROM information_schema.tables

Ten rodzaj działa odwrotnie niż normalne losowe rozwiązania w tym sensie, że utrzymuje sekwencję liczb i losuje drugą kolumnę.

Pamiętaj, że mam 511 tabel w mojej bazie danych (co jest istotne tylko b / c wybieramy z schematu_informacji). Jeśli wezmę poprzednie zapytanie i umieszczę je w tabeli tymczasowej #X, a następnie uruchomię to zapytanie na wynikowych danych:

select randomNumber, count(*) ct from #X
group by randomNumber

Otrzymuję ten wynik, pokazując mi, że moja losowa liczba jest BARDZO równomiernie rozmieszczona w wielu wierszach:

wprowadź opis zdjęcia tutaj


2
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]

zawsze dla mnie pracował



1
    DROP VIEW IF EXISTS vwGetNewNumber;
    GO
    Create View vwGetNewNumber
    as
    Select CAST(RAND(CHECKSUM(NEWID())) * 62 as INT) + 1 as NextID,
    'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'as alpha_num;

    ---------------CTDE_GENERATE_PUBLIC_KEY -----------------
    DROP FUNCTION IF EXISTS CTDE_GENERATE_PUBLIC_KEY;  
    GO
    create function CTDE_GENERATE_PUBLIC_KEY()
    RETURNS NVARCHAR(32)
    AS 
    BEGIN
        DECLARE @private_key NVARCHAR(32);
        set @private_key = dbo.CTDE_GENERATE_32_BIT_KEY();
        return @private_key;
    END;
    go

---------------CTDE_GENERATE_32_BIT_KEY -----------------
DROP FUNCTION IF EXISTS CTDE_GENERATE_32_BIT_KEY;  
GO
CREATE function CTDE_GENERATE_32_BIT_KEY()
RETURNS NVARCHAR(32)
AS 
BEGIN
    DECLARE @public_key NVARCHAR(32);
    DECLARE @alpha_num NVARCHAR(62);
    DECLARE @start_index INT = 0;
    DECLARE @i INT = 0;
    select top 1 @alpha_num = alpha_num from vwGetNewNumber;
        WHILE @i < 32
        BEGIN
          select top 1 @start_index = NextID from vwGetNewNumber;
          set @public_key = concat (substring(@alpha_num,@start_index,1),@public_key);
          set @i = @i + 1;
        END;
    return @public_key;
END;
    select dbo.CTDE_GENERATE_PUBLIC_KEY() public_key;

przepraszam @arnt, jeśli nie wyjaśniłem dobrze,
ichak khoury

przepraszam @arnt, mamy tutaj dwie funkcje CTDE_GENERATE_32_BIT_KEY, które generują 32-bitowy klucz alfanumeryczny (można go rozszerzyć tak, aby był mniej więcej), a druga o nazwie CTDE_GENERATE_PUBLIC_KEY, która wywołuje pierwszą funkcję i zwraca 32-bitowy klucz publiczny lub możesz wrócić 16-bitowy klucz prywatny ... wystarczy wywołać wybierz dbo.CTDE_GENERATE_PUBLIC_KEY () jako klucz publiczny; logika polega na tym, że wybieramy jeden znak z listy znaków alfanumerycznych 32 razy i łączymy je razem, aby uzyskać losowy klucz alfanumeryczny. po badaniach.
ichak khoury

Miły. To wyjaśnienie sprawia, że ​​jest to znacznie lepsza odpowiedź. (Ktoś oznaczył go do usunięcia; głosowałem za pozostawieniem go otwartym i pozostawiłem ten komentarz dla ciebie.)
arnt

0

Spróbuj tego:

SELECT RAND(convert(varbinary, newid()))*(b-a)+a magic_number 

Gdzie ajest niższa liczba i bwyższa liczba


1
Czy możesz odpowiedzieć na pytanie w sposób bardziej przejrzysty?
Yunus Temurlenk

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.