Re: Populating missing dates in postgresql data - Mailing list pgsql-general
| From | Adrian Klaver |
|---|---|
| Subject | Re: Populating missing dates in postgresql data |
| Date | |
| Msg-id | 551354EB.9000603@aklaver.com Whole thread Raw |
| In response to | Populating missing dates in postgresql data ("Lavrenz, Steven M" <slavrenz@purdue.edu>) |
| List | pgsql-general |
On 03/25/2015 05:25 PM, Lavrenz, Steven M wrote: > 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! > Without seeing the actual query this is just a suggestion. I would say use CASE: http://www.postgresql.org/docs/9.3/interactive/functions-conditional.html#FUNCTIONS-CASE Where if the date was not available from table A use the one from table B. > Best Regards, > > Steve > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: