manager - Asesoramiento de optimización de declaración SQLite select

sqlite3 doc

Question

Tengo una tabla SQLite ''Detalles'' con estructura:

ID Name Category
---------------------
1 Matt 0
2 Shervin 0
3 Bob 0
4 Lee 0
5 Rick 0
6 Suraya 0
7 Susan 0
8 Adam 0
9 Jon 1
10 Lorna 1
... and so on ....... 

Quiero seleccionar una fila al azar, y luego tres nombres de tres filas diferentes (de nuevo, preferiblemente al azar). Me gustaría que todo esto se devuelva de una declaración SQLite. P.ej

ID Name Category Name1 Name2 Name 3
----------------------------------------
3 Bob 0 Matt Lee Susan 

Mi intento de esto se puede ver a continuación, pero tiene dos problemas:

1. Los tres nombres adicionales no siempre son necesariamente diferentes: parece que no puedo excluir un nombre que se haya seleccionado previamente porque las variables b / c / d no están dentro del alcance, aparte de su propia función COALESCE.
2. Como cada selección anidada usa la función Aleatoria (), no es muy eficiente.

¿Alguien puede sugerir otra forma de seleccionar los datos que necesito (usando la base de datos SQLite)? Cualquier ayuda / consejo es bienvenido - espero que esté claro lo que estoy tratando de lograr, siéntase libre de pedir cualquier aclaración.

Mi intento actual:

SELECT a.Id,
a.Name,
a.Category,
COALESCE((SELECT b.Name
FROM Details b
WHERE b.Id NOT IN (a.Id)
AND b.Category IN (0)
ORDER BY Random()
LIMIT 1),'''') as "Name1",
COALESCE((SELECT c.Name
FROM Details c
WHERE c.Id NOT IN (a.Id)
AND c.Category IN (0)
ORDER BY Random()
LIMIT 1),'''') as "Name2",
COALESCE((SELECT d.Name
FROM Details d
WHERE d.Id NOT IN (a.Id)
AND d.Category IN (0)
ORDER BY Random()
LIMIT 1),'''') as "Name3"
FROM Details a
AND a.Category IN (0)
ORDER BY Random()
LIMIT 1

Answer 1

¿Qué hay de hacer una combinación externa completa x3, y luego simplemente elegir una fila al azar donde los nombres no son iguales?

Answer 2

Hay dos métodos principales para optimizar una orden por instrucción random ().

La primera es eliminar por completo la ordenación de todo el paso de la tabla, pero no funciona en todas las plataformas: limit 1 offset random() , en lugar de order by random() limit 1 .

El otro funciona en todas las plataformas, pero requiere que sus claves principales sean razonablemente densas (un entero que se autoincrementa con pequeñas eliminaciones si las elimina). Obtenga previamente un conjunto más pequeño de ID a partir de un punto de partida aleatorio y utilícelos en una subconsulta:

select *
from (select *
from tbl
where id between :x and :x + 20
)
order by random()
limit 1

Answer 3

También puede lograr lo que desea anidando las consultas para tener los nombres como un valor devuelto. Básicamente, obtienes el cuarto valor primero y luego el tercero, y así sucesivamente. Todo el tiempo asegurándose de que no coincidan. Debería haber pasado por el campo Id y haber comprobado que los Id no entren en conflicto en lugar de los nombres, pero de esta manera significa nombres únicos.

 SELECT Id
,Name
,Category
,bName
,cName
,dName
FROM Details,
(
SELECT Name AS bName, cName, dName
FROM Details,
(
SELECT Name AS cName, dName
FROM Details,
(
SELECT Name AS dName
FROM Details
WHERE Category IN (0)
ORDER BY Random()
LIMIT 1
) td
WHERE Name <> dName
AND Category IN (0)
ORDER BY Random()
LIMIT 1
) tc
WHERE Name <> dName
AND Name <> cName
AND Category IN (0)
ORDER BY Random()
LIMIT 1
) tb
WHERE Name <> dName
AND Name <> cName
AND Name <> bName
AND Category IN (0)
ORDER BY Random()
LIMIT 1;

No veo una forma de evitar la función Random () y la lentitud que genera, aparte de generar los Id aleatorios en el código, pero eso tiene otros problemas.

Answer 4

Estoy con neurino aquí. No ha dicho por qué necesita poner los cuatro nombres elegidos al azar en una sola fila, y por qué esto tiene que hacerse en el back-end.

Si le preocupa el rendimiento, genere enteros aleatorios en su cliente (rango> = min (pkcol) y <= max (pkcol)) hasta que encuentre cuatro filas distintas (es decir, entidades / nombres). Existe la posibilidad de que no exista una fila con uno de los identificadores generados, pero eso lleva solo milisegundos para averiguarlo. Tomando ese enfoque de clave aleatoria podrías evitar un orden por. El enfoque funcionaría rápidamente incluso para tablas con miles de millones de filas.

PD (después de descubrir que era una aplicación de iPhone) Necesita una llamada para obtener los valores de ID mínimo y máximo (es el PK, por lo que usa un índice). Luego necesita al menos otra llamada a la base de datos (nuevamente, asistida por índice) para obtener las cuatro filas distintas usando los valores de PK generados aleatoriamente [donde ID en (a, b, c, d)] Se desconoce el número máximo de llamadas ; cuántos dependerán de la densidad de la secuencia de teclas principal. No creo que esto sea una cantidad desmesurada de E / S y sería considerablemente menos intensivo en recursos que una orden aleatoria (), especialmente si la tabla tiene muchas filas. Siempre puede generar una lista de ID de 8, 12, 16 identificadores al azar y hacer que su cliente elimine solo las 4 filas requeridas si se devuelven más de 4.

PPS Normalmente, la instanciación de la conexión de la base de datos es costosa, y no desea hacer eso en un bucle o con más frecuencia de la que necesita. Pero puede abrir una conexión, ejecutar dos o tres selecciones eficientes que devuelven algunas filas cada una, y luego cerrar si ya ha terminado con la tarea en cuestión.

Answer 5

Una solución de instrucciones múltiples, que utiliza una tabla temporal:

CREATE TEMP TABLE names
AS
SELECT
Id,
Name,
Category
FROM Details
WHERE Category IN (0)
ORDER BY Random()
LIMIT 4;
SELECT
MAX(CASE rowid WHEN 1 THEN Id END) AS Id,
MAX(CASE rowid WHEN 1 THEN Name END) AS Name,
MAX(CASE rowid WHEN 1 THEN Category END) AS Id,
MAX(CASE rowid WHEN 2 THEN Name END) AS Name1,
MAX(CASE rowid WHEN 3 THEN Name END) AS Name2,
MAX(CASE rowid WHEN 4 THEN Name END) AS Name3
FROM names;
DROP TABLE names;