Início / dba / Perguntas / 239958
Accepted
Vérace
Asked: 2019-06-07 04:07:40 +0800 CST

Contagem de palavras em um campo (todos e únicos) - existe uma maneira mais elegante / ideal?

  • 772

Respondendo a esta pergunta ,

Dada esta tabela (construída a partir da pergunta):

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

produza este resultado:

     id  | word_count | unique_word_count |  Description                        
---------+------------+-------------------+---------------
     1   |  4         | 4                 | What a great day
     2   |  7         | 6                 | This is a product. It is useful

Eu dei a resposta (correta), que você pode encontrar aqui .

No entanto, em um comentário, o OP fez outra pergunta - e se a string em questão fosse ['a', ' ', ' ', 'b']e minha solução quebrasse completamente - para começar, a string nem entraria INSERTna tabela.

Então, a questão agora é, como se lida com strings como esta - ou seja, com apóstrofos, colchetes etc. Vou dar minha própria resposta e também oferecer um bônus por uma solução mais elegante.

Soluções com múltiplas opções serão altamente valorizadas, assim como aquelas que mostrarem evidências de "pensar fora da caixa" (desculpe o clichê - mas cabe aqui! :-) ). Eu também vou dar uma explicação detalhada do meu raciocínio - que vai ganhar elogios também! As opções que mencionam outros servidores também ganharão mérito. Obviamente, só posso conceder o bônus a uma pessoa, mas votarei positivamente em todas as respostas decentes.

Só posso oferecer um bônus em dois dias - então postarei minha resposta e oferecerei o bônus (+100) quando puder. Além disso, quaisquer soluções que lidem com strings com as quais eu não possa lidar - ainda não foram exaustivamente testadas.

postgresql query-performance

2 respostas

  1. O primeiro passo obviamente é criar a tabela e os dados (conforme a pergunta mencionada ):

    CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ($$What a great day$$);
INSERT INTO wordcount (description) VALUES ($$This is a product. It is useful$$);
INSERT INTO wordcount (description) VALUES ($$['a', ' ', ' ', 'b']$$);

    O primeiro "salva-vidas" foi a cotação do dólar ( $$) - um recurso realmente interessante do PostgreSQL. Eu estava realmente me debatendo antes de me deparar com isso - não conseguia nem colocar os dados na tabela (tentando barras invertidas, aspas duplas etc.)

    Meu SQL final se parece com isso (fiddle aqui ):

    WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT id,
       COUNT(word),
       COUNT(DISTINCT(word)),
       description
FROM cte1
WHERE LENGTH(word) > 0
GROUP BY id, description
ORDER BY id;

    Resultado:

    id  Word_count  Distinct_count  description
 1           4               4    What a great day
 2           7               6    This is a product. It is useful
 3           2               2    ['a', ' ', ' ', 'b']


    Lógica explicada:

    Eu decidi não me preocupar com capitalização - ou seja, "It" e "it" são palavras diferentes neste caso - se isso for um problema, a simples adição de uma UPPER()função resolveria isso - não é o núcleo da questão.

    Passo 1:

    SELECT id, REGEXP_SPLIT_TO_TABLE(description, ',') FROM wordcount;
-- Keeping the id field helps clarity, even if superfluous.

    Resultado:

    id  regexp_split_to_table
1   What a great day
2   This is a product. It is useful
3   ['a'
3    ' '
3    ' '
3    'b']

    Etapa 2 (remover todos os não-espaços, não-alfa)

    SELECT id, REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(description, ','), '[^a-zA-Z\s]', '', 'g')
FROM wordcount;

-- Remove all non-alpha, non-spaces. Otherwise the words "product" and "product." would
-- be counted as different! Again, keeping the id field makes things clearer, 
-- even if not strictly necessary for purists

    Resultado:

    id  regexp_replace
