Comment puis-je générer un nombre aléatoire pour chaque ligne dans un TSQL Select?

j'ai besoin d'un nombre aléatoire différent pour chaque ligne de mon tableau. La suite apparemment évidente de code utilise la même valeur aléatoire pour chaque ligne.

SELECT table_name, RAND() magic_number 
FROM information_schema.tables 

j'aimerais avoir un INT ou un char. Le reste de l'histoire est que je vais utiliser ce nombre aléatoire pour créer une date aléatoire décalé à partir d'une date connue, par exemple 1-14 jours décalé à partir d'une date de début.

pour Microsoft SQL Server 2000.

259
demandé sur rjose 2009-06-25 21:17:35

16 réponses

regardez SQL Server - Set random numbers qui a une explication très détaillée.

pour résumer, le code suivant génère un nombre aléatoire entre 0 et 13 inclusivement avec une distribution normalisée:

ABS(CHECKSUM(NewId())) % 14

pour changer votre gamme, il suffit de changer le nombre à la fin de l'expression. Soyez extrêmement prudent si vous avez besoin d'une gamme qui inclut des nombres positifs et négatifs. Si vous le faites faux, il est possible de recompter le nombre 0.

un petit avertissement pour les écrous mathématiques dans la salle: il ya un très léger biais dans ce code. CHECKSUM() donne des nombres qui sont uniformes dans toute la gamme du type de données SQL Int, ou du moins aussi proches que mes tests (l'éditeur) peuvent le montrer. Cependant, il y aura un certain biais lorsque CHECKSUM() produira un nombre à l'extrémité supérieure de cette plage. Chaque fois que vous obtenez un nombre entre le nombre entier maximum possible et le dernier multiple exact de la taille de votre gamme désirée (14 dans ce cas) avant cet entier maximum, ces résultats sont favorisés sur la partie restante de votre gamme qui ne peut pas être produit à partir de ce dernier multiple de 14.

par exemple, imaginez que toute la gamme du type Int n'est que 19. 19 est le plus grand entier que vous pouvez tenir. Quand CHECKSUM () donne 14-19, cela correspond aux résultats 0-5. Ces nombres seraient lourdement favorisé sur 6-13, parce que CHECKSUM () est deux fois plus susceptible de les générer. C'est plus facile à démontrer visuellement. Ci-dessous se trouve l'ensemble des résultats possibles pour notre gamme entière imaginaire:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

vous pouvez voir ici qu'il y a plus de chances de produire certains nombres que d'autres: biais. Heureusement, la gamme réelle du type Int est beaucoup plus grand... à tel point que, dans la plupart des cas, le biais est presque indétectable. Cependant, il est quelque chose à savoir si vous vous retrouvez à faire ça pour un code de sécurité.

407
répondu SQLMenace 2018-01-25 13:46:13

Lorsqu'il est appelé plusieurs fois dans un même lot, rand() renvoie le même nombre.

je suggère d'utiliser convert ( varbinary , newid() ) comme argument de départ:

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

newid() est garanti pour retourner une valeur différente chaque fois qu'il est appelé, même dans le même lot, donc l'utiliser comme une graine incitera rand() à donner une valeur différente chaque fois.

édité pour obtenir un nombre entier aléatoire de 1 à 14.

82
répondu Jeremy Smyth 2017-05-19 07:36:42
RAND(CHECKSUM(NEWID()))

ce qui précède générera un nombre aléatoire (pseudo-) entre 0 et 1, exclusif. S'il est utilisé dans un select, parce que la valeur de graine change pour chaque ligne, il générera un nouveau nombre aléatoire pour chaque ligne (il n'est pas garanti de générer un nombre unique par ligne cependant).

exemple lorsqu'il est combiné avec une limite supérieure de 10 (produit les numéros 1 à 10):

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

Transact-SQL Documentation:

  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
58
répondu Aaron Hoffman 2017-10-13 09:13:56

la génération de nombre Aléatoire entre 1000 et 9999 inclusive:

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

