自定义窗口函数忽略postgres中的空值

thtygnil 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(431)

postgres中的表如下所示：

timestamp           | a  | b
--------------------+----+---------------------
2016-01-01 00:00:00 | 1  | 10
2016-01-01 00:00:01 | 2  | 11
2016-01-01 00:00:02 | 3  | 
2016-01-01 00:00:03 | 4  | 12
2016-01-01 00:00:04 | 5  |  
2016-01-01 00:00:05 | 6  |  
2016-01-01 00:00:06 | 7  |  
2016-01-01 00:00:07 | 8  |  
2016-01-01 00:00:08 | 9  |  
2016-01-01 00:00:09 | 10 | 13
2016-01-01 00:00:10 | 11 |  
2016-01-01 00:00:11 | 12 |  
2016-01-01 00:00:12 | 13 | 14

我想访问查询中以前的几个非空值。假设我有以下伪查询：

SELECT timestamp,
   a,
   b,
   2 * (LAG(b, 1) IGNORE NULLS OVER (ORDER BY timestamp)) + 
   3 * (LAG(b, 2) IGNORE NULLS OVER (ORDER BY timestamp)) as calc
FROM   tbl;

将获得以下结果：

timestamp           | a  | b   | calc
--------------------+----+-----+---------------
2016-01-01 00:00:00 | 1  | 10  | 
2016-01-01 00:00:01 | 2  | 11  | 
2016-01-01 00:00:02 | 3  |     | 52    (2*11 + 3*10)
2016-01-01 00:00:03 | 4  | 12  | 52    (2*11 + 3*10)
2016-01-01 00:00:04 | 5  |     | 57    (2*12 + 3*11)
2016-01-01 00:00:05 | 6  |     | 57    (2*12 + 3*11)    
2016-01-01 00:00:06 | 7  |     | 57    (2*12 + 3*11)    
2016-01-01 00:00:07 | 8  |     | 57    (2*12 + 3*11)    
2016-01-01 00:00:08 | 9  |     | 57    (2*12 + 3*11)    
2016-01-01 00:00:09 | 10 | 13  | 57    (2*12 + 3*11)
2016-01-01 00:00:10 | 11 |     | 62    (2*13 + 3*12)    
2016-01-01 00:00:11 | 12 |     | 62    (2*13 + 3*12)        
2016-01-01 00:00:12 | 13 | 14  | 62    (2*13 + 3*12)

谢谢

sql postgresql window-functions

来源：https://stackoverflow.com/questions/62221533/custom-window-function-ignoring-nulls-in-postgres

1条答案

按热度按时间

icomxhvb1#

横向连接是一种可能性：

select t.*, (t1.b * 2 + t2.b * 3)
from t left join lateral
     (select t1.*
      from t t1
      where t1.b is not null and t1.a < t.a
      order by t1.a desc
      limit 1
     ) t1 
     on true left join lateral
     (select t2.*
      from t t2
      where t2.b is not null and t2.a < t1.a
      order by t2.a desc
      limit 1
     ) t2
     on true;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-26

我来回答

自定义窗口函数忽略postgres中的空值

1条答案

相关问题

热门标签

最新问答