SUM OVER PARTITION BY

Question

我错过了什么？

这个查询一遍又一遍地返回重复的数据。虽然计数对于完整的总数是正确的，但我期望只有一行，但实际上我得到了大约40次重复的值。有任何想法吗？

SELECT BrandId
      ,SUM(ICount) OVER (PARTITION BY BrandId ) 
  FROM Table 
WHERE DateId  = 20130618

我理解了吗？

BrandId ICount
2       421762
2       421762
2       421762
2       421762
2       421762
2       421762
2       421762
1       133346
1       133346
1       133346
1       133346
1       133346
1       133346
1       133346

我错过了什么？

我无法删除`by`分区，因为整个查询是这样的：

SELECT BrandId
       ,SUM(ICount) OVER (PARTITION BY BrandId) 
       ,TotalICount= SUM(ICount) OVER ()    
        ,SUM(ICount) OVER () / SUM(ICount) OVER (PARTITION BY BrandId)  as Percentage
FROM Table 
WHERE DateId  = 20130618

这将返回：

BrandId (No column name)    TotalICount Percentage
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76

我希望输出的结果可以像这样，而无需使用distinct关键字：

BrandId (No column name)    TotalICount Percentage
2       421762              32239892    76
9       1238442             32239892    26
10      1467473             32239892    21

- nitefrog

Answer 1

在我看来，我认为解释为什么需要使用GROUP BY以及在使用OVER()子句进行求和时为什么会出现重复数据行的情况非常重要。例如，您需要在两个日期之间按特定订单类别聚合每个订单行的总销售价格，但还需要在最终结果中保留单个订单数据。在SalesPrice列上使用SUM()将无法获得正确的总数，因为它需要一个GROUP BY，从而压缩详细信息，因为您无法在SELECT语句中保留单个订单行。

很多时候我们会看到一个#temp表、@table变量或CTE填充我们的数据总和并分组，这样我们就可以稍后再次加入它来获取我们所需的总和列。这可能会增加处理时间和额外的代码行。相反，使用OVER(PARTITION BY ())，像这样：

SELECT
  OrderLine, 
  OrderDateTime, 
  SalePrice, 
  OrderCategory,
  SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory
FROM tblSales 
WHERE OrderDateTime BETWEEN @StartDate AND @EndDate

请注意，我们没有进行分组，并选择了单独的订单行列。最后一列中的PARTITION BY将为每个类别中的每一行数据返回销售价格总计。最后一列实际上是在说，我们希望在我的结果的分区上并且按指定的类别（OVER(PARTITION BY CategoryHere)）进行求和（SUM(SalePrice))的销售价格总和。

如果我们从select语句中删除其他列，只留下我们最后的SUM()列，就像这样：

SELECT
  SUM(SalePrice) OVER(PARTITION BY OrderCategory) AS SaleTotalPerCategory
FROM tblSales 
WHERE OrderDateTime BETWEEN @StartDate AND @EndDate

结果仍将为原始结果集中的每一行重复此总和。原因是该方法不需要GROUP BY。如果您不需要保留单独的行数据，则只需使用SUM()而不使用OVER()并适当地分组数据即可。同样，如果您需要具有特定总计的附加列，则可以使用上面描述的OVER(PARTITION BY())方法，而无需其他选择加入回来。以上纯粹是为了解释为什么会得到相同数字的重复行，并帮助理解此子句提供的内容。此方法可以用于许多方面，我强烈建议从这里阅读更多文档：Over Clause。

- E10

Answer 2

你可以使用DISTINCT或者移除PARTITION BY部分并使用GROUP BY：

SELECT BrandId
       ,SUM(ICount)
       ,TotalICount = SUM(ICount) OVER ()    
       ,Percentage = SUM(ICount) OVER ()*1.0 / SUM(ICount) 
FROM Table 
WHERE DateId  = 20130618
GROUP BY BrandID

如果你不确定为什么要以BrandID每个计数来除以总数，如果这是一个错误，而你想要得到百分比，则将上面的部分颠倒：

SELECT BrandId
           ,SUM(ICount)
           ,TotalICount = SUM(ICount) OVER ()    
           ,Percentage = SUM(ICount)*1.0 / SUM(ICount) OVER () 
    FROM Table 
    WHERE DateId  = 20130618
    GROUP BY BrandID

- Hart CO

---

SQL Server 2014可以同时使用over()和group by吗？我在同时使用时遇到了错误。 - 劉鎮瑲

Answer 3

我认为你想要的查询是这样的：

SELECT BrandId, SUM(ICount),
       SUM(sum(ICount)) over () as TotalCount,
       100.0 * SUM(ICount) / SUM(sum(Icount)) over () as Percentage
FROM Table 
WHERE DateId  = 20130618
group by BrandId;

这个执行品牌的分组(group by)操作，并计算出“百分比”(Percentage)。这个版本应该会产生一个介于0到100之间的数字。

- Gordon Linoff

---

SQL Server 2014可以同时使用over()和group by吗？我在同时使用时遇到了错误。 - 劉鎮瑲

Answer 4

移除partition by，并添加group by子句，

SELECT BrandId
      ,SUM(ICount) totalSum
  FROM Table 
WHERE DateId  = 20130618
GROUP BY BrandId

- John Woo

---

那样做不行。我修改了查询以显示其全部内容。 - nitefrog

你需要什么样的输出？ - John Woo

我期望的输出应该是：品牌编号 (无列名) 总计数 百分比 2 421762 32239892 76 9 1238442 32239892 26 - nitefrog

除了您所需的输出之外，您还能否提供示例记录？ - John Woo