query - sql server random number between 1 and 10

Seleccione n filas aleatorias de la tabla de SQL Server

Question

Tengo una tabla de SQL Server con aproximadamente 50,000 filas en ella. Quiero seleccionar alrededor de 5,000 de esas filas al azar. He pensado en una forma complicada, creando una tabla temporal con una columna de "número aleatorio", copiando mi tabla en eso, repasando la tabla temporal y actualizando cada fila con RAND() , y luego seleccionando de esa tabla donde está el azar. columna numérica <0.1. Estoy buscando una forma más sencilla de hacerlo, en una sola declaración si es posible.

Este artículo sugiere usar la función NEWID() . Parece prometedor, pero no veo cómo podría seleccionar de manera confiable un determinado porcentaje de filas.

¿Alguien ha hecho esto antes? ¿Algunas ideas?

Answer 1

Aún no he visto esta variación en las respuestas. Tenía una restricción adicional donde necesitaba, dada una inicialización inicial, para seleccionar el mismo conjunto de filas cada vez.

Para MS SQL:

Ejemplo mínimo:

select top 10 percent *
from table_name
order by rand(checksum(*))

Tiempo de ejecución normalizado: 1.00

Ejemplo de NewId ():

select top 10 percent *
from table_name
order by newid()

Tiempo de ejecución normalizado: 1.02.

NewId() es insignificantemente más lento que rand(checksum(*)) , por lo que es posible que no quieras usarlo contra grandes conjuntos de registros.

Selección con semilla inicial:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % @seed) /* any other math function here */

Si necesitas seleccionar el mismo conjunto dado una semilla, esto parece funcionar.

Answer 2

Dependiendo de sus necesidades, TABLESAMPLE le proporcionará un rendimiento casi tan aleatorio como mejor. Esto está disponible en MS SQL Server 2005 y versiones posteriores.

TABLESAMPLE devolverá datos de páginas aleatorias en lugar de filas aleatorias y, por lo tanto, los deos ni siquiera recuperarán los datos que no devolverá.

En una mesa muy grande probé

select top 1 percent * from [tablename] order by newid()

Tomó más de 20 minutos.

select * from [tablename] tablesample(1 percent)

Tomó 2 minutos.

El rendimiento también mejorará en muestras más pequeñas en TABLESAMPLE mientras que no lo hará con newid() .

Tenga en cuenta que esto no es tan aleatorio como el método newid() pero le dará una muestra decente.

Ver la página de MSDN .

Answer 3

El lenguaje de procesamiento del lado del servidor en uso (p. Ej., PHP, .net, etc.) no está especificado, pero si es PHP, tome el número requerido (o todos los registros) y, en lugar de la asignación aleatoria en la consulta, use la función aleatoria de PHP. No sé si .net tiene una función equivalente, pero si la tiene, úsela si está usando .net

ORDER BY RAND () puede tener una penalización de rendimiento considerable, dependiendo de la cantidad de registros involucrados.

Answer 4

En MySQL puedes hacer esto:

SELECT `PRIMARY_KEY`, rand() FROM table ORDER BY rand() LIMIT 5000;

Answer 5

Esta es una combinación de la idea inicial inicial y una suma de comprobación, que me parece dar resultados aleatorios sin el costo de NEWID ():

SELECT TOP [number] 
FROM table_name
ORDER BY RAND(CHECKSUM(*) * RAND())

Answer 6

Este enlace tiene una comparación interesante entre Orderby (NEWID ()) y otros métodos para tablas con 1, 7 y 13 millones de filas.

A menudo, cuando se hacen preguntas sobre cómo seleccionar filas aleatorias en grupos de discusión, se propone la consulta NEWID; Es simple y funciona muy bien para mesas pequeñas.

SELECT TOP 10 PERCENT *
  FROM Table1
  ORDER BY NEWID()

Sin embargo, la consulta NEWID tiene un gran inconveniente cuando la usa para tablas grandes. La cláusula ORDER BY hace que todas las filas de la tabla se copien en la base de datos tempdb, donde se ordenan. Esto causa dos problemas:

1. La operación de clasificación usualmente tiene un alto costo asociado. La clasificación puede usar una gran cantidad de E / S de disco y puede ejecutarse durante mucho tiempo.
2. En el peor de los casos, tempdb puede quedarse sin espacio. En el mejor de los casos, tempdb puede ocupar una gran cantidad de espacio en disco que nunca se recuperará sin un comando de reducción manual.

Lo que necesita es una forma de seleccionar filas al azar que no usen tempdb y no se volverán más lentas a medida que la tabla se haga más grande. Aquí hay una nueva idea sobre cómo hacer eso:

SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

La idea básica detrás de esta consulta es que queremos generar un número aleatorio entre 0 y 99 para cada fila en la tabla, y luego elegir todas aquellas filas cuyo número aleatorio sea menor que el valor del porcentaje especificado. En este ejemplo, queremos que aproximadamente el 10 por ciento de las filas se seleccionen al azar; por lo tanto, elegimos todas las filas cuyo número aleatorio es menor que 10.

Por favor lea el artículo completo en MSDN .

Answer 7

Esto funciona para mí:

SELECT * FROM table_name
ORDER BY RANDOM()
LIMIT [number]

Answer 8

Lo estaba usando en la subconsulta y me devolvió las mismas filas en la subconsulta

 SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

