sql - Efficient time series querying in Postgres -

sql - Efficient time series querying in Postgres -

i have table in pg db looks this:

id | widget_id | for_date | score |

each referenced widget has lot of these items. it's 1 per day per widget, there gaps.

what want result contains widgets each date since x. dates brought in via generate series:

select date.date::date generate_series('2012-01-01'::timestamp time zone,'now'::text::date::timestamp time zone, '1 day') date(date) order date.date desc;

if there no entry date given widget_id, want utilize previous one. widget 1337 doesn't have entry on 2012-05-10, on 2012-05-08, want resultset show 2012-05-08 entry on 2012-05-10 well:

actual data: widget_id | for_date | score 1312 | 2012-05-07 | 20 1337 | 2012-05-07 | 12 1337 | 2012-05-08 | 41 1337 | 2012-05-11 | 500 desired output based on generate series: widget_id | for_date | score 1336 | 2012-05-07 | 20 1337 | 2012-05-07 | 12 1336 | 2012-05-08 | 20 1337 | 2012-05-08 | 41 1336 | 2012-05-09 | 20 1337 | 2012-05-09 | 41 1336 | 2012-05-10 | 20 1337 | 2012-05-10 | 41 1336 | 2012-05-11 | 20 1337 | 2012-05-11 | 500

eventually want boil downwards view have consistent info sets per day can query easily.

edit: made sample info , expected resultset clearer

sql fiddle

select widget_id, for_date, case when score not null score else first_value(score) on (partition widget_id, c order for_date) end score ( select a.widget_id, a.for_date, s.score, count(score) over(partition a.widget_id order a.for_date) c ( select widget_id, g.d::date for_date ( select distinct widget_id score ) s cross bring together generate_series( (select min(for_date) score), (select max(for_date) score), '1 day' ) g(d) ) left bring together score s on a.widget_id = s.widget_id , a.for_date = s.for_date ) s order widget_id, for_date

sql postgresql