Partitioned SUM and COUNT sql online,在线示例，online example, partition by

Partitioned SUM and COUNT

问题

I have a list of daily events as follows:

EventTypeID INT (multiple events, not unique)
EventDate (cast(EventDate) as Date)
HeadCount INT

I need to produce this:

EventDate, AvgHeadCountET, AvgHeadCountTotal

Where each day I have an average HeadCount per EventType and an average HeadCount for all events that day.

I've done this using #TempTables with a couple of passes, but I can't help but think that there's a more efficient way of doing this.

回答1

To do this, you need to calculate the average explicitly -- that is, take the sum and divide by the count:

SELECT EventDate,EventTypeID,
       AVG(HeadCount) AS AvgHeadCountET,
       (sum(sum(HeadCount)) over (partition by EventDate) /
        count(*) over (partition by EventDate)
       ) as AvgHeadCountTotal
FROM t
GROUP BY EventDate, EventTypeID;

Mixing window functions and aggregation functions does result in funky syntax (such as sum(sum()), but they do work together.

Here is a SQLFiddle illustrating it.

实例

http://www.sqlfiddle.com/#!18/58dc72/2
SELECT DISTINCT
a.ClaimId,
a.InvoiceId,
a.ProductId,
IIF(SUM(CAST(b.IsFastRefund AS INT)) OVER (PARTITION BY a.InvoiceId) > 0, 1, 0) AS IsFastRefund,
(SUM(b.VatAmount) OVER (PARTITION BY a.InvoiceId) / COUNT(1) OVER (PARTITION BY b.ItemId)) AS VatAmount,
CAST((SUM(b.VatAmount) OVER (PARTITION BY a.InvoiceId) / COUNT(1) OVER (PARTITION BY b.ItemId)) / a.ExchangeRate AS DECIMAL(19, 2)) AS VatAmountEur,
SUM(IIF(c.IsCompleted = 1, c.VatAmount, 0)) OVER (PARTITION BY a.InvoiceId) AS FundedAmount
FROM dbo.Invoice AS a
LEFT JOIN dbo.InvoiceItem AS b
ON b.InvoiceId = a.InvoiceId
LEFT JOIN dbo.StatementVatInvoiceItem AS c
ON c.InvoiceItemId = b.ItemId;