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.
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é.
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.
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:
-
CAST()
: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql -
RAND()
: http://msdn.microsoft.com/en-us/library/ms177610.aspx -
CHECKSUM()
: http://msdn.microsoft.com/en-us/library/ms189788.aspx -
NEWID()
: https://docs.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql
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)
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
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.
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
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
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.
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
vous devez appeler RAND() pour chaque ligne. Voici un bon exemple
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.
sélectionner newid ()
ou éventuellement ce select binary_checksum (newid ())
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:
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]
a toujours travaillé pour moi
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;