¿Cómo get loggings ALEATORIOS de cada categoría en MySQL?

En mi database MySQL tengo una tabla con diferentes preguntas en diferentes categorías.

Me gustaría escribir el enunciado SQL que devuelve 3 preguntas ALEATORIAS de CADA categoría.

Aquí hay un ejemplo de loggings de la database:

id question category 1 Question A 1 2 Question B 1 3 Question C 1 4 Question D 1 5 Question D 1 6 Question F 2 7 Question G 2 8 Question H 2 9 Question I 2 10 Question J 2 11 Question K 3 12 Question L 3 13 Question M 3 14 Question N 3 15 Question O 3 16 Question P 3 

Aquí está la salida / resultados de 3 aleatorios seleccionados y mezclados de todas las preguntas de cada categoría de la list anterior:

 2 Question B 1 4 Question D 1 3 Question C 1 10 Question J 2 7 Question G 2 9 Question I 2 11 Question K 3 15 Question P 3 13 Question M 3 

Hasta ahora he jugado con la siguiente statement para probar:

 SELECT * FROM `random` ORDER BY RAND() LIMIT 0,3; 

Esto devuelve solo 3 preguntas ALEATORIAS de todas las categorías.

Y luego he buscado, por ejemplo, en este enlace: MYSQL seleccionar random de cada una de las categorías

Y probé esto:

 (SELECT * FROM `random` WHERE category = 1 ORDER BY RAND() LIMIT 3) UNION ALL (SELECT * FROM `random` WHERE category = 2 ORDER BY RAND() LIMIT 3) UNION ALL (SELECT * FROM `random` WHERE category = 3 ORDER BY RAND() LIMIT 3) 

Pero aquí necesito agregar cada categoría manualmente.

Mi pregunta: me preguntaba si es posible get 3 loggings / filas ALEATORIOS de cada categoría de todas las categorías (automáticamente)?


EDITAR

Esto no es parte de la pregunta, sino ayuda.

Creador de datos falsos El código de consulta presentará una tabla llamada procedimiento almacenado random y creado llamado create_random y cuando ejecuta el procedimiento almacenado, creará datos ficticios aleatorios dentro de la tabla aleatoria:

 DELIMITER $$ DROP TABLE IF EXISTS `random`; DROP PROCEDURE IF EXISTS `create_random` $$ CREATE TABLE `random` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `question` VARCHAR(50) NULL DEFAULT NULL, `category` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=401 ; CREATE DEFINER=`root`@`localhost` PROCEDURE `create_random`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE v_max int unsigned DEFAULT 100; DECLARE v_counter int unsigned DEFAULT 0; DECLARE cat_counter int unsigned DEFAULT 0; TRUNCATE TABLE `random`; START TRANSACTION; WHILE v_counter < v_max DO IF v_counter %10=0 THEN SET cat_counter=cat_counter+1; END IF; INSERT INTO `random` (question, category) VALUES ( CONCAT('Question', FLOOR(0 + (RAND() * 65535))), cat_counter ); SET v_counter=v_counter+1; END WHILE; COMMIT; END 

Nota: Intenté todas las respuestas y todo funciona bien. Gordon Linoff y Phanaway responden seleccionando RANDOM solo de las 3 o 3 preguntas más frecuentes, revisé la respuesta de Gordon porque respondió primero, pero eso no significa que otras respuestas no sean buenas, todas son buenas y depende de los usuarios elegir la respuesta correcta o la combinación de respuestas. Amo todas las respuestas y las voto. Drew Pierce responde recientemente a esta pregunta, es más interesante en este momento y casi cerca de la meta. Gracias a todos.

Sí, puedes hacer esto al enumerar las filas y luego search los tres primeros:

 select r.id, r.question, r.category from (select r.*, (@rn := if(@c = category, @rn + 1, if(@c := category, 1, 1) ) ) as seqnum from `random` r cross join (select @rn := 0, @c := -1) params order by category, rand() ) r where seqnum <= 3; 

Además de la otra respuesta, esta también es otra forma de hacerlo.

 SELECT r.* FROM random r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER BY r.category ASC, RAND() 
 create schema so_gibberish; -- creates database use so_gibberish; -- use it -- drop table random; -- during debug create table random ( id int auto_increment primary key, question varchar(50) not null, category int not null, randomOrder int not null, key (category) ); -- drop table questions_for_user; -- during debug create table questions_for_user ( physcOrder int auto_increment primary key, id int not null, question varchar(50) not null, category int not null, randomOrder int not null, key (category) ); 

