Sebagian atau seluruh informasi di halaman ini mungkin tidak berlaku untuk Cloud de Confiance dari S3NS. Lihat Perbedaan dengan Google Cloud untuk mengetahui detail selengkapnya.

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

Bekerja dengan data deret waktu

Dokumen ini menjelaskan cara menggunakan fungsi SQL untuk mendukung analisis deret waktu.

Pengantar

Deret waktu adalah urutan titik data, yang masing-masing terdiri dari waktu dan nilai yang terkait dengan waktu tersebut. Biasanya, deret waktu juga memiliki ID, yang memberi nama deret waktu secara unik.

Dalam database relasional, deret waktu dimodelkan sebagai tabel dengan grup kolom berikut:

Kolom waktu
Mungkin memiliki kolom partisi, misalnya, kode pos
Satu atau beberapa kolom nilai, atau jenis STRUCT yang menggabungkan beberapa nilai, misalnya, suhu dan IKU

Berikut adalah contoh data deret waktu yang dimodelkan sebagai tabel:

Contoh tabel deret waktu.

Menggabungkan deret waktu

Dalam analisis deret waktu, agregasi waktu adalah agregasi yang dilakukan di sepanjang sumbu waktu.

Anda dapat melakukan agregasi waktu di BigQuery dengan bantuan fungsi pengelompokan waktu (TIMESTAMP_BUCKET, DATE_BUCKET, dan DATETIME_BUCKET). Fungsi pengelompokan waktu memetakan nilai waktu input ke bucket tempat nilai tersebut berada.

Biasanya, agregasi waktu dilakukan untuk menggabungkan beberapa titik data dalam rentang waktu menjadi satu titik data, menggunakan fungsi agregasi, seperti AVG, MIN, MAX, COUNT, atau SUM. Misalnya, latensi permintaan rata-rata 15 menit, suhu minimum dan maksimum harian, dan jumlah perjalanan taksi harian.

Untuk kueri di bagian ini, buat tabel bernama 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)
]);

Salah satu pengamatan menarik tentang data sebelumnya adalah bahwa pengukuran dilakukan pada periode waktu arbitrer, yang dikenal sebagai deret waktu yang tidak selaras. Agregasi adalah salah satu cara agar deret waktu dapat diselaraskan.

Mendapatkan rata-rata 3 jam

Kueri berikut menghitung indeks kualitas udara (AQI) dan suhu rata-rata 3 jam untuk setiap kode pos. Fungsi TIMESTAMP_BUCKET melakukan agregasi waktu dengan menetapkan setiap nilai waktu ke hari tertentu.

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

Mendapatkan nilai minimum dan maksimum 3 jam

Dalam kueri berikut, Anda menghitung suhu minimum dan maksimum 3 jam untuk setiap kode pos:

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

Mendapatkan rata-rata 3 jam dengan penyesuaian kustom

Saat melakukan agregasi deret waktu, Anda menggunakan perataan tertentu untuk jendela deret waktu, baik secara implisit maupun eksplisit. Kueri sebelumnya menggunakan perataan implisit, yang menghasilkan bucket yang dimulai pada waktu seperti 00:00:00, 03:00:00, dan 06:00:00. Untuk menyetel perataan ini secara eksplisit dalam fungsi TIMESTAMP_BUCKET, teruskan argumen opsional yang menentukan asal.

Dalam kueri berikut, asal ditetapkan sebagai 2020-01-01 02:00:00. Hal ini mengubah perataan dan menghasilkan bucket yang dimulai pada waktu seperti 02:00:00, 05:00:00, dan 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 |
 +---------------------+----------+-----+-------------*/

Menggabungkan deret waktu dengan pengisian kesenjangan

Terkadang, setelah Anda menggabungkan deret waktu, data mungkin memiliki kesenjangan yang perlu diisi dengan beberapa nilai untuk analisis atau presentasi data lebih lanjut. Teknik yang digunakan untuk mengisi kesenjangan tersebut disebut pengisian kesenjangan. Di BigQuery, Anda dapat menggunakan fungsi tabel GAP_FILL untuk mengisi kesenjangan dalam data deret waktu, menggunakan salah satu metode pengisian kesenjangan yang disediakan:

NULL, juga dikenal sebagai konstanta
LOCF, observasi terakhir yang dibawa ke depan
Linear, interpolasi linear antara dua titik data yang berdekatan

