Alcune o tutte le informazioni in questa pagina potrebbero non essere applicabili a Cloud de Confiance di S3NS. Per maggiori dettagli, consulta Differenze rispetto a Google Cloud.

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Utilizzare i dati delle serie temporali

Questo documento descrive come utilizzare le funzioni SQL per supportare l'analisi delle serie temporali.

Introduzione

Una serie temporale è una sequenza di punti dati, ognuno composto da un'ora e un valore associato a quell'ora. In genere, una serie temporale ha anche un identificatore, che la denomina in modo univoco.

Nei database relazionali, una serie temporale viene modellata come una tabella con i seguenti gruppi di colonne:

Colonna Data/Ora
Potrebbe avere colonne di partizionamento, ad esempio il codice postale
Una o più colonne di valori o un tipo STRUCT che combina più valori, ad esempio temperatura e IQA

Di seguito è riportato un esempio di dati di serie temporali modellati come tabella:

Esempio di tabella delle serie temporali.

Aggregare una serie temporale

Nell'analisi delle serie temporali, l'aggregazione temporale è un'aggregazione eseguita lungo l'asse temporale.

Puoi eseguire l'aggregazione temporale in BigQuery con l'aiuto delle funzioni di suddivisione temporale (TIMESTAMP_BUCKET, DATE_BUCKET, e DATETIME_BUCKET). Le funzioni di suddivisione temporale mappano i valori temporali di input nel bucket a cui appartengono.

In genere, l'aggregazione temporale viene eseguita per combinare più punti dati in una finestra temporale in un singolo punto dati, utilizzando una funzione di aggregazione, come AVG, MIN, MAX, COUNT o SUM. Ad esempio, la latenza media delle richieste di 15 minuti, le temperature minime e massime giornaliere e il numero giornaliero di corse in taxi.

Per le query in questa sezione, crea una tabella denominata mydataset.environmental_data_hourly:

CREATE OR REPLACE TABLE mydataset.environmental_data_hourly AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<zip_code INT64, time TIMESTAMP, aqi INT64, temperature INT64>>[
    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)
]);

Un'osservazione interessante sui dati precedenti è che le misurazioni vengono eseguite in periodi di tempo arbitrari, noti come serie temporali non allineate. L'aggregazione è uno dei modi in cui è possibile allineare una serie temporale.

Ottenere una media di 3 ore

La seguente query calcola una media di 3 ore dell'indice di qualità dell'aria (IQA) e della temperatura per ogni codice postale. La funzione TIMESTAMP_BUCKET esegue l'aggregazione temporale assegnando a ogni valore temporale un giorno specifico.

SELECT
  TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR) AS time,
  zip_code,
  CAST(AVG(aqi) AS INT64) AS aqi,
  CAST(AVG(temperature) AS INT64) AS temperature
FROM mydataset.environmental_data_hourly
GROUP BY zip_code, time
ORDER BY zip_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 |
 +---------------------+----------+-----+-------------*/

Ottenere un valore minimo e massimo di 3 ore

Nella query seguente, calcola le temperature minime e massime di 3 ore per ogni codice postale:

SELECT
  TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR) AS time,
  zip_code,
  MIN(temperature) AS temperature_min,
  MAX(temperature) AS temperature_max,
FROM mydataset.environmental_data_hourly
GROUP BY zip_code, time
ORDER BY zip_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 |
 +---------------------+----------+-----------------+-----------------*/

Ottenere una media di 3 ore con allineamento personalizzato

Quando esegui l'aggregazione delle serie temporali, utilizzi un allineamento specifico per le finestre delle serie temporali, in modo implicito o esplicito. Le query precedenti utilizzavano l'allineamento implicito, che produceva bucket che iniziavano in orari come 00:00:00, 03:00:00 e 06:00:00. Per impostare esplicitamente questo allineamento nella funzione TIMESTAMP_BUCKET, passa un argomento facoltativo che specifica l'origine.

Nella query seguente, l'origine è impostata su 2020-01-01 02:00:00. In questo modo l'allineamento viene modificato e vengono generati bucket che iniziano in orari come 02:00:00, 05:00:00 e 08:00:00:

