Work with time series data

This document describes how to use SQL functions to support time series analysis.

Introduction

A time series is a sequence of data points, each consisting of a time and a value associated with that time. Usually, a time series also has an identifier, which uniquely names the time series.

In relational databases, a time series is modeled as a table with the following groups of columns:

Time column
Might have partitioning columns, for example, zip code
One or more value columns, or a STRUCT type combining multiple values, for example, temperature and AQI

The following is an example of time series data modeled as a table:

Time series table example.

Aggregate a time series

In time series analysis, time aggregation is an aggregation performed along the time axis.

You can perform time aggregation in BigQuery with the help of time bucketing functions (TIMESTAMP_BUCKET, DATE_BUCKET, and DATETIME_BUCKET). Time bucketing functions map input time values to the bucket they belong to.

Typically, time aggregation is performed to combine multiple data points in a time window into a single data point, using an aggregation function, such as AVG, MIN, MAX, COUNT, or SUM. For example, 15-minute average request latency, daily minimum and maximum temperatures, and daily number of taxi trips.

For the queries in this section, create a table called mydataset.environmental_data_hourly:

CREATEORREPLACETABLEmydataset.environmental_data_hourlyAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<zip_codeINT64,timeTIMESTAMP,aqiINT64,temperatureINT64>>[
STRUCT(60606,TIMESTAMP'2020-09-08 00:30:51',22,66),
STRUCT(60606,TIMESTAMP'2020-09-08 01:32:10',23,63),
STRUCT(60606,TIMESTAMP'2020-09-08 02:30:35',22,60),
STRUCT(60606,TIMESTAMP'2020-09-08 03:29:39',21,58),
STRUCT(60606,TIMESTAMP'2020-09-08 04:33:05',21,59),
STRUCT(60606,TIMESTAMP'2020-09-08 05:32:01',21,57),
STRUCT(60606,TIMESTAMP'2020-09-08 06:31:14',22,56),
STRUCT(60606,TIMESTAMP'2020-09-08 07:31:06',28,55),
STRUCT(60606,TIMESTAMP'2020-09-08 08:29:59',30,55),
STRUCT(60606,TIMESTAMP'2020-09-08 09:29:34',31,55),
STRUCT(60606,TIMESTAMP'2020-09-08 10:31:24',38,56),
STRUCT(60606,TIMESTAMP'2020-09-08 11:31:24',38,56),
STRUCT(60606,TIMESTAMP'2020-09-08 12:32:38',38,57),
STRUCT(60606,TIMESTAMP'2020-09-08 13:29:59',38,56),
STRUCT(60606,TIMESTAMP'2020-09-08 14:31:22',43,59),
STRUCT(60606,TIMESTAMP'2020-09-08 15:31:38',42,63),
STRUCT(60606,TIMESTAMP'2020-09-08 16:34:22',43,65),
STRUCT(60606,TIMESTAMP'2020-09-08 17:33:23',42,68),
STRUCT(60606,TIMESTAMP'2020-09-08 18:28:47',36,69),
STRUCT(60606,TIMESTAMP'2020-09-08 19:30:28',34,67),
STRUCT(60606,TIMESTAMP'2020-09-08 20:30:53',29,67),
STRUCT(60606,TIMESTAMP'2020-09-08 21:32:28',27,67),
STRUCT(60606,TIMESTAMP'2020-09-08 22:31:45',25,65),
STRUCT(60606,TIMESTAMP'2020-09-08 23:31:02',22,63),
STRUCT(94105,TIMESTAMP'2020-09-08 00:07:11',60,74),
STRUCT(94105,TIMESTAMP'2020-09-08 01:07:24',61,73),
STRUCT(94105,TIMESTAMP'2020-09-08 02:08:07',60,71),
STRUCT(94105,TIMESTAMP'2020-09-08 03:11:05',69,69),
STRUCT(94105,TIMESTAMP'2020-09-08 04:07:26',72,67),
STRUCT(94105,TIMESTAMP'2020-09-08 05:08:11',70,66),
STRUCT(94105,TIMESTAMP'2020-09-08 06:07:30',68,65),
STRUCT(94105,TIMESTAMP'2020-09-08 07:07:10',77,64),
STRUCT(94105,TIMESTAMP'2020-09-08 08:06:35',81,64),
STRUCT(94105,TIMESTAMP'2020-09-08 09:10:18',82,63),
STRUCT(94105,TIMESTAMP'2020-09-08 10:08:10',107,62),
STRUCT(94105,TIMESTAMP'2020-09-08 11:08:01',115,62),
STRUCT(94105,TIMESTAMP'2020-09-08 12:07:39',120,62),
STRUCT(94105,TIMESTAMP'2020-09-08 13:06:03',125,61),
STRUCT(94105,TIMESTAMP'2020-09-08 14:08:37',129,62),
STRUCT(94105,TIMESTAMP'2020-09-08 15:09:19',150,62),
STRUCT(94105,TIMESTAMP'2020-09-08 16:06:39',151,62),
STRUCT(94105,TIMESTAMP'2020-09-08 17:08:01',155,63),
STRUCT(94105,TIMESTAMP'2020-09-08 18:09:23',154,64),
STRUCT(94105,TIMESTAMP'2020-09-08 19:08:43',151,67),
STRUCT(94105,TIMESTAMP'2020-09-08 20:07:19',150,69),
STRUCT(94105,TIMESTAMP'2020-09-08 21:07:37',148,72),
STRUCT(94105,TIMESTAMP'2020-09-08 22:08:01',143,76),
STRUCT(94105,TIMESTAMP'2020-09-08 23:08:41',137,75)
]);

One interesting observation about the preceding data is that measurements are taken at arbitrary time periods, known as unaligned time series. Aggregation is one of the ways by which a time series can be aligned.

Get a 3-hour average

The following query computes a 3-hour average air quality index (AQI) and temperature for each zip code. The TIMESTAMP_BUCKET function performs time aggregation by assigning each time value to a particular day.

SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR)AStime,
zip_code,
CAST(AVG(aqi)ASINT64)ASaqi,
CAST(AVG(temperature)ASINT64)AStemperature
FROMmydataset.environmental_data_hourly
GROUPBYzip_code,time
ORDERBYzip_code,time;
/*---------------------+----------+-----+-------------+
 | time | zip_code | aqi | temperature |
 +---------------------+----------+-----+-------------+
 | 2020年09月08日 00:00:00 | 60606 | 22 | 63 |
 | 2020年09月08日 03:00:00 | 60606 | 21 | 58 |
 | 2020年09月08日 06:00:00 | 60606 | 27 | 55 |
 | 2020年09月08日 09:00:00 | 60606 | 36 | 56 |
 | 2020年09月08日 12:00:00 | 60606 | 40 | 57 |
 | 2020年09月08日 15:00:00 | 60606 | 42 | 65 |
 | 2020年09月08日 18:00:00 | 60606 | 33 | 68 |
 | 2020年09月08日 21:00:00 | 60606 | 25 | 65 |
 | 2020年09月08日 00:00:00 | 94105 | 60 | 73 |
 | 2020年09月08日 03:00:00 | 94105 | 70 | 67 |
 | 2020年09月08日 06:00:00 | 94105 | 75 | 64 |
 | 2020年09月08日 09:00:00 | 94105 | 101 | 62 |
 | 2020年09月08日 12:00:00 | 94105 | 125 | 62 |
 | 2020年09月08日 15:00:00 | 94105 | 152 | 62 |
 | 2020年09月08日 18:00:00 | 94105 | 152 | 67 |
 | 2020年09月08日 21:00:00 | 94105 | 143 | 74 |
 +---------------------+----------+-----+-------------*/

