根據型別獲取記錄Z-Score-有解無憂

請考慮以下記錄：

Id        Type       Price
---------------------------
 1         1          100
 2         2          200
 3         1          150
 4         1          300
 5         2          200
 6         2          400
 7         2          500

基于這篇文章：使用普通 SQL 進行簡單例外檢測我想根據每種型別計算 Z-Score。我的意思是首先我想根據Type列對記錄進行磁區，然后在每個磁區中計算 Z-Score。我寫了這個查詢，但我不知道如何將它與Partition函式結合起來：

WITH series AS (
SELECT Price
FROM MyTable AS n
),
     stats AS (
     SELECT avg(Price) series_avg,
            stddev(Price) as series_stddev
FROM series
),
zscores AS (
              SELECT  Id, Type, Price, (price - series_avg) / series_stddev AS zscore
              FROM  series, stats
           )
SELECT *,case when zscore NOT BETWEEN -1 AND 1 then 'T' else 'F' end AS is_anomaly
FROM zscores;

謝謝

uj5u.com熱心網友回復：

當您計算 AVG 和 STDEV 時，只需按型別分組。

然后將資料加入到型別的摘要中。

WITH CTE_DATA AS (
  SELECT Id, [Type], Price
  FROM YourTable
)
, CTE_STATS AS (
  SELECT
    [Type]
  , AVG(Price) AS series_avg
  , STDEV(Price) AS series_stddev
  FROM CTE_DATA
  GROUP BY [Type]
)
, CTE_ZSCORES AS (
  SELECT d.Id, d.[Type], d.Price
  , (d.price - st.series_avg) / st.series_stddev AS zscore
  FROM CTE_DATA d
  JOIN CTE_STATS st ON d.[Type] = st.[Type]
)
SELECT *
, CASE 
  WHEN zscore NOT BETWEEN -1 AND 1 
  THEN 'T' 
  ELSE 'F' 
  END AS is_anomaly
FROM CTE_ZSCORES
ORDER BY [Type], Id;

身份證 | 型別 | 價格 | 分數 | is_anomaly
-: | ---: | ----: | -----------------: | :---------
 1 | 1 | 100 | -0.797438205949334 | F         
 3 | 1 | 150 | -0.317053744534072 | F         
 4 | 1 | 300 | 1.12409963971171 | 噸         
 2 | 2 | 200 | -0.833333333333333 | F         
 5 | 2 | 200 | -0.833333333333333 | F         
 6 | 2 | 400 | 0.5 | F         
 7 | 2 | 500 | 1.16666666666667 | 噸

或者使用帶有磁區的視窗函式。

SELECT *
, CASE 
  WHEN zscore NOT BETWEEN -1 AND 1 
  THEN 'T' 
  ELSE 'F' 
  END AS is_anomaly
FROM
(
  SELECT Id, [Type], Price
  , zscore = (Price - AVG(Price) OVER (PARTITION BY [Type])) / STDEV(Price) OVER (PARTITION BY [Type])
  FROM YourTable
) q
ORDER BY [Type], Id

身份證 | 型別 | 價格 | 分數 | is_anomaly
-: | ---: | ----: | -----------------: | :---------
 1 | 1 | 100 | -0.797438205949334 | F         
 3 | 1 | 150 | -0.317053744534072 | F         
 4 | 1 | 300 | 1.12409963971171 | 噸         
 2 | 2 | 200 | -0.833333333333333 | F         
 5 | 2 | 200 | -0.833333333333333 | F         
 6 | 2 | 400 | 0.5 | F         
 7 | 2 | 500 | 1.16666666666667 | 噸

在這里測驗db<>fiddle

uj5u.com熱心網友回復：

您可以使用視窗函式對基表進行一次掃描并且無需任何連接即可完成此操作

SELECT
  t.Id,
  t.Type,
  t.Price,
  (Price - AVG(t.Price) OVER (PARTITION BY t.Type)) /
    STDEV(t.Price) OVER (PARTITION BY t.Type) as zscore
FROM MyTable t

db<>小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456568.html

標籤：sql sql服务器分区

上一篇：將資料型別nvarchar轉換為數字時出錯

下一篇：意外輸出錯誤：將運算式轉換為資料型別int的算術溢位錯誤