Início / dba / Perguntas / 310208
Accepted
Ron Piggott
Asked: 2022-03-27 17:56:12 +0800 CST

Selecione as linhas onde a matriz de letras está contida na matriz fornecida

  • 772

Quero pesquisar as letras na text[]coluna "ortografia" ( ): annoyt

Preciso dele para encontrar as palavras: any , annoy , no , an , toy Mas não encontrar: derivados de chateado ( irritante , aborrecimento ), só não encontro uma vez.

Eu também preciso que a consulta NÃO encontre aborrecimento se estiver faltando uma única letra (como anoyt ).

Estou usando o PostgreSQL 13.5

ronshome=# SELECT reference, word, spelling FROM word_mash_dictionary
           WHERE word LIKE 'annoy';
 reference | word  |  spelling
-----------+-------+-------------
       420 | annoy | {a,n,n,o,y}
(1 row)

Esta é a estrutura da tabela:

                                                      Table "public.word_mash_dictionary"
   Column    |  Type  | Collation | Nullable |                         Default                         | Storage  | Stats target | Description
-------------+--------+-----------+----------+---------------------------------------------------------+----------+--------------+-------------
 reference   | bigint |           | not null | nextval('word_mash_dictionary_reference_seq'::regclass) | plain    |              |
 word        | text   |           |          |                                                         | extended |              |
 spelling    | text[] |           |          |                                                         | extended |              |
 ignore      | bigint |           |          |                                                         | plain    |              |
 list_100    | bigint |           |          |                                                         | plain    |              |
 list_300    | bigint |           |          |                                                         | plain    |              |
 list_500    | bigint |           |          |                                                         | plain    |              |
 list_800    | bigint |           |          |                                                         | plain    |              |
 list_1000   | bigint |           |          |                                                         | plain    |              |
 list_2000   | bigint |           |          |                                                         | plain    |              |
 list_3000   | bigint |           |          |                                                         | plain    |              |
 list_5000   | bigint |           |          |                                                         | plain    |              |
 list_7000   | bigint |           |          |                                                         | plain    |              |
 list_10000  | bigint |           |          |                                                         | plain    |              |
 word_length | bigint |           |          |                                                         | plain    |              |
postgresql select

1 respostas

  1. Best Answer

    O operador "está contido" <@para matrizes geralmente faz isso:

    SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,n,o,y,t}'::text[];

    Isso pode ser suportado com um índice GIN no array, o que o torna rápido para tabelas grandes. Curti:

    CREATE INDEX ON word_mash_dictionary USING gin (spelling);

    No entanto , um elemento na matriz de pesquisa abrange qualquer número de correspondências em spelling. Assim '{a,n,o,y}'encontraria '{a,n,n,o,y}'etc. Falsos positivos para palavras com letras repetidas.

    Uma operação set com EXCEPT ALLseria exata (considere cada cópia do mesmo elemento separadamente). Embrulhado em uma função personalizada:

    CREATE OR REPLACE FUNCTION f_arr_is_contained(arr1 text[], arr2 text[])
  RETURNS bool
  LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
DECLARE
BEGIN
   PERFORM unnest(arr1) EXCEPT ALL SELECT unnest(arr2);

   RETURN NOT FOUND;
END
$func$;

    Se cada letra for coberta no segundo termo, nenhuma linha será retornada e FOUNDserá falsa. Então volte NOT FOUND.

    Eu escolhi LANGUAGE plpgsqlporque a função não pode ser "embutida" de qualquer maneira, então o plpgsql pode ser mais rápido. Você pode testar a alternativa equivalente com LANGUAGE plpgsql:

    CREATE OR REPLACE FUNCTION f_arr_is_contained_sql(arr1 text[], arr2 text[])
  RETURNS bool
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT NOT EXISTS (SELECT unnest (arr1) EXCEPT ALL SELECT unnest (arr2))';

    A função não pode usar nenhum índice , no entanto, o que levaria a verificações sequenciais caras em toda a tabela.

    Combine ambos para ser rápido e exato :

    SELECT reference, word, spelling
FROM   word_mash_dictionary
WHERE  spelling <@ '{a,n,o,y,t}'::text[]
AND    f_arr_is_contained(spelling, '{a,n,o,y,t}'::text[]);

    db<>fique aqui

    O primeiro predicado encontra todas as correspondências (e possivelmente alguns falsos positivos) rapidamente com suporte a índice; o segundo predicado elimina os (poucos!) falsos positivos.

    De lado, worde spellingprovavelmente deve ser declarado NOT NULL.

    • 2

relate perguntas