SQL: select una fila random, pero teniendo en count un peso

Estoy usando MySQL. Tengo una table que se ve así:

id: primary key content: varchar weight: int 

Lo que quiero hacer es seleccionar random una fila de esta tabla, pero teniendo en count el peso. Por ejemplo, si tengo 3 filas:

 id, content, weight 1, "some content", 60 2, "other content", 40 3, "something", 100 

La primera fila tiene un 30% de posibilidades de ser seleccionada, la segunda fila tiene un 20% de posibilidades de ser seleccionada, y la tercera fila tiene un 50% de posibilidades de ser seleccionada.

Hay una manera de hacer eso ? Si tengo que ejecutar 2 o 3 consultas, no hay problema.

Esto funciona en MSSQL y estoy seguro de que debería ser posible cambiar algunas palabras key para que también funcione en MySQL (tal vez incluso mejor):

 SELECT TOP 1 t.* FROM @Table t INNER JOIN (SELECT t.id, sum(tt.weight) AS cum_weight FROM @Table t INNER JOIN @Table tt ON tt.id <= t.id GROUP BY t.id) tc ON tc.id = t.id, (SELECT SUM(weight) AS total_weight FROM @Table) tt, (SELECT RAND() AS rnd) r WHERE r.rnd * tt.total_weight <= tc.cum_weight ORDER BY t.id ASC 

La idea es tener un peso acumulado para cada fila (subselección-1), luego encontrar la position de la RAND extendida () en este range acumulativo.

Un enfoque simple (evitar combinaciones o subconsultas) es simplemente multiplicar el peso por un número aleatorio entre 0 y 1 para producir un peso temporal para orderar por:

 SELECT t.*, RAND() * t.weight AS w FROM table t ORDER BY w DESC LIMIT 1 

Para entender esto, considere que RAND() * 2x tendrá un valor mayor que RAND() * x aproximadamente dos terceras partes del time. En consecuencia, con el time cada fila debe seleccionarse con una frecuencia que sea proporcional a su peso relativo (por ejemplo, una fila con un peso de 100 se seleccionará aproximadamente 100 veces más a menudo que una fila con un peso de 1, etc.).

Actualización: este método en realidad no produce las distribuciones correctas , ¡así que por ahora no lo use! (ver los comentarios a continuación). Creo que todavía debería haber un método simple similar al anterior que funcionará, pero por ahora el método más complejo a continuación, que implica uniones, podría ser mejor. Dejo esta respuesta porque: (a) hay una discusión relevante en los comentarios a continuación, y (b) si / cuando tengo la oportunidad, intentaré solucionarlo.

He probado la solución de van y, aunque funciona, no es rápido.

Mi solución

La forma en que estoy resolviendo este problema es manteniendo una tabla separada y vinculada para la ponderación. La estructura básica de la tabla es similar a esto:

 CREATE TABLE `table1` ( `id` int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, `name` varchar(100), `weight` tinyint(4) NOT NULL DEFAULT '1', ); CREATE TABLE `table1_weight` ( `id` bigint(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, `table1_id` int(11) NOT NULL ); 

Si tengo un logging en la table1 con un peso de 3, entonces creo 3 loggings en table1_weight , vinculados a la table1 través del campo table1_id . Cualquiera que sea el valor del weight en la table1 , ese es el número de loggings vinculados que creo en table1_weight .

Pruebas

En un set de datos con 976 loggings en la table1 con un peso total de 2031 y, por lo tanto, 2031 loggings en table1_weight , ejecuté los siguientes dos SQL:

1) Una versión de la solución de van

 SELECT t.* FROM table1 t INNER JOIN ( SELECT t.id, SUM(tt.weight) AS cum_weight FROM table1 t INNER JOIN table1 tt ON tt.id <= t.id GROUP BY t.id) tc ON tc.id = t.id, ( SELECT SUM(weight) AS total_weight FROM table1) tt, ( SELECT RAND() AS rnd) r WHERE r.rnd * tt.total_weight <= tc.cum_weight ORDER BY t.id ASC LIMIT 1 

2) Unirse a una table secundaria para la ponderación

 SELECT t.* FROM table1 t INNER JOIN table1_weight w ON w.table1_id = t.id ORDER BY RAND() LIMIT 1 

SQL 1 toma consistentemente 0.4 segundos.

SQL 2 tarda entre 0.01 y 0.02 segundos.

Conclusión

Si la velocidad de selección de un logging ponderado random no es un problema, entonces el SQL de tabla única sugerido por van está bien y no tiene la sobrecarga de mantener una tabla separada.

Si, como en mi caso, un time de selección corto es crítico, entonces recomendaría el método de dos tablas.

PD: esta es mi primera publicación de StackOverflow y me ha tomado años, así que espero que alguien la encuentre útil.

Quizás este:

 SELECT * FROM <Table> T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM <Table> ) AS x ON T.ID >= x.ID LIMIT 1; 

O este:

 SELECT * FROM tablename WHERE somefield='something' ORDER BY RAND() LIMIT 1 

No recuerdo cómo RND () en mysql, pero aquí el ejemplo de trabajo para MSSQL:

 SELECT TOP(1) (weight +RAND ()) r, id, content, weight FROM Table ORDER BY 1 DESC 

Si TOP (1) no es aplicable, solo obtiene el primer logging del set de resultados total.