如何在SQLite全文列中按年份统计指定词的出现频率？

需求明确 #

基于给定的treatments主表和treatmentsFts FTS5虚拟表，需要：

• 统计指定单词/短语在fulltext列中按journalYear分组的出现频率，生成类似Google Ngram Viewer的折线图，并关联对应treatmentId
• 为提升查询性能构建TF/IDF表
• 确认内置bm25()函数和fts5vocab表的适用性（用于Node.js better-sqlite3应用）

fts5vocab表的适用性 #

fts5vocab完全适配需求：

• 它是FTS5内置的虚拟表，可直接提取索引中的词汇统计数据，包括每个词汇在单篇文档中的出现次数，正好满足TF（词频）计算需求
• 通过关联主表treatments，能快速按journalYear分组，并绑定对应的treatmentId

bm25()函数的使用说明 #

• bm25()是FTS5的内置排序函数，基于TF/IDF思想计算文档与查询的相关性得分，但不直接输出原始TF/IDF数值
• 如果仅需要相对频率用于折线图展示，bm25()得分可作为参考；但如果需要精确的TF/IDF数值，仍需通过fts5vocab或自定义逻辑计算

具体实现步骤 #

1. 确保FTS5表与主表同步 #

如果尚未关联主表，先配置自动同步并重建索引：

-- 设置FTS5表的内容来源为主表
ALTER VIRTUAL TABLE treatmentsFts SET content='treatments';
-- 重建索引以同步已有数据
INSERT INTO treatmentsFts(treatmentsFts) VALUES('rebuild');

2. 用fts5vocab统计单单词年度词频 #

查询指定单词按年份分组的出现次数，同时关联treatmentId：

SELECT
    t.journalYear,
    t.treatmentId,
    SUM(vocab.cols) AS termFrequency
FROM fts5vocab('treatmentsFts', 'row') vocab
JOIN treatments t ON vocab.rowid = t.id
WHERE vocab.word = 'formica' -- 替换为目标单词
GROUP BY t.journalYear, t.treatmentId
ORDER BY t.journalYear;

• fts5vocab('treatmentsFts', 'row')：按单篇文档统计词汇出现次数，cols列代表该词汇在当前文档中的出现次数

3. 预构建TF/IDF表（提升高频查询性能） #

创建专门的TF/IDF存储表，预计算并存储数据：

-- 创建TF/IDF存储表
CREATE TABLE treatmentTfIdf (
    treatmentId TEXT REFERENCES treatments(treatmentId),
    word TEXT,
    tf REAL,
    idf REAL,
    tfidf REAL,
    PRIMARY KEY(treatmentId, word)
);

-- 计算并插入指定单词的TF/IDF数据
WITH docCount AS (SELECT COUNT(*) AS totalDocs FROM treatments),
     wordDocCount AS (
         SELECT COUNT(DISTINCT rowid) AS docFreq FROM fts5vocab('treatmentsFts', 'row') WHERE word = 'formica'
     )
INSERT INTO treatmentTfIdf(treatmentId, word, tf, idf, tfidf)
SELECT
    t.treatmentId,
    'formica' AS word,
    -- TF：词频/文档总词数（简单按空格分割统计）
    vocab.cols / CAST(LENGTH(t.fulltext) - LENGTH(REPLACE(t.fulltext, ' ', '')) + 1 AS REAL) AS tf,
    -- IDF：总文档数/包含该词的文档数的对数
    LOG((SELECT totalDocs FROM docCount) / (SELECT docFreq FROM wordDocCount)) AS idf,
    tf * idf AS tfidf
FROM fts5vocab('treatmentsFts', 'row') vocab
JOIN treatments t ON vocab.rowid = t.id
WHERE vocab.word = 'formica';

4. 短语查询的词频统计 #

如果需要统计短语（如"formica rufa"）的出现频率，需用FTS5的短语匹配语法：

SELECT
    t.journalYear,
    t.treatmentId,
    COUNT(*) AS phraseFrequency
FROM treatments t
WHERE t.fulltext MATCH '"formica rufa"' -- 双引号包裹表示短语查询
GROUP BY t.journalYear, t.treatmentId;

5. Node.js better-sqlite3集成示例 #

const sqlite = require('better-sqlite3');
const db = sqlite('./your-database.db');

// 查询指定单词的年度总词频及关联treatmentId
const getTermFrequencyByYear = (term) => {
    const stmt = db.prepare(`
        SELECT
            t.journalYear,
            SUM(vocab.cols) AS totalFrequency,
            GROUP_CONCAT(t.treatmentId) AS relatedTreatmentIds
        FROM fts5vocab('treatmentsFts', 'row') vocab
        JOIN treatments t ON vocab.rowid = t.id
        WHERE vocab.word = ?
        GROUP BY t.journalYear
        ORDER BY t.journalYear;
    `);
    return stmt.all(term);
};

// 使用示例：获取"formica"的年度统计数据
const ngramData = getTermFrequencyByYear('formica');
console.log(ngramData); // 结果可直接用于生成交互式折线图

性能优化提示 #

• 若数据更新频繁，可设置触发器自动更新treatmentTfIdf表，避免手动重建
• 对于超大规模数据集，可按年份分区存储TF/IDF数据，进一步提升查询速度

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