選擇與截面平均值不同的值

我正在嘗試從已生成的表中選擇數據，該表包含每十二小時獲取的文件大小記錄。該表如下所示：

surrogate_id  | partition_id | data_wanted | collection_ts
1             | 1            | 100         | 2015-09-30 17:00:00
2             | 1            | 100         | 2015-09-30 17:00:00
3             | 1            | 150         | 2015-09-30 17:00:00
4             | 2            | 120         | 2015-09-30 17:00:00
5             | 2            | 130         | 2015-09-30 17:00:00
6             | 3            | 100         | 2015-09-30 17:00:00
7             | 3            | 100         | 2015-09-30 17:00:00
8             | 3            | 100         | 2015-09-30 17:00:00
9             | 3            | 100         | 2015-09-30 17:00:00
10            | 3            | 100         | 2015-09-30 17:00:00
11            | 3            | 100         | 2015-09-30 17:00:00
1             | 1            | 100         | 2015-09-30 18:00:00
2             | 1            | 100         | 2015-09-30 18:00:00
3             | 1            | 150         | 2015-09-30 18:00:00
4             | 2            | 120         | 2015-09-30 18:00:00
5             | 2            | 130         | 2015-09-30 18:00:00
6             | 3            | 100         | 2015-09-30 18:00:00
7             | 3            | 100         | 2015-09-30 18:00:00
8             | 3            | 100         | 2015-09-30 18:00:00
9             | 3            | 100         | 2015-09-30 18:00:00
10            | 3            | 100         | 2015-09-30 18:00:00
11            | 3            | 100         | 2015-09-30 18:00:00

這裡的主鍵是（surrogate_id，collection_ts）。

我需要做的是data_wanted根據最新的timestamp. 所以在上面的例子中，我想選擇分區 1 和 2。我知道為了獲得正確的時間戳，選擇一個視窗函式是合適的，我用它來生成其他報告。這是我正在使用的實現視窗功能的 CTE：

;WITH cte
AS
(
   SELECT *, ROW_NUMBER() OVER(PARTITION BY surrogate_id 
   ORDER BY collection_ts DESC) RowNumber
   FROM example_table
)

我一直在嘗試用平均值來做事情，但我似乎無法找到一種特別有效的方法。至少對我來說很明顯，如果一個分區的給定數據與該時間戳中該分區的平均值不同，那麼我們需要選擇該分區。我只是不知道如何單獨將分區中的每條數據與該分區的平均值進行比較，並且仍然允許自己在使用我定義的 CTE 的同時返回 partition_id。

您正在路上並了解 CTE 和視窗函式，所以這裡有一個選項：

With AvgSize As
(
SELECT
    AVG(Data_Wanted) As AveragePartitionSize
   ,[partition_id]
FROM Tble
GROUP BY [partition_id]
)

,LatestValue As
(
SELECT
     surrogate_id
    ,collection_ts
   ,ROW_NUMBER() OVER(PARTITION BY surrogate_id ORDER BY collection_ts DESC) RowNumber
FROM example_table
)

SELECT
   *
FROM BaseTable BT
JOIN AvgSize
   ON BT.[partition_id] = AvgSize.[partition_id]
JOIN LatestValue LV
   ON BT.surrogate_id = LV.surrogate_id
   AND BT.collection_ts = LV.collection_ts
   AND LV.RowNumber = 1
WHERE 
   BT.Data_Wanted > AveragePartitionSize

CTE AvgSize 建立分區 ID 的平均值。

CTE LatestValue 是您提供的 CTE，僅限於建立最新行的必要列。

最後一步是將它們放在一起。CTE 將查詢限制為最新行並提供分區平均值。剩下要做的就是將最新的行分區大小與平均分區大小進行比較，然後就剩下結果集了。

您可以通過變異數檢查獲得更高級的資訊，例如：

ABS(AveragePartitionSize - BT.Data_Wanted) > 20

引用自：https://dba.stackexchange.com/questions/116666