¿Cómo puedo generar un número aleatorio para cada fila en un TSQL Select?
Necesito un número aleatorio diferente para cada fila en mi tabla. El siguiente código aparentemente obvio usa el mismo valor aleatorio para cada fila.
SELECT table_name, RAND() magic_number
FROM information_schema.tables
Me gustaría obtener un INT o un FLOTADOR de esto. El resto de la historia es que voy a usar este número aleatorio para crear un desplazamiento de fecha aleatorio a partir de una fecha conocida, por ejemplo, desplazamiento de 1-14 días a partir de una fecha de inicio.
Esto es para Microsoft SQL Server 2000.
16 answers
Eche un vistazo a SQL Server - Set based random numbers que tiene una explicación muy detallada.
En resumen, el siguiente código genera un número aleatorio entre 0 y 13 inclusive con una distribución normalizada:
ABS(CHECKSUM(NewId())) % 14
Para cambiar su rango, simplemente cambie el número al final de la expresión. Tenga mucho cuidado si necesita un rango que incluya números positivos y negativos. Si lo haces mal, es posible contar dos veces el número 0.
A pequeña advertencia para los tuercas matemáticas en la habitación: hay un sesgo muy leve en este código. CHECKSUM()
resulta en números que son uniformes en todo el rango del tipo de datos sql Int, o al menos tan cerca como mi prueba (el editor) puede mostrar. Sin embargo, habrá algún sesgo cuando CHECKSUM() produzca un número en el extremo superior de ese rango. Cada vez que obtenga un número entre el número entero máximo posible y el último múltiplo exacto del tamaño de su rango deseado (14 en este caso) antes de eso número entero máximo, esos resultados se favorecen sobre la porción restante de su rango que no se puede producir a partir de ese último múltiplo de 14.
Como ejemplo, imagine que todo el rango del tipo Int es solo 19. 19 es el entero más grande posible que puede contener. Cuando CHECKSUM () resulta en 14-19, estos corresponden a los resultados 0-5. Esos números serían fuertemente favorecidos sobre 6-13, porque CHECKSUM() es el doble de probable que los genere. Es más fácil demostrar esto visualmente. A continuación se muestra todo el conjunto posible de resultados para nuestro rango entero imaginario:
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
Aquí se puede ver que hay más posibilidades de producir algunos números que otros: sesgo. Afortunadamente, el rango real del tipo Int es mucho mayor... tanto es así que en la mayoría de los casos el sesgo es casi indetectable. Sin embargo, es algo que debe tener en cuenta si alguna vez se encuentra haciendo esto para un código de seguridad serio.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-25 13:46:13
Cuando se llama varias veces en un solo lote, rand() devuelve el mismo número.
Yo sugeriría usar convert(varbinary
,newid()
) como argumento semilla:
SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number
FROM information_schema.tables
newid()
se garantiza que devuelve un valor diferente cada vez que se llama, incluso dentro del mismo lote, por lo que usarlo como semilla le pedirá a rand() que dé un valor diferente cada vez.
Editado para obtener un número entero aleatorio del 1 al 14.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-19 07:36:42
RAND(CHECKSUM(NEWID()))
Lo anterior generará un (pseudo) número aleatorio entre 0 y 1, exclusivo. Si se usa en una selección, debido a que el valor de semilla cambia para cada fila, generará un nuevo número aleatorio para cada fila (sin embargo, no se garantiza que genere un número único por fila).
Ejemplo cuando se combina con un límite superior de 10 (produce números 1-10):
CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1
Documentación de Transact-SQL:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-13 09:13:56
Generación de números aleatorios entre 1000 y 9999 inclusive:
FLOOR(RAND(CHECKSUM(NEWID()))*(9999-1000+1)+1000)
"+1" - para incluir valores límite superiores (9999 para el ejemplo anterior)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-07-24 20:10:09
Respondiendo a la vieja pregunta, pero esta respuesta no se ha proporcionado previamente, y esperamos que esto sea útil para alguien que encuentre estos resultados a través de un motor de búsqueda.
Con SQL Server 2008, se ha introducido una nueva función, CRYPT_GEN_RANDOM(8)
, que utiliza CryptoAPI para producir un número aleatorio criptográficamente fuerte, devuelto como VARBINARY(8000)
. Aquí está la página de documentación: https://docs.microsoft.com/en-us/sql/t-sql/functions/crypt-gen-random-transact-sql
Así que para obtener una número aleatorio, simplemente puede llamar a la función y enviarla al tipo necesario:
select CAST(CRYPT_GEN_RANDOM(8) AS bigint)
O para obtener un float
entre -1 y +1, podrías hacer algo como esto:
select CAST(CRYPT_GEN_RANDOM(8) AS bigint) % 1000000000 / 1000000000.0
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-14 20:13:55
La función Rand() generará el mismo número aleatorio, si se usa en una consulta de selección de tabla. Lo mismo se aplica si se utiliza una semilla para la función Rand. Una forma alternativa de hacerlo, es usar esto:
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]
Obtuvo la información de aquí, lo que explica muy bien el problema.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-07-23 20:15:29
¿Tiene un valor entero en cada fila que podría pasar como semilla a la función RAND?
Para obtener un entero entre 1 y 14 creo que esto funcionaría:
FLOOR( RAND(<yourseed>) * 14) + 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-06-25 17:38:41
Si necesita conservar su semilla para que genere los "mismos" datos aleatorios cada vez, puede hacer lo siguiente:
1. Crear una vista que devuelve 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. Cree un UDF que seleccione el valor de la vista.
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. Antes de seleccionar los datos, seed la función rand() y, a continuación, utilice el UDF en la instrucción 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-11 17:15:34
Intente usar un valor de semilla en el RAND(seedInt). RAND () solo se ejecutará una vez por sentencia, es por eso que ves el mismo número cada vez.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-06-25 17:22:42
Si no necesita que sea un entero, sino cualquier identificador único aleatorio, puede usar newid()
SELECT table_name, newid() magic_number
FROM information_schema.tables
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-06-25 17:22:42
Tendría que llamar a RAND() para cada fila. He aquí un buen ejemplo
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-11-11 13:47:00
select round(rand(checksum(newid()))*(10)+20,2)
Aquí el número aleatorio vendrá entre 20 y 30.
round
dará dos decimales como máximo.
Si quieres números negativos puedes hacerlo con
select round(rand(checksum(newid()))*(10)-60,2)
Entonces el valor mínimo será -60 y máximo será -50.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-07 11:25:06
Seleccione newid()
O posiblemente esto seleccione binary_checksum (newid ())
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-06-25 17:22:23
El problema que a veces tengo con la "Respuesta" seleccionada es que la distribución no siempre es uniforme. Si necesita una distribución muy uniforme de random 1 - 14 entre muchas filas, puede hacer algo como esto (mi base de datos tiene 511 tablas, por lo que esto funciona. Si tiene menos filas que el número aleatorio, esto no funciona bien):
SELECT table_name, ntile(14) over(order by newId()) randomNumber
FROM information_schema.tables
Este tipo de hace lo contrario de las soluciones aleatorias normales en el sentido de que mantiene los números secuenciados y aleatoriza el otro columna.
Recuerde, tengo 511 tablas en mi base de datos (que es pertinente solo b/c que estamos seleccionando de la information_schema). Si tomo la consulta anterior y la pongo en una tabla temporal #X, y luego corro esta consulta sobre los datos resultantes:
select randomNumber, count(*) ct from #X
group by randomNumber
Obtengo este resultado, mostrándome que mi número aleatorio está MUY uniformemente distribuido entre las muchas filas:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-04-04 02:17:14
select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) as [Randomizer]
Siempre me ha funcionado
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-06-28 07:14:38