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
2003-01-03 (null) 0.003
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
Whats exactly is wrong with the following query?
select
dx date,
nx,
nx1
from
test t
join test1 t1 on t.dx=t1.dx1
;
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
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