在sqlite中如何查找一列中出现最频繁的单词？

Question

我有这样的数据:

            movie_id    comment
            1           tom cruise is great
            1           great action movie
            2           got teary eyed
            2           great cast
            1           tom cruise is hott

我希望有一个函数，根据我选择的电影ID返回评论中最常见的单词。因此，如果我查询movie_id=1，我应该得到：

            tom, 2
            cruise, 2
            is, 2
            great, 2
            hott, 1
            action, 1
            movie, 1

如果我查询的电影ID是2，那么我会得到如下结果：

            got, 1
            teary, 1
            eyed, 1
            great, 1
            cast, 1

我看到一些使用tsql的解决方案，但我以前从未使用过，也不理解代码。寻找在sqlite3中完成此操作的方法。

- user1956609

---

SQLite 中没有 Split 函数或等效函数，因此无法在简单查询中完成此操作，您可能需要编写一些代码。也许可以使用 Subtr() 函数来处理。 - Scotch

Answer 1

你可以使用一个非常丑陋的查询来完成此操作。

select word, count(*) from (
select (case when instr(substr(m.comments, nums.n+1), ' ') then substr(m.comments, nums.n+1)
             else substr(m.comments, nums.n+1, instr(substr(m.comments, nums.n+1), ' ') - 1)
        end) as word
from (select ' '||comments as comments
      from m
     )m cross join
     (select 1 as n union all select 2 union all select 3
     ) nums
where substr(m.comments, nums.n, 1) = ' ' and substr(m.comments, nums.n, 1) <> ' '
) w
group by word
order by count(*) desc

这段代码尚未经过测试。内部查询需要一个数字列表（这里仅限于3个，您可以看到如何添加更多数字）。然后它检查单词是否从n+1位置开始。一个单词从一个空格后开始，所以我在注释开头加了一个空格。

然后为了聚合目的，它提取了这个单词。

- Gordon Linoff

---

M应该是电影ID。那么nums是什么？ - user1956609

@user1956609……Nums是一个仅包含从1开始的整数的表。我正在使用它来测试对注释的偏移量，以确定一个单词是否开始。在这种情况下，它只有3个数字。您可能需要更多，如50或100。您可能已经有一个数字表可用（比如另一个表中的主键），您可以使用该表代替。 - Gordon Linoff

天啊，我已经在sqlite3中尝试了instr和charindex，但都没有被识别为函数。有没有其他方法可以获得这个功能，或者我需要添加自定义函数？ - user1956609

@user1956609...这可能取决于版本，但在这里有记录...http://www.sqlite.org/lang_corefunc.html。 - Gordon Linoff

好的，我更新了我的sqlite版本并成功运行，但是没有返回任何结果。 我对instr / substr不熟悉，很难理解其中一些操作的意义。 有可能是打错字吗？ 我不确定'as word'或'w'在做什么。 - user1956609

Answer 2

这是一个hack，但你也可以使用SQLite的表值函数来解决这个问题：

select value from (
    select
        '["' || replace(trim(title), '"', '') || '"]' as arr
    from story limit 2500000
    ) e, json_each(e.arr)

注意 - 你可能会发现你的列中有很多字符，这些字符可能会破坏JSON解析并引发JSON解析错误。

在这种情况下，你可能需要删除一些控制字符：

select value from (select
'["' || replace(replace(replace(replace(replace(replace(trim(title), '"', ''), CHAR(10), ""), CHAR(13), ''), "  ", ""), "\", ""), ' ', '","') 
|| '"]' as arr
from your_table limit 2500000
) e, json_each(e.arr)

为了更方便调试，您可以使用限制和偏移量的组合（例如limit 1000000 offset 2200000）来帮助您找到问题行，然后用空字符串替换该字符。

- oxuser