303 votos

Seleccione n filas al azar de tabla de SQL Server

Tengo una tabla de SQL Server con cerca de 50.000 filas. Quiero seleccione alrededor de 5.000 de esas filas al azar. He pensado de una forma complicada, crear una tabla temporal con un "número aleatorio" columna, la copia de mi mesa en que, recorriendo la tabla temporal y la actualización de cada fila RAND(), y, a continuación, seleccionando de la tabla donde la columna de números aleatorios < 0.1. Estoy buscando una manera más simple de hacerlo, en una sola sentencia, si es posible.

En este artículo se sugieren el uso de la NEWID() función. Que parece prometedor, pero no puedo ver cómo me fiable puede seleccionar un porcentaje determinado de filas.

A nadie hacer esto antes? Alguna idea?

381voto

Ralph Shillington Puntos 8016
select top 10 percent * from [yourtable] order by newid()

En respuesta al comentario de "pura basura" sobre tablas de gran tamaño: puedes 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 fundamental de valores más el costo de la Unión, que en una gran mesa con una pequeño porcentaje de selección debe ser razonable.

79voto

Patrick Taylor Puntos 416

Según sus necesidades, TABLESAMPLE va a llegar casi al azar y un mejor rendimiento. este está disponible en MS SQL server 2005 y versiones posteriores.

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

En una mesa muy grande he probado

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 va a mejorar también en muestras más pequeñas en TABLESAMPLE mientras que no va con newid().

Por favor, tenga en cuenta que esto no es tan aleatorio como el newid() método, pero le dará una decente de muestreo.

Ver la página de MSDN.

38voto

Rob Boek Puntos 1253

newid()/orden de trabajo, pero va a ser muy caro para grandes conjuntos de resultados porque se tiene que generar un identificador para cada fila, y luego ordenar.

TABLESAMPLE() es buena desde el punto de vista del rendimiento, pero el agrupamiento de los resultados (todas las filas de una página será devuelto).

Para mejorar el rendimiento de la verdadera muestra aleatoria, la mejor manera es filtrar filas al azar. He encontrado el siguiente ejemplo de código en SQL Server Books Online el artículo Limitar los Conjuntos de Resultados Mediante el uso de TABLESAMPLE:

Si usted realmente desea una muestra aleatoria de cada una de las filas, modifique la consulta para filtrar filas al azar, en lugar de el uso de TABLESAMPLE. Por ejemplo, el consulta siguiente utiliza la función NEWID la función de devolución de aproximadamente un 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 suma de comprobación de expresión, de manera que NEWID() evalúa una vez por fila para lograr el muestreo en una fila por fila. La expresión CAST(suma de comprobación(NEWID(), SalesOrderID) y 0x7fffffff COMO float / CAST (0x7fffffff COMO int) se evalúa a un valor float aleatorio entre 0 y 1.

Cuando se ejecuta en una mesa 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 usted puede conseguir lejos con usar TABLESAMPLE, que le dará el mejor rendimiento. De lo contrario, utilice la función newid()/método de filtro. newid()/orden debe ser el último recurso, si usted tiene un gran conjunto de resultados.

23voto

Kyle McClellan Puntos 1741

Seleccionar filas al azar de una gran mesa en MSDN tiene una solución simple, bien articulada que aborda los problemas de rendimiento a gran escala.

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

8voto

Daniel Brückner Puntos 36242

Sólo ordenar la tabla por un número al azar y obtener las primeras 5.000 filas utilizando TOP .

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

ACTUALIZACIÓN

Acabo de intentar y una newid() llamada es suficiente - no hay necesidad de todos los moldes y todas las matemáticas.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X