Fill missing values in clickhouse

Question

I have a clickhouse table with this schema:

ISIN String
Date Date
AdjCoef Float

So I have some missing Date in the table

I need to apply two changes : 1- fill in the missing dates with the first value after that date 2- multiply each value in all previous records for that ISIN

The final view should be this:

How can i do this. Any suggestions please.

Answer 1

Try this:

WITH arrayJoin (r) as day_inc
SELECT
  ISIN
, Date + day_inc as Date
-- xCoef if it's the original row, xCoef_next - if produced to fill the gap
, if (day_inc = 0, AdjCoef, AdjCoef_next) as AdjCoef
, if (day_inc = 0, NewAdjCoef, NewAdjCoef_next) as NewAdjCoef
FROM
(
SELECT 
  *
-- Next values calculation
, any (AdjCoef) over (partition by ISIN order by Date rows between 1 following and 1 following)
  as AdjCoef_next
, any (NewAdjCoef) over (partition by ISIN order by Date rows between 1 following and 1 following)
  as NewAdjCoef_next
FROM
(
SELECT 
*
-- running product calculation
, exp10 (sum (log10 (AdjCoef)) over (partition by ISIN order by Date rows between current row and unbounded following))
  as NewAdjCoef
-- gap in days between current and next Date
, coalesce
(
  any (cast (Date as Nullable(Date))) over (partition by ISIN order by Date rows between 1 following and 1 following)
- Date
, 1
) as days_gap
-- array to insert absent rows with arrayJoin 
, range (days_gap) as r
FROM VALUES 
(
  'ISIN String, Date Date, AdjCoef double'
, ('x', '2024-01-01', 0.9)
, ('y', '2024-01-01', 0.8)
, ('x', '2024-01-04', 0.7)
, ('x', '2024-01-05', 0.9)
)
)
)
ORDER BY Date, ISIN
FORMAT PrettyCompact

The result is:

+-ISIN-+-------Date-+-AdjCoef-+-NewAdjCoef-+
| x    | 2024-01-01 |     0.9 |      0.567 |
| y    | 2024-01-01 |     0.8 |        0.8 |
| x    | 2024-01-02 |     0.7 |       0.63 |
| x    | 2024-01-03 |     0.7 |       0.63 |
| x    | 2024-01-04 |     0.7 |       0.63 |
| x    | 2024-01-05 |     0.9 |        0.9 |
+------+------------+---------+------------+

fiddle