Thread: Merging timeseries in postgres

Hi,

I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.

Assume I have a bunch of time-series tables :

create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
insert into test1 values('2002-01-02','0.002');
insert into test1 values('2003-01-03','0.002');

What I want to do is create a view that merges these together with
time as the index, i.e the output would look like :


2000-01-01  0.001  (null)
2002-01-02  (null)   0.002
2003-01-03  (null)   0.003

I can't quite figure out how to keep the index independent and make a
clean join, typical outer join constructs end up with results like :

 dx | nx |    dx1     |  nx1
----+----+------------+-------
    |    | 2002-01-02 | 0.001
    |    | 2003-01-02 | 0.002
(2 rows)

     dx     |  nx   |    dx1     |  nx1
------------+-------+------------+-------
 2000-01-02 | 0.005 |            |
            |       | 2002-01-02 | 0.001
            |       | 2003-01-02 | 0.002


Which isn't very pretty and doesn't really achieve what I want.

As I said "sql-writers block !"   ;-(

Tim

Whats exactly is wrong with the following query?

select
        dx date,
        nx,
        nx1
    from
        test t
            join test1 t1 on t.dx=t1.dx1
;



----- Original Message -----
From: "Tim Smith" <randomdev4+postgres@gmail.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, July 14, 2016 2:56:19 PM
Subject: [GENERAL] Merging timeseries in postgres

Hi,

I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.

Assume I have a bunch of time-series tables :

create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
insert into test1 values('2002-01-02','0.002');
insert into test1 values('2003-01-03','0.002');

What I want to do is create a view that merges these together with
time as the index, i.e the output would look like :


2000-01-01  0.001  (null)
2002-01-02  (null)   0.002
2003-01-03  (null)   0.003

I can't quite figure out how to keep the index independent and make a
clean join, typical outer join constructs end up with results like :

 dx | nx |    dx1     |  nx1
----+----+------------+-------
    |    | 2002-01-02 | 0.001
    |    | 2003-01-02 | 0.002
(2 rows)

     dx     |  nx   |    dx1     |  nx1
------------+-------+------------+-------
 2000-01-02 | 0.005 |            |
            |       | 2002-01-02 | 0.001
            |       | 2003-01-02 | 0.002


Which isn't very pretty and doesn't really achieve what I want.

As I said "sql-writers block !"   ;-(

Tim


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Nevermind, I misunderstood your question.

The answer is an outer join and if you want the exact output you provided then you can use the following clause.

coalesce(dx, dx1) as date

Is there any reason why these are two different tables? I'd consider changing data structure.

----- Original Message -----
From: "Tim Smith" <randomdev4+postgres@gmail.com>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Thursday, July 14, 2016 2:56:19 PM
Subject: [GENERAL] Merging timeseries in postgres

Hi,

I've got a bit of query-writers block ! I've tried various join styles
but can't get it to do what I want to achieve.

Assume I have a bunch of time-series tables :

create table test(dx date,n numeric);
create table test1(dx1 date,nx1 numeric);
insert into test values('2000-01-01','0.001');
insert into test1 values('2002-01-02','0.002');
insert into test1 values('2003-01-03','0.002');

What I want to do is create a view that merges these together with
time as the index, i.e the output would look like :


2000-01-01  0.001  (null)
2002-01-02  (null)   0.002
2003-01-03  (null)   0.003

I can't quite figure out how to keep the index independent and make a
clean join, typical outer join constructs end up with results like :

 dx | nx |    dx1     |  nx1
----+----+------------+-------
    |    | 2002-01-02 | 0.001
    |    | 2003-01-02 | 0.002
(2 rows)

     dx     |  nx   |    dx1     |  nx1
------------+-------+------------+-------
 2000-01-02 | 0.005 |            |
            |       | 2002-01-02 | 0.001
            |       | 2003-01-02 | 0.002


Which isn't very pretty and doesn't really achieve what I want.

As I said "sql-writers block !"   ;-(

Tim


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for the replies guys.  In the end I found an example lurking in the depths of the Internet that used a CTE and a join to that which seemed to have the desired effect

On Thursday, 14 July 2016, Begin Daniel <jfd553@hotmail.com> wrote:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G. Johnston
Sent: Thursday, 14 July, 2016 08:23
To: Nick Babadzhanian
Cc: Tim Smith; pgsql-general
Subject: Re: [GENERAL] Merging timeseries in postgres

 

On Thu, Jul 14, 2016 at 8:18 AM, Nick Babadzhanian <nb@cobra.ru> wrote:

Whats exactly is wrong with the following query?

select
        dx date,
        nx,
        nx1
    from
        test t
            join test1 t1 on t.dx=t1.dx1
;

 

 

​Please don't top-post.

 

test t join test1 t1 -- this is the default inner join, your query returns no records for the given data.

 

David J.

​

Look at the above documentation (7.2.1.1) on full join

https://www.postgresql.org/docs/9.3/static/queries-table-expressions.html

 

select coalesce(dx,dx1)as dt, n, nx1

from test full join test1 on dx=dx1;

 

Daniel