Crea un procedimiento almacenado para insert preguntas aleatorias. Crea 300 a la vez cuando lo llamas.

 DELIMITER $$ drop procedure if exists createRandomQuestions$$ -- 17 categories of questions randomly created. yes random word questions and categories. create procedure createRandomQuestions() BEGIN set @i=1; WHILE @i<=300 DO insert random (question,category) values ('xxx',1); SELECT @lid:=LAST_INSERT_ID(); -- use id to seed, next 8 guaranteed different i think UPDATE random SET question=concat( substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1), substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1), ' ?' ), category=floor(rand()*17+1),randomOrder=0 WHERE id=@lid; set @i=@i+1; END WHILE; END; $$ DELIMITER ; 

llámalo:

 call createRandomQuestions(); 

verificar:

 select category,count(*) from random group by category order by category; select count(*) from random; select * from random limit 10; 

Ahora obtenga tres preguntas random para cada categoría para un usuario a pedido.

Vamos con un buen algorithm de SEMILLA random. El siguiente es un apéndice, lo mejor es usar una fuente externa como http para un service, etc.

 update random set randomOrder=rand()*unix_timestamp(); truncate table questions_for_user; insert into questions_for_user (id,question,category,randomOrder) select id,question,category,randomOrder from random order by rand(); select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by r.category ASC, randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 297 | 266 | JNQH26DD ? | 1 | 841700408 | | 247 | 286 | ORK15577 ? | 1 | 980764662 | | 54 | 267 | T2HPRW88 ? | 1 | 1331420535 | | 190 | 275 | U5BFCUFF ? | 2 | 836160445 | | 192 | 285 | E3KDJ144 ? | 2 | 1166207975 | | 55 | 293 | GFWQ0BEE ? | 2 | 1356597807 | | 266 | 296 | 8MJCWR55 ? | 3 | 1121232849 | | 173 | 288 | 6GGOI2CC ? | 3 | 1209081435 | | 205 | 231 | LQMYMV44 ? | 3 | 1330946482 | | 103 | 289 | FUODJHJJ ? | 4 | 392498036 | | 274 | 295 | Y85VNBWW ? | 4 | 613800522 | | 204 | 280 | 441X1YTT ? | 4 | 1141251557 | | 162 | 273 | CC5FGKFF ? | 5 | 99041401 | | 36 | 252 | Y7V58ZEE ? | 5 | 1124240820 | | 143 | 234 | DWZULKBB ? | 5 | 1286225785 | | 15 | 276 | 3J1KGJZZ ? | 6 | 51285374 | | 265 | 294 | PTEMZ7GG ? | 6 | 649256602 | | 91 | 271 | TNG23R11 ? | 6 | 1393790410 | | 90 | 277 | DXE1W0GG ? | 7 | 622567356 | | 20 | 274 | LQTFS7RR ? | 7 | 1376505632 | | 121 | 279 | VQN6TVSS ? | 7 | 1428714367 | | 268 | 265 | A99UU1YY ? | 8 | 479923553 | | 242 | 284 | 5PDRR5YY ? | 8 | 658551160 | | 283 | 282 | NWZSPCZZ ? | 8 | 802197234 | | 114 | 233 | 3IHU1YKK ? | 9 | 118146688 | | 85 | 226 | BQTD6A44 ? | 9 | 292147382 | | 255 | 291 | YNT0YN11 ? | 9 | 559911752 | | 281 | 258 | IHK46577 ? | 10 | 196799301 | | 198 | 263 | SHEVXPBB ? | 10 | 545833955 | | 11 | 298 | QEHHSAEE ? | 10 | 1087006220 | | 60 | 250 | GEH9PVBB ? | 11 | 225193615 | | 270 | 272 | 3YY2EMM ? | 11 | 1143544695 | | 300 | 290 | O8B4WRCC ? | 11 | 1209867696 | | 68 | 246 | FTEEVJ00 ? | 12 | 608441021 | | 177 | 244 | X1JYHUBB ? | 12 | 1192309224 | | 208 | 240 | W771N588 ? | 12 | 1347800170 | | 249 | 300 | 97V6UXYY ? | 13 | 500846709 | | 100 | 292 | 71JZTMQQ ? | 13 | 607266604 | | 38 | 283 | WBQ0DGLL ? | 13 | 1187952919 | | 87 | 235 | MBPPXJ44 ? | 14 | 327445349 | | 178 | 242 | EZ1ET3ZZ ? | 14 | 852480639 | | 62 | 281 | EIHVH3ZZ ? | 14 | 1420114728 | | 95 | 251 | PT8E2CII ? | 15 | 72502146 | | 170 | 253 | 8L35PFYY ? | 15 | 1091765038 | | 65 | 287 | X5ZR3LHH ? | 15 | 1405199431 | | 138 | 278 | MBW03OUU ? | 16 | 84358922 | | 7 | 268 | 2G4G42WW ? | 16 | 1257379717 | | 1 | 299 | ZT8QRRMM ? | 16 | 1327297420 | | 221 | 297 | H00HLNRR ? | 17 | 890140945 | | 40 | 204 | O7VUW2NN ? | 17 | 1238474743 | | 122 | 229 | 3XEZG0PP ? | 17 | 1359337651 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec) 