SELECT
  TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR, TIMESTAMP '2020-01-01 02:00:00') AS time,
  zip_code,
  CAST(AVG(aqi) AS INT64) AS aqi,
  CAST(AVG(temperature) AS INT64) AS temperature
FROM mydataset.environmental_data_hourly
GROUP BY zip_code, time
ORDER BY zip_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 |
 +---------------------+----------+-----+-------------*/

Aggregare una serie temporale con il riempimento dei gap

A volte, dopo aver aggregato una serie temporale, i dati potrebbero presentare dei gap che devono essere riempiti con alcuni valori per un'ulteriore analisi o presentazione dei dati. La tecnica utilizzata per riempire questi gap è chiamata riempimento dei gap. In BigQuery, puoi utilizzare la GAP_FILL funzione di tabella per riempire i gap nei dati delle serie temporali, utilizzando uno dei metodi di riempimento dei gap forniti:

NULL, noto anche come costante
LOCF, ultima osservazione riportata
Lineare, interpolazione lineare tra i due punti dati adiacenti

Per le query in questa sezione, crea una tabella denominata mydataset.environmental_data_hourly_with_gaps, basata sui dati utilizzati nella sezione precedente, ma con dei gap. Negli scenari reali, i dati potrebbero avere punti dati mancanti a causa di un malfunzionamento a breve termine della stazione meteorologica.

CREATE OR REPLACE TABLE mydataset.environmental_data_hourly_with_gaps AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<zip_code INT64, time TIMESTAMP, aqi INT64, temperature INT64>>[
    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)
]);

Ottenere una media di 3 ore (inclusi i gap)

La seguente query calcola la media di 3 ore dell'IQA e della temperatura per ogni codice postale:

SELECT
  TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR) AS time,
  zip_code,
  CAST(AVG(aqi) AS INT64) AS aqi,
  CAST(AVG(temperature) AS INT64) AS temperature
FROM mydataset.environmental_data_hourly_with_gaps
GROUP BY zip_code, time
ORDER BY zip_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 |
 +---------------------+----------+-----+-------------*/

Nota come l'output presenta dei gap in determinati intervalli di tempo. Ad esempio, la serie temporale per il codice postale 60606 non ha un punto dati in 2020-09-08 12:00:00 e la serie temporale per il codice postale 94105 non ha un punto dati in 2020-09-08 15:00:00.

Ottenere una media di 3 ore (riempire i gap)

Utilizza la query della sezione precedente e aggiungi la funzione GAP_FILL per riempire i gap:

WITH aggregated_3_hr AS (
  SELECT
    TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR) AS time,
    zip_code,
    CAST(AVG(aqi) AS INT64) AS aqi,
    CAST(AVG(temperature) AS INT64) AS temperature
   FROM mydataset.environmental_data_hourly_with_gaps
   GROUP BY zip_code, time)

SELECT *
FROM GAP_FILL(
  TABLE aggregated_3_hr,
  ts_column => 'time',
  bucket_width => INTERVAL 3 HOUR,
  partitioning_columns => ['zip_code']
)
ORDER BY zip_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 |
 +---------------------+----------+------+-------------*/

La tabella di output ora contiene una riga mancante in 2020-09-08 12:00:00 per il codice postale 60606 e in 2020-09-08 15:00:00 per il codice postale 94105, con valori NULL nelle colonne delle metriche corrispondenti. Poiché non hai specificato alcun metodo di riempimento dei gap, GAP_FILL ha utilizzato il metodo di riempimento dei gap predefinito, NULL.

Riempire i gap con il riempimento dei gap lineare e LOCF

Nella query seguente, la funzione GAP_FILL viene utilizzata con il metodo di riempimento dei gap LOCF per la colonna aqi e l'interpolazione lineare per la colonna temperature:

WITH aggregated_3_hr AS (
  SELECT
    TIMESTAMP_BUCKET(time, INTERVAL 3 HOUR) AS time,
    zip_code,
    CAST(AVG(aqi) AS INT64) AS aqi,
    CAST(AVG(temperature) AS INT64) AS temperature
   FROM mydataset.environmental_data_hourly_with_gaps
   GROUP BY zip_code, time)

