¿Cómo puedo solicitar una fila al azar (o tan cerca de la verdad al azar como sea posible) en el más puro SQL?

InformationsquelleAutor sverrejoh | 2008-08-21

28 Comentarios

  1. 692

    Ver este post: SQL para Seleccionar una fila al azar a partir 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 (el siguiente es copia de este enlace):

    Seleccionar una fila al azar con MySQL:

    SELECT column FROM table
    ORDER BY RAND()
    LIMIT 1

    Seleccionar una fila al azar con PostgreSQL:

    SELECT column FROM table
    ORDER BY RANDOM()
    LIMIT 1

    Seleccionar una fila al azar con Microsoft SQL Server:

    SELECT TOP 1 column FROM table
    ORDER BY NEWID()

    Seleccionar una fila al azar 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
    • -1 por confiar en order by rand() o equivalentes en todos los dbs :|. también se menciona aquí.
    • Hace diez años, algún tipo dijo que el uso de ORDER BY RAND() está mal…
    • ORDEN POR NEWID() parece ser notablemente más lento en SQL Server. Mi consulta sería: select top 1000 C. CustomerId, CL.LoginName de Cliente C inner join LinkedAccount en LA C. CustomerId=LA.CustomerId inner join CustomerLogin CL en C. CustomerId=CL.CustomerId grupo C. CustomerId, CL.LoginName having count(*)>1 orden por NEWID() la Eliminación de la «orden por NEWID()» de la línea de la devolución de los resultados mucho más rápido.
    • Para SQLite uso ALEATORIO() función.
    • Estas soluciones no escala. Son O(n) con n siendo el número de registros de la tabla. Imagina que tienes 1 millón de registros, lo que realmente quieres para generar 1 millón de números aleatorios o identificadores únicos? Prefiero usar el COUNT() e implican que en un nuevo LIMIT expresión con un único número aleatorio.
  2. 173

    Soluciones como Jeremies:

    SELECT * FROM table ORDER BY RAND() LIMIT 1

    trabajo, pero se necesita un recorrido secuencial de todos los de la mesa (porque el valor aleatorio asociado con cada fila debe ser calculado para que el más pequeño puede ser determinado), lo cual puede ser bastante lenta como para que incluso medianas tablas. Mi recomendación sería utilizar algún tipo de indexado columna numérica (muchas tablas tienen estos como sus claves primarias) y, a continuación, 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, sin importar el tamaño de la tabla, si num_value está indexado. Una advertencia: esto supone que num_value se distribuye por igual en el rango de 0..MAX(num_value). Si el conjunto de datos se desvían mucho de este supuesto, usted va a obtener resultados sesgados (algunas filas aparecerán más a menudo que otros).

    • La segunda sugerencia es no aleatoria. Usted no puede predecir la fila que va a ser elegido, pero si tuviera que apostar, apostaría por la segunda fila. Y nunca apuesta en la última fila, es la menos probabilidades de ser escogido lo que es la distribución de su num_value y el tamaño de su tabla.
    • Sé que normalmente RAND() funciones no son de muy alta calidad, pero aparte de que puede que explique por qué la selección no aleatoria?
    • Yo también creo que va a ser tan aleatorio como RAND() puede ser.
    • U puede explicar lo que es un indexed numeric column? Es como la clave principal de incremento automático de 0 a la corriente de max?
    • La primera es INCORRECTO en SQL Server. La función RAND() se invoca sólo una vez por consulta no una vez por cada fila. Por lo que siempre selecciona la primera fila (probarlo).
    • El segundo también se supone que todas las filas se daba cuenta: es posible que se seleccione una fila que ha sido eliminado.
    • En realidad, num_value >= RAND() … el límite 1 garantiza que las filas vacías serán omitidos hasta que encuentra existentes fila.
    • Solución agradable. Hay también una manera eficiente para agarrar una muestra aleatoria de tamaño n en lugar de 1? (LIMIT n resultaría en entradas contiguas)
    • He usado mi clave principal como num_value, pero la ejecución de EXPLAIN en el que dice que todavía va a través de TODAS las filas…
    • La segunda solución se ve tan prometedor, pero no funcionó bien. En una mesa con más de 60.000 registros siempre recogido números bajos. Casi siempre por debajo de los 1000. No estoy seguro de por qué. Esto es lo que realmente funcionó y fue muy random: SELECT * FROM preguntas COMO t1 JOIN (SELECT RAND()*(SELECT MAX(id) DE preguntas) COMO max_id) COMO t2, DONDE t1.id >= t2.max_id ORDER BY id LÍMITE 1
    • Me fijo el caso cuando no se han distribuido equitativamente los valores en num_value. Usted puede ver la respuesta aquí

  3. 59

    No sé qué tan eficiente es este, pero yo he usado antes:

    SELECT TOP 1 * FROM MyTable ORDER BY newid()

    Porque Guid son bastante aleatorios, el orden significa que usted obtiene una fila al azar.

    • Estoy utilizando MS SQL server, SELECCIONE SUPERIOR 1 * DE some_table_name ORDEN POR NEWID() trabajó muy bien para mí, gracias por los consejos chicos!
    • Que es exactamente lo mismo que ORDER BY RAND() LIMIT 1
    • Esto también es muy específico de base de datos, ya que utiliza el TOP 1 y newid().
    • Esto es una mala idea. Este método no se utiliza un índice menos cada columna está indexada indivdually. Tabla con 100million registros podría tomar un tiempo muy largo para obtener un registro.
    • y ¿qué solución propones?
  4. 27
    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.

    • La segunda opción de no escoger la última fila. No sé por qué – sólo señalándolo.
    • devuelve un número de punto flotante de n donde 0 < n < 1. Suponiendo num_value es un número entero, el valor de retorno de rand() * max(num_value) también ser obligado a un número entero, así truncar cualquier cosa después del punto decimal. Por lo tanto, rand() * max(num_value) se siempre ser menos de max(num_value), que es la razón por la última fila nunca será seleccionado.
    • Yo no será eficiente si mis datos son eliminados a menudo – si encuentro un hueco, voy a tener que volver a ejecutar la consulta completa.
    • Pregunta estúpida, ¿entonces por qué no simplemente usar SELECT MAX(num_value) + 1 ?? Desde rand (o al AZAR en la mayoría de los casos) devuelve [0,1), se obtendrá el rango completo de valores. También, sí, tienes razón, tengo que arreglar una consulta.
  5. 13

    No decir que servidor que está usando. En versiones anteriores de SQL Server, puede utilizar este:

    select top 1 * from mytable order by newid()

    En SQL Server 2005 y, puede utilizar TABLESAMPLE para obtener una muestra al azar que es repetible:

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

    Para SQL Server

    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 ordenarlas.

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

    Para mejorar el rendimiento de la verdadera muestra aleatoria, la mejor manera es para filtrar filas al azar. He encontrado el siguiente ejemplo de código en los Libros de SQL Server en Línea artículo La limitación de Conjuntos de Resultados Mediante el uso de TABLESAMPLE:

    Si usted realmente desea una muestra aleatoria de
    cada una de las filas, modificar su consulta
    filtrar filas al azar, en lugar de
    el uso de TABLESAMPLE. Por ejemplo, la
    consulta siguiente utiliza la función NEWID
    la función de devolución de aproximadamente un
    por ciento de las filas de la
    De ventas.Tabla 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 la expresión, de modo 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) & 0x7fffffff COMO float /
    CAST (0x7fffffff COMO int) se evalúa a
    un valor float 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 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.

  7. 4

    Si es posible, utilice almacenados declaraciones para evitar la ineficiencia de ambos índices en RND() y la creación de un número de registro de campo.

    PREPARAR RandomRecord DE "SELECT * FROM tabla LÍMITE ?,1"; 
    SET @n=FLOOR(ALEATORIO()*(SELECT COUNT(*) FROM tabla)); 
    EJECUTAR RandomRecord CON @n; 
    
    • Esta solución también se encarga de devolver al azar filas cuando la indexado valor numérico que se utiliza en la cláusula where de arriba no está igualmente distribuido; por lo que incluso si se toma casi el mismo (constante) tiempo como el uso de donde id_value >= RAND() * MAX(id_value), es mejor.
    • Como puedo decirle a este no se ejecuta en tiempo constante, se ejecuta en tiempo lineal. En el peor de los casos, @n es igual al número de filas de la tabla, y «SELECT * FROM tabla LÍMITE ?,1» evalúa @n – 1 filas, hasta que llega a la última.
  8. 3

    Mejor manera es poner un valor aleatorio en una nueva columna sólo para ese fin, y el uso de 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 de MediaWiki. Por supuesto, hay un cierto sesgo en contra de los valores más pequeños, pero se encontraron con que era suficiente para envolver el valor aleatorio en torno a cero cuando no se recuperan las filas.

    newid() solución puede requerir un análisis completo de la tabla, de modo que cada fila puede ser asignado a un nuevo guid, que va a ser mucho menos eficiente.

    rand() solución puede no funcionar en absoluto (es decir, con MSSQL) debido a que la función se evalúa sólo una vez, y cada fila se les asignará el mismo «aleatoria» número.

    • Envolver alrededor cuando usted consigue 0 resultados proporciona un modo demostrable muestra aleatoria (no sólo «lo suficientemente bueno»). Esta solución casi de las escalas multi-fila de consultas (piense en «party shuffle»). El problema es que los resultados tienden a ser seleccionados en los mismos grupos en varias ocasiones. Para evitar esto, usted tendría que volver a distribuir los números aleatorios acaba de utilizar. Se puede hacer trampa al mantener un registro de randomNo y ajuste a max(aleatoriedad) a partir de los resultados, pero luego p(fila i de la consulta 1 Y la fila i de la consulta 2) == 0, lo cual no es justo. Permítanme hacer algo de matemáticas, y voy a volver a usted con una verdadera feria de esquema.
  9. 3

    Para SQL Server 2005 y 2008, si queremos que una muestra aleatoria de cada una de las filas (de Libros Online):

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

    Lugar de el uso de la función RAND(), ya que no se anima, usted puede simplemente obtener max ID (=Max):

    SELECT MAX(ID) FROM TABLE;

    obtener un aleatorio entre 1..Max (=My_Generated_Random)

    My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

    y, a continuación, ejecute este SQL:

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

    Nota de que se compruebe cualquiera de las filas que Ids IGUAL o MAYOR que el valor elegido.
    También es posible que, a la caza de la fila de abajo en la tabla, y obtener un igual o inferior ID de la My_Generated_Random, a continuación, modificar la consulta como esta:

    SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1
    • ¿Qué pasaría si aleatoria generada por el ID no existe en la tabla de más? Elimina o pasivo filas que no desea mostrar al usuario podría causar un problema.
  11. 2

    Como se señaló en @BillKarwin del comentario de @cnu la respuesta…

    Cuando se combina con un LÍMITE, he encontrado que funciona mucho mejor (al menos con PostgreSQL 9.1) para UNIRSE con un ordenamiento al azar en lugar de directamente el orden de las filas: 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

    Sólo asegúrese de que la ‘r’ genera un «rand» valor para cada posible valor de la clave en el complejo de consulta al que se unen con él, pero todavía limitar el número de filas de la ‘r’ donde sea posible.

    Al ELENCO como el Entero es especialmente útil para PostgreSQL 9.2 específicas de ordenación de la optimización para el entero y solo flotante de precisión tipos.

  12. 1

    La mayoría de las soluciones aquí con el fin de evitar la clasificación, pero que todavía tienen que hacer un recorrido secuencial sobre una mesa.

    También hay una manera de evitar el recorrido secuencial por el cambio de recorrido de índice. Si usted sabe el valor de índice de la fila al azar puede obtener el resultado casi instantially. 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;

    Que por encima de solución que adivinar de 10 distintos aleatoria de los valores del índice de rango 0 .. [último valor de id].

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

    También hay un problema – si usted tiene escasa id usted podría perder. La solución es tener un plan de copia de seguridad 🙂 En este caso, un puro antiguo orden aleatorio() de la consulta. Cuando se combina id se parece a esto:

    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 TODOS cláusula. En este caso, si la primera parte se devuelve los datos de la segunda no se ejecuta NUNCA!

  13. 1

    Tarde, pero llegué aquí a través de Google, así que por el bien de la posteridad, voy a añadir una solución alternativa.

    Otro método es utilizar la parte SUPERIOR dos veces, alternando con los pedidos. No sé si es «pura SQL», porque utiliza una variable en la parte SUPERIOR, pero funciona en SQL Server 2008. He aquí un ejemplo que uso contra una tabla de palabras de diccionario, si quiero una palabra al azar.

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

    De curso, @idx es algunos generado al azar entero que va desde 1 a COUNT(*) en la tabla de destino, inclusive. Si la columna está indizada, usted se beneficiará también de ella. Otra ventaja es que se puede utilizar en una función, ya que NEWID() no está permitida.

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

  14. 1

    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 detalle http://jan.kneschke.de/projects/mysql/order-by-rand/

    • Después de probar muchas de las respuestas creo que este es el mejor. Parece ser rápido y recoge un buen número aleatorio cada vez. Parece similar a la de @GreyPanther ‘s segunda sugerencia anterior, pero esta respuesta selecciones más números al azar.
  15. 1

    No acababa de ver esta variación en las respuestas todavía. Yo tenía una restricción adicional en el que necesitaba, dado un inicial de la semilla, para seleccionar el mismo conjunto de filas de cada tiempo.

    Para MS SQL:

    Mínimo ejemplo:

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

    Normalizada tiempo de ejecución: 1.00

    NewId() ejemplo:

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

    Normalizada tiempo de ejecución: 1.02

    NewId() es insignificantemente más lento que rand(checksum(*)), por lo que usted no desea utilizarlo en contra de grandes conjuntos de registros.

    De la selección Inicial de la 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 usted necesita para seleccionar el mismo conjunto dado una semilla, esto parece funcionar.

  16. 1

    En MSSQL (probado en 11.0.5569) utilizando

    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()
  17. 1

    En SQL Server puede combinar TABLESAMPLE con NEWID() para obtener bastante bueno aleatoriedad y todavía tienen la velocidad. Esto es especialmente útil si usted realmente desea solamente 1, o un pequeño número de filas.

    SELECT TOP 1 * FROM [table] 
    TABLESAMPLE (500 ROWS) 
    ORDER BY NEWID()
  18. 0
     SELECT * FROM table ORDER BY RAND() LIMIT 1
    • Diez años atrás (2005), algunos de tipo dijo que el uso de ORDER BY RAND() está mal…
  19. 0

    Tengo que estar de acuerdo con la unidad de CD-MaN: el Uso de «ORDER BY RAND()» funcionará muy bien para tablas pequeñas o cuando hacen SELECCIONE sólo un par de veces.

    Yo también uso el «num_value >= RAND() * …» técnica, y si realmente quiero tener resultados aleatorios tengo un especial «al azar» de la columna en la tabla que puedo actualizar una vez al día o así. Que la sola ejecución de ACTUALIZACIÓN tomará algún tiempo (sobre todo porque vas a tener un índice en la columna), pero es mucho más rápido que la creación de números aleatorios para cada fila y cada vez que la seleccione ejecutar.

  20. 0

    Que tener cuidado porque TableSample en realidad no devolver una muestra aleatoria de filas. Dirige su consulta para buscar en una muestra aleatoria de 8 kb de páginas que componen su fila. A continuación, la consulta se ejecuta en contra de los datos contenidos en estas páginas. Porque de cómo los datos pueden ser agrupados en estas páginas (orden de inserción, etc), esto podría conducir a datos que no es en realidad una muestra aleatoria.

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

    Esta página de MSDN para TableSample incluye un ejemplo de cómo generar un hecho al azar de la muestra de datos.

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

  21. 0

    Parece que muchas de las ideas que están todavía en uso pedidos

    Sin embargo, si utiliza una tabla temporal, que son capaces de asignar un índice aleatorio (como muchas de las soluciones que se han sugerido), y luego tome la primera que es mayor que un número aleatorio 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
    • Después de considerar esta solución, he encontrado un error fundamental en mi lógica. Este sería constantemente volver por el mismo pequeño conjunto de valores, cerca del principio de la tabla, porque supongo que si hay que ael distribución entre 0 y 1, hay un 50% de probabilidad de que la primera fila se cumple con el criterio.
  22. 0

    No es mejor la solución de Oracle en lugar de utilizar dbms_random.valor, si bien requiere de un análisis completo para el fin de filas por dbms_random.valor y es bastante lento para tablas grandes.

    El uso de este lugar:

    SELECT *
    FROM employee sample(1)
    WHERE rownum=1
  23. 0

    Con SQL Server 2012+ puede utilizar el OFFSET FETCH consulta a hacer esto por una sola fila al azar

    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 – calcula como un número aleatorio entre 0 y count()-1 de la tabla (offset 0 es el primero de la fila, después de todo)

    Esto funciona con agujeros en los datos de la tabla, siempre y cuando usted tiene un índice de trabajo con la cláusula ORDER BY. También es muy bueno para la aleatoriedad – como que el trabajo que fuera de sí mismo para pasar, pero los peros en otros métodos no están presentes. Además, el rendimiento es bastante bueno, en un conjunto de datos más pequeño que se defiende bien, aunque no lo he probado graves de rendimiento de las pruebas en contra de varios millones de filas.

  24. 0

    Para SQL Server 2005 y anteriores, que se extiende @GreyPanther la respuesta para los casos cuando num_value no tiene valores continuos. Esto también funciona para los casos que no se han distribuido uniformemente 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)
    )
  25. -1

    Aleatoria de la función de sql podría ayudar. También si usted desea limitar a sólo una fila, sólo añadir que en el final.

    SELECT column FROM table
    ORDER BY RAND()
    LIMIT 1

Dejar respuesta

Please enter your comment!
Please enter your name here