Início / dba / Perguntas / 330605
Accepted
snappymcsnap
Asked: 2023-08-25 04:28:22 +0800 CST

GROUP BY com múltiplas contagens

  • 772

Estou consultando uma tabela Invoice em um banco de dados MySQL 8.0.28 que é filho da tabela Customer (FK = CustomerId) e quero poder obter várias contagens com base em critérios diferentes nos últimos 30 dias, onde agrupo por o cliente e a origem da fatura. Então, se eu escrevesse como duas consultas separadas, seria assim:

/* this is just the count of all invoices in last 30 days */
SELECT i.CustomerId, i.InvoiceSource, COUNT(*) AS TotalInvoices
FROM Invoice i
WHERE i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
GROUP BY i.CustomerId, i.InvoiceSource;

e

/* count of all invoices in last 30 days where the total amount exceeds 1000 */
SELECT i.CustomerId, i.InvoiceSource, COUNT(*) AS LargeInvoices
FROM Invoice i
WHERE i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH) and CURDATE() AND i.Amount > 1000
GROUP BY i.CustomerId, i.InvoiceSource;

Você entendeu, provavelmente haverá mais 2 ou 3 com critérios específicos também.

Então minhas perguntas são:

  1. em primeiro lugar, qual é a melhor abordagem e SQL para escrever isso como uma única consulta?
  2. isso começará a atrapalhar a consulta à medida que adiciono mais contagens para diferentes condições? em caso afirmativo, qual é o excesso/falta disso?
  3. O GROUP BY é o mesmo para todos. Posso movê-lo de cada subconsulta e torná-lo um group by para tudo? Não tem certeza se isso seria mais eficiente ou não?

Muito grato

mysql

1 respostas

  1. Best Answer

    Uma maneira de fazer isso é usar um CASE dentro de uma função de agregação.

    Por exemplo, você poderia combinar as duas consultas desta forma:

    SELECT
    i.CustomerId,
    i.InvoiceSource,
    COUNT(*) AS TotalInvoices,
    SUM(CASE WHEN i.Amount >1000 then 1 ELSE 0 END ) as LargeInvoices
FROM
    Invoice i
WHERE
    i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH)
    and CURDATE()
GROUP BY
    i.CustomerId,
    i.InvoiceSource;

    Também poderia ser feito com COUNT:

    SELECT
    i.CustomerId,
    i.InvoiceSource,
    COUNT(*) AS TotalInvoices,
    COUNT(CASE WHEN i.Amount >1000 then 1 ELSE NULL END ) as LargeInvoices
FROM
    Invoice i
WHERE
    i.CreatedDate BETWEEN (CURDATE() - INTERVAL 1 MONTH)
    and CURDATE()
GROUP BY
    i.CustomerId,
    i.InvoiceSource;
    • 1

relate perguntas