MYSQL按短语中的每个单词分组以计数/量化比率

Question

我有一个包含如下数据的 MYSQL/MariaDB 表:

language      phrase              tries   success
-----------   ---------------     -----   -------
Spanish       hola como estas       5        3
Spanish       como estas tu         3        1
Spanish       adios me voy          2        0
Spanish       adios me voy          1        0
Spanish       adios me voy          2        1
Spanish       como voy              3        2
English       hello how are you     5        2
English       hello who are you     3        0
English       how good are you      5        1
English       be good               3        3
French        au revoir             4        0
French        merci beaucoup        2        1
French        merci beaucoup        5        2
French        beaucoup des choses   2        2
French        voir et revoir        3        2

我想对每种语言和短语中包含的每个单词进行分组，通过对尝试次数和成功次数求和然后计算它们之间的比率来量化每个单词的“成功”，例如，在“西类牙语”中，“como”一词出现在 3 行中，总计 11 次尝试和 6 次成功，因此西类牙语中“como”的“比率”为 0.54，并对每个单词执行此操作，所以我会以这样的结尾:

language   word    tries  successes  ratio
--------   ------  -----  ---------  -----
spanish    hola      5        3       0.60
spanish    como     11        6       0.54
spanish    estas     8        4       0.54
spanish    tu        3        1       0.33
spanish    adios     5        1       0.20
spanish    me        5        1       0.20
spanish    voy       8        3       0.38
english    hello     8        2       0.25
english    how       10       3       0.30
english    are       13       3       0.23
english    you       13       3       0.23
english    who       3        0       0
english    good      8        4       0.54
english    be        3        3       1
french     au        4        0       0
french     revoir    7        2       0.29
french     merci     7        3       0.43
french     beaucoup  9        5       0.55
french     des       2        2       1
french     choses    2        2       1 
french     voir      3        2       0.66
french     et        3        2       0.66

我不知道如何在 SQL 中完成此任务。

仅供引用，真实表格包含大约 50,000 行，短语可以包含 1 到 7 个单词，最常见的是 2-4 个单词

Answer 1

您必须首先遍历字符串中的单词。您可以使用 SQL 中的递归查询来执行此操作。剩下的就是聚合。

with recursive words (language, word, tries, success, rest) as
(
  select
    language,
    substring_index(concat(trim(phrase), ' '), ' ', 1) as word,
    tries,
    success,
    substring(trim(phrase), instr(concat(trim(phrase), ' '), ' ') + 1) as rest
  from mytable
  union all
  select
    language,
    substring_index(concat(rest, ' '), ' ', 1) as word,
    tries,
    success,
    substring(rest, instr(concat(rest, ' '), ' ') + 1) as rest
  from words
  where rest <> ''
)
select
  language,
  word,
  sum(tries) as sum_tries,
  sum(success) as sum_success,
  sum(success) / sum(tries) as ratio
from words
group by language, word
order by language, word;

演示:https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=dd68b85cc7478697edebd0a9abd5b87f

这里解释了 MariaDB 中的递归查询:https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/