Início / dba / Perguntas / 139980
Accepted
user606521
Asked: 2016-06-01 01:36:51 +0800 CST

Selecione as linhas para as quais pelo menos uma linha por conjunto atende a uma condição

  • 772

Tenho a seguinte tabela:

create table test (
  company_id integer not null, 
  client_id integer not null, 
  client_status text,
  unique (company_id, client_id)
);

insert into test values
  (1, 1, 'y'),    -- company1

  (2, 2, null),   -- company2

  (3, 3, 'n'),    -- company3

  (4, 4, 'y'),    -- company4
  (4, 5, 'n'),

  (5, 6, null),   -- company5
  (5, 7, 'n')
;

Basicamente são 5 empresas diferentes, cada uma tem um ou mais clientes e cada cliente tem status: 'y' ou 'n' (pode ser nulo também).

O que tenho que fazer é selecionar todos os pares (company_id, client_id)para todas as empresas para as quais há pelo menos um cliente cujo status não seja 'n' ('y' ou nulo). Portanto, para os dados de exemplo acima, a saída deve ser:

company_id;client_id
1;1
2;2
4;4
4;5
5;6
5;7

Eu tentei algo com funções de janela, mas não consigo descobrir como comparar o número de TODOS os clientes com o número de clientes com STATUS = 'n'.

select company_id,
count(*) over (partition by company_id) as all_clients_count
from test
-- where all_clients_count != ... ?

Eu descobri como fazer isso, mas não tenho certeza se é o caminho certo:

select sub.company_id, unnest(sub.client_ids)
from (
  select company_id, array_agg(client_id) as client_ids
  from test
  group by company_id
  having count(*) != count( (case when client_status = 'n' then 1 else null end) )
) sub
postgresql postgresql-9.4

4 respostas

  1. Best Answer

    Basicamente você está procurando a expressão:

    client_status IS DISTINCT FROM 'n'

    A coluna client_statusdeve ser realmente tipo de dados boolean, não text, o que permitiria a expressão mais simples:

    client_status IS NOT FALSE

    O manual tem detalhes no capítulo Operadores de Comparação .

    Supondo que sua tabela real tenha uma restrição UNIQUEouPK , chegamos a:

    CREATE TABLE test (
  company_id    integer NOT NULL, 
  client_id     integer NOT NULL, 
  client_status boolean,
  PRIMARY KEY (company_id, client_id)
);

    Consultas

    Todos eles fazem o mesmo (o que você pediu), que é o mais rápido depende da distribuição de dados:

    SELECT company_id, client_id
FROM   test t
WHERE  EXISTS (
   SELECT FROM test
   WHERE  company_id = t.company_id
   AND    client_status IS NOT FALSE
   );

    Ou:

    SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT company_id
   FROM   test t
   GROUP  BY 1
   HAVING bool_or(client_status IS NOT FALSE)
   ) c USING (company_id);

    Ou:

    SELECT company_id, client_id
FROM   test t
JOIN  (
   SELECT DISTINCT company_id, client_status 
   FROM   test t
   ORDER  BY company_id, client_status DESC
   ) c USING (company_id)
WHERE  c.client_status IS NOT FALSE;

    Valores booleanos sort FALSE-> TRUE-> NULLem ordem crescente. Então FALSEvem por último em ordem decrescente. Se houver algum outro valor disponível, esse será escolhido primeiro ...

    O PK adicionado é implementado com um índice útil para essas consultas. Se você quiser mais rápido, adicione um índice parcial para a consulta 1:

    CREATE INDEX test_special_idx ON test (company_id, client_id)
WHERE  client_status IS NOT FALSE;

    Você também pode usar funções de janela, mas isso seria mais lento. Exemplo com first_value():

    SELECT company_id, client_id
FROM  (
   SELECT company_id, client_id
        , first_value(client_status) OVER (PARTITION BY company_id
                                           ORDER BY client_status DESC) AS stat
   FROM   test t
   ) sub
WHERE  stat IS NOT FALSE;

    Para muitas linhas por company_id, uma dessas técnicas pode ser mais rápida, ainda:

    • 6

  2. Acho que isso pode ser um pouco simplificado:

    select company_id 
from test 
group by company_id 
having count(*) filter (where client_status!='n' or client_status is null) > 0;
    • 2

  3. Posso ter entendido errado, mas imagino algo como:

     select * 
 from test x 
 where exists ( 
     select 1 
     from test y 
     where x.company_id = y.company_id 
       and coalesce(client_status, 'y') <> 'n'
 );

    vai funcionar. coalesce é usado para mapear null para 'y', mas qualquer coisa diferente de 'n' deve fazer

    Usar uma função OLAP pode nos salvar um "join":

    select company_id, client_id 
from (
    select x.*
         , count(nullif(coalesce(client_status,'y'),'n')) 
               over (partition by company_id) as cnt 
    from test x
) 
where cnt > 0;

    Aqui mapeamos null -> 'y' e 'n' -> null. Como count(x) contará linhas onde x não é nulo, contamos linhas onde client_status <> 'n'. Usei uma função OLAP para evitar GROUP BY, o que significa que só precisamos referenciar a tabela uma vez.

    • 2

  4. Uma consulta SQL padrão abaixo deve funcionar

    select
  company_id,
  client_id
from test
where client_status!='n' or client_status is null;
    • 0

relate perguntas