Untuk kueri di bagian ini, buat tabel bernama mydataset.environmental_data_hourly_with_gaps, yang didasarkan pada data yang digunakan di bagian sebelumnya, tetapi dengan celah di dalamnya. Dalam skenario dunia nyata, data mungkin memiliki titik data yang tidak ada karena malfungsi stasiun cuaca jangka pendek.

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)
]);

Mendapatkan rata-rata 3 jam (termasuk kesenjangan)

Kueri berikut menghitung AQI dan suhu rata-rata 3 jam untuk setiap kode pos:

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

Perhatikan bagaimana output memiliki kesenjangan pada interval waktu tertentu. Misalnya, deret waktu untuk kode pos 60606 tidak memiliki titik data pada 2020-09-08 12:00:00, dan deret waktu untuk kode pos 94105 tidak memiliki titik data pada 2020-09-08 15:00:00.

Mendapatkan rata-rata 3 jam (mengisi kesenjangan)

Gunakan kueri dari bagian sebelumnya dan tambahkan fungsi GAP_FILL untuk mengisi kesenjangan:

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

Tabel output kini berisi baris yang hilang di 2020-09-08 12:00:00 untuk kode pos 60606 dan di 2020-09-08 15:00:00 untuk kode pos 94105, dengan nilai NULL di kolom metrik yang sesuai. Karena Anda tidak menentukan metode pengisian celah, GAP_FILL menggunakan metode pengisian celah default, NULL.

Mengisi kesenjangan dengan pengisian kesenjangan linier dan LOCF

Dalam kueri berikut, fungsi GAP_FILL digunakan dengan metode pengisian kesenjangan LOCF untuk kolom aqi dan interpolasi linear untuk kolom 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 |
 +---------------------+----------+-----+-------------*/

Dalam kueri ini, baris pertama yang diisi memiliki nilai aqi 36, yang diambil dari titik data sebelumnya dari deret waktu ini (kode pos 60606) pada 2020-09-08 09:00:00. Nilai temperature 62 adalah hasil interpolasi linear antara titik data 2020-09-08 09:00:00 dan 2020-09-08 15:00:00. Baris yang hilang lainnya dibuat dengan cara yang serupa - nilai aqi 125 dipertahankan dari titik data sebelumnya dari deret waktu ini (kode pos 94105), dan nilai suhu 65 adalah hasil interpolasi linear antara titik data sebelumnya dan berikutnya yang tersedia.

Menyelaraskan deret waktu dengan pengisian kesenjangan

Deret waktu dapat diselaraskan atau tidak diselaraskan. Deret waktu diselaraskan saat titik data hanya terjadi pada interval reguler.

Di dunia nyata, pada saat pengumpulan, deret waktu jarang diselaraskan dan biasanya memerlukan pemrosesan lebih lanjut untuk menyelaraskannya.

Misalnya, pertimbangkan perangkat IoT yang mengirimkan metriknya ke pengumpul terpusat setiap menit. Tidaklah wajar mengharapkan perangkat mengirimkan metriknya pada waktu yang sama persis. Biasanya, setiap perangkat mengirimkan metriknya dengan frekuensi (periode) yang sama, tetapi dengan selisih waktu (penyelarasan) yang berbeda. Diagram berikut menggambarkan contoh ini. Anda dapat melihat setiap perangkat mengirimkan datanya dengan interval satu menit dengan beberapa instance data yang hilang (Perangkat 3 pada 9:36:39) dan data yang tertunda (Perangkat 1 pada 9:37:28).

Contoh perataan deret waktu

Anda dapat melakukan penyelarasan deret waktu pada data yang tidak selaras, menggunakan agregasi waktu. Hal ini berguna jika Anda ingin mengubah periode pengambilan sampel deret waktu, seperti mengubah dari periode pengambilan sampel 1 menit asli ke periode 15 menit. Anda dapat menyelaraskan data untuk pemrosesan deret waktu lebih lanjut, seperti menggabungkan data deret waktu, atau untuk tujuan tampilan (seperti pembuatan grafik).