Get a 3-hour minimum and maximum value

In the following query, you compute 3-hour minimum and maximum temperatures for each zip code:

SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR)AStime,
zip_code,
MIN(temperature)AStemperature_min,
MAX(temperature)AStemperature_max,
FROMmydataset.environmental_data_hourly
GROUPBYzip_code,time
ORDERBYzip_code,time;
/*---------------------+----------+-----------------+-----------------+
 | time | zip_code | temperature_min | temperature_max |
 +---------------------+----------+-----------------+-----------------+
 | 2020年09月08日 00:00:00 | 60606 | 60 | 66 |
 | 2020年09月08日 03:00:00 | 60606 | 57 | 59 |
 | 2020年09月08日 06:00:00 | 60606 | 55 | 56 |
 | 2020年09月08日 09:00:00 | 60606 | 55 | 56 |
 | 2020年09月08日 12:00:00 | 60606 | 56 | 59 |
 | 2020年09月08日 15:00:00 | 60606 | 63 | 68 |
 | 2020年09月08日 18:00:00 | 60606 | 67 | 69 |
 | 2020年09月08日 21:00:00 | 60606 | 63 | 67 |
 | 2020年09月08日 00:00:00 | 94105 | 71 | 74 |
 | 2020年09月08日 03:00:00 | 94105 | 66 | 69 |
 | 2020年09月08日 06:00:00 | 94105 | 64 | 65 |
 | 2020年09月08日 09:00:00 | 94105 | 62 | 63 |
 | 2020年09月08日 12:00:00 | 94105 | 61 | 62 |
 | 2020年09月08日 15:00:00 | 94105 | 62 | 63 |
 | 2020年09月08日 18:00:00 | 94105 | 64 | 69 |
 | 2020年09月08日 21:00:00 | 94105 | 72 | 76 |
 +---------------------+----------+-----------------+-----------------*/

Get a 3-hour average with custom alignment

When you perform time series aggregation, you use a specific alignment for time series windows, either implicitly or explicitly. The preceding queries used implicit alignment, which produced buckets that started at times like 00:00:00, 03:00:00, and 06:00:00. To explicitly set this alignment in the TIMESTAMP_BUCKET function, pass an optional argument that specifies the origin.

In the following query, the origin is set as 2020年01月01日 02:00:00. This changes the alignment and produces buckets that start at times like 02:00:00, 05:00:00, and 08:00:00:

SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR,TIMESTAMP'2020-01-01 02:00:00')AStime,
zip_code,
CAST(AVG(aqi)ASINT64)ASaqi,
CAST(AVG(temperature)ASINT64)AStemperature
FROMmydataset.environmental_data_hourly
GROUPBYzip_code,time
ORDERBYzip_code,time;
/*---------------------+----------+-----+-------------+
 | time | zip_code | aqi | temperature |
 +---------------------+----------+-----+-------------+
 | 2020年09月07日 23:00:00 | 60606 | 23 | 65 |
 | 2020年09月08日 02:00:00 | 60606 | 21 | 59 |
 | 2020年09月08日 05:00:00 | 60606 | 24 | 56 |
 | 2020年09月08日 08:00:00 | 60606 | 33 | 55 |
 | 2020年09月08日 11:00:00 | 60606 | 38 | 56 |
 | 2020年09月08日 14:00:00 | 60606 | 43 | 62 |
 | 2020年09月08日 17:00:00 | 60606 | 37 | 68 |
 | 2020年09月08日 20:00:00 | 60606 | 27 | 66 |
 | 2020年09月08日 23:00:00 | 60606 | 22 | 63 |
 | 2020年09月07日 23:00:00 | 94105 | 61 | 74 |
 | 2020年09月08日 02:00:00 | 94105 | 67 | 69 |
 | 2020年09月08日 05:00:00 | 94105 | 72 | 65 |
 | 2020年09月08日 08:00:00 | 94105 | 90 | 63 |
 | 2020年09月08日 11:00:00 | 94105 | 120 | 62 |
 | 2020年09月08日 14:00:00 | 94105 | 143 | 62 |
 | 2020年09月08日 17:00:00 | 94105 | 153 | 65 |
 | 2020年09月08日 20:00:00 | 94105 | 147 | 72 |
 | 2020年09月08日 23:00:00 | 94105 | 137 | 75 |
 +---------------------+----------+-----+-------------*/

Aggregate a time series with gap filling

Sometimes after you aggregate a time series, the data might have gaps that need to be filled with some values for further analysis or presentation of the data. The technique used to fill in those gaps is called gap filling. In BigQuery, you can use the GAP_FILL table function for filling gaps in time series data, using one of the provided gap-filling methods:

NULL, also known as constant
LOCF, last observation carried forward
Linear, linear interpolation between the two neighboring data points

For the queries in this section, create a table called mydataset.environmental_data_hourly_with_gaps, which is based on the data used in the preceding section, but with gaps in it. In the real world scenarios, data could have missing data points due to a short-term weather station malfunction.

