0

With the following query I can use the LAG() function to repeat the last non null value of c column:

SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000)               open_time,
   coalesce(o, LAG(c) over w)                                                                          o,
   coalesce(h, LAG(c) over w)                                                                          h,
   coalesce(l, LAG(c) over w)                                                                          l,
   coalesce(c, LAG(c) over w)                                                                          c,
   coalesce(v, 0)                                                                                      v,
   coalesce(close_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000 + ((60000 * 1) - 1)) close_time
from temporary_timeframe 
window w as (order by datetime);

Getting the following result:

enter image description here

But I need to repeat the value of the c column while the current column value is null. I see that if PostgreSQL supports IGNORE NULLS attribute on window functions this would be solved. How to solve this without IGNORE NULLS?

1

LAG() doesn't repeat the last non null value.

Quoted from docs

returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value)

But you can set a partition depending on one column value and then use firts_value() function.

create table tbl (id int, a int, b int, c int);
insert into tbl values
(1, 12, 4, 3),
(2, 10, 10, 5),
(3, 6, 12, 23),
(4, 6, null, 10),
(5, 7, null, 4),
(6, 1, 8, 10),
(7, 4, null, 3);
7 rows affected
select id, a, first_value(b) over (partition by grp) as b, c
from (
      select id, a, b, c, 
             sum(case when b is not null then 1 end) over (order by id) as grp
      from   tbl
     ) t
id |  a |  b |  c
-: | -: | -: | -:
 1 | 12 |  4 |  3
 2 | 10 | 10 |  5
 3 |  6 | 12 | 23
 4 |  6 | 12 | 10
 5 |  7 | 12 |  4
 6 |  1 |  8 | 10
 7 |  4 |  8 |  3

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.