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

最新的理解

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;