SELECT *
FROM GAP_FILL(
  TABLE aggregated_3_hr,
  ts_column => 'time',
  bucket_width => INTERVAL 3 HOUR,
  partitioning_columns => ['zip_code'],
  value_columns => [
    ('aqi', 'locf'),
    ('temperature', 'linear')
  ]
)
ORDER BY zip_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 questa query, la prima riga con riempimento dei gap ha il valore aqi 36, che viene ricavato dal punto dati precedente di questa serie temporale (codice postale 60606) in 2020-09-08 09:00:00. Il valore temperature 62 è il risultato dell'interpolazione lineare tra i punti dati 2020-09-08 09:00:00 e 2020-09-08 15:00:00. L'altra riga mancante è stata creata in modo simile: il valore aqi 125 è stato riportato dal punto dati precedente di questa serie temporale (codice postale 94105) e il valore della temperatura 65 è il risultato dell'interpolazione lineare tra i punti dati precedenti e successivi disponibili.

Allineare una serie temporale con il riempimento dei gap

Le serie temporali possono essere allineate o non allineate. Una serie temporale è allineata quando i punti dati si verificano solo a intervalli regolari.

Nella realtà, al momento della raccolta, le serie temporali sono raramente allineate e in genere richiedono un'ulteriore elaborazione per allinearle.

Ad esempio, considera i dispositivi IoT che inviano le loro metriche a un collector centralizzato ogni minuto. Non sarebbe ragionevole aspettarsi che i dispositivi inviino le loro metriche esattamente negli stessi istanti di tempo. In genere, ogni dispositivo invia le sue metriche con la stessa frequenza (periodo), ma con un offset temporale (allineamento) diverso. Il seguente diagramma illustra questo esempio. Puoi vedere ogni dispositivo che invia i suoi dati con un intervallo di un minuto con alcune istanze di dati mancanti (Dispositivo 3 in 9:36:39) e dati ritardati (Dispositivo 1 in 9:37:28).

Esempio di allineamento delle serie temporali

Puoi eseguire l'allineamento delle serie temporali sui dati non allineati, utilizzando l'aggregazione temporale. Questa operazione è utile se vuoi modificare il periodo di campionamento delle serie temporali, ad esempio passando dal periodo di campionamento originale di 1 minuto a un periodo di 15 minuti. Puoi allineare i dati per un'ulteriore elaborazione delle serie temporali, ad esempio unire i dati delle serie temporali o per scopi di visualizzazione (ad esempio la creazione di grafici).

Puoi utilizzare la funzione di tabella GAP_FILL con i metodi di riempimento dei gap LOCF o lineare per eseguire l'allineamento delle serie temporali. L'idea è di utilizzare GAP_FILL con il periodo di output e l'allineamento selezionati (controllati dall'argomento facoltativo origin). Il risultato dell'operazione è una tabella con serie temporali allineate, in cui i valori di ogni punto dati vengono ricavati dalle serie temporali di input con il metodo di riempimento dei gap utilizzato per quella particolare colonna di valori (LOCF o lineare).

Crea una tabella mydataset.device_data, simile all'illustrazione precedente:

CREATE OR REPLACE TABLE mydataset.device_data AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<device_id INT64, time TIMESTAMP, signal INT64, state STRING>>[
    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')
]);

Di seguito sono riportati i dati effettivi ordinati in base alle colonne time e device_id:

SELECT * FROM mydataset.device_data ORDER BY time, 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   |
 +-----------+---------------------+--------+----------*/

La tabella contiene le serie temporali per ogni dispositivo con due colonne di metriche:

signal : livello del segnale osservato dal dispositivo al momento del campionamento, rappresentato come un valore intero compreso tra 0 e 100.
state : stato del dispositivo al momento del campionamento, rappresentato come una stringa in formato libero.

Nella query seguente, la funzione GAP_FILL viene utilizzata per allineare le serie temporali a intervalli di 1 minuto. Nota come l'interpolazione lineare viene utilizzata per calcolare i valori della colonna signal e LOCF per la colonna state. Per questi dati di esempio, l'interpolazione lineare è una scelta adatta per calcolare i valori di output.

SELECT *
FROM GAP_FILL(
  TABLE mydataset.device_data,
  ts_column => 'time',
  bucket_width => INTERVAL 1 MINUTE,
  partitioning_columns => ['device_id'],
  value_columns => [
    ('signal', 'linear'),
    ('state', 'locf')
  ]
)
ORDER BY time, 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   |
 +---------------------+-----------+--------+----------*/

