主页 / dba / 问题 / 56726
Accepted
Tiago Stapenhorst Martins
Asked: 2014-01-14 20:58:54 +0800 CST

如何将类似的驼峰式、帕斯卡式、下划线、下划线单词映射到相同的 id？

  • 772

我在 PostgreSQL 上工作，我有一个这样的表，叫做 words_table：
表表

我需要所有等效词（如 software_design 和 software-design）使其映射值 = 到其词的等效 id_word。换句话说，我希望所有单词 softwaredesign、software_design、software.design... 等在其映射列上具有相同的编号（在本例中，编号为 1 到 10）。这同样适用于土木工程和工业设计。

我知道这涉及一些正则表达式和不区分大小写的比较，但我坚持使用 SQL 逻辑。我知道这些表达式可能有用：

regexp_replace(word, '(\.|:|,|&|-)','','g')

处理分隔符

lower(something)

处理大写小写匹配或

UPDATE words_table SET a.map = b.id_word WHERE word ILIKE something

... WHERE word ~* something

用于不区分大小写的匹配。

我应该用 regexp_replaced 词创建新列，然后用一些连接进行映射吗？或者可能是 CamelCase 和下划线匹配？也许功能？哪个是最优解？

postgresql update

3 个回答

  1. Best Answer

    数据库设计

    要么您已经有了查找表但忘了提及它，要么您应该创建一个。

    CREATE TABLE map (
  map_id int PRIMARY KEY
 ,map    text UNIQUE NOT NULL
);

-- And that's how I would shape your original table:
CREATE TABLE word (
  word_id serial PRIMARY KEY
 ,map_id  int REFERENCES map(map_id)
 ,word    text
);

    这里和这里就是为什么我通常更text喜欢varchar(n).
    这就是为什么我将两integer列放在表中的第一位word。

    INSERT INTO map(map_id, map) VALUES
  (1, softwaredesign)
 ,(2, civilengineering)
 ,(3, industrialdesign)
 , ... ;

    地图数据

    定义一个像@Daniel 已经建议的函数。

    根据您的具体要求，我会尽可能在正则表达式中使用预定义的字符类，而不是自己滚动。Postgres 使用来自操作系统的区域设置信息来识别字符、数字等。

    [:alnum:]包括所有数字和数字，具体取决于您的区域设置。与[a-zA-Z0-9]此不同的是，它还标识ä或é作为字符 - 取决于您的语言环境。

    [^[:alnum:]]
    是否定，即所有其他字符。
    还有类似的类速记\W，但会包括下划线_。

    CREATE FUNCTION map_word(text) RETURNS text AS
$$SELECT lower(regexp_replace($1, '[^[:alnum:]]', '', 'g'))$$
LANGUAGE sql IMMUTABLE;

    此更新将是最有效的：

    UPDATE word w
SET    map_id = m.map_id
FROM   map m
WHERE  m.map = map_word(w.word)
AND    w.map_id IS DISTINCT FROM m.map_id -- avoid empty updates
    • 3

  2. 您可以将其分解为两个较小的问题。

    首先创建一个函数来简化单词。此版本仅删除任何非字母数字字符并将结果设置为小写。如有必要，请对其进行优化。

    CREATE FUNCTION simplify(text) RETURNS text AS
$$ SELECT lower(regexp_replace($1, '[^a-zA-Z0-9]', '', 'g')); $$
LANGUAGE sql;

    接下来，map通过连接表本身来更新列，将每个单词与其“简化”版本匹配。

     UPDATE words_table w 
    set map=w2.id_word
 FROM words_table w2
     WHERE w2.word=simplify(w.word);

    这假设一个单词的任何简化版本已经是表格的一部分，就像您的示例数据中的情况一样。否则它们应该被插入。

    • 2

  3. 在小型数据集上，您可以使用每次需要时计算导数的函数，但在大型连接上，将为每一行一次又一次地调用该函数。这会降低整体性能。

    更有效的方法是存储预先计算好的导数。一种方法是将它们与原始字符串一起存储，但您会得到一个需要索引的低基数字段。另一种方法是维护连接中使用的导数中间表以粘合其他表。

    • 1

相关问题