CREATEORREPLACETABLEmydataset.environmental_data_hourly_with_gapsAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<zip_codeINT64,timeTIMESTAMP,aqiINT64,temperatureINT64>>[
STRUCT(60606,TIMESTAMP'2020-09-08 00:30:51',22,66),
STRUCT(60606,TIMESTAMP'2020-09-08 01:32:10',23,63),
STRUCT(60606,TIMESTAMP'2020-09-08 02:30:35',22,60),
STRUCT(60606,TIMESTAMP'2020-09-08 03:29:39',21,58),
STRUCT(60606,TIMESTAMP'2020-09-08 04:33:05',21,59),
STRUCT(60606,TIMESTAMP'2020-09-08 05:32:01',21,57),
STRUCT(60606,TIMESTAMP'2020-09-08 06:31:14',22,56),
STRUCT(60606,TIMESTAMP'2020-09-08 07:31:06',28,55),
STRUCT(60606,TIMESTAMP'2020-09-08 08:29:59',30,55),
STRUCT(60606,TIMESTAMP'2020-09-08 09:29:34',31,55),
STRUCT(60606,TIMESTAMP'2020-09-08 10:31:24',38,56),
STRUCT(60606,TIMESTAMP'2020-09-08 11:31:24',38,56),
-- No data points between hours 12 and 15.
STRUCT(60606,TIMESTAMP'2020-09-08 16:34:22',43,65),
STRUCT(60606,TIMESTAMP'2020-09-08 17:33:23',42,68),
STRUCT(60606,TIMESTAMP'2020-09-08 18:28:47',36,69),
STRUCT(60606,TIMESTAMP'2020-09-08 19:30:28',34,67),
STRUCT(60606,TIMESTAMP'2020-09-08 20:30:53',29,67),
STRUCT(60606,TIMESTAMP'2020-09-08 21:32:28',27,67),
STRUCT(60606,TIMESTAMP'2020-09-08 22:31:45',25,65),
STRUCT(60606,TIMESTAMP'2020-09-08 23:31:02',22,63),
STRUCT(94105,TIMESTAMP'2020-09-08 00:07:11',60,74),
STRUCT(94105,TIMESTAMP'2020-09-08 01:07:24',61,73),
STRUCT(94105,TIMESTAMP'2020-09-08 02:08:07',60,71),
STRUCT(94105,TIMESTAMP'2020-09-08 03:11:05',69,69),
STRUCT(94105,TIMESTAMP'2020-09-08 04:07:26',72,67),
STRUCT(94105,TIMESTAMP'2020-09-08 05:08:11',70,66),
STRUCT(94105,TIMESTAMP'2020-09-08 06:07:30',68,65),
STRUCT(94105,TIMESTAMP'2020-09-08 07:07:10',77,64),
STRUCT(94105,TIMESTAMP'2020-09-08 08:06:35',81,64),
STRUCT(94105,TIMESTAMP'2020-09-08 09:10:18',82,63),
STRUCT(94105,TIMESTAMP'2020-09-08 10:08:10',107,62),
STRUCT(94105,TIMESTAMP'2020-09-08 11:08:01',115,62),
STRUCT(94105,TIMESTAMP'2020-09-08 12:07:39',120,62),
STRUCT(94105,TIMESTAMP'2020-09-08 13:06:03',125,61),
STRUCT(94105,TIMESTAMP'2020-09-08 14:08:37',129,62),
-- No data points between hours 15 and 18.
STRUCT(94105,TIMESTAMP'2020-09-08 19:08:43',151,67),
STRUCT(94105,TIMESTAMP'2020-09-08 20:07:19',150,69),
STRUCT(94105,TIMESTAMP'2020-09-08 21:07:37',148,72),
STRUCT(94105,TIMESTAMP'2020-09-08 22:08:01',143,76),
STRUCT(94105,TIMESTAMP'2020-09-08 23:08:41',137,75)
]);

Get a 3-hour average (include gaps)

The following query computes 3-hour average AQI and temperature for each zip code:

SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR)AStime,
zip_code,
CAST(AVG(aqi)ASINT64)ASaqi,
CAST(AVG(temperature)ASINT64)AStemperature
FROMmydataset.environmental_data_hourly_with_gaps
GROUPBYzip_code,time
ORDERBYzip_code,time;
/*---------------------+----------+-----+-------------+
 | time | zip_code | aqi | temperature |
 +---------------------+----------+-----+-------------+
 | 2020年09月08日 00:00:00 | 60606 | 22 | 63 |
 | 2020年09月08日 03:00:00 | 60606 | 21 | 58 |
 | 2020年09月08日 06:00:00 | 60606 | 27 | 55 |
 | 2020年09月08日 09:00:00 | 60606 | 36 | 56 |
 | 2020年09月08日 15:00:00 | 60606 | 43 | 67 |
 | 2020年09月08日 18:00:00 | 60606 | 33 | 68 |
 | 2020年09月08日 21:00:00 | 60606 | 25 | 65 |
 | 2020年09月08日 00:00:00 | 94105 | 60 | 73 |
 | 2020年09月08日 03:00:00 | 94105 | 70 | 67 |
 | 2020年09月08日 06:00:00 | 94105 | 75 | 64 |
 | 2020年09月08日 09:00:00 | 94105 | 101 | 62 |
 | 2020年09月08日 12:00:00 | 94105 | 125 | 62 |
 | 2020年09月08日 18:00:00 | 94105 | 151 | 68 |
 | 2020年09月08日 21:00:00 | 94105 | 143 | 74 |
 +---------------------+----------+-----+-------------*/

Note how the output has gaps at certain time intervals. For example, the time series for zip code 60606 doesn't have a data point at 2020年09月08日 12:00:00, and the time series for zip code 94105 doesn't have a data point at 2020年09月08日 15:00:00.

Get a 3-hour average (fill gaps)

Use the query from the previous section and add the GAP_FILL function to fill the gaps:

WITHaggregated_3_hrAS(
SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR)AStime,
zip_code,
CAST(AVG(aqi)ASINT64)ASaqi,
CAST(AVG(temperature)ASINT64)AStemperature
FROMmydataset.environmental_data_hourly_with_gaps
GROUPBYzip_code,time)
SELECT*
FROMGAP_FILL(
TABLEaggregated_3_hr,
ts_column=>'time',
bucket_width=>INTERVAL3HOUR,
partitioning_columns=>['zip_code']
)
ORDERBYzip_code,time;
/*---------------------+----------+------+-------------+
 | time | zip_code | aqi | temperature |
 +---------------------+----------+------+-------------+
 | 2020年09月08日 00:00:00 | 60606 | 22 | 63 |
 | 2020年09月08日 03:00:00 | 60606 | 21 | 58 |
 | 2020年09月08日 06:00:00 | 60606 | 27 | 55 |
 | 2020年09月08日 09:00:00 | 60606 | 36 | 56 |
 | 2020年09月08日 12:00:00 | 60606 | NULL | NULL |
 | 2020年09月08日 15:00:00 | 60606 | 43 | 67 |
 | 2020年09月08日 18:00:00 | 60606 | 33 | 68 |
 | 2020年09月08日 21:00:00 | 60606 | 25 | 65 |
 | 2020年09月08日 00:00:00 | 94105 | 60 | 73 |
 | 2020年09月08日 03:00:00 | 94105 | 70 | 67 |
 | 2020年09月08日 06:00:00 | 94105 | 75 | 64 |
 | 2020年09月08日 09:00:00 | 94105 | 101 | 62 |
 | 2020年09月08日 12:00:00 | 94105 | 125 | 62 |
 | 2020年09月08日 15:00:00 | 94105 | NULL | NULL |
 | 2020年09月08日 18:00:00 | 94105 | 151 | 68 |
 | 2020年09月08日 21:00:00 | 94105 | 143 | 74 |
 +---------------------+----------+------+-------------*/

The output table now contains a missing row at 2020年09月08日 12:00:00 for zip code 60606 and at 2020年09月08日 15:00:00 for zip code 94105, with NULL values in the corresponding metric columns. Since you didn't specify any gap-filling method, GAP_FILL used the default gap-filling method, NULL.

Fill gaps with linear and LOCF gap filling

In the following query, the GAP_FILL function is used with the LOCF gap-filling method for the aqi column and linear interpolation for the temperature column:

