Help needed structuring Postgresql correlation query

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

Responses

Browse pgsql-general by date

  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