使用聚合函数和 OVER

使用汽车表 ，我们将计算每个客户花费的总金额，最大值，最小金额和平均金额以及多次（COUNT）她带来汽车进行维修。

Id CustomerId MechanicId 模型状态总成本

SELECT CustomerId,  
       SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
       AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
       COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
       MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
       MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
  FROM CarsTable
 WHERE Status = 'READY'

请注意，以这种方式使用 OVER 不会聚合返回的行。以上查询将返回以下内容：

重复的行可能对报告目的没有用。

如果你希望简单地汇总数据，最好使用 GROUP BY 子句以及相应的聚合函数，例如：

SELECT CustomerId,  
       SUM(TotalCost) AS Total,
       AVG(TotalCost) AS Avg,
       COUNT(TotalCost) AS Count,
       MIN(TotalCost) AS Min,
       MAX(TotalCost)  AS Max
  FROM CarsTable
 WHERE Status = 'READY'
GROUP BY CustomerId