Início / dba / Perguntas / 122629
Accepted
timpone
Asked: 2015-12-02 21:20:22 +0800 CST

seria possível no postgres obter uma contagem de acertos sql em uma instrução select

  • 772

Digamos que eu tenha a seguinte tabela no Postgres 9.4:

items 
id |  name             |   tag_ids
1     tire                  -1--2-
2     wheel                 -1--3-
3     transmisson           -3-

Eu gostaria de fazer algo como:

select id, tags from items where tag_ids ilike '%-1-%' or tags ilike '%-3-%';

mas se acertar duas vezes (como id 2), retorne uma contagem de acertos. Isso seria possível? Ou, possivelmente, volte como dois registros e, em seguida, processe no código do aplicativo.

editar

ok, supondo que tivéssemos uma segunda tabela como esta:

items_tags
item_id  tag_id
1         1
1         2
2         1
2         3
3         3

Existe uma maneira de juntá-los e, em seguida, agrupar pelo número de "acertos" - acho que apenas linhas selecionadas e ordem inversa? E dê uma representação dos hit tag_ids

select i.name from items inner join items_tags using (id) where items_tags.tag_id in (1,3);

mas como eu faria uma contagem e ordem. Estou assumindo um grupo, mas isso está além de mim.

editar 2

então não consigo fazer essa segunda consulta funcionar. Parece que deveria, mas não é:

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int as hits 
from items order by  hits desc;

ditadoERROR: column "hits" does not exist

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int
as hits from items where hits > 0 order by  hits desc;
postgresql

2 respostas

  1. Sim, você pode usar isso na selectlista:

     (case when tags ilike '%-1-%' then 1 else 0 end) 
 + (case when tags ilike '%-3-%' then 1 else 0 end) 
   as hits

    ou alternativamente (converta os valores booleanos para inteiros ( TRUE -> 1, FALSE -> 0) e adicione:

     (tags ilike '%-1-%')::int + (tags ilike '%-3-%')::int
   as hits

    Mas a consulta (e muitas outras) seria muito mais fácil se você normalizasse o design e armazenasse a relação item-tag em uma tabela de junção separada.

    • 3
  2. Best Answer

    Sobre o erro em "editar 2"

    A primeira instrução é válida (seu post está confundindo as coisas), a mensagem de erro se aplica apenas à 2ª instrução, onde você tenta referenciar a coluna de saída hitsna WHEREcláusula e isso não é possível. Detalhes:

    Solução: repita a expressão (longa) na WHEREcláusula ou use uma subconsulta ou, melhor, especifique as WHEREcondições individuais:

    SELECT tag_ids
    , (tag_ids LIKE '%-1-%')::int
    + (tag_ids LIKE '%-11-%')::int AS hits
FROM   items
WHERE (tag_ids LIKE '%-1-%' OR
       tag_ids LIKE '%-11-%')  -- cannot reference out column in WHERE
ORDER  BY hits DESC;           -- but you can in ORDER BY

    Isso é mais rápido porque o Postgres só precisa verificar uma única correspondência para qualificar a linha, e o uso de índices é possível (um índice trigrama neste caso).

    Além disso: substituído ILIKEpor apenas LIKE- não há caracteres relevantes envolvidos.

    A melhor solução depende da falta de informações, mas os arrays seriam mais elegantes e fáceis de indexar. Como você teve em sua pergunta relacionada:

    Um design normalizado como @ypercube já sugerido é provavelmente o melhor.

    • 3

relate perguntas