From: | "Lavrenz, Steven M" <slavrenz(at)purdue(dot)edu> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Populating missing dates in postgresql data |
Date: | 2015-03-26 00:25:09 |
Message-ID: | A6E66984D41D2643B95A86C24AB9BBF344B2F9DC@wpvexcmbx02.purdue.lcl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alright everyone, this is a doozy of a problem. I am new to Postgres so I appreciate patience/understanding. I have a database of hardware objects, each of which has several different "channels". Once per day, these channels are supposed to check in with a central server, generating an event log table (TABLE A) like the following:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-01
286 2 2014-12-02
286 5 2014-12-01
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
And so on. Occasionally, communications will break down to the hardware, such that no reporting occurs. For example, let's say that object 286 loses communications on 12/1/2014. Then the table might look like:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-02
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
Or let's say that for some reason, just channel 2 loses reporting for a day. Then we would have:
object_id channel check-in date
****************************************
990 1 2014-12-01
990 1 2014-12-02
990 2 2014-12-01
990 2 2014-12-02
286 2 2014-12-02
286 5 2014-12-01
286 5 2014-12-02
4507 1 2014-12-01
4507 1 2014-12-02
4507 2 2014-12-01
4507 2 2014-12-02
I have a second table (TABLE B) with all of the object_ids and channels that are supposed to be reporting in each day. For cases where a certain channel does not check in, I want to add a column that indicates the comm failure. So, for the example where all channels on object 286 do not check in, I would like to get is something like this:
object_id channel check-in date comm failure
**********************************************************
990 1 2014-12-01 No
990 1 2014-12-02 No
990 2 2014-12-01 No
990 2 2014-12-02 No
286 2 2014-12-01 Yes
286 2 2014-12-02 No
286 5 2014-12-01 Yes
286 5 2014-12-02 No
4507 1 2014-12-01 No
4507 1 2014-12-02 No
4507 2 2014-12-01 No
4507 2 2014-12-02 No
I have been racking my mind for the better part of a day on how to do this. The thing is that I can do a right join of TABLE B on TABLE A, and this will populate the missing object ids and channels. However, this only works for a single day, and it gives me something like:
object_id channel check-in date comm failure
**********************************************************
990 1 2014-12-01 No
990 1 2014-12-02 No
990 2 2014-12-01 No
990 2 2014-12-02 No
286 2 Yes
286 2 2014-12-02 No
286 5 Yes
286 5 2014-12-02 No
4507 1 2014-12-01 No
4507 1 2014-12-02 No
4507 2 2014-12-01 No
4507 2 2014-12-02 No
I need to do a count of comm failures by day, so I need to populate the check-in date field. Please help!
Best Regards,
Steve
*************************************************
Steven Lavrenz, MS, EIT
Doctoral Research Fellow, Ph.D. Candidate
Purdue University | Transportation Engineering
Hampton Hall of Civil Engineering, Room 1122
550 Stadium Mall Drive
West Lafayette, IN 47907
765-775-6423
slavrenz(at)purdue(dot)edu<https://exchange.purdue.edu/owa/redir.aspx?C=1KvRJRKXRUmCQXrzhc8xxDD2sumyJNIICxsOWx4t9faOapFqc_-gBVMV21AYScHluQX0uMJmOYA.&URL=mailto%3aslavrenz%40purdue.edu>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-03-26 00:38:03 | Re: Populating missing dates in postgresql data |
Previous Message | Craig Ringer | 2015-03-26 00:24:00 | Re: BDR - triggers on receiving node? |