WITHaggregated_3_hrAS(
SELECT
TIMESTAMP_BUCKET(time,INTERVAL3HOUR)AStime,
zip_code,
CAST(AVG(aqi)ASINT64)ASaqi,
CAST(AVG(temperature)ASINT64)AStemperature
FROMmydataset.environmental_data_hourly_with_gaps
GROUPBYzip_code,time)
SELECT*
FROMGAP_FILL(
TABLEaggregated_3_hr,
ts_column=>'time',
bucket_width=>INTERVAL3HOUR,
partitioning_columns=>['zip_code'],
value_columns=>[
('aqi','locf'),
('temperature','linear')
]
)
ORDERBYzip_code,time;
/*---------------------+----------+-----+-------------+
 | time | zip_code | aqi | temperature |
 +---------------------+----------+-----+-------------+
 | 2020年09月08日 00:00:00 | 60606 | 22 | 63 |
 | 2020年09月08日 03:00:00 | 60606 | 21 | 58 |
 | 2020年09月08日 06:00:00 | 60606 | 27 | 55 |
 | 2020年09月08日 09:00:00 | 60606 | 36 | 56 |
 | 2020年09月08日 12:00:00 | 60606 | 36 | 62 |
 | 2020年09月08日 15:00:00 | 60606 | 43 | 67 |
 | 2020年09月08日 18:00:00 | 60606 | 33 | 68 |
 | 2020年09月08日 21:00:00 | 60606 | 25 | 65 |
 | 2020年09月08日 00:00:00 | 94105 | 60 | 73 |
 | 2020年09月08日 03:00:00 | 94105 | 70 | 67 |
 | 2020年09月08日 06:00:00 | 94105 | 75 | 64 |
 | 2020年09月08日 09:00:00 | 94105 | 101 | 62 |
 | 2020年09月08日 12:00:00 | 94105 | 125 | 62 |
 | 2020年09月08日 15:00:00 | 94105 | 125 | 65 |
 | 2020年09月08日 18:00:00 | 94105 | 151 | 68 |
 | 2020年09月08日 21:00:00 | 94105 | 143 | 74 |
 +---------------------+----------+-----+-------------*/

In this query, the first gap-filled row has aqi value 36, which is taken from the previous data point of this time series (zip code 60606) at 2020年09月08日 09:00:00. The temperature value 62 is a result of linear interpolation between data points 2020年09月08日 09:00:00 and 2020年09月08日 15:00:00. The other missing row was created in a similar way - aqi value 125 was carried over from the previous data point of this time series (zip code 94105), and the temperature value 65 is a result of linear interpolation between the previous and the next available data points.

Align a time series with gap filling

Time series can be aligned or unaligned. A time series is aligned when data points only occur at regular intervals.

In the real world, at the time of collection, time series are rarely aligned and usually require some further processing to align them.

For example, consider IoT devices that send their metrics to a centralized collector every minute. It would be unreasonable to expect the devices to send their metrics at exactly the same instants of time. Usually, each device sends its metrics with the same frequency (period) but with different time offset (alignment). The following diagram illustrates this example. You can see each device sending its data with a one-minute interval with some instances of missing data (Device 3 at 9:36:39 ) and delayed data (Device 1 at 9:37:28).

Align time series example

You can perform time series alignment on unaligned data, using time aggregation. This is helpful if you want to change the sampling period of the time series, such as changing from the original 1-minute sampling period to a 15-minute period. You can align data for further time series processing, such as joining the time series data, or for display purposes (such as graphing).

You can use the GAP_FILL table function with LOCF or linear gap-filling methods to perform time series alignment. The idea is to use GAP_FILL with the selected output period and alignment (controlled by the optional origin argument). The result of the operation is a table with aligned time series, where values for each data point are derived from the input time series with the gap-filling method used for that particular value column (LOCF of linear).

Create a table mydataset.device_data, which resembles the previous illustration:

CREATEORREPLACETABLEmydataset.device_dataAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<device_idINT64,timeTIMESTAMP,signalINT64,stateSTRING>>[
STRUCT(2,TIMESTAMP'2023-11-01 09:35:07',87,'ACTIVE'),
STRUCT(1,TIMESTAMP'2023-11-01 09:35:26',82,'ACTIVE'),
STRUCT(3,TIMESTAMP'2023-11-01 09:35:39',74,'INACTIVE'),
STRUCT(2,TIMESTAMP'2023-11-01 09:36:07',88,'ACTIVE'),
STRUCT(1,TIMESTAMP'2023-11-01 09:36:26',82,'ACTIVE'),
STRUCT(2,TIMESTAMP'2023-11-01 09:37:07',88,'ACTIVE'),
STRUCT(1,TIMESTAMP'2023-11-01 09:37:28',80,'ACTIVE'),
STRUCT(3,TIMESTAMP'2023-11-01 09:37:39',77,'ACTIVE'),
STRUCT(2,TIMESTAMP'2023-11-01 09:38:07',86,'ACTIVE'),
STRUCT(1,TIMESTAMP'2023-11-01 09:38:26',81,'ACTIVE'),
STRUCT(3,TIMESTAMP'2023-11-01 09:38:39',77,'ACTIVE')
]);

The following is the actual data ordered by time and device_id columns:

SELECT*FROMmydataset.device_dataORDERBYtime,device_id;
/*-----------+---------------------+--------+----------+
 | device_id | time | signal | state |
 +-----------+---------------------+--------+----------+
 | 2 | 2023年11月01日 09:35:07 | 87 | ACTIVE |
 | 1 | 2023年11月01日 09:35:26 | 82 | ACTIVE |
 | 3 | 2023年11月01日 09:35:39 | 74 | INACTIVE |
 | 2 | 2023年11月01日 09:36:07 | 88 | ACTIVE |
 | 1 | 2023年11月01日 09:36:26 | 82 | ACTIVE |
 | 2 | 2023年11月01日 09:37:07 | 88 | ACTIVE |
 | 1 | 2023年11月01日 09:37:28 | 80 | ACTIVE |
 | 3 | 2023年11月01日 09:37:39 | 77 | ACTIVE |
 | 2 | 2023年11月01日 09:38:07 | 86 | ACTIVE |
 | 1 | 2023年11月01日 09:38:26 | 81 | ACTIVE |
 | 3 | 2023年11月01日 09:38:39 | 77 | ACTIVE |
 +-----------+---------------------+--------+----------*/

The table contains the time series for each device with two metric columns:

signal - signal level as observed by the device at the time of sampling, represented as an integer value between 0 and 100.
state - state of the device at the time of sampling, represented as a free form string.

In the following query, the GAP_FILL function is used to align the time series at 1-minute intervals. Note how linear interpolation is used to compute values for the signal column and LOCF for the state column. For this example data, linear interpolation is a suitable choice to compute the output values.

SELECT*
FROMGAP_FILL(
TABLEmydataset.device_data,
ts_column=>'time',
bucket_width=>INTERVAL1MINUTE,
partitioning_columns=>['device_id'],
value_columns=>[
('signal','linear'),
('state','locf')
]
)
ORDERBYtime,device_id;
/*---------------------+-----------+--------+----------+
 | time | device_id | signal | state |
 +---------------------+-----------+--------+----------+
 | 2023年11月01日 09:36:00 | 1 | 82 | ACTIVE |
 | 2023年11月01日 09:36:00 | 2 | 88 | ACTIVE |
 | 2023年11月01日 09:36:00 | 3 | 75 | INACTIVE |
 | 2023年11月01日 09:37:00 | 1 | 81 | ACTIVE |
 | 2023年11月01日 09:37:00 | 2 | 88 | ACTIVE |
 | 2023年11月01日 09:37:00 | 3 | 76 | INACTIVE |
 | 2023年11月01日 09:38:00 | 1 | 81 | ACTIVE |
 | 2023年11月01日 09:38:00 | 2 | 86 | ACTIVE |
 | 2023年11月01日 09:38:00 | 3 | 77 | ACTIVE |
 +---------------------+-----------+--------+----------*/