La tabella di output contiene una serie temporale allineata per ogni dispositivo e le colonne dei valori (signal e state), calcolate utilizzando i metodi di riempimento dei gap specificati nella chiamata di funzione.

Unire i dati delle serie temporali

Puoi unire i dati delle serie temporali utilizzando un join con finestra o un join AS OF.

Join con finestra

A volte è necessario unire due o più tabelle con dati di serie temporali. Considera le seguenti due tabelle:

mydataset.sensor_temperatures, contiene i dati sulla temperatura riportati da ogni sensore ogni 15 secondi.
mydataset.sensor_fuel_rates, contiene la velocità di consumo di carburante misurata da ogni sensore ogni 15 secondi.

Per creare queste tabelle, esegui le seguenti query:

CREATE OR REPLACE TABLE mydataset.sensor_temperatures AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<sensor_id INT64, ts TIMESTAMP, temp FLOAT64>>[
  (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)
]);

CREATE OR REPLACE TABLE mydataset.sensor_fuel_rates AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<sensor_id INT64, ts TIMESTAMP, rate FLOAT64>>[
    (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)
]);

Di seguito sono riportati i dati effettivi delle tabelle:

SELECT * FROM mydataset.sensor_temperatures ORDER BY sensor_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 * FROM mydataset.sensor_fuel_rates ORDER BY sensor_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 |
 +-----------+---------------------+------*/

Per controllare la velocità di consumo di carburante alla temperatura riportata da ogni sensore, puoi unire le due serie temporali.

Sebbene i dati nelle due serie temporali non siano allineati, vengono campionati allo stesso intervallo (15 secondi), pertanto questi dati sono un buon candidato per il join con finestra. Utilizza le funzioni di suddivisione temporale per allineare i timestamp utilizzati come chiavi di join.

Le seguenti query illustrano come ogni timestamp può essere assegnato a finestre di 15 secondi utilizzando la TIMESTAMP_BUCKET funzione:

SELECT *, TIMESTAMP_BUCKET(ts, INTERVAL 15 SECOND) ts_window
FROM mydataset.sensor_temperatures
ORDER BY sensor_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, INTERVAL 15 SECOND) ts_window
FROM mydataset.sensor_fuel_rates
ORDER BY sensor_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 |
 +-----------+---------------------+------+---------------------*/

Puoi utilizzare questo concetto per unire i dati sulla velocità di consumo di carburante con la temperatura riportata da ogni sensore:

SELECT
  t1.sensor_id AS sensor_id,
  t1.ts AS temp_ts,
  t1.temp AS temp,
  t2.ts AS rate_ts,
  t2.rate AS rate
FROM mydataset.sensor_temperatures t1
LEFT JOIN mydataset.sensor_fuel_rates t2
ON TIMESTAMP_BUCKET(t1.ts, INTERVAL 15 SECOND) =
     TIMESTAMP_BUCKET(t2.ts, INTERVAL 15 SECOND)
   AND t1.sensor_id = t2.sensor_id
ORDER BY sensor_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 |
 +-----------+---------------------+------+---------------------+------*/

Join `AS OF`

Per questa sezione, utilizza la tabella mydataset.sensor_temperatures e crea una nuova tabella, mydataset.sensor_location.

La tabella mydataset.sensor_temperatures contiene i dati sulla temperatura di diversi sensori, riportati ogni 15 secondi:

SELECT * FROM mydataset.sensor_temperatures ORDER BY sensor_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 |
 +-----------+---------------------+------*/

Per creare mydataset.sensor_location, esegui la seguente query:

