Accueil‎ > ‎

MAterialized view of average - Backup query

posted 14 May 2019, 04:30 by Christophe Noël   [ updated 14 May 2019, 04:44 ]
SELECT time, value, average, average2 FROM (SELECT 
 row_number() OVER(ORDER BY time) AS ROWNUM,
time ,
value,
AVG(value) OVER(ORDER BY time ROWS 1 PRECEDING) as average,
AVG(value) OVER(ORDER BY time ROWS 2 PRECEDING) as average2
from cnl ORDER BY time)  AS cnl
WHERE mod(ROWNUM,1)=0 LIMIT 10;

CREATE MATERIALIZED VIEW dataset_table_resampled AS
SELECT time  AS time,
AVG(value) OVER(ORDER BY time ROWS 10 PRECEDING)  AS average10,
MIN(value) OVER(ORDER BY time ROWS 10 PRECEDING)  AS minimum10,
MAX(value) OVER(ORDER BY time ROWS 10 PRECEDING)  AS maximum10,
AVG(value) OVER(ORDER BY time ROWS 100 PRECEDING)  AS average100,
MIN(value) OVER(ORDER BY time ROWS 100 PRECEDING)  AS minimum100,
MAX(value) OVER(ORDER BY time ROWS 100 PRECEDING)  AS maximum100,
FROM dataset_table
ORDER BY time;

Comments