The output table contains an aligned time series for each device and value columns (signal and state), computed using the gap-filling methods specified in the function call.

Join time series data

You can join time series data using a windowed join or AS OF join.

Windowed join

Sometimes you need to join two or more tables with time series data. Consider the following two tables:

mydataset.sensor_temperatures, contains temperature data reported by each sensor every 15 seconds.
mydataset.sensor_fuel_rates, contains the fuel consumption rate measured by each sensor every 15 seconds.

To create these tables, run the following queries:

CREATEORREPLACETABLEmydataset.sensor_temperaturesAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,tsTIMESTAMP,tempFLOAT64>>[
(1,TIMESTAMP'2020-01-01 12:00:00.063',37.1),
(1,TIMESTAMP'2020-01-01 12:00:15.024',37.2),
(1,TIMESTAMP'2020-01-01 12:00:30.032',37.3),
(2,TIMESTAMP'2020-01-01 12:00:01.001',38.1),
(2,TIMESTAMP'2020-01-01 12:00:15.082',38.2),
(2,TIMESTAMP'2020-01-01 12:00:31.009',38.3)
]);
CREATEORREPLACETABLEmydataset.sensor_fuel_ratesAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,tsTIMESTAMP,rateFLOAT64>>[
(1,TIMESTAMP'2020-01-01 12:00:11.016',10.1),
(1,TIMESTAMP'2020-01-01 12:00:26.015',10.2),
(1,TIMESTAMP'2020-01-01 12:00:41.014',10.3),
(2,TIMESTAMP'2020-01-01 12:00:08.099',11.1),
(2,TIMESTAMP'2020-01-01 12:00:23.087',11.2),
(2,TIMESTAMP'2020-01-01 12:00:38.077',11.3)
]);

The following is the actual data from the tables:

SELECT*FROMmydataset.sensor_temperaturesORDERBYsensor_id,ts;
/*-----------+---------------------+------+
 | sensor_id | ts | temp |
 +-----------+---------------------+------+
 | 1 | 2020年01月01日 12:00:00 | 37.1 |
 | 1 | 2020年01月01日 12:00:15 | 37.2 |
 | 1 | 2020年01月01日 12:00:30 | 37.3 |
 | 2 | 2020年01月01日 12:00:01 | 38.1 |
 | 2 | 2020年01月01日 12:00:15 | 38.2 |
 | 2 | 2020年01月01日 12:00:31 | 38.3 |
 +-----------+---------------------+------*/
SELECT*FROMmydataset.sensor_fuel_ratesORDERBYsensor_id,ts;
/*-----------+---------------------+------+
 | sensor_id | ts | rate |
 +-----------+---------------------+------+
 | 1 | 2020年01月01日 12:00:11 | 10.1 |
 | 1 | 2020年01月01日 12:00:26 | 10.2 |
 | 1 | 2020年01月01日 12:00:41 | 10.3 |
 | 2 | 2020年01月01日 12:00:08 | 11.1 |
 | 2 | 2020年01月01日 12:00:23 | 11.2 |
 | 2 | 2020年01月01日 12:00:38 | 11.3 |
 +-----------+---------------------+------*/

To check the fuel consumption rate at the temperature reported by each sensor, you can join the two time series.

Although the data in the two time series is unaligned, it is sampled at the same interval (15 seconds), therefore such data is a good candidate for windowed join. Use the time bucketing functions to align timestamps used as join keys.

The following queries illustrate how each timestamp can be assigned to 15-second windows using the TIMESTAMP_BUCKET function:

SELECT*,TIMESTAMP_BUCKET(ts,INTERVAL15SECOND)ts_window
FROMmydataset.sensor_temperatures
ORDERBYsensor_id,ts;
/*-----------+---------------------+------+---------------------+
 | sensor_id | ts | temp | ts_window |
 +-----------+---------------------+------+---------------------+
 | 1 | 2020年01月01日 12:00:00 | 37.1 | 2020年01月01日 12:00:00 |
 | 1 | 2020年01月01日 12:00:15 | 37.2 | 2020年01月01日 12:00:15 |
 | 1 | 2020年01月01日 12:00:30 | 37.3 | 2020年01月01日 12:00:30 |
 | 2 | 2020年01月01日 12:00:01 | 38.1 | 2020年01月01日 12:00:00 |
 | 2 | 2020年01月01日 12:00:15 | 38.2 | 2020年01月01日 12:00:15 |
 | 2 | 2020年01月01日 12:00:31 | 38.3 | 2020年01月01日 12:00:30 |
 +-----------+---------------------+------+---------------------*/
SELECT*,TIMESTAMP_BUCKET(ts,INTERVAL15SECOND)ts_window
FROMmydataset.sensor_fuel_rates
ORDERBYsensor_id,ts;
/*-----------+---------------------+------+---------------------+
 | sensor_id | ts | rate | ts_window |
 +-----------+---------------------+------+---------------------+
 | 1 | 2020年01月01日 12:00:11 | 10.1 | 2020年01月01日 12:00:00 |
 | 1 | 2020年01月01日 12:00:26 | 10.2 | 2020年01月01日 12:00:15 |
 | 1 | 2020年01月01日 12:00:41 | 10.3 | 2020年01月01日 12:00:30 |
 | 2 | 2020年01月01日 12:00:08 | 11.1 | 2020年01月01日 12:00:00 |
 | 2 | 2020年01月01日 12:00:23 | 11.2 | 2020年01月01日 12:00:15 |
 | 2 | 2020年01月01日 12:00:38 | 11.3 | 2020年01月01日 12:00:30 |
 +-----------+---------------------+------+---------------------*/

You can use this concept to join the fuel consumption rate data with the temperature reported by each sensor:

SELECT
t1.sensor_idASsensor_id,
t1.tsAStemp_ts,
t1.tempAStemp,
t2.tsASrate_ts,
t2.rateASrate
FROMmydataset.sensor_temperaturest1
LEFTJOINmydataset.sensor_fuel_ratest2
ONTIMESTAMP_BUCKET(t1.ts,INTERVAL15SECOND)=
TIMESTAMP_BUCKET(t2.ts,INTERVAL15SECOND)
ANDt1.sensor_id=t2.sensor_id
ORDERBYsensor_id,temp_ts;
/*-----------+---------------------+------+---------------------+------+
 | sensor_id | temp_ts | temp | rate_ts | rate |
 +-----------+---------------------+------+---------------------+------+
 | 1 | 2020年01月01日 12:00:00 | 37.1 | 2020年01月01日 12:00:11 | 10.1 |
 | 1 | 2020年01月01日 12:00:15 | 37.2 | 2020年01月01日 12:00:26 | 10.2 |
 | 1 | 2020年01月01日 12:00:30 | 37.3 | 2020年01月01日 12:00:41 | 10.3 |
 | 2 | 2020年01月01日 12:00:01 | 38.1 | 2020年01月01日 12:00:08 | 11.1 |
 | 2 | 2020年01月01日 12:00:15 | 38.2 | 2020年01月01日 12:00:23 | 11.2 |
 | 2 | 2020年01月01日 12:00:31 | 38.3 | 2020年01月01日 12:00:38 | 11.3 |
 +-----------+---------------------+------+---------------------+------*/

