Início / dba / Perguntas / 185962
Accepted
Randomize
Asked: 2017-09-15 05:49:22 +0800 CST

Esmagando linhas agregadas com PostgreSQL

  • 772

Dada uma tabela my_datacomo esta:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | NULL
 1  | NULL | smith   | 32
 1  | NULL | NULL    | NULL
 1  | john | smith   | NULL
 1  | john | NULL    | 32

CREATE TABLE my_data(id,name,surname,age)
AS ( VALUES 
  (1::int, 'john', 'smith' ,NULL::int),
  (1, NULL,   'smith' ,32),
  (1, NULL,   NULL    ,NULL),
  (1, 'john', 'smith' ,NULL),
  (1, 'john', NULL    ,32)
);

Para o mesmo id, os valores (se presentes) nas respectivas colunas são sempre os mesmos, então como posso "esmagá-los" para obter:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | 32

Minha tentativa

A cross join lateralpara cada coluna é minha única ideia até agora, mas duvido que seja boa:

select 
distinct column1, c2.value, c3.value, c4.value
from my_data md
cross join lateral (select column2 from my_data where column1 = md.column1 and column2 is not null limit 1) as c2(value)
cross join lateral (select column3 from my_data where column1 = md.column1 and column3 is not null limit 1) as c3(value)
cross join lateral (select column4 from my_data where column1 = md.column1 and column4 is not null limit 1) as c4(value);
postgresql aggregate

2 respostas

  1. Best Answer

    Usandopercentile_disc

    Eu acho que algo assim seria o mais rápido,

    SELECT id,
  percentile_disc(0) WITHIN GROUP (ORDER BY name NULLS LAST)    AS name,
  percentile_disc(0) WITHIN GROUP (ORDER BY surname NULLS LAST) AS surname,
  percentile_disc(0) WITHIN GROUP (ORDER BY age NULLS LAST)     AS age
FROM my_data
GROUP BY id;

 id | name | surname | age 
----+------+---------+-----
  1 | john | smith   |  32
(1 row)

    Aqui estamos usando percentile_discum Agregado de Conjunto Ordenado descrito como "percentil discreto: retorna o primeiro valor de entrada cuja posição na ordenação é igual ou superior à fração especificada " . Então,

    • se essa posição for "0", é essencialmente first_valuesobre as linhas que estão sendo agregadas.
    • se NULLS for o último ( NULLS LAST), o primeiro valor não será nulo, e isso é tudo o que importa aqui (porque você disse "para o mesmo id, os valores (se presentes) nas respectivas colunas são sempre os mesmos" )

    Você também pode fazer isso, eu acho, com first_valueuma função de janela e, em seguida, extrair disso com DISTINCT ON.

    Usandomode

    Se você quer o consenso dos não-valores, podemos fazer isso também. Eu estava assumindo percentile_discque era o que o OP queria. Outra opção é usar uma Função Agregada de Conjunto Ordenadomode() diferente . É descrito como "retorna o valor de entrada mais frequente (escolhendo arbitrariamente o primeiro se houver vários resultados igualmente frequentes)". Parece,

    SELECT id,
  mode() WITHIN GROUP (ORDER BY name)    AS name,
  mode() WITHIN GROUP (ORDER BY surname) AS surname,
  mode() WITHIN GROUP (ORDER BY age)     AS age
FROM my_data
GROUP BY id;
    • 2

  2. A solução interessante de Evan Carroll recebeu +1 (nunca ouvi falar percentile_disc!).

    No entanto, tenho outra solução possível, que tem a vantagem de ser genérica (para aqueles RDBMSs que possuem CTEs - ou seja, a WITHcláusula).

    Ele NÃO requer nenhuma funcionalidade específica especial do PostgreSQL - além do LIMIT- que possui palavras-chave correspondentes em outros servidores.

    (Reescrita completa!)

    Eu fiz o seguinte:

    Criei minha tabela e dados:

    CREATE TABLE person
(
  person_id INTEGER,
  person_firstname VARCHAR (25),
  person_surname VARCHAR (25),
  person_age INTEGER
);

INSERT INTO person VALUES
( 1, 'john', 'smith', NULL),
( 1,   NULL, 'smith',   32),
( 1,   NULL,    NULL, NULL),
( 1, 'john', 'smith', NULL),
( 1, 'john',    NULL,   32),
( 1, 'John', 'Smith', 3456);  <-- note capitalised names and outlier age!

    E então executei o seguinte:

    WITH fname AS
(
  SELECT person_id, person_firstname
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(*) DESC
  LIMIT 1
),
lname AS
(
  SELECT person_id, person_surname
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(person_surname) DESC
  LIMIT 1
),
age AS
(
  SELECT person_id, person_age
  FROM person
  GROUP BY 1, 2
  ORDER BY COUNT(person_age) DESC
  LIMIT 1
)
SELECT fname.person_firstname, lname.person_surname, age.person_age
FROM fname
JOIN lname on fname.person_id = lname.person_id
JOIN age   on lname.person_id = age.person_id;

    com o resultado (desejado) (correto para os dados):

     person_firstname | person_surname | 
------------------+----------------+-----
 john             | smith          |  32   <--- average good - outlier ignored!
(1 row)

    Esta solução fornece a solução correta mesmo com valores discrepantes - (veja edições anteriores deste post para o problema!) - depende da maioria das respostas estar correta. O uso da UPPER()função também eliminaria quaisquer problemas de capitalização.

    Claro, uma solução muito melhor seria fazer a depuração dos dados antes que eles cheguem perto do HDD - colocar NOT NULLdeclarações, por exemplo, seria um bom começo!

    • 1

relate perguntas