CODEWITHSUNDEEP
sqlsqlitegrafanaquantilerolling-average
mar79s
mar79s

Reputation: 1

calculation of rolling average with removed outliers using quantile or similar function

I would really appreciate if someone could help me to come out with sql code to calculate rolling average of sensor data “Y” in sqlite database. “time” is the unique column.

I would like to calculate “Y_clean_avg". It is rolling average of “Y” within a window when outliers are removed. Outliers are Y values outside 0.9 quantile in a rolling window. In python I use following function:

df['Y_clean_avg'] = df['Y'].rolling(window=20, min_periods=1).quantile(0.90)

Tanks a lot!

Sql below works ok to get average or min, but I really need to get the outliers first.

SELECT 
time,
Y0,
AVG(Y0) OVER (ORDER BY time ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS Y_avg,
MIN(Y0) OVER (ORDER BY time ROWS BETWEEN 20 PRECEDING AND CURRENT ROW) AS Y_min

FROM 
    sensor_data;

Upvotes: 0

Views: 63

Answers (1)

colin
colin

Reputation: 25

Suggestion..? A bit beyond the limits of my working knowledge... but can you not do an intermediate select (or function or view) where you select rows with values within limits you calculate from agv, max and min? There is probably some way to then work this into a rolling window formulation. Good luck!

Upvotes: 0

Related Questions