¿Cómo solicitar una fila aleatoria en SQL?

Soluciones como Jeremies:

SELECT * FROM table ORDER BY RAND() LIMIT 1

Funciona, pero necesitan un escaneo secuencial de toda la tabla (porque el valor aleatorio asociado con cada fila debe calcularse - para que se pueda determinar la más pequeña), que puede ser bastante lento incluso para tablas de tamaño mediano. Mi recomendación sería usar algún tipo de columna numérica indexada (muchas tablas tienen estas como sus claves primarias), y luego escribir algo como:

SELECT * FROM table WHERE num_value >= RAND() * 
    ( SELECT MAX (num_value ) FROM table ) 
ORDER BY num_value LIMIT 1

Esto funciona en tiempo logarítmico, independientemente de la tamaño de la tabla, si num_value está indexado. Una advertencia: esto asume que num_value se distribuye equitativamente en el rango 0..MAX(num_value). Si su conjunto de datos se desvía fuertemente de esta suposición, obtendrá resultados sesgados (algunas filas aparecerán con más frecuencia que otras).

No se que tan eficiente es esto, pero lo he usado antes:

SELECT TOP 1 * FROM MyTable ORDER BY newid()

Debido a que los GUID son bastante aleatorios, el orden significa que obtienes una fila aleatoria.

ORDER BY NEWID()

Toma 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

Toma 0.0065 milliseconds!

Definitivamente voy a ir con el último método.

No dijiste qué servidor estás usando. En versiones anteriores de SQL Server, puede usar esto:

select top 1 * from mytable order by newid()

En SQL Server 2005 y posteriores, puede usar TABLESAMPLE para obtener una muestra aleatoria que sea repetible:

SELECT FirstName, LastName
FROM Contact 
TABLESAMPLE (1 ROWS) ;

Para SQL Server

Newid()/order by funcionará, pero será muy caro para conjuntos de resultados grandes porque tiene que generar un id para cada fila, y luego ordenarlas.

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

Para un mejor rendimiento de la muestra aleatoria verdadera, la mejor manera es filtrar las filas al azar. Encontré el siguiente ejemplo de código en el artículo de SQL Server Books Online Limitar los Conjuntos de Resultados mediante el Uso de TABLAS:

