我的PG数据库中有一个表,看起来有点像这样：
id | widget_id | for_date | score |
每个引用的小部件都有很多这些项目.每个小部件每天总是1个,但是存在差距.
我想得到的结果是包含自X以来每个日期的所有小部件.日期通过生成系列引入：
SELECT date.date::date
FROM generate_series('2012-01-01'::timestamp with time zone,'now'::text::date::timestamp with time zone,'1 day') date(date)
ORDER BY date.date DESC;
如果没有给定widget_id的日期条目,我想使用前一个.所以说小工具1337在2012-05-10没有条目,但在2012-05-08,那么我希望结果集在2012-05-10也显示2012-05-08条目：
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
最终我想把它归结为一个视图,所以我每天都有一致的数据集,我可以轻松查询.
编辑：使样本数据和预期结果集更清晰
解决方法
SQL Fiddle
select
widget_id,for_date,case
when score is not null then score
else first_value(score) over (partition by widget_id,c order by for_date)
end score
from (
select
a.widget_id,a.for_date,s.score,count(score) over(partition by a.widget_id order by a.for_date) c
from (
select widget_id,g.d::date for_date
from (
select distinct widget_id
from score
) s
cross join
generate_series(
(select min(for_date) from score),(select max(for_date) from score),'1 day'
) g(d)
) a
left join
score s on a.widget_id = s.widget_id and a.for_date = s.for_date
) s
order by widget_id,for_date
