Início / dba / Perguntas / 331042
Accepted
Ade
Asked: 2023-09-09 01:03:55 +0800 CST

Ignore linhas duplicadas em uma junção – e pegue apenas uma delas

  • 772

No MySQL tenho tabelas, simplificadas da seguinte forma:

companies (id, name, rank, trade_sector_id)
orders (id, company_id, paid, subtotal)
trade_sectors (id, label)
companies_notes (id, company_id, notes)

Quero uma única linha para cada empresa contendo:

  • Nome da companhia
  • Número de encomendas
  • Total de todos os subtotais
  • Notas da empresa

Para simplificar aqui estou selecionando apenas uma empresa, com id=14401. Possui 68 pedidos:

SELECT 
  companies.id AS company_id, 
  companies.account_name, 
  COUNT(orders.id) AS numSales,
  SUM(orders.`subtotal`) AS subtotal,   
  MAX(trade_sectors.label) AS trade_sector,
  MAX(companies_notes.`notes`) AS notes

FROM companies
  LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
  LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)
  LEFT JOIN `companies_notes` ON (`companies_notes`.`company_id` = companies.id)

  WHERE companies.id = '14401'
  GROUP BY companies.id
  ORDER BY companies.rank DESC;

O problema

Existem 68 pedidos para esta empresa, mas estou obtendo numSales como 136 (ou seja, 2x o número) e também o subtotal é 2x maior do que deveria ser.

Mas se eu remover a junção do NOTES, está correto:

SELECT 
  companies.id AS company_id, 
  companies.account_name, 
  COUNT(orders.id) AS numSales,
  SUM(orders.`subtotal`) AS subtotal,   
  MAX(trade_sectors.label) AS trade_sector

FROM companies
  LEFT JOIN `orders` ON (companies.id = orders.`company_id` AND orders.`paid` = 1)
  LEFT JOIN `trade_sectors` ON (companies.trade_sector_id = trade_sectors.`id`)

  WHERE companies.id = '14401'
  GROUP BY companies.id
  ORDER BY companies.rank DESC;

Parece que a junção de notas está me dando 2 linhas por pedido. Sim, EXISTEM duas linhas de notas para esta empresa (deve haver apenas 1), mas isso não é aplicado tecnicamente. Eu pensei que usando a função de agregação MAX no companies_notes. notesapenas um seria considerado. Na verdade, a cláusula Group BY exige que as colunas sejam agregadas.

Como posso evitar que a junção crie registros duplicados que afetam os valores SUM()e MAX()?

mysql

2 respostas

  1. Achei que usando a função de agregação MAX em companies_notes.notes apenas uma seria considerada... mas ainda não entendo porque a função de agregação MAX não resolve

    As funções agregadas são aplicadas após a junção e o agrupamento e aplicam-se somente à coluna que você está agregando. Portanto, a ordem geral dos eventos é que os dados são duplicados da junção, depois são agrupados e, em seguida, a função agregada analisa as linhas do agrupamento e aplica sua função a essas linhas, apenas para aquela coluna.

    Assim, MAX(companies_notes.'notes')ele analisa as duas notas por cliente no agrupamento e escolhe a última (classificada lexicograficamente). Pois COUNT(orders.id), da mesma forma, analisa todas as linhas do agrupamento (136 delas devido à junção com a tabela de notas) e depois as conta. É por isso que você ainda acaba com 136 como o arquivo numSales. A função agregada de cada coluna é independente uma da outra.

    • 3
  2. Best Answer

    Tornar companies_notes.company_idexclusivo de acordo com seu comentário certamente resolverá o problema (depois de remover ou mesclar as entradas extras para permitir a aplicação da restrição).

    Mas enquanto isso, o que você precisa fazer é pré-processar companies_notespara garantir que haja apenas uma entrada por company_id, antes de tentar ingressar nela.

    Existem várias técnicas usando uma consulta aninhada/tabela derivada.

    O que normalmente faço é aplicar um número de linha e depois filtrar para a primeira linha. Isso requer o aninhamento das consultas em dois níveis de profundidade, mas significa que no nível externo você obtém acesso a todos os campos da linha selecionada.

    Você também pode agrupar por company_ide selecionar um MAX (ou outro agregado adequado, como GROUP_CONCAT) nos campos que deseja disponibilizar no nível externo.

    Para ser claro, ambas as soluções são aplicadas dentro de uma consulta aninhada. Em seguida, você ingressa nesta consulta aninhada. Escrevo principalmente em TSQL, então peço desculpas se a sintaxe a seguir não estiver correta, mas mostra a ideia geral com uma consulta aninhada:

    SELECT 
  companies.id AS company_id, 
  ...
  MAX(companies_notes.`notes`) AS notes

FROM 
    companies
...

LEFT JOIN (
    SELECT company_id, MAX(notes) AS notes
    FROM companies_notes
    GROUP BY company_id
) AS `companies_notes` ON (`companies_notes`.`company_id` = companies.id)

.... (the rest of your original query)
    • 3

relate perguntas