일반적으로 BigQuery는 쿼리를 실행할 때 열 전체를 검색합니다.
legacy SQL에서 테이블 데코레이터를 사용하면 데이터의 하위 집합에 대한 쿼리를 더욱 경제적으로 실행할 수 있습니다. 테이블 복사, 테이블 내보내기 또는 tabledata.list를 사용한 데이터 나열 작업에서처럼 테이블을 읽을 때마다 테이블 데코레이터를 사용할 수 있습니다.
테이블 데코레이터는 <time>의 상댓값과 절댓값을 사용합니다. 상댓값은 음수로, 절댓값은 양수로 표시됩니다. 예를 들어 -3600000은 현재 시간 기준 1시간 전을 밀리초 단위로 표시한 것이며, 3600000은 1970년 1월 1일 이후의 1시간을 밀리초 단위로 표시한 것입니다.
시간 데코레이터
시간 데코레이터(이전에는 스냅샷 데코레이터)는 특정 시점의 테이블 이전 데이터를 참조합니다.
구문
@<time>
에포크 이후의 <time>(밀리초)에서 테이블의 이전 데이터를 참조합니다.
<time>은 지난 7일 이내여야 하며 테이블 생성 시간보다 길거나 같아야 하지만, 테이블 삭제 또는 만료 시간보다 작아야 합니다.
@0은 테이블에 사용할 수 있는 가장 오래된 데이터를 참조하는 특수한 사례입니다.
시간 데코레이터는 legacy SQL 외부에서도 사용됩니다. bq cp 명령어에서 테이블을 삭제한 후 7일 이내에 삭제된 테이블을 복원하기 위해 사용할 수 있습니다.
예시
1시간 전의 테이블 이전 데이터를 가져오려면 다음 안내를 따르세요.
상댓값 예시
#legacySQL
SELECT COUNT(*) FROM [PROJECT_ID:DATASET.TABLE@-3600000]
[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-06-25(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]"]]
