如果缺少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; 
  

表事件有 id ，创建和其他一些列，但只有那些在这里相关。上面的查询给出了给定的 trunc 时间段生成的上一个事件的 id （感谢 DISTINCT ON 每个周期我都会得到一个很好的聚合）。

现在，这个查询产生 NULL 如果在特定时间段内没有发生任何事件。我希望它返回以前可用的 id ，即使它来自不同的时间段。即：

 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 
  

我相信我错过了一些简单的方法来完成这一点。任何建议？

解决方案

c $ c> WITH t as（
SELECT time_series as trunc
FROM generate_series（'2013-02-27 22:00':: timestamp，'2013-02-28 2:00'，
'1小时'）as time_series
）
SELECT DISTINCT ON（t.trunc）t.trunc，e.id
FROM t
JOIN事件e
ON e.created< t.trunc
ORDER BY t.trunc，e.created DESC

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

I have a following query:

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

which yields the following result:

"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";

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).

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?

解决方案

Try:

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屋！