Fill non-existent data with NULL in Clickhouse

Viewed 23

I have a given time range, here is a simple query example:

WITH
    toDateTime('2022-08-31 18:00:00') AS start,
    toDateTime('2022-08-31 21:00:00') AS end
SELECT
    id,
    timestamp,
    value,
    arrayMap(x -> toUnixTimestamp(x), range(toUInt32(start), toUInt32(end), 3600)) AS time_range
FROM
    table
WHERE
    has(time_range, timestamp) 
AND 
    id IN (1, 2)
ORDER BY
    id ASC,
    timestamp ASC

As expected this returns:

1  1661958000 351  [1661958000,1661961600,1661965200]
2  1661958000 3000 [1661958000,1661961600,1661965200] 
2  1661961600 2999 [1661958000,1661961600,1661965200] 
2  1661965200 0    [1661958000,1661961600,1661965200] 

I'm trying to find a solution for a similar result:

1  1661958000 351  [1661958000,1661961600,1661965200]
1  1661961600 null [1661958000,1661961600,1661965200]
1  1661965200 null [1661958000,1661961600,1661965200] 
2  1661958000 3000 [1661958000,1661961600,1661965200] 
2  1661961600 2999 [1661958000,1661961600,1661965200] 
2  1661965200 0    [1661958000,1661961600,1661965200]

The point is that I need to replace the value column with null in non-existent records with specific timestamps.

As far as I understand, the WITH FILL construct is not quite suitable for my case. How can I achieve this?

0 Answers
Related