Eu tenho esta tabela no servidor SQL:
| data | var | vale |
|---|---|---|
| 2022-2-1 | UM | 1.1 |
| 2022-3-1 | UM | 2.3 |
| 2022-4-1 | UM | 1.5 |
| 2022-5-1 | UM | 1.7 |
| 2022-09-1 | B | 1.8 |
| 2022-10-1 | B | 1.9 |
| 2022-11-1 | B | 2.1 |
| 2022-12-1 | B | 2.22 |
Quero agrupar por coluna var e date e implementar a média expansiva e o desvio padrão para criar um intervalo superior e inferior de um desvio padrão. No final, quero que cada entrada na coluna val (exceto a primeira) seja verificada se cai dentro do intervalo do tempo anterior (ou lag 1).
Como posso fazer isso no SQL Server?
Minha tentativa
-- Create the table
CREATE TABLE DataTable (
[date] DATE,
var CHAR(1),
val DECIMAL(4, 2)
);
-- Insert the data
INSERT INTO DataTable ([date], var, val)
VALUES
('2022-02-01', 'A', 1.1),
('2022-03-01', 'A', 2.3),
('2022-04-01', 'A', 1.5),
('2022-05-01', 'A', 1.7),
('2022-09-01', 'B', 1.8),
('2022-10-01', 'B', 1.9),
('2022-11-01', 'B', 2.1),
('2022-12-01', 'B', 2.22);
WITH DataWithStats AS (
SELECT
[date],
var,
val,
AVG(val) OVER (PARTITION BY var ORDER BY [date]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_avg,
STDEV(val) OVER (PARTITION BY var ORDER BY [date]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS exp_std
FROM DataTable
)
SELECT
[date],
var,
exp_avg - 2 * exp_std AS lower,
val,
exp_avg + 2 * exp_std AS upper,
CASE
WHEN val < LAG(exp_avg - 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date]) OR
val > LAG(exp_avg + 2 * exp_std, 1) OVER (PARTITION BY var ORDER BY [date])
THEN 'Warning'
ELSE 'ok'
END AS status
FROM DataWithStats;
O problema com minha tentativa é que a coluna de verificação não avalia corretamente a verificação.
https://sqlfiddle.com/sql-server/online-compiler?id=fe2e764f-9627-418f-bd08-dd13250b0dab
Se você acredita que o status "warning" está incorreto nos resultados da consulta, porque 2.10 não está fora do intervalo exibido de 1.62782795366962 - 2.23883804633038, então acredito que o problema é que seus valores exibidos não são os usados para calcular o status. Os valores de média e desvio padrão LAG() são usados em vez dos valores atuais. O valor 2.10 está fora do intervalo real de 1.7085786437627 - 1.9914213562373 usado pelo teste.
Os resultados estavam corretos (conforme codificados), mas a apresentação era enganosa.
Resultados originais:
Resultados mostrando o intervalo real usado pelo cálculo de status.
Observe que
STDEV()será nulo e nenhum intervalo será calculado até que haja pelo menos dois valores anteriores.A consulta a seguir modifica o intervalo de janelas no CTE, de modo que isso
LAG()não seja necessário na consulta final. Os valores de limite de intervalo exibidos são os mesmos usados no teste.Veja este db<>fiddle .