CREATE OR REPLACE TABLE mydataset.sensor_locations AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<sensor_id INT64, ts TIMESTAMP, location GEOGRAPHY>>[
  (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) |
 +-----------+---------------------+------------------------*/

Ora unisci i dati di mydataset.sensor_temperatures con i dati di mydataset.sensor_location.

In questo scenario, non puoi utilizzare un join con finestra, poiché i dati sulla temperatura e i dati sulla posizione non vengono riportati allo stesso intervallo.

Un modo per farlo in BigQuery è trasformare i dati timestamp in un intervallo, utilizzando il RANGE tipo di dati. L'intervallo rappresenta la validità temporale di una riga, fornendo l'ora di inizio e di fine per cui la riga è valida.

Utilizza la funzione finestra LEAD per trovare il punto dati successivo nella serie temporale, rispetto al punto dati corrente, che è anche il limite finale della validità temporale della riga corrente. Le seguenti query lo dimostrano, convertendo i dati sulla posizione in intervalli di validità:

WITH locations_ranges AS (
  SELECT
    sensor_id,
    RANGE(ts, LEAD(ts) OVER (PARTITION BY sensor_id ORDER BY ts ASC)) AS ts_range,
    location
  FROM mydataset.sensor_locations
)
SELECT * FROM locations_ranges ORDER BY sensor_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) |
 +-----------+--------------------------------------------+------------------------*/

Ora puoi unire i dati sulla temperatura (a sinistra) con i dati sulla posizione (a destra):

WITH locations_ranges AS (
  SELECT
    sensor_id,
    RANGE(ts, LEAD(ts) OVER (PARTITION BY sensor_id ORDER BY ts ASC)) AS ts_range,
    location
  FROM mydataset.sensor_locations
)
SELECT
  t1.sensor_id AS sensor_id,
  t1.ts AS temp_ts,
  t1.temp AS temp,
  t2.location AS location
FROM mydataset.sensor_temperatures t1
LEFT JOIN locations_ranges t2
ON RANGE_CONTAINS(t2.ts_range, t1.ts)
AND t1.sensor_id = t2.sensor_id
ORDER BY sensor_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) |
 +-----------+---------------------+------+------------------------*/

Combinare e suddividere i dati degli intervalli

In questa sezione, combina i dati degli intervalli che hanno intervalli sovrapposti e suddividi i dati degli intervalli in intervalli più piccoli.

Combinare i dati degli intervalli

Le tabelle con valori di intervallo potrebbero avere intervalli sovrapposti. Nella query seguente, gli intervalli di tempo acquisiscono lo stato dei sensori a intervalli di circa 5 minuti:

CREATE OR REPLACE TABLE mydataset.sensor_metrics AS
SELECT * FROM UNNEST(
  ARRAY<STRUCT<sensor_id INT64, duration RANGE<DATETIME>, flow INT64, spins INT64>>[
  (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)
]);

La seguente query sulla tabella mostra diversi intervalli sovrapposti:

SELECT * FROM mydataset.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 |
 +-----------+--------------------------------------------+------+-------*/

Per alcuni degli intervalli sovrapposti, il valore nella colonna flow è lo stesso. Ad esempio, le righe 1 e 2 si sovrappongono e hanno anche le stesse letture flow. Puoi combinare queste due righe per ridurre il numero di righe nella tabella. Puoi utilizzare la funzione di tabella RANGE_SESSIONIZE per trovare gli intervalli che si sovrappongono a ogni riga e fornire una colonna session_range aggiuntiva che contiene un intervallo che è l'unione di tutti gli intervalli che si sovrappongono. Per visualizzare gli intervalli di sessione per ogni riga, esegui la seguente query:

SELECT sensor_id, session_range, flow
FROM RANGE_SESSIONIZE(
  # Input data.
  (SELECT sensor_id, duration, flow FROM mydataset.sensor_metrics),
  # Range column.
  "duration",
  # Partitioning columns. Ranges are sessionized only within these partitions.
  ["sensor_id", "flow"],
  # Sessionize mode.
  "OVERLAPS")
ORDER BY sensor_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   |
 +-----------+--------------------------------------------+------*/

Tieni presente che per sensor_id con valore 2, il limite finale della prima riga ha lo stesso valore di data/ora del limite iniziale della seconda riga. Tuttavia, poiché i limiti finali sono esclusivi, non si sovrappongono (si incontrano solo) e quindi non si trovavano negli stessi intervalli di sessione. Se vuoi inserire queste due righe negli stessi intervalli di sessione, utilizza la MEETS modalità di sessionizzazione.

Per combinare gli intervalli, raggruppa i risultati in base a session_range e alle colonne di partizionamento (sensor_id e flow):

SELECT sensor_id, session_range, flow
FROM RANGE_SESSIONIZE(
  (SELECT sensor_id, duration, flow FROM mydataset.sensor_metrics),
  "duration",
  ["sensor_id", "flow"],
  "OVERLAPS")
