I have a table where 1 of the rows is an integer that represents the rows time. Problem is the table isn’t full, there are missing timestamps.
I would like to fill missing values such that every 10 seconds there is a row. I want the rest of the columns to be nuns (later I’ll forward fill these nuns).
10 secs is basically 10,000. If this was python the range would be:
range( min(table[column]), max(table[column]), 10000 )
Advertisement
Answer
I ended up using the following query through python API:
""" SELECT i.time, Sensor_Reading, Sensor_Name FROM ( SELECT time FROM UNNEST(GENERATE_ARRAY({min_time}, {max_time}+{sampling_period}+1, {sampling_period})) AS time ) AS i LEFT JOIN `{input_table}` AS input ON i.time =input.Time ORDER BY i.time """.format(sampling_period=sampling_period, min_time=min_time, max_time=max_time, input_table=input_table)
Thanks to both answers