CODEWITHSUNDEEP
sqlgoogle-bigquery
Daniel Ziv
Daniel Ziv

Reputation: 5

Adding missing date rows to a BigQuery Table

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
   )

Upvotes: 0

Views: 827

Answers (3)

Daniel Ziv
Daniel Ziv

Reputation: 5

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

Upvotes: 0

saifuddin778
saifuddin778

Reputation: 7298

If your values are strictly parted by 10 seconds, and there are just some multiples of 10 seconds intervals which are missing, you can go by this approach to fill your data holes:

WITH minsmax AS (
   SELECT
     MIN(time) AS minval,
     MAX(time) AS maxval
   FROM `dataset.table` 
)

SELECT
   IF (d.time <= i.time, d.time, i.time) as time,
   MAX(IF(d.time <= i.time, d.value, NULL)) as value
FROM (
  SELECT time FROM minsmax m, UNNEST(GENERATE_ARRAY(m.minval, m.maxval+100, 100)) AS time 
) AS i
LEFT JOIN `dataset.table` d ON 1=1
WHERE ABS(d.time - i.time) >= 100
GROUP BY 1
ORDER BY 1

Hope this helps.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use arrays. For numbers, you can do:

select n
from unnest(generate_array(1, 1000, 1)) n;

There are similar functions for generate_timestamp_array() and generate_date_array() if you really need those types.

Upvotes: 0

Related Questions