Hi,
My postgresql-fu is not good enough to write a query to achieve this
(some may well say r is a better suited tool to achieve this !).
I need to calculate what I would call a correlation window on a time
series of data, my table looks like this :
create table data(data_date date,data_measurement numeric);
insert into data values('2016-01-01',16.23);
<etc>
insert into data values('2016-06-19',30.54);
My "target sample" would be the N most recent samples in the table
(e.g. 20, the most recent 20 days)
My "potential sample" would be a moving window of size N (the same
size N as above), starting at T0 (i.e. 2016-01-01 in this example) and
incrementing by one (i.e. 2016-01-01 + 20, then 2016-01-02+20 etc),
but the "target sample" would obviously be excluded.
The output needs to display window date range (or at least the start
date of the "potential sample" window) and the result
corr(target,potential).
Hope that makes sense
Tim