Anda dapat menggunakan fungsi tabel GAP_FILL dengan metode pengisian kesenjangan linier atau LOCF untuk melakukan penyelarasan deret waktu. Idenya adalah menggunakan GAP_FILL dengan periode output dan perataan yang dipilih (dikontrol oleh argumen asal opsional). Hasil operasi adalah tabel dengan deret waktu yang diselaraskan, dengan nilai untuk setiap titik data berasal dari deret waktu input dengan metode pengisian kesenjangan yang digunakan untuk kolom nilai tertentu tersebut (LOCF atau linear).

Buat tabel mydataset.device_data, yang menyerupai ilustrasi sebelumnya:

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')
]);

Berikut adalah data sebenarnya yang diurutkan berdasarkan kolom time dan 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   |
 +-----------+---------------------+--------+----------*/

Tabel ini berisi deret waktu untuk setiap perangkat dengan dua kolom metrik:

signal - tingkat sinyal yang diamati oleh perangkat pada saat pengambilan sampel, direpresentasikan sebagai nilai bilangan bulat antara 0 dan 100.
state - status perangkat pada saat pengambilan sampel, yang ditampilkan sebagai string bentuk bebas.

Dalam kueri berikut, fungsi GAP_FILL digunakan untuk menyelaraskan deret waktu pada interval 1 menit. Perhatikan cara interpolasi linear digunakan untuk menghitung nilai untuk kolom signal dan LOCF untuk kolom state. Untuk data contoh ini, interpolasi linear adalah pilihan yang sesuai untuk menghitung nilai 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   |
 +---------------------+-----------+--------+----------*/

Tabel output berisi deret waktu yang selaras untuk setiap perangkat dan kolom nilai (signal dan state), yang dihitung menggunakan metode pengisian kesenjangan yang ditentukan dalam panggilan fungsi.

Menggabungkan data deret waktu

Anda dapat menggabungkan data deret waktu menggunakan gabungan berwindow atau gabungan AS OF.

Gabungan berjendela

Terkadang Anda perlu menggabungkan dua tabel atau lebih dengan data deret waktu. Pertimbangkan dua tabel berikut:

mydataset.sensor_temperatures, berisi data suhu yang dilaporkan oleh setiap sensor setiap 15 detik.
mydataset.sensor_fuel_rates, berisi laju konsumsi bahan bakar yang diukur oleh setiap sensor setiap 15 detik.

Untuk membuat tabel ini, jalankan kueri berikut:

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)
]);

Berikut adalah data sebenarnya dari tabel:

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

Untuk memeriksa tingkat konsumsi bahan bakar pada suhu yang dilaporkan oleh setiap sensor, Anda dapat menggabungkan kedua deret waktu.

Meskipun data dalam dua deret waktu tidak selaras, data tersebut diambil sampelnya pada interval yang sama (15 detik), sehingga data tersebut merupakan kandidat yang baik untuk gabungan berwindow. Gunakan fungsi pengelompokan waktu untuk menyelaraskan stempel waktu yang digunakan sebagai kunci gabungan.

Kueri berikut menggambarkan cara setiap stempel waktu dapat ditetapkan ke jendela 15 detik menggunakan fungsi TIMESTAMP_BUCKET:

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

Anda dapat menggunakan konsep ini untuk menggabungkan data laju konsumsi bahan bakar dengan suhu yang dilaporkan oleh setiap sensor:

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

`AS OF` bergabung

Untuk bagian ini, gunakan tabel mydataset.sensor_temperatures dan buat tabel baru, mydataset.sensor_location.

Tabel mydataset.sensor_temperatures berisi data suhu dari berbagai sensor, yang dilaporkan setiap 15 detik:

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

Untuk membuat mydataset.sensor_location, jalankan kueri berikut:

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

Sekarang gabungkan data dari mydataset.sensor_temperatures dengan data dari mydataset.sensor_location.

Dalam skenario ini, Anda tidak dapat menggunakan gabungan berwindow, karena data suhu dan data lokasi tidak dilaporkan pada interval yang sama.

Salah satu cara untuk melakukannya di BigQuery adalah dengan mengubah data stempel waktu menjadi rentang, menggunakan jenis data RANGE. Rentang ini merepresentasikan validitas temporal baris, yang memberikan waktu mulai dan berakhir validitas baris.

Gunakan fungsi jendela LEAD untuk menemukan titik data berikutnya dalam deret waktu, relatif terhadap titik data saat ini, yang juga merupakan batas akhir validitas temporal baris saat ini. Kueri berikut menunjukkan hal ini, dengan mengonversi data lokasi menjadi rentang validitas:

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