GROUP BY sensor_id, session_range, flow
ORDER BY sensor_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   |
 +-----------+--------------------------------------------+------*/

Infine, aggiungi la colonna spins nei dati della sessione aggregandola utilizzando SUM.

SELECT sensor_id, session_range, flow, SUM(spins) as spins
FROM RANGE_SESSIONIZE(
  TABLE mydataset.sensor_metrics,
  "duration",
  ["sensor_id", "flow"],
  "OVERLAPS")
GROUP BY sensor_id, session_range, flow
ORDER BY sensor_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 |
 +-----------+--------------------------------------------+------+-------*/

Suddividere i dati degli intervalli

Puoi anche suddividere un intervallo in intervalli più piccoli. Per questo esempio, utilizza la seguente tabella con i dati degli intervalli:

/*-----------+--------------------------+------+-------+
 | 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 |
 +-----------+--------------------------+------+-------*/

Ora suddividi gli intervalli originali in intervalli di 3 mesi:

WITH sensor_data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<sensor_id INT64, duration RANGE<DATE>, flow INT64, spins INT64>>[
    (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)
  ])
)
SELECT sensor_id, expanded_range, flow, spins
FROM sensor_data, UNNEST(GENERATE_RANGE_ARRAY(duration, INTERVAL 3 MONTH)) AS 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, 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 |
 +-----------+--------------------------+------+-------*/

Nella query precedente, ogni intervallo originale è stato suddiviso in intervalli più piccoli, con la larghezza impostata su INTERVAL 3 MONTH. Tuttavia, gli intervalli di 3 mesi non sono allineati a un'origine comune. Per allineare questi intervalli a un'origine comune 2020-01-01, esegui la seguente query:

WITH sensor_data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<sensor_id INT64, duration RANGE<DATE>, flow INT64, spins INT64>>[
    (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)
  ])
)
SELECT sensor_id, expanded_range, flow, spins
FROM sensor_data
JOIN UNNEST(GENERATE_RANGE_ARRAY(RANGE<DATE> "[2020-01-01, 2022-12-31)", INTERVAL 3 MONTH)) AS expanded_range
ON RANGE_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 |
 +-----------+--------------------------+------+-------*/

Nella query precedente, la riga con l'intervallo [2020-04-15, 2021-04-15) viene suddivisa in 5 intervalli, a partire dall'intervallo [2020-04-01, 2020-07-01). Tieni presente che il limite iniziale ora si estende oltre il limite iniziale originale, per allinearsi all'origine comune. Se non vuoi che il limite iniziale si estenda oltre il limite iniziale originale, puoi limitare la condizione JOIN:

WITH sensor_data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<sensor_id INT64, duration RANGE<DATE>, flow INT64, spins INT64>>[
    (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)
  ])
)
SELECT sensor_id, expanded_range, flow, spins
FROM sensor_data
JOIN UNNEST(GENERATE_RANGE_ARRAY(RANGE<DATE> "[2020-01-01, 2022-12-31)", INTERVAL 3 MONTH)) AS expanded_range
ON RANGE_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 |
 +-----------+--------------------------+------+-------*/

Ora vedrai che l'intervallo [2020-04-15, 2021-04-15) è stato suddiviso in 4 intervalli, a partire dall'intervallo [2020-07-01, 2020-10-01).

Best practice per l'archiviazione dei dati

Quando archivi i dati delle serie temporali, è importante considerare i pattern di query utilizzati nelle tabelle in cui sono archiviati i dati. In genere, quando esegui una query sui dati delle serie temporali, puoi filtrare i dati per un intervallo di tempo specifico.
Per ottimizzare questi pattern di utilizzo, ti consigliamo di archiviare i dati delle serie temporali in tabelle partizionate, con i dati partizionati in base alla colonna Data/Ora o all'ora di importazione. In questo modo è possibile migliorare significativamente le prestazioni del tempo di query dei dati delle serie temporali, in quanto BigQuery può eliminare le partizioni che non contengono i dati sottoposti a query.
Puoi abilitare il clustering sulla colonna Data/Ora, Intervallo o su una delle colonne di partizionamento per migliorare ulteriormente le prestazioni del tempo di query.