Ejecútelo nuevamente para el siguiente usuario o el mismo tipo:

 update random set randomOrder=rand()*unix_timestamp(); truncate table questions_for_user; insert into questions_for_user (id,question,category,randomOrder) select id,question,category,randomOrder from random order by rand(); select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by r.category ASC, randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 112 | 286 | ORK15577 ? | 1 | 193281314 | | 176 | 266 | JNQH26DD ? | 1 | 530153269 | | 296 | 267 | T2HPRW88 ? | 1 | 891041924 | | 37 | 293 | GFWQ0BEE ? | 2 | 222852606 | | 239 | 285 | E3KDJ144 ? | 2 | 679635152 | | 262 | 275 | U5BFCUFF ? | 2 | 846163956 | | 95 | 288 | 6GGOI2CC ? | 3 | 1244253481 | | 81 | 231 | LQMYMV44 ? | 3 | 1380298624 | | 59 | 296 | 8MJCWR55 ? | 3 | 1420850554 | | 28 | 295 | Y85VNBWW ? | 4 | 806083444 | | 30 | 289 | FUODJHJJ ? | 4 | 814854070 | | 34 | 280 | 441X1YTT ? | 4 | 1119188021 | | 232 | 234 | DWZULKBB ? | 5 | 296692881 | | 295 | 273 | CC5FGKFF ? | 5 | 337915901 | | 140 | 252 | Y7V58ZEE ? | 5 | 644987638 | | 257 | 271 | TNG23R11 ? | 6 | 619359840 | | 27 | 294 | PTEMZ7GG ? | 6 | 869855570 | | 218 | 276 | 3J1KGJZZ ? | 6 | 1390090875 | | 64 | 279 | VQN6TVSS ? | 7 | 33942495 | | 290 | 277 | DXE1W0GG ? | 7 | 100007602 | | 173 | 274 | LQTFS7RR ? | 7 | 946909650 | | 291 | 265 | A99UU1YY ? | 8 | 107468716 | | 49 | 284 | 5PDRR5YY ? | 8 | 1068298164 | | 228 | 282 | NWZSPCZZ ? | 8 | 1115906220 | | 284 | 226 | BQTD6A44 ? | 9 | 45678738 | | 179 | 291 | YNT0YN11 ? | 9 | 700305900 | | 164 | 233 | 3IHU1YKK ? | 9 | 1416089612 | | 193 | 258 | IHK46577 ? | 10 | 460111512 | | 214 | 298 | QEHHSAEE ? | 10 | 482322673 | | 7 | 263 | SHEVXPBB ? | 10 | 766681927 | | 178 | 290 | O8B4WRCC ? | 11 | 341509950 | | 31 | 272 | 3YY2EMM ? | 11 | 726662739 | | 297 | 250 | GEH9PVBB ? | 11 | 1386568968 | | 32 | 240 | W771N588 ? | 12 | 303493686 | | 283 | 246 | FTEEVJ00 ? | 12 | 710591266 | | 177 | 244 | X1JYHUBB ? | 12 | 916685336 | | 212 | 283 | WBQ0DGLL ? | 13 | 595739692 | | 159 | 300 | 97V6UXYY ? | 13 | 688431139 | | 52 | 292 | 71JZTMQQ ? | 13 | 1039681379 | | 56 | 235 | MBPPXJ44 ? | 14 | 109832248 | | 207 | 242 | EZ1ET3ZZ ? | 14 | 418951740 | | 6 | 281 | EIHVH3ZZ ? | 14 | 1182157711 | | 149 | 287 | X5ZR3LHH ? | 15 | 364819476 | | 88 | 251 | PT8E2CII ? | 15 | 673475236 | | 123 | 253 | 8L35PFYY ? | 15 | 1204512525 | | 190 | 278 | MBW03OUU ? | 16 | 641720378 | | 160 | 299 | ZT8QRRMM ? | 16 | 1289470813 | | 75 | 268 | 2G4G42WW ? | 16 | 1427431541 | | 202 | 297 | H00HLNRR ? | 17 | 374047531 | | 243 | 204 | O7VUW2NN ? | 17 | 951026810 | | 300 | 229 | 3XEZG0PP ? | 17 | 1319302198 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec) 

Entonces las preguntas son diferentes .