Sekarang Anda dapat menggabungkan data suhu (kiri) dengan data lokasi (kanan):

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

Menggabungkan dan memisahkan data rentang

Di bagian ini, gabungkan data rentang yang memiliki rentang yang tumpang-tindih dan bagi data rentang menjadi rentang yang lebih kecil.

Menggabungkan data rentang

Tabel dengan nilai rentang mungkin memiliki rentang yang tumpang-tindih. Dalam kueri berikut, rentang waktu mencatat status sensor dengan interval sekitar 5 menit:

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)
]);

Kueri berikut pada tabel menunjukkan beberapa rentang yang tumpang-tindih:

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

Untuk beberapa rentang yang tumpang-tindih, nilai di kolom flow sama. Misalnya, baris 1 dan 2 tumpang-tindih, dan juga memiliki pembacaan flow yang sama. Anda dapat menggabungkan kedua baris ini untuk mengurangi jumlah baris dalam tabel. Anda dapat menggunakan fungsi tabel RANGE_SESSIONIZE untuk menemukan rentang yang tumpang-tindih dengan setiap baris, dan memberikan kolom session_range tambahan yang berisi rentang yang merupakan gabungan dari semua rentang yang tumpang-tindih. Untuk menampilkan rentang sesi untuk setiap baris, jalankan kueri berikut:

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

Perhatikan bahwa untuk sensor_id yang memiliki nilai 2, batas akhir baris pertama memiliki nilai tanggal dan waktu yang sama dengan batas awal baris kedua. Namun, karena batas akhir bersifat eksklusif, batas tersebut tidak tumpang-tindih (hanya bertemu) dan oleh karena itu tidak berada dalam rentang sesi yang sama. Jika Anda ingin menempatkan kedua baris ini dalam rentang sesi yang sama, gunakan mode penetapan sesi MEETS.

Untuk menggabungkan rentang, kelompokkan hasil menurut session_range dan kolom partisi (sensor_id dan 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   |
 +-----------+--------------------------------------------+------*/

Terakhir, tambahkan kolom spins dalam data sesi dengan menggabungkannya menggunakan 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 |
 +-----------+--------------------------------------------+------+-------*/

Membagi data rentang

Anda juga dapat membagi rentang menjadi rentang yang lebih kecil. Untuk contoh ini, gunakan tabel berikut dengan data rentang:

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

Sekarang, bagi rentang asli menjadi interval 3 bulan:

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

Dalam kueri sebelumnya, setiap rentang asli dipecah menjadi rentang yang lebih kecil, dengan lebar yang ditetapkan ke INTERVAL 3 MONTH. Namun, rentang 3 bulan tidak selaras dengan asal yang sama. Untuk menyelaraskan rentang ini dengan asal yang sama 2020-01-01, jalankan kueri berikut:

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

Dalam kueri sebelumnya, baris dengan rentang [2020-04-15, 2021-04-15) dibagi menjadi 5 rentang, dimulai dengan rentang [2020-04-01, 2020-07-01). Perhatikan bahwa batas awal kini melampaui batas awal asli, agar sejajar dengan asal umum. Jika Anda tidak ingin batas awal tidak melampaui batas awal asli, Anda dapat membatasi kondisi 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 |
 +-----------+--------------------------+------+-------*/

Sekarang Anda akan melihat bahwa rentang [2020-04-15, 2021-04-15) dibagi menjadi 4 rentang, dimulai dengan rentang [2020-07-01, 2020-10-01).

Praktik terbaik untuk menyimpan data

Saat menyimpan data deret waktu, penting untuk mempertimbangkan pola kueri yang digunakan terhadap tabel tempat data disimpan. Biasanya, saat mengirimkan kueri data deret waktu, Anda dapat memfilter data untuk rentang waktu tertentu.
Untuk mengoptimalkan pola penggunaan ini, sebaiknya simpan data deret waktu dalam tabel berpartisi, dengan data yang dipartisi berdasarkan kolom waktu atau waktu penyerapan. Hal ini dapat meningkatkan performa waktu kueri data deret waktu secara signifikan, karena memungkinkan BigQuery memangkas partisi yang tidak berisi data yang dikueri.
Anda dapat mengaktifkan pengelompokan pada waktu, rentang, atau salah satu kolom partisi untuk peningkatan performa waktu kueri lebih lanjut.