`AS OF` join

For this section, use the mydataset.sensor_temperatures table and create a new table, mydataset.sensor_location.

The mydataset.sensor_temperatures table contains temperature data from different sensors, reported every 15 seconds:

SELECT*FROMmydataset.sensor_temperaturesORDERBYsensor_id,ts;
/*-----------+---------------------+------+
 | sensor_id | ts | temp |
 +-----------+---------------------+------+
 | 1 | 2020年01月01日 12:00:00 | 37.1 |
 | 1 | 2020年01月01日 12:00:15 | 37.2 |
 | 1 | 2020年01月01日 12:00:30 | 37.3 |
 | 2 | 2020年01月01日 12:00:45 | 38.1 |
 | 2 | 2020年01月01日 12:01:01 | 38.2 |
 | 2 | 2020年01月01日 12:01:15 | 38.3 |
 +-----------+---------------------+------*/

To create mydataset.sensor_location, run the following query:

CREATEORREPLACETABLEmydataset.sensor_locationsAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,tsTIMESTAMP,locationGEOGRAPHY>>[
(1,TIMESTAMP'2020-01-01 11:59:47.063',ST_GEOGPOINT(-122.022,37.406)),
(1,TIMESTAMP'2020-01-01 12:00:08.185',ST_GEOGPOINT(-122.021,37.407)),
(1,TIMESTAMP'2020-01-01 12:00:28.032',ST_GEOGPOINT(-122.020,37.405)),
(2,TIMESTAMP'2020-01-01 07:28:41.239',ST_GEOGPOINT(-122.390,37.790))
]);
/*-----------+---------------------+------------------------+
 | sensor_id | ts | location |
 +-----------+---------------------+------------------------+
 | 1 | 2020年01月01日 11:59:47 | POINT(-122.022 37.406) |
 | 1 | 2020年01月01日 12:00:08 | POINT(-122.021 37.407) |
 | 1 | 2020年01月01日 12:00:28 | POINT(-122.02 37.405) |
 | 2 | 2020年01月01日 07:28:41 | POINT(-122.39 37.79) |
 +-----------+---------------------+------------------------*/

Now join data from mydataset.sensor_temperatures with data from mydataset.sensor_location.

In this scenario, you can't use a windowed join, since the temperature data and location date are not reported at the same interval.

One way to do this in BigQuery is to transform the timestamp data into a range, using the RANGE data type. The range represents the temporal validity of a row, providing the start and end time for which the row is valid.

Use the LEAD window function to find the next data point in the time series, relative to the current data point, which is also the end-boundary of the temporal validity of the current row. The following queries demonstrate this, converting location data to validity ranges:

WITHlocations_rangesAS(
SELECT
sensor_id,
RANGE(ts,LEAD(ts)OVER(PARTITIONBYsensor_idORDERBYtsASC))ASts_range,
location
FROMmydataset.sensor_locations
)
SELECT*FROMlocations_rangesORDERBYsensor_id,ts_range;
/*-----------+--------------------------------------------+------------------------+
 | sensor_id | ts_range | location |
 +-----------+--------------------------------------------+------------------------+
 | 1 | [2020年01月01日 11:59:47, 2020年01月01日 12:00:08) | POINT(-122.022 37.406) |
 | 1 | [2020年01月01日 12:00:08, 2020年01月01日 12:00:28) | POINT(-122.021 37.407) |
 | 1 | [2020年01月01日 12:00:28, UNBOUNDED) | POINT(-122.02 37.405) |
 | 2 | [2020年01月01日 07:28:41, UNBOUNDED) | POINT(-122.39 37.79) |
 +-----------+--------------------------------------------+------------------------*/

Now you can join temperatures data (left) with the location data (right):

WITHlocations_rangesAS(
SELECT
sensor_id,
RANGE(ts,LEAD(ts)OVER(PARTITIONBYsensor_idORDERBYtsASC))ASts_range,
location
FROMmydataset.sensor_locations
)
SELECT
t1.sensor_idASsensor_id,
t1.tsAStemp_ts,
t1.tempAStemp,
t2.locationASlocation
FROMmydataset.sensor_temperaturest1
LEFTJOINlocations_rangest2
ONRANGE_CONTAINS(t2.ts_range,t1.ts)
ANDt1.sensor_id=t2.sensor_id
ORDERBYsensor_id,temp_ts;
/*-----------+---------------------+------+------------------------+
 | sensor_id | temp_ts | temp | location |
 +-----------+---------------------+------+------------------------+
 | 1 | 2020年01月01日 12:00:00 | 37.1 | POINT(-122.022 37.406) |
 | 1 | 2020年01月01日 12:00:15 | 37.2 | POINT(-122.021 37.407) |
 | 1 | 2020年01月01日 12:00:30 | 37.3 | POINT(-122.02 37.405) |
 | 2 | 2020年01月01日 12:00:01 | 38.1 | POINT(-122.39 37.79) |
 | 2 | 2020年01月01日 12:00:15 | 38.2 | POINT(-122.39 37.79) |
 | 2 | 2020年01月01日 12:00:31 | 38.3 | POINT(-122.39 37.79) |
 +-----------+---------------------+------+------------------------*/

Combine and split range data

In this section, combine range data that have overlapping ranges and split range data into smaller ranges.

Combine range data

Tables with range values might have overlapping ranges. In the following query, the time ranges capture the state of sensors at approximately 5-minute intervals:

CREATEORREPLACETABLEmydataset.sensor_metricsAS
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,durationRANGE<DATETIME>,flowINT64,spinsINT64>>[
(1,RANGE<DATETIME>"[2020年01月01日 12:00:01, 2020年01月01日 12:05:23)",10,1),
(1,RANGE<DATETIME>"[2020年01月01日 12:05:12, 2020年01月01日 12:10:46)",10,20),
(1,RANGE<DATETIME>"[2020年01月01日 12:10:27, 2020年01月01日 12:15:56)",11,4),
(1,RANGE<DATETIME>"[2020年01月01日 12:16:00, 2020年01月01日 12:20:58)",11,9),
(1,RANGE<DATETIME>"[2020年01月01日 12:20:33, 2020年01月01日 12:25:08)",11,8),
(2,RANGE<DATETIME>"[2020年01月01日 12:00:19, 2020年01月01日 12:05:08)",21,31),
(2,RANGE<DATETIME>"[2020年01月01日 12:05:08, 2020年01月01日 12:10:30)",21,2),
(2,RANGE<DATETIME>"[2020年01月01日 12:10:22, 2020年01月01日 12:15:42)",21,10)
]);

The following query on the table shows several overlapping ranges:

