values - sql server random select query




Seleccione n filas aleatorias de la tabla de SQL Server (10)

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?


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.


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 .


En MySQL puedes hacer esto:

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

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())

Esto funciona para mí:

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

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


Prueba esto:

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

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)


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.








random