1   What a great day
2   This is a product It is useful
3   a
3     
3     
3    b

    Passo 3 (coloque as strings em um array):

    SELECT id, STRING_TO_ARRAY(REGEXP_REPLACE(
          REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')
FROM wordcount;  
--              id again - not strictly necessary at this step.

    Resultado:

    id  string_to_array
1   {What,a,great,day}
2   {This,is,a,product,It,is,useful}
3   {a}
3   {"","",""}
3   {"","",""}
3   {"",b}

    Por fim, a resposta em si - UNNESTe, em seguida, selecione essas palavras LENGTH > 0agrupadas por id e descrição.

    ou seja, SELECTo necessário do seguinte cte (Common Table Expression) - o cte não é estritamente necessário - eu poderia ter usado o UNNEST...em toda a minha consulta final, mas isso teria sido horrível para ler e depurar. É a razão pela qual as Expressões de Tabela Comuns foram inventadas!

    WITH cte1 AS
(
  SELECT id, 
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT blah... (see above)
    • 4
  2. Best Answer

    Quanto à sua solução: inteligente e com uma explicação sólida. Mas e esses casos: '', NULL, '"§$%', '-'? Sem palavras. A contagem deve ser 0- mas sua solução descarta essas linhas completamente.

    Além disso, qualquer solução depende em primeiro lugar da definição exata de "palavra" , que pode variar muito ...

    Processamento de string baseado em expressão regular

    Semelhante à sua solução, com algumas sugestões alternativas:

    SELECT id
     , COALESCE(cardinality(arr), 0) AS word_count
     , unique_word_count
     , description
FROM  (
   SELECT *
        , string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ') AS arr
   FROM   wordcount
   ) a
LEFT   JOIN LATERAL (
   SELECT count(DISTINCT elem) AS unique_word_count
   FROM   unnest(arr) elem
   ) b ON true;

    db<>fiddle aqui (caso de teste estendido)

    O núcleo é regexp_replace(description, '\W+', ' ', 'g')substituir todas as substrings de caracteres que não sejam palavras por um único espaço. Consulte Escapes abreviados de classe de expressão regular . Isso remove todo o ruído no início do jogo.

    Seguido de barato trim()para remover espaços à esquerda/à direita e string_to_array()converter a string preparada em uma matriz.

    Obter word_countda matriz diretamente. Mais uma vez: barato.

    O unique_word_countde uma LATERALsubconsulta com count(DISTINCT ...). Essa parte pode ou não ser mais lenta do que um desaninhamento/agregação total. É um pouco mais simples.

    O COALESCEno exterior SELECTcuida da NULLentrada (a pergunta original não mencionou uma NOT NULLrestrição). Opcional, caso você precise 0em vez de NULL.

    Ou (mais rápido em um teste rápido com strings curtas):

    SELECT id
     , count(*) AS word_count
     , count(DISTINCT elem) AS unique_word_count
     , description
FROM  (
   SELECT id, description
        , unnest(string_to_array(trim(regexp_replace(description, '\W+', ' ', 'g')), ' ')) AS elem
   FROM   wordcount
   ) sub
GROUP  BY id, description;

    Isso descarta linhas com 0 palavras, como sua resposta.

    (Ab-) usando analisador de pesquisa de texto

    Usar a função de pesquisa de texto ts_parse()é mais simples. Pode ou não ser mais rápido. Mas primeiro estude os vários tokens identificados pelo analisador de pesquisa de texto e veja o que corresponde à sua definição de "palavra":

    SELECT * FROM ts_token_type('default')

    Para apenas "Palavras ASCII" : (Ao contrário de acima, o sublinhado ( _) não é tratado como caractere de palavra aqui):

    SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', w.description) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;

    Para evitar _separar palavras, use simples replace()primeiro:

    SELECT w.id
     , count(*) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w, ts_parse('default', replace(w.description, '_', 'x')) t
WHERE  t.tokid = 1 -- 'asciiword'
GROUP  BY w.id;

    Novamente, para manter todas as linhas:

    SELECT w.id
     , count(token) AS word_count
     , count(DISTINCT token) AS unique_word_count
     , w.description
FROM   wordcount w
LEFT   JOIN LATERAL (
   SELECT t.token
   FROM   ts_parse('default', w.description) t
   WHERE  t.tokid = 1 -- 'asciiword'
   ) t ON true
GROUP  BY w.id;

    db<>fique aqui

    Relacionado:

    • 2

relate perguntas