...
This SQL statement pre-generate bins using "generate_series()" and tstzrange type; then it uses filter with avg function instead of join
Code Block | ||
---|---|---|
| ||
with bin as (
select tstzrange(s, s+'1 year'::interval) as r
from generate_series('2002-01-01 00:00:00-05'::timestamp, '2017-12-31 23:59:59-05'::timestamp, '1 year') as s
)
select
bin.r,
avg(cast( data->> 'pH' as DOUBLE PRECISION)) filter(where datapoints.start_time <@ bin.r)
from datapoints, bin
where datapoints.stream_id = 1584
group by 1
order by 1;
|
Method 4: Using aggregate function with "over" and "window" (not working yet)
Jong Lee Looked into this option; but couldn't find a way to do it. Jong Lee may not understand the functionality.
Method 5: Using procedure function (PL/pgsql or PL/python)
TODO...
Performance
Tested with GLGT production database. Used the stream_id 1584 which has 987,384 datapoints. Used "explain analyze"
...