sql - español - powerapps ejemplos

¿Cómo solicitar una fila aleatoria en SQL?

Question

¿Cómo puedo solicitar una fila aleatoria (o lo más cerca posible de lo aleatorio posible) en SQL puro?

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

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

Al combinar con un LIMIT, he encontrado que se desempeña mucho mejor (al menos con PostgreSQL 9.1) para UNIR con un orden aleatorio en lugar de ordenar directamente las filas reales: por ejemplo

SELECT * FROM tbl_post AS t JOIN ... JOIN ( SELECT id, CAST(-2147483648 * RANDOM() AS integer) AS rand FROM tbl_post WHERE create_time >= 1349928000 ) r ON r.id = t.id WHERE create_time >= 1349928000 AND ... ORDER BY r.rand LIMIT 100

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

El CAST como Integer es especialmente útil para PostgreSQL 9.2, que tiene una optimización de clasificación específica para los tipos flotantes de precisión simple y entera.

Answer 3

Con SQL Server 2012+ puede usar la consulta OFFSET FETCH 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, calculada como un número aleatorio entre 0 y count () - 1 de la tabla (el desplazamiento 0 es la primera fila después de todo)

Esto funciona con agujeros en los datos de la tabla, siempre que tenga un índice con el que trabajar para la cláusula ORDER BY. También es muy bueno para la aleatoriedad: mientras trabajas para que puedas pasar, pero no están presentes los inconvenientes de otros métodos. 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.

Answer 4

Consulte esta publicación: SQL para seleccionar una fila aleatoria de una tabla de base de datos . Pasa a través de métodos para hacer esto en MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 y Oracle (lo siguiente se copia de ese enlace):

Seleccione una fila aleatoria con MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Seleccione una fila aleatoria con PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Seleccione una fila aleatoria con Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Seleccione una fila aleatoria con IBM DB2

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Seleccione un registro aleatorio con Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Answer 5

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

Answer 6

En SQL Server puede combinar TABLESAMPLE con NEWID () para obtener una buena aleatoriedad y tener 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()

Answer 7

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

Otro enfoque es usar TOP dos veces, con órdenes alternas. No sé si es "SQL puro", porque usa una variable en el TOP, pero funciona en SQL Server 2008. Este es 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 oscila entre 1 y COUNT (*) en la tabla de destino, inclusive. Si su columna está indexada, también se beneficiará de ella. Otra ventaja es que puedes usarlo en una función, ya que NEWID () está deshabilitado.

Por último, la consulta anterior se ejecuta en aproximadamente 1/10 de la hora ejecutiva de un tipo de consulta NEWID () en la misma tabla. YYMV.

Answer 8

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

También utilizo la técnica "num_value> = RAND () * ...", y si realmente quiero tener resultados aleatorios, tengo una columna especial "aleatoria" en la tabla que actualizo una vez al día. Esa única ejecución de ACTUALIZACIÓN llevará tiempo (especialmente porque tendrá 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.

Answer 9

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

Use esto en su lugar:

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

Answer 10

Instado de usar RAND (), ya que no se recomienda , simplemente puede obtener la ID máxima (= Max):

SELECT MAX(ID) FROM TABLE;

obtener un valor aleatorio 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á las filas cuyos Ids sean IGUALES o MÁS ALTOS que el valor elegido. También es posible buscar la fila hacia abajo en la tabla y obtener una ID igual o inferior a My_Generated_Random, luego modificar la consulta de esta manera:

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

Answer 11

La función aleatoria del sql podría ayudar. Además, si desea limitarse a una sola fila, simplemente agregue eso al final.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Answer 12

La mayoría de las soluciones aquí apuntan a evitar la clasificación, pero aún necesitan realizar un escaneo secuencial sobre una tabla.

También hay una forma de evitar la exploración secuencial cambiando a la exploración de índice. Si conoce el valor de índice de su fila aleatoria, puede obtener el resultado casi instantáneamente. 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;

Por encima de la solución, adivina 10 diversos valores de índice aleatorio de rango 0 .. [último 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 identificaciones dispersas , podría pasar por alto . La solución es tener un plan de copia de seguridad :) En este caso, una orden antigua por consulta aleatoria (). Cuando la identificación combinada 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 de la cláusula ALL . En este caso, si la primera parte devuelve algún dato, ¡la segunda NUNCA se ejecuta!

Answer 13

La mejor manera es poner un valor aleatorio en una nueva columna solo para ese propósito, y usar algo como esto (pseude código + 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 en contra de valores más pequeños, pero encontraron que era suficiente para ajustar el valor aleatorio alrededor de cero cuando no se recuperan filas.

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

Es posible que la solución rand () no funcione en absoluto (es decir, con MSSQL) porque la función se evaluará solo una vez, y a cada fila se le asignará el mismo número "aleatorio".

Answer 14

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 TABLESAMPLE posteriores, puede usar TABLESAMPLE para obtener una muestra aleatoria que sea repetible:

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

Answer 15

No sé cuán 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 ordenamiento significa que se obtiene una fila aleatoria.

Answer 16

Para Firebird:

Select FIRST 1 column from table ORDER BY RAND()

Answer 17

Para SQL Server 2005 y 2008, si queremos una muestra aleatoria de filas individuales (de msdn.microsoft.com/en-us/library/ms189108.aspx ):

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

Answer 18

Para SQL Server 2005 y versiones posteriores, se extiende la respuesta de @ GreyPanther para los casos en que num_value no tiene valores continuos. Esto también funciona para los casos en los que no hemos distribuido uniformemente los conjuntos de datos 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)
)

Answer 19

Para SQL Server

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 msdn.microsoft.com/en-us/library/ms189108.aspx SQL Server msdn.microsoft.com/en-us/library/ms189108.aspx :

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 20

Para que MySQL obtenga un 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/

Answer 21

Parece que muchas de las ideas enumeradas todavía usan orden

Sin embargo, si usa una tabla temporal, puede asignar un índice aleatorio (como han sugerido muchas de las soluciones) 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

Answer 22

Si es posible, use declaraciones 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;

Answer 23

Soluciones como Jeremies:

SELECT * FROM table ORDER BY RAND() LIMIT 1

funcionan, pero necesitan un escaneo secuencial de toda la tabla (porque el valor aleatorio asociado con cada fila debe calcularse, de modo que se pueda determinar el más pequeño), que puede ser bastante lento incluso para tablas de tamaño medio. Mi recomendación sería usar algún tipo de columna numérica indexada (muchas tablas tienen estas como sus claves principales), 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 del tamaño de la tabla, si num_value está indexado. Una advertencia: esto supone 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).

Answer 24

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

Simplemente escriba su consulta y use order by new id() function. Es bastante aleatorio.

Answer 25

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

Consulte: http://www.mssqltips.com/tip.asp?tip=1308

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

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

Answer 26

Una forma 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;

Answer 27

 SELECT * FROM table ORDER BY RAND() LIMIT 1

Answer 28

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 este último método.