SELECT*FROMmydataset.sensor_metrics;
/*-----------+--------------------------------------------+------+-------+
 | sensor_id | duration | flow | spins |
 +-----------+--------------------------------------------+------+-------+
 | 1 | [2020年01月01日 12:00:01, 2020年01月01日 12:05:23) | 10 | 1 |
 | 1 | [2020年01月01日 12:05:12, 2020年01月01日 12:10:46) | 10 | 20 |
 | 1 | [2020年01月01日 12:10:27, 2020年01月01日 12:15:56) | 11 | 4 |
 | 1 | [2020年01月01日 12:16:00, 2020年01月01日 12:20:58) | 11 | 9 |
 | 1 | [2020年01月01日 12:20:33, 2020年01月01日 12:25:08) | 11 | 8 |
 | 2 | [2020年01月01日 12:00:19, 2020年01月01日 12:05:08) | 21 | 31 |
 | 2 | [2020年01月01日 12:05:08, 2020年01月01日 12:10:30) | 21 | 2 |
 | 2 | [2020年01月01日 12:10:22, 2020年01月01日 12:15:42) | 21 | 10 |
 +-----------+--------------------------------------------+------+-------*/

For some of the overlapping ranges, the value in the flow column is the same. For example, rows 1 and 2 overlap, and also have the same flow readings. You can combine these two rows to reduce the number of rows in the table. You can use the RANGE_SESSIONIZE table function to find ranges that overlap with each row, and provide an additional session_range column that contains a range that is the union of all ranges that overlap. To display the session ranges for each row, run the following query:

SELECTsensor_id,session_range,flow
FROMRANGE_SESSIONIZE(
# Input data.
(SELECTsensor_id,duration,flowFROMmydataset.sensor_metrics),
# Range column.
"duration",
# Partitioning columns. Ranges are sessionized only within these partitions.
["sensor_id","flow"],
# Sessionize mode.
"OVERLAPS")
ORDERBYsensor_id,session_range;
/*-----------+--------------------------------------------+------+
 | sensor_id | session_range | flow |
 +-----------+--------------------------------------------+------+
 | 1 | [2020年01月01日 12:00:01, 2020年01月01日 12:10:46) | 10 |
 | 1 | [2020年01月01日 12:00:01, 2020年01月01日 12:10:46) | 10 |
 | 1 | [2020年01月01日 12:10:27, 2020年01月01日 12:15:56) | 11 |
 | 1 | [2020年01月01日 12:16:00, 2020年01月01日 12:25:08) | 11 |
 | 1 | [2020年01月01日 12:16:00, 2020年01月01日 12:25:08) | 11 |
 | 2 | [2020年01月01日 12:00:19, 2020年01月01日 12:05:08) | 21 |
 | 2 | [2020年01月01日 12:05:08, 2020年01月01日 12:15:42) | 21 |
 | 2 | [2020年01月01日 12:05:08, 2020年01月01日 12:15:42) | 21 |
 +-----------+--------------------------------------------+------*/

Note that for sensor_id having value 2, the first row's end boundary has the same datetime value as the second row's start boundary. However, because end boundaries are exclusive, they don't overlap (only meet) and hence were not in the same session ranges. If you want to place these two rows in the same session ranges, use the MEETS sessionize mode.

To combine the ranges, group the results by session_range and the partitioning columns (sensor_id and flow):

SELECTsensor_id,session_range,flow
FROMRANGE_SESSIONIZE(
(SELECTsensor_id,duration,flowFROMmydataset.sensor_metrics),
"duration",
["sensor_id","flow"],
"OVERLAPS")
GROUPBYsensor_id,session_range,flow
ORDERBYsensor_id,session_range;
/*-----------+--------------------------------------------+------+
 | sensor_id | session_range | flow |
 +-----------+--------------------------------------------+------+
 | 1 | [2020年01月01日 12:00:01, 2020年01月01日 12:10:46) | 10 |
 | 1 | [2020年01月01日 12:10:27, 2020年01月01日 12:15:56) | 11 |
 | 1 | [2020年01月01日 12:16:00, 2020年01月01日 12:25:08) | 11 |
 | 2 | [2020年01月01日 12:00:19, 2020年01月01日 12:05:08) | 21 |
 | 2 | [2020年01月01日 12:05:08, 2020年01月01日 12:15:42) | 21 |
 +-----------+--------------------------------------------+------*/

Finally, add the spins column in the session data by aggregating it using SUM.

SELECTsensor_id,session_range,flow,SUM(spins)asspins
FROMRANGE_SESSIONIZE(
TABLEmydataset.sensor_metrics,
"duration",
["sensor_id","flow"],
"OVERLAPS")
GROUPBYsensor_id,session_range,flow
ORDERBYsensor_id,session_range;
/*-----------+--------------------------------------------+------+-------+
 | sensor_id | session_range | flow | spins |
 +-----------+--------------------------------------------+------+-------+
 | 1 | [2020年01月01日 12:00:01, 2020年01月01日 12:10:46) | 10 | 21 |
 | 1 | [2020年01月01日 12:10:27, 2020年01月01日 12:15:56) | 11 | 4 |
 | 1 | [2020年01月01日 12:16:00, 2020年01月01日 12:25:08) | 11 | 17 |
 | 2 | [2020年01月01日 12:00:19, 2020年01月01日 12:05:08) | 21 | 31 |
 | 2 | [2020年01月01日 12:05:08, 2020年01月01日 12:15:42) | 21 | 12 |
 +-----------+--------------------------------------------+------+-------*/

Split range data

You can also split a range into smaller ranges. For this example, use the following table with range data:

/*-----------+--------------------------+------+-------+
 | sensor_id | duration | flow | spins |
 +-----------+--------------------------+------+-------+
 | 1 | [2020年01月01日, 2020年12月31日) | 10 | 21 |
 | 1 | [2021年01月01日, 2021年12月31日) | 11 | 4 |
 | 2 | [2020年04月15日, 2021年04月15日) | 21 | 31 |
 | 2 | [2021年04月15日, 2021年04月15日) | 21 | 12 |
 +-----------+--------------------------+------+-------*/

Now, split the original ranges into 3-month intervals:

WITHsensor_dataAS(
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,durationRANGE<DATE>,flowINT64,spinsINT64>>[
(1,RANGE<DATE>"[2020年01月01日, 2020年12月31日)",10,21),
(1,RANGE<DATE>"[2021年01月01日, 2021年12月31日)",11,4),
(2,RANGE<DATE>"[2020年04月15日, 2021年04月15日)",21,31),
(2,RANGE<DATE>"[2021年04月15日, 2022年04月15日)",21,12)
])
)
SELECTsensor_id,expanded_range,flow,spins
FROMsensor_data,UNNEST(GENERATE_RANGE_ARRAY(duration,INTERVAL3MONTH))ASexpanded_range;
/*-----------+--------------------------+------+-------+
 | sensor_id | expanded_range | flow | spins |
 +-----------+--------------------------+------+-------+
 | 1 | [2020年01月01日, 2020年04月01日) | 10 | 21 |
 | 1 | [2020年04月01日, 2020年07月01日) | 10 | 21 |
 | 1 | [2020年07月01日, 2020年10月01日) | 10 | 21 |
 | 1 | [2020年10月01日, 2020年12月31日) | 10 | 21 |
 | 1 | [2021年01月01日, 2021年04月01日) | 11 | 4 |
 | 1 | [2021年04月01日, 2021年07月01日) | 11 | 4 |
 | 1 | [2021年07月01日, 2021年10月01日) | 11 | 4 |
 | 1 | [2021年10月01日, 2021年12月31日) | 11 | 4 |
 | 2 | [2020年04月15日, 2020年07月15日) | 21 | 31 |
 | 2 | [2020年07月15日, 2020年10月15日) | 21 | 31 |
 | 2 | [2020年10月15日, 2021年01月15日) | 21 | 31 |
 | 2 | [2021年01月15日, 2021年04月15日) | 21 | 31 |
 | 2 | [2021年04月15日, 2021年07月15日) | 21 | 12 |
 | 2 | [2021年07月15日, 2021年10月15日) | 21 | 12 |
 | 2 | [2021年10月15日, 2022年01月15日) | 21 | 12 |
 | 2 | [2022年01月15日, 2022年04月15日) | 21 | 12 |
 +-----------+--------------------------+------+-------*/

