Clickhouse fill empty rows with next non-empty value

Question

I have a table like this

date	id	price	desired previous value	desired next value
2019-08-17	1	5	5	5
2019-08-17	2	15.4	15.4	15.4
2019-08-18	1	0	5	5.6
2019-08-18	2	0	15.4	14
2019-08-19	1	0	5	5.6
2019-08-19	2	0	15.4	14
2019-08-20	1	0	5	5.6
2019-08-20	2	0	15.4	14
2019-08-21	1	5.6	5.6	5.6
2019-08-21	2	14	14	14

Its a result of this query:

SELECT a.date AS date,
       a.id AS id,
       p.price AS price
  FROM articles a
  LEFT JOIN (SELECT date,
                    id,
                    price
               FROM prices) p
    ON a.date = p.date
   AND a.id = p.id

because prices table does not have data for all dates. How can I fill zero price values with next or previous non zero value in clickhouse?

ver is 22.8.9.24

Answer 1

Try this:

WITH T AS 
(
SELECT c1 as date, c2 as id, c3 as price
FROM VALUES 
(
  (toDate ('2019-08-17'), 1, 5)     
, (toDate ('2019-08-17'), 2, 15.4)  
, (toDate ('2019-08-18'), 1, 0)     
, (toDate ('2019-08-18'), 2, 0)     
, (toDate ('2019-08-19'), 1, 0)     
, (toDate ('2019-08-19'), 2, 0)     
, (toDate ('2019-08-20'), 1, 0)     
, (toDate ('2019-08-20'), 2, 0)     
, (toDate ('2019-08-21'), 1, 5.6)   
, (toDate ('2019-08-21'), 2, 14)    
)
)
SELECT 
  *
, last_value (nullif (price, 0)) over (partition by id order by date)
    as prev_value
, last_value (nullif (price, 0)) over (partition by id order by date desc)
    as next_value
FROM T
ORDER BY date, id

date	id	price	prev_value	next_value
2019-08-17	1	5.0	5.0	5.0
2019-08-17	2	15.4	15.4	15.4
2019-08-18	1	0.0	5.0	5.6
2019-08-18	2	0.0	15.4	14.0
2019-08-19	1	0.0	5.0	5.6
2019-08-19	2	0.0	15.4	14.0
2019-08-20	1	0.0	5.0	5.6
2019-08-20	2	0.0	15.4	14.0
2019-08-21	1	5.6	5.6	5.6
2019-08-21	2	14.0	14.0	14.0