Início / dba / Perguntas / 56726
Accepted
Tiago Stapenhorst Martins
Asked: 2014-01-14 20:58:54 +0800 CST

Como mapear palavras semelhantes em camelcase, pascalcase, sublinhado e com pontuação baixa para o mesmo id?

  • 772

Estou trabalhando no PostgreSQL e tenho uma tabela como esta chamada words_table:
Mesa de mesa

Eu preciso de todas as palavras equivalentes (como software_design e software-design) para ter seu valor de mapa = para id_word equivalente de sua palavra. Em outras palavras, quero que todas as palavras softwaredesign, software_design, software.design... e assim por diante tenham o mesmo número na coluna do mapa (neste caso específico, um número de 1 a 10). O mesmo se aplica à engenharia_civil e ao Desenho Industrial.

Eu sei que isso envolve algumas expressões regulares e comparações que não diferenciam maiúsculas de minúsculas, mas estou preso na lógica SQL. Eu sei que essas expressões podem ser úteis:

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

Para lidar com os separadores

lower(something)

Para lidar com a correspondência de maiúsculas e minúsculas ou

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

... WHERE word ~* something

para correspondência insensível a maiúsculas e minúsculas.

Devo criar novas colunas com palavras regexp_replaced e fazer o mapeamento depois disso com algumas junções? Ou talvez algo com CamelCase e correspondência de sublinhado? Talvez funções? Qual é a solução ótima?

postgresql update

3 respostas

  1. Best Answer

    Projeto de banco de dados

    Ou você já tem a tabela de consulta e esqueceu de mencioná-la, ou deve criar uma.

    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
);

    Aqui e aquitext é porque eu normalmente prefiro varchar(n).
    É por isso que coloquei as duas integercolunas primeiro na tabela word.

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

    dados do mapa

    Defina uma função como @Daniel já sugeriu .

    Dependendo de seus requisitos exatos, eu usaria classes de caracteres predefinidas sempre que possível em uma expressão regular, em vez de lançar minhas próprias. O Postgres usa informações de localidade do seu sistema operacional para identificar caracteres, dígitos, etc.

    [:alnum:]inclui todos os números e dígitos, dependendo da sua localidade. Ao contrário , [a-zA-Z0-9]isso também identifica äou écomo caracteres - dependendo da sua localidade.

    [^[:alnum:]]
    é a negação, ou seja, todos os outros personagens.
    Há também a abreviação de classe\W semelhante , mas isso incluiria o sublinhado _.

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

    Esta atualização seria mais eficiente então:

    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. Você pode dividi-lo em dois problemas menores.

    Primeiro crie uma função que faça a simplificação da palavra. Esta versão apenas remove qualquer caractere não alfanumérico e define o resultado em letras minúsculas. Refine-o, se necessário.

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

    Em seguida, atualize a mapcoluna juntando a tabela nela mesma, combinando cada palavra com sua versão "simplificada".

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

    Isso pressupõe que qualquer versão simplificada de uma palavra já faz parte da tabela, como parece ser o caso em seus dados de amostra. Caso contrário, eles devem ser inseridos.

    • 2

  3. Nos pequenos conjuntos de dados, você pode usar funções que calculam derivadas sempre que precisar delas, mas em grandes junções essa função será invocada para cada linha repetidas vezes. Isso pode diminuir o desempenho geral.

    Mais eficiente é armazenar derivadas pré-calculadas. Uma maneira é armazená-los junto com as strings originais, mas você obterá um campo de baixa cardinalidade que precisa ser indexado. A outra abordagem é manter a tabela intermediária de derivadas usadas em junções para colar outras tabelas.

    • 1

relate perguntas