From: | Nick Babadzhanian <nb(at)cobra(dot)ru> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Merging timeseries in postgres |
Date: | 2016-07-14 12:18:28 |
Message-ID: | 663129846.34292.1468498708144.JavaMail.zimbra@cobra.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
;
----- Original Message -----
From: "Tim Smith" <randomdev4+postgres(at)gmail(dot)com>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Nick Babadzhanian | 2016-07-14 12:22:56 | Re: Merging timeseries in postgres |
Previous Message | David G. Johnston | 2016-07-14 12:11:26 | Re: Merging timeseries in postgres |