postgresql – 如何有效地选择以前的非空值？

我在Postgres中有一张表，看起来像这样：

# select * from p;
 id | value 
----+-------
  1 |   100
  2 |      
  3 |      
  4 |      
  5 |      
  6 |      
  7 |      
  8 |   200
  9 |          
(9 rows)

我想查询，使其看起来像这样：

# select * from p;
 id | value | new_value
----+-------+----------
  1 |   100 |    
  2 |       |    100
  3 |       |    100
  4 |       |    100
  5 |       |    100
  6 |       |    100
  7 |       |    100
  8 |   200 |    100
  9 |       |    200
(9 rows)

我可以用select中的子查询来做这个，但是在我的实际数据中，我有20k行或更多的行，它的速度很慢。

这是否可以在窗口函数中执行？我很乐意使用lag()，但似乎并不支持 IGNORE NULLS选项。

select id,value,lag(value,1) over (order by id) as new_value from p;
 id | value | new_value
----+-------+-----------
  1 |   100 |      
  2 |       |       100
  3 |       |      
  4 |       |
  5 |       |
  6 |       |
  7 |       |
  8 |   200 |
  9 |       |       200
(9 rows)

我发现也可以在Postgres中使用sql Server的 this answer。从来没有这样做，我以为这个技巧很聪明。基本上，他通过使用嵌套查询内的case语句为窗口函数创建一个自定义分区，当该值不为空时，它将增加一个总和，否则单独留下。这样就可以使用与前一个非空值相同的数字来描绘每个空白部分。这是查询：

SELECT
  id,value_partition,first_value(value) over (partition by value_partition order by id)
FROM (
  SELECT
    id,sum(case when value is null then 0 else 1 end) over (order by id) as value_partition

  FROM p
  ORDER BY id ASC
) as q

结果：

id | value | value_partition | first_value
----+-------+-----------------+-------------
  1 |   100 |               1 |         100
  2 |       |               1 |         100
  3 |       |               1 |         100
  4 |       |               1 |         100
  5 |       |               1 |         100
  6 |       |               1 |         100
  7 |       |               1 |         100
  8 |   200 |               2 |         200
  9 |       |               2 |         200
(9 rows)

postgresql – 如何有效地选择以前的非空值？

猜你在找的Postgre SQL相关文章