In the previous query, each original range was broken down into smaller ranges, with width set to INTERVAL 3 MONTH. However, the 3-month ranges are not aligned to a common origin. To align these ranges to a common origin 2020年01月01日, run the following query:

WITHsensor_dataAS(
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,durationRANGE<DATE>,flowINT64,spinsINT64>>[
(1,RANGE<DATE>"[2020年01月01日, 2020年12月31日)",10,21),
(1,RANGE<DATE>"[2021年01月01日, 2021年12月31日)",11,4),
(2,RANGE<DATE>"[2020年04月15日, 2021年04月15日)",21,31),
(2,RANGE<DATE>"[2021年04月15日, 2022年04月15日)",21,12)
])
)
SELECTsensor_id,expanded_range,flow,spins
FROMsensor_data
JOINUNNEST(GENERATE_RANGE_ARRAY(RANGE<DATE>"[2020年01月01日, 2022年12月31日)",INTERVAL3MONTH))ASexpanded_range
ONRANGE_OVERLAPS(duration,expanded_range);
/*-----------+--------------------------+------+-------+
 | sensor_id | expanded_range | flow | spins |
 +-----------+--------------------------+------+-------+
 | 1 | [2020年01月01日, 2020年04月01日) | 10 | 21 |
 | 1 | [2020年04月01日, 2020年07月01日) | 10 | 21 |
 | 1 | [2020年07月01日, 2020年10月01日) | 10 | 21 |
 | 1 | [2020年10月01日, 2021年01月01日) | 10 | 21 |
 | 1 | [2021年01月01日, 2021年04月01日) | 11 | 4 |
 | 1 | [2021年04月01日, 2021年07月01日) | 11 | 4 |
 | 1 | [2021年07月01日, 2021年10月01日) | 11 | 4 |
 | 1 | [2021年10月01日, 2022年01月01日) | 11 | 4 |
 | 2 | [2020年04月01日, 2020年07月01日) | 21 | 31 |
 | 2 | [2020年07月01日, 2020年10月01日) | 21 | 31 |
 | 2 | [2020年10月01日, 2021年01月01日) | 21 | 31 |
 | 2 | [2021年01月01日, 2021年04月01日) | 21 | 31 |
 | 2 | [2021年04月01日, 2021年07月01日) | 21 | 31 |
 | 2 | [2021年04月01日, 2021年07月01日) | 21 | 12 |
 | 2 | [2021年07月01日, 2021年10月01日) | 21 | 12 |
 | 2 | [2021年10月01日, 2022年01月01日) | 21 | 12 |
 | 2 | [2022年01月01日, 2022年04月01日) | 21 | 12 |
 | 2 | [2022年04月01日, 2022年07月01日) | 21 | 12 |
 +-----------+--------------------------+------+-------*/

In the previous query, the row with the range [2020年04月15日, 2021年04月15日) is split into 5 ranges, starting with the range [2020年04月01日, 2020年07月01日). Note that the start boundary now extends beyond the original start boundary, in order to align with the common origin. If you don't want the start boundary to not extend beyond the original start boundary, you can restrict the JOIN condition:

WITHsensor_dataAS(
SELECT*FROMUNNEST(
ARRAY<STRUCT<sensor_idINT64,durationRANGE<DATE>,flowINT64,spinsINT64>>[
(1,RANGE<DATE>"[2020年01月01日, 2020年12月31日)",10,21),
(1,RANGE<DATE>"[2021年01月01日, 2021年12月31日)",11,4),
(2,RANGE<DATE>"[2020年04月15日, 2021年04月15日)",21,31),
(2,RANGE<DATE>"[2021年04月15日, 2022年04月15日)",21,12)
])
)
SELECTsensor_id,expanded_range,flow,spins
FROMsensor_data
JOINUNNEST(GENERATE_RANGE_ARRAY(RANGE<DATE>"[2020年01月01日, 2022年12月31日)",INTERVAL3MONTH))ASexpanded_range
ONRANGE_CONTAINS(duration,RANGE_START(expanded_range));
/*-----------+--------------------------+------+-------+
 | sensor_id | expanded_range | flow | spins |
 +-----------+--------------------------+------+-------+
 | 1 | [2020年01月01日, 2020年04月01日) | 10 | 21 |
 | 1 | [2020年04月01日, 2020年07月01日) | 10 | 21 |
 | 1 | [2020年07月01日, 2020年10月01日) | 10 | 21 |
 | 1 | [2020年10月01日, 2021年01月01日) | 10 | 21 |
 | 1 | [2021年01月01日, 2021年04月01日) | 11 | 4 |
 | 1 | [2021年04月01日, 2021年07月01日) | 11 | 4 |
 | 1 | [2021年07月01日, 2021年10月01日) | 11 | 4 |
 | 1 | [2021年10月01日, 2022年01月01日) | 11 | 4 |
 | 2 | [2020年07月01日, 2020年10月01日) | 21 | 31 |
 | 2 | [2020年10月01日, 2021年01月01日) | 21 | 31 |
 | 2 | [2021年01月01日, 2021年04月01日) | 21 | 31 |
 | 2 | [2021年04月01日, 2021年07月01日) | 21 | 31 |
 | 2 | [2021年07月01日, 2021年10月01日) | 21 | 12 |
 | 2 | [2021年10月01日, 2022年01月01日) | 21 | 12 |
 | 2 | [2022年01月01日, 2022年04月01日) | 21 | 12 |
 | 2 | [2022年04月01日, 2022年07月01日) | 21 | 12 |
 +-----------+--------------------------+------+-------*/

You now see that the range [2020年04月15日, 2021年04月15日) was split into 4 ranges, starting with the range [2020年07月01日, 2020年10月01日).

Best practices for storing data

When storing time series data, it is important to consider the query patterns that are used against the tables where the data is stored. Typically, when querying time series data, you can filter the data for a specific time range.
To optimize these usage patterns, it is recommended to store time series data in partitioned tables, with data partitioned either by the time column or ingestion time. This can significantly improve the query time performance of the time series data, as this lets BigQuery to prune partitions that don't contain queried data.
You can enable clustering on the time, range, or one of the partitioning columns for further query time performance improvements.