这是@Erwin对Postgres中的有效时间序列查询的回答中的一个问题。
为了使事情简单，我将使用与该问题相同的表结构
id | widget_id | for_date | score |
最初的问题是要获取范围内每个日期的每个小部件的分数。如果日期没有小部件的条目，则显示该小部件上一个条目的得分。如果所有数据都包含在您要查询的范围内，则使用交叉联接和窗口函数的解决方案效果很好。我的问题是，即使以前的分数超出了我们正在查看的日期范围，我也想要它。
示例数据：
INSERT INTO score (id, widget_id, for_date, score) values
(1, 1337, '2012-04-07', 52),
(2, 2222, '2012-05-05', 99),
(3, 1337, '2012-05-07', 112),
(4, 2222, '2012-05-07', 101);
当我查询2012年5月5日至5月10日的范围（即generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')）时，我希望获得以下信息：
DAY WIDGET_ID SCORE
May, 05 2012 1337 52
May, 05 2012 2222 99
May, 06 2012 1337 52
May, 06 2012 2222 99
May, 07 2012 1337 112
May, 07 2012 2222 101
May, 08 2012 1337 112
May, 08 2012 2222 101
May, 09 2012 1337 112
May, 09 2012 2222 101
May, 10 2012 1337 112
May, 10 2012 2222 101
迄今为止最好的解决方案（也是@Erwin的解决方案）是：
SELECT a.day, a.widget_id, s.score
FROM (
SELECT d.day, w.widget_id
,max(s.for_date) OVER (PARTITION BY w.widget_id ORDER BY d.day) AS effective_date
FROM (SELECT generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date AS day) d
CROSS JOIN (SELECT DISTINCT widget_id FROM score) AS w
LEFT JOIN score s ON s.for_date = d.day AND s.widget_id = w.widget_id
) a
LEFT JOIN score s ON s.for_date = a.effective_date AND s.widget_id = a.widget_id
ORDER BY a.day, a.widget_id;
但是，正如您在此SQL Fiddle中所看到的那样，它在前两天为小部件1337生成了空分数。我希望看到第1行的得分为52。
有可能以一种有效的方式做到这一点吗？
如@Roman所述，DISTINCT ON可以解决此问题。相关答案的详细信息：
子查询通常比CTE快一点：
SELECT DISTINCT ON (d.day, w.widget_id)
d.day, w.widget_id, s.score
FROM generate_series('2012-05-05'::date, '2012-05-10'::date, '1d') d(day)
CROSS JOIN (SELECT DISTINCT widget_id FROM score) AS w
LEFT JOIN score s ON s.widget_id = w.widget_id AND s.for_date <= d.day
ORDER BY d.day, w.widget_id, s.for_date DESC;
您可以使用集合返回函数，例如列表中的FROM表格。
一个多列索引应该是性能的关键：
CREATE INDEX score_multi_idx ON score (widget_id, for_date, score)
score仅包括第三列，以使其成为Postgres 9.2或更高版本中的覆盖索引。您不会在早期版本中包括它。
当然，如果您有许多小部件且工作日很长，则会CROSS JOIN产生很多行，并带有价格标签。仅选择您实际需要的小部件和日期。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句