Postgres中的时间序列查询

Question

这是对 @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示例中所看到的，它会在前两天为小部件1337生成空分数。我希望能够看到第一行中52的早期得分。

有没有一种高效的方法来实现这个需求？

- bpaul

Answer 1

就像你写的一样，需要找到匹配得分，但如果有间隔 - 请使用最近的早期得分填充。在SQL中，它将是：

SELECT d.day, w.widget_id, 
  coalesce(s.score, (select s2.score from score s2
    where s2.for_date<d.day and s2.widget_id=w.widget_id order by s2.for_date desc limit 1)) as score
from (select distinct widget_id FROM score) AS w
cross join (SELECT generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date AS day) d
left join score s ON (s.for_date = d.day AND s.widget_id = w.widget_id)
order by d.day, w.widget_id;

在这种情况下，Coalesce 的意思是“如果有间隙”。

- Tomasz Myrta

---

非常好的解决方案，谢谢。对于大数据集来说，这似乎是迄今为止最快的解决方案。 - bpaul

Answer 2

您可以在PostgreSQL中使用distinct on语法。

with cte_d as (
    select generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date as day
), cte_w as (
    select distinct widget_id from score
)
select distinct on (d.day, w.widget_id)
    d.day, w.widget_id, s.score
from cte_d as d
    cross join cte_w as w
    left outer join score as 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;

使用子查询获取最大日期：

with cte_d as (
    select generate_series('2012-05-05'::date, '2012-05-10'::date, '1d')::date as day
), cte_w as (
    select distinct widget_id from score
)
select
    d.day, w.widget_id, s.score
from cte_d as d
    cross join cte_w as w
    left outer join score as s on s.widget_id = w.widget_id
where
    exists (
        select 1
        from score as tt
        where tt.widget_id = w.widget_id and tt.for_date <= d.day
        having max(tt.for_date) = s.for_date
    )
order by d.day, w.widget_id;

性能实际上取决于您在表上拥有的索引（如果可能，唯一的widget_id, for_date）。我认为，如果每个widget_id有很多行，则第二个选项将更有效，但您必须在自己的数据上进行测试。 >> sql fiddle demo <<

- Roman Pekar

---

谢谢您的回答。选择distinct似乎是正确的方法，但我认为@Erwin的解决方案更加简洁高效。 - bpaul

Answer 3

正如@Roman提到的那样，DISTINCT ON可以解决这个问题。详见相关答案：

• 在每个GROUP BY组中选择第一行？

子查询通常比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列表中使用表格一样使用返回集函数。

SQL Fiddle

一个多列索引应该是性能的关键：

CREATE INDEX score_multi_idx ON score (widget_id, for_date, score)

第三列score仅用于使其成为Postgres 9.2或更高版本中的覆盖索引。在早期版本中不需要包含它。

当然，如果您有许多小部件和广泛的日期范围，则CROSS JOIN会产生大量行，这是有代价的。只选择您实际需要的小部件和日期。

- Erwin Brandstetter

---

这个程序可以运行，但是随着行数的增加，速度变得非常慢。我有40-50k行数据，需要超过2分钟才能完成。是交叉连接中记录数量导致速度变慢吗？ - bpaul

@bpaul，你的表上有索引吗？ - Roman Pekar

@bpaul：特别是一个（可能是覆盖的）多列索引。我添加了一些细节。 - Erwin Brandstetter

@RomanPekar，@Erwin 目前我分别在 widget_id 和 for_date 上建立索引。我将添加多列索引并回报。我使用的是 Postgres 9.1.10，所以我会做 widget_id, for_date。 - bpaul

多列索引并没有起到太大的帮助。我现在正在聚合表中缓存更大查询的值。 - bpaul