Seleccionar una fila aleatoria de una tabla sqlite

Tengo una tabla sqlite con el siguiente esquema:

 CREATE TABLE foo (bar VARCHAR) 

Estoy usando esta tabla como almacenamiento para una lista de cadenas.

¿Cómo selecciono una fila al azar de esta tabla?

Eche un vistazo a Selección de una fila aleatoria desde una tabla de SQLite

 SELECT * FROM table ORDER BY RANDOM() LIMIT 1; 

Las siguientes soluciones son mucho más rápidas que las de Anktastic (el conteo (*) cuesta mucho, pero si puedes guardarlo en caché, entonces la diferencia no debería ser tan grande), que a su vez es mucho más rápido que el “orden por azar ()” cuando tiene una gran cantidad de filas, aunque tienen algunos inconvenientes.

Si tus rowids están bastante empaquetados (es decir, algunas eliminaciones), puedes hacer lo siguiente (usar (select max(rowid) from foo)+1 lugar de max(rowid)+1 da mejor rendimiento, como se explica en los comentarios) :

 select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)); 

Si tiene huecos, a veces tratará de seleccionar un rowid no existente, y el seleccionar devolverá un conjunto de resultados vacío. Si esto no es aceptable, puede proporcionar un valor predeterminado como este:

 select * from foo where rowid = (abs(random()) % (select (select max(rowid) from foo)+1)) or rowid = (select max(rowid) from node) order by rowid limit 1; 

Esta segunda solución no es perfecta: la distribución de probabilidad es más alta en la última fila (la que tiene el hándito más alto), pero si a menudo agregas cosas a la mesa, se convertirá en un objective en movimiento y la distribución de probabilidades debería ser mucho mejor.

Otra solución más, si a menudo selecciona elementos aleatorios de una tabla con muchos agujeros, entonces es posible que desee crear una tabla que contenga las filas de la tabla original ordenadas en orden aleatorio:

 create table random_foo(foo_id); 

Luego, periodicalliy, rellene la tabla random_foo

 delete from random_foo; insert into random_foo select id from foo; 

Y para seleccionar una fila al azar, puedes usar mi primer método (aquí no hay agujeros). Por supuesto, este último método tiene algunos problemas de concurrencia, pero la reconstrucción de random_foo es una operación de mantenimiento que no es probable que suceda muy a menudo.

Sin embargo, otra forma, que encontré recientemente en una lista de correo , es poner un desencadenador en eliminar para mover la fila con el rowid más grande en la fila eliminada actual, para que no queden agujeros.

Por último, tenga en cuenta que el comportamiento de la autoincrementación de clave primaria rowid y entero no es idéntico (con rowid, cuando se inserta una nueva fila, se elige max (rowid) +1, si es el valor más alto visto alguna vez + 1 para una clave principal), por lo que la última solución no funcionará con una autoincrementación en random_foo, pero los demás métodos lo harán.

Qué pasa:

 SELECT COUNT(*) AS n FROM foo; 

luego elija un número aleatorio m en [0, n) y

 SELECT * FROM foo LIMIT 1 OFFSET m; 

Incluso puede guardar el primer número ( n ) en algún lugar y solo actualizarlo cuando cambie el recuento de la base de datos. De esta manera, no tiene que hacer el COUNT SELECTO cada vez.

 SELECT bar FROM foo ORDER BY Random() LIMIT 1 

Necesita poner “orden por ALEATORIO ()” en su consulta.

Ejemplo:

 select * from quest order by RANDOM(); 

Veamos un ejemplo completo

  1. Crea una tabla:
 CREATE TABLE quest ( id INTEGER PRIMARY KEY AUTOINCREMENT, quest TEXT NOT NULL, resp_id INTEGER NOT NULL ); 

Insertar algunos valores:

 insert into quest(quest, resp_id) values ('1024/4',6), ('256/2',12), ('128/1',24); 

Una selección predeterminada:

 select * from quest; | id | quest | resp_id | 1 1024/4 6 2 256/2 12 3 128/1 24 -- 

Un selecto al azar:

 select * from quest order by RANDOM(); | id | quest | resp_id | 3 128/1 24 1 1024/4 6 2 256/2 12 -- 

* Cada vez que seleccione, el orden será diferente.

Si quieres devolver solo una fila

 select * from quest order by RANDOM() LIMIT 1; | id | quest | resp_id | 2 256/2 12 -- 

* Cada vez que seleccione, el retorno será diferente.

Aquí hay una modificación de la solución de @ank:

 SELECT * FROM table LIMIT 1 OFFSET ABS(RANDOM()) % MAX((SELECT COUNT(*) FROM table), 1) 

Esta solución también funciona para índices con espacios vacíos, porque aleatorizamos un desplazamiento en un rango [0, recuento). MAX se usa para manejar un caso con una tabla vacía.

Aquí hay resultados de prueba simples en una tabla con 16k filas:

 sqlite> .timer on sqlite> select count(*) from payment; 16049 Run Time: real 0.000 user 0.000140 sys 0.000117 sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment); 14746 Run Time: real 0.002 user 0.000899 sys 0.000132 sqlite> select payment_id from payment limit 1 offset abs(random()) % (select count(*) from payment); 12486 Run Time: real 0.001 user 0.000952 sys 0.000103 sqlite> select payment_id from payment order by random() limit 1; 3134 Run Time: real 0.015 user 0.014022 sys 0.000309 sqlite> select payment_id from payment order by random() limit 1; 9407 Run Time: real 0.018 user 0.013757 sys 0.000208 

Se me ocurrió la siguiente solución para las grandes bases de datos sqlite3 :

 SELECT * FROM foo WHERE rowid = abs(random()) % (SELECT max(rowid) FROM foo) + 1; 

La función abs (X) devuelve el valor absoluto del argumento numérico X.

La función random () devuelve un entero pseudoaleatorio entre -9223372036854775808 y +9223372036854775807.

El operador% emite el valor entero de su módulo de operando izquierdo, su operando derecho.

Finalmente, agregas +1 para evitar que rowid sea igual a 0.