Início / dba / Perguntas / 27477
Accepted
Joshua Burns
Asked: 2012-10-24 08:19:56 +0800 CST

PostgreSQL: consulta o banco de dados para linhas contendo integer[] > ou < do que o valor especificado

  • 772

Eu tenho uma tabela que armazena as temperaturas históricas dos últimos 5 dias armazenadas em uma coluna integer[] com base na cidade. A questão é: como posso consultar entradas que tenham um valor maior ou menor que um valor especificado? Aqui está uma tabela de exemplo:

+=========+==================+
| city    | temps            |
+=========+==================+
| Seattle | {65,72,63,56,72} |
+---------+------------------+
| Miami   | {83,75,69,72,79} |
+---------+------------------+

Digamos que eu queira consultar cidades com temperatura superior a 80. Neste exemplo, apenas Miami apresentou temperaturas superiores a 80, portanto, apenas essa linha deve ser retornada. Eu tentei algumas consultas sem sucesso, procurei em intarray, mas isso também não parece resolver meu problema. Muito obrigado!

Estou executando o PostgreSQL 9.1

postgresql postgresql-9.1

2 respostas

  1. Supondo que sua tabela seja nomeada readings:

    Encontre cidades onde pelo menos uma temperatura é maior que 80:

    select *
from readings r
where exists (select 1 
              from unnest(r.temps) i
              where i > 80)

    Encontre cidades onde todas as temperaturas são maiores que 68:

    select *
from readings r
where 68 < all (select i
                from unnest(r.temps) i)
    • 6
  2. Best Answer

    Como alternativa à solução de a_horse_with_no_name, a opção mais simples é usar <e a anycomparação de linha ou matriz com a matriz:

    SELECT city FROM cities WHERE 80 < any (temps);

    Veja SQLFiddle .

    Não é necessário desaninhar a matriz, pois anye alltrabalhar em matrizes e também em conjuntos de linhas.

    Ao contrário de usar os operadores de matriz, esta operação não se beneficia e não pode usar um índice (b-tree ou GIN) em temps, portanto, um design normalizado que se divide tempsem uma tabela separada com referência de chave estrangeira para citiespode ser mais rápido se você tiver muitas cidades e/ou muitas amostras.

    Anteriormente, sugeri que você desejasse usar um GINíndice de array e os operadores de array , mas me enganei. Estes não suportam a operação desejada; Cometi um erro no meu teste que os fez parecer.

    Para atualizações frequentes, eu normalizaria em outra tabela e indexaria essa tabela. Se houver apenas uma pequena quantidade de dados, eu não me incomodaria com índices e apenas usaria < any (temps).

    • 6

relate perguntas