Início / dba / Perguntas / 78407
Accepted
Dave Jarvis
Asked: 2014-10-06 01:42:03 +0800 CST

Sufixo correspondente mais longo ao usar funções agregadas

  • 772

Fundo

Procurando encontrar o sufixo de string correspondente mais longo.

Configurar

Considere o seguinte violino :

CREATE TABLE noun
    ("label" varchar(10))
;

INSERT INTO noun
    ("label")
VALUES
    ('bar'),
    ('blue bar'),
    ('red bar'),
    ('green bar'),
    ('purple bar'),
    ('handlebar')
;

CREATE TABLE noun_inflection
    ("label_singular" varchar(9), "label_plural" varchar(9))
;

INSERT INTO noun_inflection
    ("label_singular", "label_plural")
VALUES
    ('bar', 'bars'),
    ('handlebar', 'handlebar')
;

E a seguinte consulta:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

Isso retorna duas linhas:

LABEL       | LABEL_SINGULAR | LABEL_PLURAL
------------+----------------+-------------
handlebar   | bar            | bars
handlebar   | handlebar      | handlebar

A primeira linha está correta, mas não desejada. Para este propósito específico, a distância de Levenshtein pode ser usada para eliminar a duplicata:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  levenshtein( n.label, ni.label_singular )
limit 1;

Isso reordena as linhas com base na semelhança do rótulo. Neste exemplo, "handlebar" corresponde exatamente a "handlebar" e tem uma distância de 0. Adicionar o limit 1restringe a consulta a uma única linha.

Problema

A configuração funciona, exceto que o PostgreSQL 9.1 não respeita os modificadores LIMIT em funções agregadas. Ou seja, o seguinte não funciona:

SELECT
  xmlagg( xmlement( ... ) ORDER BY levenshtein( ... ) LIMIT 1 )
FROM
  noun n, noun_inflection ni

O problema persiste. A palavra 'handlebar'corresponde a '%bar'e '%handlebar', portanto, isso retorna duas linhas, que, por sua vez, injetam dois elementos xml no documento XML resultante quando apenas um é esperado.

Atualização nº 1

Esclarecer:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

Isso deve retornar um único elemento XML 'handlebar'. Atualmente, ele retorna 'handlebars' e 'handlebar':

{ "Value": "<noun>handlebars</noun><noun>handlebar</noun>", "Type": "xml" }

A saída desejada é:

{ "Value": "<noun>handlebar</noun>", "Type": "xml" }

Atualização nº 2

Mesmo que o código a seguir resolva o problema do guidão/guidão, ele evita que vários substantivos diferentes sejam retornados:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
group by n.label, ni.label_singular
order by levenshtein( n.label, ni.label_singular )
limit 1

Atualização nº 3

Isso parece exigir uma função armazenada. Algo na linha de:

  SELECT
    trim( TRAILING label_singular FROM p_noun ) || ni.label_plural
  FROM
    noun_inflection ni
  WHERE
    p_noun ILIKE '%'||ni.label_singular
  ORDER BY
    levenshtein( p_noun, ni.label_singular )
  LIMIT 1;

Pergunta

Como você combinaria e retornaria apenas a substring mais longa?

postgresql xml

3 respostas

  1. O que há de errado com (talvez óbvio demais?):

    select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  char_length(ni.label_singular) DESC
limit 1;
    • 4

  2. Se você deseja a substring mais longa, isso significa que não há outra que seja mais longa. Um NOT EXISTSpredicado dará isso.

    select
    <whatever>
from <your table> as aa
where <predicates>
and not exists
    (
    select 1
    from <your table> as bb
    where <predicates>
    and len(bb.SomeColumn) > len(aa.SomeColumn)
    );

    Claro que a len()função pode ser substituída por levenshtein()como mostram seus exemplos. A consulta correlacionada pode causar problemas de desempenho. Seu conjunto de valores de teste é suficientemente pequeno para pré-calcular os valores de função para cada um?

    Você pode usar uma das consultas rápidas, mas erradas, para reduzir os conjuntos iniciais a um superconjunto administrável das respostas corretas, que podem então ser processadas por um algoritmo lento, mas correto.

    • 1
  3. Best Answer

    A única solução viável que encontrei foi escrever uma função:

      FUNCTION get_noun_inflection( p_noun text, ... params ... )
  -- ... body, declare, variable, etc. 
  SELECT
    CASE
      -- ... conditions ...
      THEN trim( TRAILING ni.label_singular FROM p_noun ) || ni.label_plural
      -- Noun in singular form (no pluralization)
      ELSE p_noun
    END
  FROM
    noun_inflection ni
  INTO
    v_result
  WHERE
    p_noun ILIKE '%'||ni.label_singular
  ORDER BY
    levenshtein( p_noun, ni.label_singular )
  LIMIT 1;

  IF NOT found THEN
    v_result := p_noun;
  END IF;

  RETURN v_result;

  -- ... exception handling, default values, cost, etc.

    Então use a função:

    select
  xmlagg(
    xmlelement(
      name "noun",
      get_noun_inflection( n.label )
    )
  )
from
  noun n
    • 0

relate perguntas