存储表达式及其在文本中出现的数据库模型

除非你有数百万个单词,否则仅仅存储它们的前缀似乎是个糟糕的计划。

为了将新数据添加到表中,您只需编写一个临时表,其中包含传入的单词,然后在导入运行结束时一次性聚合并合并这些单词。也就是说,类似于:

BEGIN;
CREATE TEMP TABLE word_stage(word text) ON COMMIT DROP;
COPY word_stage FROM stdin;
-- use pgputcopydata to send all the words to the db...
SET work_mem = 256MB; -- use lots of memory for this aggregate..
CREATE TEMP TABLE word_count_stage AS
    SELECT word, count(*) as occurrences
    FROM word_stage
    GROUP BY word;
-- word should be unique, check that and maybe use this index for merging
ALTER TABLE word_count_stage ADD PRIMARY KEY(word);
-- this UPDATE/INSERT pair is not comodification-safe
LOCK TABLE word_count IN SHARE ROW EXCLUSIVE MODE;
-- now update the existing words in the main table
UPDATE word_count
SET word_count.occurrences = word_count.occurrences + word_count_stage.occurrences,
    word_count.min_occurrences = least(word_count.occurrences, word_count_stage.occurrences),
    word_count.max_occurrences = greatest(word_count.occurrences, word_count_stage.occurrences)
FROM word_count_stage
WHERE word_count_stage.word = word_count.word;
-- and add the new words, if any
INSERT INTO word_count(word, occurrences, min_occurrences, max_occurrences)
  SELECT word, occurrences, occurrences, occurrences
  FROM word_count_stage
  WHERE NOT EXISTS (SELECT 1 FROM word_count WHERE word_count.word = word_count_stage.word);
END;

因此,这将聚合一批单词,然后将它们应用于单词计数表。有索引的 word_stage(word) word_count(word) word_count . (尽管指定了一个较低的填充因子,比如60左右 字数

如果您的输入实际上是单词/事件对,而不仅仅是单词(您的文本不是很清楚),那么您可以去掉首字母 word_stage 然后复制到 word_count_stage .

说真的,至少在开始的时候,我会尝试把整个单词作为一个键——你引用的数字在可用性的范围之内。另外请注意,我上面概述的加载方法可以很容易地修改为将单词截断为前两个字符(或者以任意方式将其转换为内存键),或者在数据移动到内存时进行转换 字数阶段 或者在最后将转换放到update/insert语句中(尽管这样可能会失去在temp表上建立索引的好处)。