Luego resolví con la inclusión de la variable de la tabla principal en donde

SELECT  ID ,
            ( SELECT TOP 1
                        ImageURL
              FROM      SubTable 
              Where Mytable.ID>0
              ORDER BY  NEWID()
            ) AS ImageURL,
            GETUTCDATE() ,
            1
    FROM    Mytable

Tenga en cuenta la condición de donde

Answer 9

Parece que newid () no se puede usar en la cláusula where, por lo que esta solución requiere una consulta interna:

SELECT *
FROM (
    SELECT *, ABS(CHECKSUM(NEWID())) AS Rnd
    FROM MyTable
) vw
WHERE Rnd % 100 < 10        --10%

Answer 10

Prueba esto:

SELECT TOP 10 Field1, ..., FieldN
FROM Table1
ORDER BY NEWID()

Answer 11

Si usted (a diferencia del OP) necesita un número específico de registros (lo que dificulta el enfoque CHECKSUM) y desea una muestra más aleatoria de la que TABLESAMPLE proporciona por sí solo, y también desea una mejor velocidad que CHECKSUM, puede conformarse con una fusión de Métodos TABLESAMPLE y NEWID (), como este:

DECLARE @sampleCount int = 50
SET STATISTICS TIME ON

SELECT TOP (@sampleCount) * 
FROM [yourtable] TABLESAMPLE(10 PERCENT)
ORDER BY NEWID()

SET STATISTICS TIME OFF

En mi caso, este es el compromiso más sencillo entre la aleatoriedad (no es realmente, lo sé) y la velocidad. Varíe el porcentaje de TABLESAMPLE (o filas) según corresponda: cuanto mayor sea el porcentaje, más aleatoria será la muestra, pero espere una caída lineal de la velocidad. (Tenga en cuenta que TABLESAMPLE no aceptará una variable)

Answer 12

Simplemente ordene la tabla por un número aleatorio y obtenga las primeras 5,000 filas usando TOP .

SELECT TOP 5000 * FROM [Table] ORDER BY newid();

ACTUALIZAR

Solo lo probé y una llamada newid() es suficiente, sin necesidad de todos los lanzamientos y todos los cálculos.

Answer 13

newid () / order by funcionará, pero será muy costoso para conjuntos de resultados grandes porque tiene que generar un ID para cada fila y luego ordenarlos.

TABLESAMPLE () es bueno desde el punto de vista del rendimiento, pero obtendrá una acumulación de resultados (se devolverán todas las filas de una página).

Para obtener una muestra aleatoria verdadera de mejor rendimiento, la mejor manera es filtrar filas aleatoriamente. Encontré el siguiente ejemplo de código en el artículo de los Libros en pantalla de SQL Server Limitar los conjuntos de resultados utilizando TABLESAMPLE :

Si realmente desea una muestra aleatoria de filas individuales, modifique su consulta para filtrar filas aleatoriamente, en lugar de usar TABLESAMPLE. Por ejemplo, la siguiente consulta utiliza la función NEWID para devolver aproximadamente el uno por ciento de las filas de la tabla Sales.SalesOrderDetail:

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(),SalesOrderID) & 0x7fffffff AS float)
              / CAST (0x7fffffff AS int)

La columna SalesOrderID se incluye en la expresión CHECKSUM para que NEWID () evalúe una vez por fila para lograr el muestreo por fila. La expresión CAST (CHECKSUM (NEWID (), SalesOrderID) y 0x7fffffff AS float / CAST (0x7fffffff AS int) se evalúa como un valor flotante aleatorio entre 0 y 1.

Cuando se ejecuta en una tabla con 1,000,000 de filas, aquí están mis resultados:

SET STATISTICS TIME ON
SET STATISTICS IO ON

/* newid()
   rows returned: 10000
   logical reads: 3359
   CPU time: 3312 ms
   elapsed time = 3359 ms
*/
SELECT TOP 1 PERCENT Number
FROM Numbers
ORDER BY newid()

/* TABLESAMPLE
   rows returned: 9269 (varies)
   logical reads: 32
   CPU time: 0 ms
   elapsed time: 5 ms
*/
SELECT Number
FROM Numbers
TABLESAMPLE (1 PERCENT)

/* Filter
   rows returned: 9994 (varies)
   logical reads: 3359
   CPU time: 641 ms
   elapsed time: 627 ms
*/    
SELECT Number
FROM Numbers
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), Number) & 0x7fffffff AS float) 
              / CAST (0x7fffffff AS int)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Si puede salirse con el uso de TABLESAMPLE, le dará el mejor rendimiento. De lo contrario, utilice el método newid () / filter. newid () / order by debe ser el último recurso si tiene un gran conjunto de resultados.

Answer 14

La selección aleatoria de filas de una tabla grande en MSDN tiene una solución simple y bien articulada que aborda los problemas de rendimiento a gran escala.

  SELECT * FROM Table1
  WHERE (ABS(CAST(
  (BINARY_CHECKSUM(*) *
  RAND()) as int)) % 100) < 10

Answer 15

select top 10 percent * from [yourtable] order by newid()

En respuesta al comentario de "basura pura" sobre tablas grandes: podría hacerlo así para mejorar el rendimiento.

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())

El costo de esto será el análisis clave de los valores más el costo de la unión, que en una tabla grande con una pequeña selección de porcentaje debe ser razonable.