Para get los últimos resultados en un sentido mezclado:

 select r.* FROM questions_for_user r WHERE ( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 ORDER by randomOrder; +------------+-----+------------+----------+-------------+ | physcOrder | id | question | category | randomOrder | +------------+-----+------------+----------+-------------+ | 64 | 279 | VQN6TVSS ? | 7 | 33942495 | | 284 | 226 | BQTD6A44 ? | 9 | 45678738 | | 290 | 277 | DXE1W0GG ? | 7 | 100007602 | | 291 | 265 | A99UU1YY ? | 8 | 107468716 | | 56 | 235 | MBPPXJ44 ? | 14 | 109832248 | | 112 | 286 | ORK15577 ? | 1 | 193281314 | | 37 | 293 | GFWQ0BEE ? | 2 | 222852606 | | 232 | 234 | DWZULKBB ? | 5 | 296692881 | | 32 | 240 | W771N588 ? | 12 | 303493686 | | 295 | 273 | CC5FGKFF ? | 5 | 337915901 | | 178 | 290 | O8B4WRCC ? | 11 | 341509950 | | 149 | 287 | X5ZR3LHH ? | 15 | 364819476 | | 202 | 297 | H00HLNRR ? | 17 | 374047531 | | 207 | 242 | EZ1ET3ZZ ? | 14 | 418951740 | | 193 | 258 | IHK46577 ? | 10 | 460111512 | | 214 | 298 | QEHHSAEE ? | 10 | 482322673 | | 176 | 266 | JNQH26DD ? | 1 | 530153269 | | 212 | 283 | WBQ0DGLL ? | 13 | 595739692 | | 257 | 271 | TNG23R11 ? | 6 | 619359840 | | 190 | 278 | MBW03OUU ? | 16 | 641720378 | | 140 | 252 | Y7V58ZEE ? | 5 | 644987638 | | 88 | 251 | PT8E2CII ? | 15 | 673475236 | | 239 | 285 | E3KDJ144 ? | 2 | 679635152 | | 159 | 300 | 97V6UXYY ? | 13 | 688431139 | | 179 | 291 | YNT0YN11 ? | 9 | 700305900 | | 283 | 246 | FTEEVJ00 ? | 12 | 710591266 | | 31 | 272 | 3YY2EMM ? | 11 | 726662739 | | 7 | 263 | SHEVXPBB ? | 10 | 766681927 | | 28 | 295 | Y85VNBWW ? | 4 | 806083444 | | 30 | 289 | FUODJHJJ ? | 4 | 814854070 | | 262 | 275 | U5BFCUFF ? | 2 | 846163956 | | 27 | 294 | PTEMZ7GG ? | 6 | 869855570 | | 296 | 267 | T2HPRW88 ? | 1 | 891041924 | | 177 | 244 | X1JYHUBB ? | 12 | 916685336 | | 173 | 274 | LQTFS7RR ? | 7 | 946909650 | | 243 | 204 | O7VUW2NN ? | 17 | 951026810 | | 52 | 292 | 71JZTMQQ ? | 13 | 1039681379 | | 49 | 284 | 5PDRR5YY ? | 8 | 1068298164 | | 228 | 282 | NWZSPCZZ ? | 8 | 1115906220 | | 34 | 280 | 441X1YTT ? | 4 | 1119188021 | | 6 | 281 | EIHVH3ZZ ? | 14 | 1182157711 | | 123 | 253 | 8L35PFYY ? | 15 | 1204512525 | | 95 | 288 | 6GGOI2CC ? | 3 | 1244253481 | | 160 | 299 | ZT8QRRMM ? | 16 | 1289470813 | | 300 | 229 | 3XEZG0PP ? | 17 | 1319302198 | | 81 | 231 | LQMYMV44 ? | 3 | 1380298624 | | 297 | 250 | GEH9PVBB ? | 11 | 1386568968 | | 218 | 276 | 3J1KGJZZ ? | 6 | 1390090875 | | 164 | 233 | 3IHU1YKK ? | 9 | 1416089612 | | 59 | 296 | 8MJCWR55 ? | 3 | 1420850554 | | 75 | 268 | 2G4G42WW ? | 16 | 1427431541 | +------------+-----+------------+----------+-------------+ 51 rows in set (0.01 sec) 

La razón por la cual las otras respuestas parecen devolver las mismas preguntas (top 3 o bottom 3) imo es debido al order físico de la tabla, por lo que creé la 2da tabla. La segunda tabla usa un nuevo order físico falso y la inserción en él es por order aleatorio (por lo que el problema de order física no existe). Espero que tenga sentido.

Y, por supuesto, aproveché la respuesta de Pjanaway y no conseguí que Gordon trabajara en mi sistema, aunque me gusta su enfoque variable.

Buena suerte.