From: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Help needed structuring Postgresql correlation query |
Date: | 2016-06-19 08:58:58 |
Message-ID: | CA+HuS5HxYeXNuWnpXuBbu8xtFR0KvqkAvqq_SVVz0UapTjZutQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Allan Kamau | 2016-06-19 21:09:08 | Help with namespaces in xpath (PostgreSQL 9.5.3) |
Previous Message | Mike Sofen | 2016-06-18 21:06:28 | Re: OT hardware recommend |