PostgreSQL 使用前一行的值(如果缺失) [英] PostgreSQL use value from previous row if missing

问题描述

我有以下问题:

WITH t as (
  SELECT date_trunc('hour', time_series) as trunc 
  FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', 
                       '1 hour') as time_series
  GROUP BY trunc
  ORDER BY trunc
)
SELECT DISTINCT ON(trunc) trunc, id
FROM t
LEFT JOIN (
   SELECT id, created, date_trunc('hour', created) as trunc_u
   FROM event
   ORDER BY created DESC
) u
ON trunc = trunc_u

产生以下结果:

"2013-02-27 22:00:00";
"2013-02-27 23:00:00";2
"2013-02-28 00:00:00";5
"2013-02-28 01:00:00";
"2013-02-28 02:00:00";

表 event 有 id、created 和其他一些列，但这里只有这些列相关.上面的查询为我提供了每个给定 trunc 时间段生成的最后一个事件的 id(感谢 DISTINCT ON 我在每个时间段得到了很好的聚合).

Table event has id, created and some other columns, but only those are relevant here. The query above gives me id of last event generated per given trunc time period (thanks to DISTINCT ON I get a nice aggregation per period).

现在，如果在给定时间段内没有发生任何事件，则此查询将产生 NULL.我希望它返回以前可用的 id，即使它来自不同的时间段.即:

Now, this query yields NULL if no events happened in given time period. I would like it to return the previous available id, even if it is from different time period. I.e.:

"2013-02-27 22:00:00";0
"2013-02-27 23:00:00";2
"2013-02-28 00:00:00";5
"2013-02-28 01:00:00";5
"2013-02-28 02:00:00";5

我确信我错过了一些简单的方法来实现这一点.有什么建议吗?

I am sure I am missing some easy way to accomplish this. Any advice?

推荐答案

试试:

WITH t as (
  SELECT time_series as trunc 
    FROM generate_series('2013-02-27 22:00'::timestamp, '2013-02-28 2:00', 
                         '1 hour') as time_series
)
SELECT DISTINCT ON(t.trunc) t.trunc, e.id
  FROM t
  JOIN event e
    ON e.created < t.trunc 
 ORDER BY t.trunc, e.created DESC

如果它太慢 - 告诉我.我会给你一个更快的查询.

If it is too slow - tell me. I will give you a faster query.

这篇关于PostgreSQL 使用前一行的值(如果缺失)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！