Escribir una consulta compleja de MySQL

Nota: puedes encontrar mi pregunta anterior y su respuesta aquí – MySQL: Escribir una consulta compleja


Tengo 3 tablas.

Table Words_Learned contiene todas las palabras conocidas por un usuario y el order en que se aprendieron las palabras. Tiene 3 columnas 1) ID de palabra y 2) ID de usuario y 3) order en que se aprendió la palabra.

El Article tabla contiene los artículos. Tiene 3 columnas 1) ID del artículo, 2) conteo de palabras único y 3) contenido del artículo.

Table Words contiene una list de todas las palabras únicas contenidas en cada artículo. Tiene 2 columnas 1) ID de palabra y 2) ID de artículo

El diagtwig de la database es el siguiente /

enter image description here

Puede download el código de DB desde aquí: https://www.dropbox.com/s/3gr659y5mk05i5w/tests.sql?dl=0

Ahora, usando esta database y usando "solo" mysql, necesito hacer el siguiente trabajo.

Dado un ID de usuario, debería get una list de todas las palabras conocidas por este usuario, orderadas en el order reverente del que fueron aprendidas. En otras palabras, las palabras aprendidas más recientes estarán en la parte superior de la list.

Digamos que una consulta en una identificación de usuario muestra que han memorizado las siguientes 3 palabras, y seguimos el order en el que aprendieron las palabras. Pulpo – 3 perros – 2 cucharas – 1

Primero obtenemos una list de todos los artículos que contienen la palabra Octopus, y luego hacemos el cálculo usando la tabla Words en solo esos artículos. Cálculo significa que si ese artículo contiene más de 10 palabras que no aparecen en la list de vocabulario del usuario (extraído de la tabla words_learned ), entonces se excluye de la list.

Luego, hacemos una consulta para todos los loggings que contienen perro, pero NO contienen "pulpo"

Luego, hacemos una consulta para todos los loggings que contienen cuchara, pero NO contienen las palabras Octopus o Dog

Y sigues haciendo este process repetitivo hasta que hayamos encontrado 100 loggings que cumplen con este criterio.

Para lograr este process, hice lo siguiente

 SELECT `words_learned`.`idwords`, Words.`idArticle` FROM words_learned INNER JOIN Words ON Words.idWords = Words_Learned.`idwords` WHERE words_learned.userId = 1 ORDER BY Words_Learned.`order` DESC 

En mi consulta, he cubierto para get los artículos, lo que significa que aquí – First we get a list of all articles containing the word Octopus, and then do the calculation using table Words on just those articles. . Pero, ¿qué debería hacer para cubrir el rest?

Actualizar

Aquí hay un phudocode para una mejor comprensión.

 Do while articles found < 100 { for each ($X as known words, in order that those words were learned) { Select all articles that contain the word $X, where the 1) article has not been included in any previous loops, and 2)where the count of "unknown" words is less than 10. Keep these articles in order. } } 

Me sentiría tentado de tener una sub consulta que obtenga todas las palabras que una persona aprendió y únete a ella en contra de sí misma, con las palabras GROUP_CONCAT juntas junto con un conteo. Así que dando: –

 Octopus, NULL, 0 Dog, "Octopus", 1 Spoon, "Octopus,Dog", 2 

Entonces la consulta secundaria sería algo así como:

 SELECT sub0.idwords, GROUP_CONCAT(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERE sub0.userId = 1 GROUP BY sub0.idwords 

dando

 idwords excl_words older_words_cnt 1 NULL 0 2 1 1 3 1,2 2 

Luego, una los resultados de esto con las otras tablas, revisando los artículos donde coinciden las idwords principales pero no se encuentra ninguna de las otras.

Algo como esto (aunque no probado como no hay datos de testing): –

 SELECT sub_words.idwords, words_inc.idArticle ( SELECT sub0.idwords, SUBSTRING_INDEX(GROUP_CONCAT(sub1.idwords), ',', 10) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERE sub0.userId = 1 GROUP BY sub0.idwords ) sub_words INNER JOIN words words_inc ON sub_words.idwords = words_inc.idwords LEFT OUTER JOIN words words_exc ON words_inc.idArticle = words_exc.idArticle AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words) WHERE words_exc.idwords IS NULL ORDER BY older_words_cnt LIMIT 100 

EDITAR: se actualizó para excluir artículos con más de 10 palabras que aún no se aprendieron.

 SELECT sub_words.idwords, words_inc.idArticle, sub2.idArticle, sub2.count, sub2.content FROM ( SELECT sub0.idwords, GROUP_CONCAT(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERE sub0.userId = 1 GROUP BY sub0.idwords ) sub_words INNER JOIN words words_inc ON sub_words.idwords = words_inc.idwords INNER JOIN ( SELECT a.idArticle, a.count, a.content, SUM(IF(c.idwords_learned IS NULL, 1, 0)) AS unlearned_words_count FROM Article a INNER JOIN words b ON a.idArticle = b.idArticle LEFT OUTER JOIN words_learned c ON b.idwords = c.idwords AND c.userId = 1 GROUP BY a.idArticle, a.count, a.content HAVING unlearned_words_count < 10 ) sub2 ON words_inc.idArticle = sub2.idArticle LEFT OUTER JOIN words words_exc ON words_inc.idArticle = words_exc.idArticle AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words) WHERE words_exc.idwords IS NULL ORDER BY older_words_cnt LIMIT 100 

