Início / dba / Perguntas / 129371
Accepted
MSIS
Asked: 2016-02-16 21:00:29 +0800 CST

Consulta confusa: misturando agregados e não agregados

  • 772

Espero que alguém possa esclarecer como fazer essa consulta no SQL Server 2012, ou talvez o que está pedindo; parece que estou sendo solicitado a fazer uma consulta que combina agregados com não agregados, levando ao que parece ser uma consulta impossível, já que só se pode agrupar por campos que aparecem na Selectcláusula, o que atrapalha a consulta.

Me pedem para fazer uma consulta na tabela Invoices:

Invoices (InvoiceID, VendorID,InvoiceDate, InvoiceTotal,...)

Sou solicitado a fazer uma consulta que produz 6 colunas: 3 colunas já aparecem "como estão" na tabela:VendorID, InvoiceDate, InvoiceTotal

E outras 3 colunas são agregadas:

4) Sum(InvoiceTotal) AS VendorTotal: Soma das faturas de cadaVendorID

5) Count(InvoiceID) AS VendorCount: Contagem de Faturas para cadaVendorId

6) Avg(InvoiceTotal) AS VendorAvg: Média de Faturas porVendorId

Agora, o problema é que não se pode combinar na mesma consulta (com exceções que não posso usar aqui) agregados e não agregados, a menos que se agrupe pelos não agregados. Então eu posso fazer:

Select 
    VendorId, InvoiceDate, InvoiceTotal, 
    Sum(InvoiceTotal) AS VendorTotal, 
    Count(InvoiceId) AS VendorCount, 
    Avg(InvoiceTotal) AS VendorAvg 
GROUP BY
    VendorID

Agora, não tenho problemas se eu apenas group by VendorID. Mas, para obter uma consulta válida, se eu selecionar um InvoiceDateou InvoiceTotal-- ambos não agregados -- devo agrupar por cada um deles para obter uma consulta válida.

Mas agrupar por esses dois últimos basicamente desfaz os outros agregados: Se eu agrupar por data de faturamento, perco a agregação por Fornecedor, pois cada fornecedor tem datas de faturamento diferentes.

Eu tentei fazer junções automáticas, usando Invoices AS I1 join Invoices AS I2 e fazendo agregações I1e não agregações em I2, mas isso parece não funcionar.

Estou perdendo algo óbvio aqui? Alguma ideia?

Edit: A resposta foi encontrada usando OVER (PARTITION BY VendorId), assim:

Select 
VendorId, InvoiceDate, InvoiceTotal, 
    Sum(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorTotal, 
    Count(InvoiceId) OVER (PARTITION BY VendorId) AS VendorCount, 
    Avg(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorAvg 
GROUP BY
    VendorID
sql-server sql-server-2012

2 respostas

  1. Best Answer

    A solução é o que você já encontrou, em DBMS como o SQL Server 2005+ que implementou funções de janela, podemos usá-los para obter agregados overenquanto mantemos partitiona tabela original, para não colapsar a tabela como group byfaz. Não estou escrevendo a consulta aqui, edite sua resposta.

    Em versões mais antigas (ou outros SGBDs) que não possuem a OVER ()sintaxe da cláusula disponível, poderíamos utilizar GROUP BYem uma tabela derivada (ou CTE) e depois um "self" join de volta à tabela original, assim:

    SELECT 
    i.VendorID, i.InvoiceDate, i.InvoiceTotal, 
    grp.VendorTotal, 
    grp.VendorCount, 
    grp.VendorAvg 
FROM
      dbo.Invoices AS i             -- our original table
  JOIN
      ( SELECT 
            VendorID,
            Sum(InvoiceTotal) AS VendorTotal, 
            Count(InvoiceId) AS VendorCount, 
            Avg(InvoiceTotal) AS VendorAvg 
        FROM 
            dbo.Invoices  
        GROUP BY
            VendorID 
      ) AS grp                      -- the aggregated table 
  ON 
      grp.VendorID = i.VendorID ;
    • 5

  2. Acabei de repassar a resposta por acidente, enquanto olhava para o livro: Usamos OVERcláusula e PARTITION BY.

    Select 
VendorId, InvoiceDate, InvoiceTotal, 
    Sum(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorTotal, 
    Count(InvoiceId) OVER (PARTITION BY VendorId) AS VendorCount, 
    Avg(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorAvg 
GROUP BY
    VendorID
    • 2

relate perguntas