1

This question already has an answer here:

I am using PostgreSQL and I am trying to create a view of data:

forecast_datetime, file_date, thing_id, thing_name, facts...

The forecast_datetime column is a datetime for each 30 minute interval. The issue is that the forecast is for 1 month into the future which causes a lot of duplicate data. (so today, there will be a forecast for 2016-12-25 01:30:00 and then tomorrow there will be a forecast for the same interval, over and over again potentially until the period actually happens.).

The view I want to create is to just take a look at the last forecast which was appended to the table. This is indicated by the file_date column (as the name suggests, I receive a daily forecast file which is appended to the database. This column indicates the date from the filename).

I basically want to filter all rows for each thing_id and select the forecast_datetime columns where there file_date was the maximum date for that forecast. So each thing_id should only have one row per unique forecast_datetime interval which is based on the latest file_date.

CREATE OR REPLACE VIEW schema.view AS 
SELECT DISTINCT ON (thing_id, forecast_datetime, file_date)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id, 
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  file_date DESC 

Would that query do the trick? I can do this in pandas/python easily, but I am newer to SQL and wanted to try a view instead of writing a new physical table.

Here is some sample data after applying the query I wrote above this. Because I included the the file_date in the SELECT DISTINCT query, there is still a repeat row which duplicates each forecast_datetime. This causes issues when I join this data based on the thing_id and forecast_datetime back to the actual results table.

forecast_datetime   file_date   thing_id
2016-12-12 20:30    2016-12-11  99
2016-12-12 20:30    2016-12-10  99
2016-12-12 20:30    2016-12-09  99
2016-12-12 20:30    2016-12-08  99

I would like to only have the row with the max file_date. In the example above, that would be the first row (2016-12-11). The other rows would be dropped from the view.

marked as duplicate by billinkc, Andriy M, LowlyDBA, Erik, Philᵀᴹ Dec 20 '16 at 9:24

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Wouldn't it make sense to store the forecasts (without the data points) in a separate table and to use a FK from the existing table pointing to it? This would make your query a no-brainer. – dezso Dec 12 '16 at 14:11
  • 2
    I think you need SELECT DISTINCT ON (thing_id, forecast_datetime) ... but it would be better if you provided some sample data. – ypercubeᵀᴹ Dec 12 '16 at 14:49
  • I added some sample data. Yeah, I need the distinct to only be on the thing_id and forecast_datetime and then, somehow, only keep the rows with the max file_date. – trench Dec 12 '16 at 15:15
  • 2
    No, the query you have in the last edit seems fine. You should post it as an answer and remove it from the question. – ypercubeᵀᴹ Dec 12 '16 at 17:09
  • Cool. I assume that the select distinct executes after the order by? Is that a general rule? – trench Dec 12 '16 at 17:11
0
CREATE OR REPLACE VIEW schema.view AS 
SELECT DISTINCT ON (thing_id, forecast_datetime)
       forecast_datetime,
       interval,
       time_zone,
       file_date,
       thing_id, 
       thing_name,
       other_cols...
FROM   schema.orig_table
ORDER  BY  thing_id, forecast_datetime ASC, file_date DESC 

This appears to have worked perfectly.

  • 1
    Can you describe what was different or what you used from other people to make this the right answer? – jcolebrand Dec 12 '16 at 17:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.