EDITAR – bash de comentar la consulta anterior: –

Esto solo selecciona las columnas

 SELECT sub_words.idwords, words_inc.idArticle, sub2.idArticle, sub2.count, sub2.content FROM 

Esta sub consulta obtiene cada una de las palabras aprendidas, junto con una list de palabras separadas por comas con un order_aprendido mayor. Esto es para una identificación de usuario particular

 ( SELECT sub0.idwords, GROUP_CONCAT(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERE sub0.userId = 1 GROUP BY sub0.idwords ) sub_words 

Esto es solo para get los artículos de las palabras (es decir, las palabras aprendidas de la sub consulta anterior) se utilizan en

 INNER JOIN words words_inc ON sub_words.idwords = words_inc.idwords 

Esta sub consulta obtiene los artículos que tienen less de 10 palabras en ellos que aún no ha aprendido el usuario en particular.

 INNER JOIN ( SELECT a.idArticle, a.count, a.content, SUM(IF(c.idwords_learned IS NULL, 1, 0)) AS unlearned_words_count FROM Article a INNER JOIN words b ON a.idArticle = b.idArticle LEFT OUTER JOIN words_learned c ON b.idwords = c.idwords AND c.userId = 1 GROUP BY a.idArticle, a.count, a.content HAVING unlearned_words_count < 10 ) sub2 ON words_inc.idArticle = sub2.idArticle 

Esta unión es para encontrar artículos que tienen palabras en la list separada por comas de la primera consulta secundaria (es decir, palabras con un order más grande aprendido). Esto se hace como una UNIÓN EXTERIOR IZQUIERDA ya que quiero excluir cualquier palabra que se encuentre (esto se hace en la cláusula WHERE marcando para NULL)

 LEFT OUTER JOIN words words_exc ON words_inc.idArticle = words_exc.idArticle AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words) WHERE words_exc.idwords IS NULL ORDER BY older_words_cnt LIMIT 100 

Entonces, creo que esto es todo. Desea get los "mejores" 100 artículos, donde "lo mejor" significa que cuanto más tarde se encuentre una palabra que contenga, mejor. Así que busco la última palabra aprendida de cada artículo (el máximo (words_learned.order) por artículo). Luego muestro los ID del artículo en ese order y me detengo en 100.

 select w.idarticle, max(l.`order`) from words w join words_learned l on l.idwords = w.idwords and l.userid = 123 group by w.idarticle order by max(l.`order`) desc limit 100; 

Usted ha editado su request. Desea limitar los resultados a artículos que no contengan más de diez palabras desconocidas. Para hacerlo, ahora debes unir las palabras aprendidas, para que puedas contar las palabras desconocidas (es decir, los loggings externos). Use HAVING para eliminar los artículos no deseados de la list.

 select w.idarticle, max(l.`order`) from words w left join words_learned l on l.idwords = w.idwords and l.iduser = 123 group by w.idarticle having sum(l.idwords is null) <= 10 and max(l.`order`) is not null order by max(l.`order`) desc limit 100; 

He leído esa pregunta nuevamente y me doy count de que es mucho más complicada.

Antes que nada, quieres mostrar palabras. Y si muestra una palabra depende de esa palabra y de todas las palabras aprendidas anteriormente (y de los artículos en los que aparecen).

Entonces con estas palabras aprendidas:

 order de las palabras
 Octopus 3
 Perro 2
 Cuchara 1 (iefirst aprendido)

Y estos artículos:

 el artículo contiene Octopus contiene Dog contiene cuchara palabras desconocidas
 A si si si 5
 B sí sí no 11
 C sí no sí 15
 D no si si 2
 E no sí no 0
 F no no sí 8
 G no no no 3
 H no no no 20

Usted …

  • marque "Octupus" y deséchelo por el artículo B o C.
  • marque "Perro" y guárdelo, porque los artículos D y E están bien y B debe ignorarse (ya que contiene "Pulpo").
  • marque "Cuchara" y guárdela, el artículo F está bien y C debe ignorarse (ya que contiene "Pulpo").

Entonces muestras "Perro" y "Cuchara" y no "Pulpo". Y si no solo hubiera dos partidos, sino miles, mostrarías los primeros 100 y luego te detendrías.

Dado este algorithm, podemos concluir:

  • Mientras se aprendieron muy pocas palabras, no se mostrarán resultados en absoluto.
  • En algún momento, se aprenderán suficientes palabras para encontrar artículos con less de 11 palabras desconocidas. Las últimas palabras aprendidas probablemente no se mostrarán (como "Octopus" en el ejemplo), porque todavía hay muchos artículos con demasiadas palabras desconocidas. Pero las palabras anteriores se mostrarán (porque las últimas palabras aprendidas filtran los artículos difíciles de leer).
  • Entonces, algún día, la mayoría de las palabras serán aprendidas. Entonces son las últimas palabras aprendidas que se mostrarán.

La consulta:

 select idwords from words_learned where userid = 123 and not exists ( select w.idarticle from words w left join words_learned l on l.idwords = w.idwords and l.userid = 123 group by w.idarticle having sum(l.idwords is null) > 10 and max(l.`order`) = words_learned.`order` ) order by `order` desc limit 100; 

Aquí hay un violín de SQL: http://sqlfiddle.com/#!2/19bf8/1 .