使用聚合函式和 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