Seleccione al menos uno de cada categoría?

Enlace de SQLFiddle

Tengo una base de datos SQLite con un montón de preguntas de examen / examen. Cada pregunta pertenece a una categoría de pregunta .

¿Qué hace exactamente la restricción de clave externa "NO ACTION" de SQLite y cómo es diferente de "RESTRICT"?
Almacenamiento de datos en Android: almacenamiento de archivos vs base de datos SQLite vs preferencias compartidas
Especificar el tamaño de sqlite db máximo para mi aplicación. Cómo manejar db completa excepción
ORMLite insertar o reemplazar
¿Qué tipos de bases de datos están disponibles en Android?

Mi tabla se parece a esto:
Tabla so_questions

La meta
Lo que estoy tratando de hacer es seleccionar 5 preguntas aleatorias, pero el resultado debe contener al menos una de cada categoría. El objetivo es seleccionar un conjunto aleatorio de preguntas con preguntas de cada categoría.

Por ejemplo, la salida podría ser IDs de pregunta 1, 2, 5, 7, 8 , o 2, 3, 6, 7, 8 u 8, 6, 3, 1, 7 .

ORDER BY category_id, RANDOM ()
Puedo obtener una lista aleatoria de preguntas de SQLite ejecutando el SQL a continuación, pero ¿cómo me aseguraría de que el resultado contenga una pregunta de cada una de mis categorías?

SELECT ORDER BY category_id, random

Básicamente, estoy buscando algo como esto , la versión SQLite.

Me gustaría obtener sólo 5 resultados, pero uno (o más) de cada categoría, con todas las categorías representadas en el conjunto de resultados.

Generosidad
Se agregó una recompensa porque estoy curioso o no es posible lograr esto en SQLite solamente. ¿Puedo hacerlo en SQLite + Java, pero hay una manera de hacer esto en SQLite solamente? 🙂

Enlace de SQLFiddle

La clave de la respuesta es que hay dos tipos de preguntas en el resultado: para cada categoría, una pregunta que debe ser obligado a venir de esa categoría; Y algunas preguntas restantes.

En primer lugar, las preguntas restringidas: sólo seleccionamos un registro de cada categoría:

 SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r FROM so_questions GROUP BY category_id

(Esta consulta se basa en una característica introducida en SQLite 3.7.11 (en Jelly Bean o posterior): en una consulta SELECT a, max(b) , el valor de a se garantiza que proviene del registro que tiene el valor b máximo. )

También tenemos que obtener las preguntas no restringidas (filtrando los duplicados que ya están en el conjunto restringido que sucederá en el siguiente paso):

 SELECT id, category_id, question_text, 0 AS constrained, random() AS r FROM so_questions

Cuando combinamos estas dos consultas con UNION y luego agrupamos por el id , tenemos todos los duplicados juntos. Seleccionando max(constrained) entonces asegura que para los grupos que tienen duplicados, sólo permanece la pregunta restringida (mientras que todas las otras preguntas sólo tienen un registro por grupo de todos modos).

Finalmente, la cláusula ORDER BY garantiza que las preguntas restringidas vienen primero, seguidas por otras preguntas aleatorias:

 SELECT *, max(constrained) FROM (SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r FROM so_questions GROUP BY category_id UNION ALL SELECT id, category_id, question_text, 0 AS constrained, random() AS r FROM so_questions) GROUP BY id ORDER BY constrained DESC, r LIMIT 5

Para versiones anteriores de SQLite / Android, no he encontrado una solución sin usar una tabla temporal (porque la subconsulta para la pregunta restringida debe utilizarse varias veces, pero no permanece constante debido al random() ):

 BEGIN TRANSACTION; CREATE TEMPORARY TABLE constrained AS SELECT (SELECT id FROM so_questions WHERE category_id = cats.category_id ORDER BY random() LIMIT 1) AS id FROM (SELECT DISTINCT category_id FROM so_questions) AS cats; SELECT ids.id, category_id, question_text FROM (SELECT id FROM (SELECT id, 1 AS c FROM constrained UNION ALL SELECT id, 0 AS c FROM so_questions WHERE id NOT IN (SELECT id FROM constrained)) ORDER BY c DESC, random() LIMIT 5) AS ids JOIN so_questions ON ids.id = so_questions.id; DROP TABLE constrained; COMMIT TRANSACTION;

Básicamente lo que está buscando es seleccionar los valores N máx . Paso 3-4 horas en la mañana para buscarlo. (Todavía no tengo éxito en él, usted puede necesitar esperar algunas más horas).

Para la solución temporal se puede usar el grupo por opción de la siguiente manera,

String strQuery = "SELECT * FROM grupo de so_questions por categoría_id;";

La salida es como sigue,

Introduzca aquí la descripción de la imagen

Estará de vuelta con exacta su requisito.

Puesto que es sqlite (así local): Cuánto tiempo sería solo consultar hasta tener 5 respuestas y cuatro categorías diferentes, eliminando las filas de la categoría duplicada cada iteración.

Creo que, si cada categoría está representada por igual, que sería altamente improbable que necesite más de 3 iteraciones que todavía deben estar por debajo de un segundo.

No es algorítmicamente agradable, pero para mí el uso de random () en una sentencia SQL no es algorítmicamente agradable de todos modos.