如何基于Thinking Sphinx获取索引关键词的统计数据？

嘿，刚好我之前也碰到过类似的统计需求，基于你已经搭好的Sphinx+MySQL环境，给你分享两个实用的实现方案：

1. 统计单个关键词/短语的总出现次数（支持时间区间过滤） #

无时间区间限制的快速统计 #

如果不需要限定时间范围，直接用Sphinx内置的CALL KEYWORDS命令就能拿到全局总出现次数，这是最高效的方式：

-- 单个关键词
CALL KEYWORDS('your_target_keyword', 'your_article_index', 1);

-- 短语（需要用双引号包裹）
CALL KEYWORDS('"your_target_phrase"', 'your_article_index', 1);

执行后返回的结果里，hits字段就是该关键词/短语在整个索引中的总出现次数。

带时间区间的统计 #

如果要限定X到Y的时间范围，需要先筛选出该区间内的文档，再统计关键词的出现次数。可以用Sphinx的SELECT语句结合字符串长度差计算：

SELECT 
  SUM(
    (LENGTH(content) - LENGTH(REPLACE(content, 'your_keyword', ''))) 
    / LENGTH('your_keyword')
  ) AS total_hits
FROM your_article_index
-- 替换成你的时间字段和目标时间区间（Sphinx通常用UNIX时间戳）
WHERE created_at BETWEEN UNIX_TIMESTAMP('2023-01-01 00:00:00') 
  AND UNIX_TIMESTAMP('2023-12-31 23:59:59')
-- 先过滤出包含关键词的文档，提升效率
AND MATCH('your_keyword');

原理是通过计算替换关键词前后的内容长度差，除以关键词本身的长度，得到单篇文档的出现次数，再用SUM()求和得到总次数。短语统计的话，把'your_keyword'换成'your_target_phrase'即可。

2. 统计“x AND y”组合的总出现次数 #

这里分两种场景：一种是统计同时包含x和y的文档中，x和y各自的出现次数总和；另一种是统计x和y作为连续短语的出现次数，分别给你方案：

场景1：同时包含x和y的文档中，两者的总出现次数 #

用MATCH('x AND y')筛选出符合条件的文档，再分别统计x和y的次数后相加：

SELECT 
  -- x的总出现次数
  SUM(
    (LENGTH(content) - LENGTH(REPLACE(content, 'x', ''))) / LENGTH('x')
  ) AS x_total_hits,
  -- y的总出现次数
  SUM(
    (LENGTH(content) - LENGTH(REPLACE(content, 'y', ''))) / LENGTH('y')
  ) AS y_total_hits,
  -- 两者的总次数
  (
    SUM((LENGTH(content) - LENGTH(REPLACE(content, 'x', ''))) / LENGTH('x')) 
    + SUM((LENGTH(content) - LENGTH(REPLACE(content, 'y', ''))) / LENGTH('y'))
  ) AS combined_total_hits
FROM your_article_index
-- 可选：添加时间区间过滤
WHERE created_at BETWEEN UNIX_TIMESTAMP('2023-01-01') AND UNIX_TIMESTAMP('2023-12-31')
AND MATCH('x AND y');

场景2：x和y作为连续短语的出现次数 #

如果要统计“x y”这个连续短语的出现次数，直接把短语用双引号包裹，参考第一个需求的方法即可：

-- 全局统计
CALL KEYWORDS('"x y"', 'your_article_index', 1);

-- 带时间区间统计
SELECT 
  SUM(
    (LENGTH(content) - LENGTH(REPLACE(content, 'x y', ''))) 
    / LENGTH('x y')
  ) AS phrase_total_hits
FROM your_article_index
WHERE created_at BETWEEN UNIX_TIMESTAMP('2023-01-01') AND UNIX_TIMESTAMP('2023-12-31')
AND MATCH('"x y"');

注意事项 #

• 确保你的Sphinx索引配置中，要统计的字段（比如content）是可存储的：在sphinx.conf里设置sql_field_text = content（或sql_field_string），并且stored_fields包含该字段，否则无法在SELECT中进行字符串操作。
• 如果是中文关键词/短语，要确保Sphinx配置了正确的中文分词器（比如mmseg），否则短语匹配和统计会不准确。
• 特殊字符需要提前转义，避免在MATCH()中出现语法错误。

内容的提问来源于stack exchange，提问作者rails9231