Si realmente quieres una muestra aleatoria de filas individuales, modifique su consulta a filtrar filas aleatoriamente, en lugar de usando TABLES-MUESTRA. Por ejemplo, el la siguiente consulta utiliza el NEWID función para devolver aproximadamente uno porcentaje de las filas de la Venta.Tabla del detalle del pedido:

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úa una vez por fila a lograr un muestreo por fila. La expresión CAST(CHECKSUM (NEWID(), SalesOrderID) & 0x7fffffff COMO flotador / CAST (0x7fffffff COMO int) evalúa a un valor flotante aleatorio entre 0 y 1.

Cuando se ejecuta contra una tabla con 1.000.000 de filas, estos son 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 la suya usando TABLES-MUESTRA, le dará el mejor rendimiento. De lo contrario, utilice el método newid()/filter. newid () / order by debería ser el último recurso si tienes un resultado grande establecer.

Si es posible, use instrucciones almacenadas para evitar la ineficiencia de ambos índices en RND() y crear un campo de número de registro.

PREPARE RandomRecord FROM "SELECT * FROM table LIMIT ?,1";
SET @n=FLOOR(RAND()*(SELECT COUNT(*) FROM table));
EXECUTE RandomRecord USING @n;

La mejor manera es poner un valor aleatorio en una nueva columna solo para ese propósito, y usar algo como esto (código de pseudónimo + SQL):

randomNo = random()
execSql("SELECT TOP 1 * FROM MyTable WHERE MyTable.Randomness > $randomNo")

Esta es la solución empleada por el código MediaWiki. Por supuesto, hay cierto sesgo contra valores más pequeños, pero encontraron que era suficiente envolver el valor aleatorio alrededor de cero cuando no se obtienen filas.

La solución Newid() puede requerir un análisis completo de la tabla para que a cada fila se le pueda asignar un nuevo guid, que será mucho menos performant.

La solución Rand() puede no funcionar en absoluto (es decir, con MSSQL) porque la función se evaluará una sola vez, y a cada fila se le asignará el mismo número "aleatorio".

Para SQL Server 2005 y 2008, si queremos una muestra aleatoria de filas individuales (de Libros en línea):

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

Insted de usando RAND(), como no se recomienda, simplemente puede obtener max ID (=Max):

SELECT MAX(ID) FROM TABLE;

Obtener un azar entre 1..Max (=My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

Y luego ejecute este SQL:

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

Tenga en cuenta que comprobará si hay filas cuyos Id son IGUALES o SUPERIORES al valor elegido. También es posible buscar la fila hacia abajo en la tabla, y obtener un ID igual o menor que el My_Generated_Random, luego modificar la consulta de la siguiente manera:

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

Como se señala en el comentario de @BillKarwin sobre la respuesta de @cnu...

Cuando se combina con un LÍMITE, he encontrado que funciona mucho mejor (al menos con PostgreSQL 9.1) para UNIRSE con un orden aleatorio en lugar de ordenar directamente las filas reales: e. g. {[0]}

Solo asegúrese de que la 'r' genera un valor 'rand' para cada valor de clave posible en la consulta compleja que se une a ella, pero aún limite el número de filas de 'r' cuando sea posible.

El FUNDIDO como Entero es especialmente útil para PostgreSQL 9.2 que tiene optimización de ordenación específica para tipos flotantes enteros y de precisión única.

La mayoría de las soluciones aquí tienen como objetivo evitar la clasificación, pero todavía necesitan hacer un escaneo secuencial sobre una tabla.

También hay una manera de evitar el escaneo secuencial cambiando a index scan. Si conoce el valor del índice de su fila aleatoria, puede obtener el resultado casi en una instancia. El problema es cómo adivinar un valor de índice.

La siguiente solución funciona en PostgreSQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

I solución anterior adivina 10 varios valores de índice aleatorio de rango 0 .. [Última valor de id].

El número 10 es arbitrario: puede usar 100 o 1000, ya que (sorprendentemente) no tiene un gran impacto en el tiempo de respuesta.

También hay un problema: si tiene identificadores dispersos puede que se pierda. La solución es tener un plan de copia de seguridad:) En este caso una consulta de orden antiguo puro por random (). Cuando el id combinado se ve así:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

No la unión ALL clause. En este caso, si la primera parte devuelve cualquier dato, ¡el segundo nunca es ejecutado!

En los últimos tiempos, pero llegó aquí a través de Google, así que por el bien de la posteridad, voy a añadir una solución alternativa.

Otro enfoque es usar TOP dos veces, con órdenes alternas. No se si es" SQL puro", porque usa una variable en la PARTE SUPERIOR, pero funciona en SQL Server 2008. Aquí hay un ejemplo que uso contra una tabla de palabras del diccionario, si quiero una palabra aleatoria.

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

Por supuesto, @idx es un entero generado aleatoriamente que varía de 1 a CONTAR (*) en la tabla de destino, inclusivamente. Si tu columna está indexada, también te beneficiarás de ella. Otra ventaja es que puede usarlo en una función, ya que NEWID () no está permitido.

Por último, la consulta anterior se ejecuta en aproximadamente 1/10 del tiempo de ejecución de un tipo de consulta NEWID () en la misma tabla. YYMV.

También puede intentar usar la función new id().

Simplemente escriba su consulta y use la función order by new id(). Es bastante aleatorio.

Para MySQL para obtener registro aleatorio

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

Más detalles http://jan.kneschke.de/projects/mysql/order-by-rand /

Todavía no he visto esta variación en las respuestas. Tenía una restricción adicional en la que necesitaba, dada una semilla inicial, 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

NewId () ejemplo:

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 desee usarlo contra grandes conjuntos de registros.

Selección con Inicial Semilla:

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 necesita seleccionar el mismo conjunto dado una semilla, esto parece funcionar.

En MSSQL (probado en 11.0.5569) usando

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

Es significativamente más rápido que

SELECT TOP 100 * FROM employee ORDER BY NEWID()

En SQL Server puedes combinar TABLESAMPLE con NEWID() para obtener una buena aleatoriedad y seguir teniendo velocidad. Esto es especialmente útil si realmente solo desea 1, o un pequeño número, de filas.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

 SELECT * FROM table ORDER BY RAND() LIMIT 1

Estoy de acuerdo con CD-MaN: Usar "ORDER BY RAND()" funcionará muy bien para tablas pequeñas o cuando haga su SELECCIÓN solo unas pocas veces.

También uso "num_value >= RAND() * ..."técnica, y si realmente quiero tener resultados aleatorios tengo una columna especial "aleatoria" en la tabla que actualizo una vez al día más o menos. Esa única actualización llevará algún tiempo (especialmente porque tendrás que tener un índice en esa columna), pero es mucho más rápido que crear números aleatorios para cada fila cada vez que se ejecuta la selección.

Tenga cuidado porque TableSample realmente no devuelve una muestra aleatoria de filas. Dirige su consulta a mirar una muestra aleatoria de las páginas de 8 KB que componen su fila. Luego, su consulta se ejecuta contra los datos contenidos en estas páginas. Debido a cómo se pueden agrupar los datos en estas páginas (orden de inserción, etc.), esto podría conducir a datos que en realidad no son una muestra aleatoria.

Véase: http://www.mssqltips.com/tip.asp?tip=1308

Esta página de MSDN para tablas muestra incluye un ejemplo de cómo generar una muestra aleatoria de datos.

Http://msdn.microsoft.com/en-us/library/ms189108.aspx

Parece que muchas de las ideas enumeradas todavía utilizan el orden

Sin embargo, si usa una tabla temporal, puede asignar un índice aleatorio (como muchas de las soluciones han sugerido), y luego tomar el primero que sea mayor que un número arbitrario entre 0 y 1.

Por ejemplo (para DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

Una manera simple y eficiente de http://akinas.com/pages/en/blog/mysql_random_row /

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

Hay una mejor solución para Oracle en lugar de usar dbms_random.valor, mientras que requiere análisis completo para ordenar filas por dbms_random.valor y es bastante lento para tablas grandes.

Usa esto en su lugar:

SELECT *
FROM employee sample(1)
WHERE rownum=1

Para Firebird:

Select FIRST 1 column from table ORDER BY RAND()

Con SQL Server 2012 + puede usar la consulta de BÚSQUEDA DE DESPLAZAMIENTO para hacer esto para una sola fila aleatoria

select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

Donde id es una columna de identidad, y n es la fila que desea-calculado como un número aleatorio entre 0 y count()-1 de la tabla (offset 0 es la primera fila después de todo)

Esto funciona con agujeros en los datos de la tabla, siempre y cuando tenga un índice con el que trabajar para la cláusula ORDER BY. También es muy bueno para la aleatoriedad, ya que trabajas para pasar, pero los problemas en otros métodos no están presentes. Además, el rendimiento es bastante bueno, en un conjunto de datos más pequeño se mantiene bien, aunque no he probado pruebas de rendimiento serias contra varios millones de filas.

Para SQL Server 2005 y superiores, extendiendo la respuesta de @GreyPanther para los casos en los que num_value no tiene valores continuos. Esto también funciona para los casos en que no tenemos conjuntos de datos distribuidos uniformemente y cuando num_value no es un número sino un identificador único.

WITH CTE_Table (SelRow, num_value) 
AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM table
) 

SELECT * FROM table Where num_value = ( 
    SELECT TOP 1 num_value FROM CTE_Table  WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)

La función aleatoria del sql podría ayudar. También si desea limitar a una sola fila, simplemente agregue eso al final.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1