"+1" - pour inclure les valeurs limites supérieures (9999 pour l'exemple précédent)

26
répondu Vova 2018-07-24 20:10:09

répondre à l'ancienne question, mais cette réponse n'a pas été fournie auparavant, et espérons que ce sera utile pour quelqu'un de trouver ces résultats à travers un moteur de recherche.

avec SQL Server 2008, une nouvelle fonction a été introduite, CRYPT_GEN_RANDOM(8) , qui utilise CryptoAPI pour produire un nombre aléatoire cryptographiquement fort, retourné comme VARBINARY(8000) . Voici la page de documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql

ainsi pour obtenir un nombre aléatoire, vous pouvez simplement appeler la fonction et la lancer au type nécessaire:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint)

ou pour obtenir un float entre -1 et +1, vous pouvez faire quelque chose comme ceci:

select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0
13
répondu Andrei Tanas 2017-06-14 20:13:55

la fonction Rand() générera le même nombre aléatoire, si elle est utilisée dans une requête table SELECT. Même si vous utilisez une graine à la fonction Rand. Une autre façon de le faire, est d'utiliser ceci:

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

a obtenu l'information de ici , ce qui explique le problème très bien.

11
répondu MicSim 2014-07-23 20:15:29

avez-vous une valeur entière dans chaque rangée que vous pourriez passer comme une graine à la fonction RAND?

pour obtenir un entier entre 1 et 14 je crois que cela fonctionnerait:

FLOOR( RAND(<yourseed>) * 14) + 1
5
répondu CoderDennis 2009-06-25 17:38:41

si vous avez besoin de préserver votre graine pour qu'elle génère les "mêmes" données aléatoires à chaque fois, vous pouvez faire ce qui suit:

1. Créer une vue qui retourne sélectionner 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. Créer un UDF qui sélectionne la valeur de la vue.

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. Avant de sélectionner vos données, lancez la fonction rand (), puis utilisez L'UDF dans votre select déclaration.

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
5
répondu Mitselplik 2015-05-11 17:15:34

essayez d'utiliser une valeur de graine dans le RAND(seedInt). RAND () n'exécutera qu'une fois par instruction, c'est pourquoi vous voyez le même nombre à chaque fois.

4
répondu northpole 2009-06-25 17:22:42

si vous n'avez pas besoin que ce soit un entier, mais n'importe quel identifiant aléatoire unique, vous pouvez utiliser newid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables
4
répondu Peter Cooper Jr. 2009-06-25 17:22:42
4
répondu David 2014-11-11 13:47:00
select round(rand(checksum(newid()))*(10)+20,2)

Ici, le nombre aléatoire viendra entre 20 et 30. round donnera deux décimales maximum.

Si vous voulez des nombres négatifs, vous pouvez le faire avec

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

alors la valeur min sera -60 et max sera -50.

3
répondu Tirthankar 2017-11-07 11:25:06

sélectionner newid ()

ou éventuellement ce select binary_checksum (newid ())

2
répondu Chris Klepeis 2009-06-25 17:22:23

le problème que j'ai parfois avec la" réponse " sélectionnée est que la distribution n'est pas toujours égale. Si vous avez besoin d'une distribution très égale de 1 à 14 au hasard parmi beaucoup de lignes, vous pouvez faire quelque chose comme ceci (ma base de données a 511 tables, donc cela fonctionne. Si vous avez moins de lignes que vous faites l'intervalle de nombre aléatoire, cela ne fonctionne pas bien):

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

ce genre de fait le contraire de solutions aléatoires normales dans le sens où il maintient les nombres séquencés et permet de rendre aléatoire l'autre colonne.

rappelez-vous, j'ai 511 tables dans ma base de données (qui est pertinente seulement b/C nous sélectionnons à partir de l'information_schema). Si je prends la requête précédente et la mets dans une table temp #X, et puis exécute cette requête sur les données résultantes:

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

j'obtiens ce résultat, me montrant que mon nombre aléatoire est distribué très uniformément parmi les nombreuses lignes:

enter image description here

2
répondu Trevor 2015-04-04 02:17:14
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]

a toujours travaillé pour moi

1
répondu bteague 2017-10-24 20:31:29
    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;
1
répondu ichak khoury 2018-06-28 07:14:38