Partitioned SUM and COUNT

最新的理解

4张表 as cte1， abcd

3张表 as cte2,  aef

把cet1和cte2进行join的时候，partition需要调整，

cte1里面的partition by需要加上ef的字段

cte2里面的partition by需要加上bcd的字段

 

 

 

核心，这里做除法的时候

count(1) over (字段) ，sum的哪一张表的数据，就over那张表的主键

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;