Tetap teratur dengan koleksi
Simpan dan kategorikan konten berdasarkan preferensi Anda.
Dekorator tabel di Legacy SQL
Biasanya, BigQuery melakukan pemindaian kolom penuh saat menjalankan kueri.
Anda dapat menggunakan dekorator tabel di legacy SQL untuk melakukan kueri sebagian data
yang lebih hemat biaya. Dekorator tabel dapat digunakan setiap kali tabel dibaca,
seperti saat menyalin tabel,
mengekspor tabel,
atau mencantumkan data menggunakan tabledata.list.
Dekorator tabel mendukung nilai <time> yang relatif dan absolut. Nilai
relatif ditunjukkan dengan bilangan negatif, dan nilai
absolut ditunjukkan dengan bilangan positif. Misalnya, -3600000 menunjukkan satu
jam yang lalu dalam milidetik, relatif terhadap waktu saat ini; 3600000
menunjukkan satu jam dalam milidetik setelah 1/1/1970.
Dekorator waktu
Dekorator waktu (sebelumnya dikenal sebagai dekorator snapshot) mereferensikan data historis tabel pada suatu waktu.
Sintaksis
@<time>
Mereferensikan data historis tabel pada <time>,
dalam milidetik sejak epoch.
<time> harus berada dalam waktu tujuh hari terakhir dan lebih lama dari atau sama dengan waktu pembuatan tabel, tetapi kurang dari waktu penghapusan atau habis masa berlaku tabel.
@0 adalah kasus khusus yang mereferensikan data terlama yang tersedia untuk
tabel.
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-08-17 UTC."],[[["\u003cp\u003eTable decorators in legacy SQL allow for cost-effective querying of data subsets, as opposed to full column scans, and can be used when copying, exporting, or listing table data.\u003c/p\u003e\n"],["\u003cp\u003eTime decorators, indicated by \u003ccode\u003e@<time>\u003c/code\u003e, reference a table's historical data at a specific point in time, measured in milliseconds since the epoch and within the last seven days.\u003c/p\u003e\n"],["\u003cp\u003eRange decorators, using the \u003ccode\u003e@<time1>-<time2>\u003c/code\u003e syntax, specify a range of time to target data added between the two times, with the second time defaulting to "now" if not specified.\u003c/p\u003e\n"],["\u003cp\u003eTime decorators can also be used in the \u003ccode\u003ebq cp\u003c/code\u003e command to restore tables deleted within seven days, expanding beyond their usage just within SQL queries.\u003c/p\u003e\n"],["\u003cp\u003eGoogleSQL's \u003ccode\u003eFOR SYSTEM_TIME AS OF\u003c/code\u003e clause and time-partitioned tables offer comparable functionality to time and range decorators, respectively, making table decorators not available in GoogleSQL.\u003c/p\u003e\n"]]],[],null,["# Table decorators in legacy SQL\n==============================\n\n| **Caution:** This document describes table decorators in legacy SQL query syntax. The preferred query syntax for BigQuery is GoogleSQL. Standard SQL does not support table decorators, but the [`FOR SYSTEM_TIME AS OF`](/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of) clause in GoogleSQL provides functionality equivalent to time decorators. For range decorators, you can achieve similar semantics in GoogleSQL by using time-partitioned tables. For more information, see [Table decorators](/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#table_decorators) in the GoogleSQL migration guide.\n\nNormally, BigQuery performs a full column scan when\n[running a query](https://cloud.google.com/bigquery#querying-data).\nYou can use table decorators in legacy SQL to perform a more cost-effective query of a\nsubset of your data. Table decorators can be used whenever a table is read,\nsuch as when copying a table,\n[exporting a table](https://cloud.google.com/bigquery/exporting-data-from-bigquery),\nor listing data using `tabledata.list`.\n| **Note:** Range decorators aren't supported in GoogleSQL. To view the status of this feature request, see the [BigQuery feature request tracker](https://issuetracker.google.com/issues/35905931). You can click the **Vote for this issue and get email notifications** icon (the star) to register your support for the feature.\n\nTable decorators support relative and absolute `\u003ctime\u003e` values. Relative\nvalues are indicated by a negative number, and absolute\nvalues are indicated by a positive number. For example, `-3600000` indicates one\nhour ago in milliseconds, relative to the current time; `3600000`\nindicates one hour in milliseconds after 1/1/1970.\n\nTime decorators\n---------------\n\nTime decorators (formerly known as *snapshot decorators*) reference a table's\nhistorical data at a point in time.\n\n### Syntax\n\n```\n@\u003ctime\u003e\n```\n\n- References a table's historical data at *\u003ctime\u003e*, in milliseconds since the epoch.\n- *\u003ctime\u003e* must be within the last seven days and greater than or equal to the table's creation time, but less than the table's deletion or expiration time.\n- `@0` is a special case that references the oldest data available for the table.\n\nTime decorators are also used outside of legacy SQL. You can use them in the\n[`bq cp` command](/bigquery/docs/reference/bq-cli-reference#bq_cp) to\n[restore deleted tables](/bigquery/docs/restore-deleted-tables)\nwithin seven days of table deletion.\n\n### Examples\n\nTo get the historical data for a table at one hour ago:\n\n**Relative value example** \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000]\n\n**Absolute value example**\n\n1. Get *\u003ctime\u003e* for one hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)\n\n2. Then, replace *\u003ctime\u003e* in the following query:\n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time]\n\nRange decorators\n----------------\n\n### Syntax\n\n```\n@\u003ctime1\u003e-\u003ctime2\u003e\n```\n\n- References table data added between *\u003ctime1\u003e* and *\u003ctime2\u003e*, in milliseconds since the epoch.\n- *\u003ctime1\u003e* and *\u003ctime2\u003e* must be within the last seven days.\n- *\u003ctime2\u003e* is optional and defaults to 'now'.\n\n### Examples\n\n**Relative value examples**\n\nTo get table data added between one hour and half an hour ago: \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000--1800000]\n\nTo get data from the last 10 minutes: \n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-600000-]\n\n**Absolute value example**\n\nTo get table data added between one hour and half an hour ago:\n\n1. Get *\u003ctime1\u003e* for one hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -1, 'HOUR')/1000)\n\n2. Get *\u003ctime2\u003e* for a half hour ago:\n\n #legacySQL\n SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -30, 'MINUTE')/1000)\n\n3. Replace *\u003ctime1\u003e* and\n *\u003ctime2\u003e* in the following query:\n\n #legacySQL\n SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@time1-time2]"]]
