💻 基本的な使い方
1. 相対時間指定（ミリ秒）
SELECT *
FROM `project.dataset.sales@-3600000`
WHERE region = 'APAC'
SELECT *
FROM `project.dataset.sales@-86400000`
SELECT *
FROM `project.dataset.sales@-1800000`
2. 絶対時刻指定
SELECT *
FROM `project.dataset.sales@'2024-12-05 10:30:00'`
WHERE product_id = 12345
SELECT *
FROM `project.dataset.sales@'2024-12-04'`
3. Unixエポック（ミリ秒）
SELECT *
FROM `project.dataset.sales@1733400000000`
DECLARE snapshot_time INT64;
SET snapshot_time = UNIX_MILLIS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR));
SELECT *
FROM `project.dataset.sales`
FOR SYSTEM_TIME AS OF TIMESTAMP_MILLIS(snapshot_time)
4. FOR SYSTEM_TIME AS OF 構文（SQL標準）
SELECT *
FROM `project.dataset.sales`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
WHERE region = 'APAC'
SELECT *
FROM `project.dataset.sales`
FOR SYSTEM_TIME AS OF TIMESTAMP('2024-12-05 10:30:00')
🔍 実用的なクエリ例
1. 誤って削除したデータの復元
SELECT *
FROM `project.dataset.customers@-3600000`
WHERE customer_id = 'CUST-12345'
INSERT INTO `project.dataset.customers`
SELECT *
FROM `project.dataset.customers@-3600000`
WHERE customer_id = 'CUST-12345'
AND NOT EXISTS (
SELECT 1 FROM `project.dataset.customers` current
WHERE current.customer_id = 'CUST-12345'
)
2. データ変更の差分確認
WITH current_data AS (
SELECT product_id, price, stock
FROM `project.dataset.products`
),
past_data AS (
SELECT product_id, price, stock
FROM `project.dataset.products@-3600000`
)
SELECT
c.product_id,
p.price AS old_price,
c.price AS new_price,
c.price - p.price AS price_change,
p.stock AS old_stock,
c.stock AS new_stock,
c.stock - p.stock AS stock_change
FROM current_data c
LEFT JOIN past_data p USING (product_id)
WHERE
c.price != p.price
OR c.stock != p.stock
ORDER BY ABS(c.price - p.price) DESC
3. 削除されたレコードの特定
SELECT
old.*,
'DELETED' AS status
FROM `project.dataset.orders@-86400000` old
LEFT JOIN `project.dataset.orders` current
ON old.order_id = current.order_id
WHERE current.order_id IS NULL
4. 新規追加されたレコードの特定
SELECT
current.*,
'NEW' AS status
FROM `project.dataset.orders` current
LEFT JOIN `project.dataset.orders@-3600000` old
ON current.order_id = old.order_id
WHERE old.order_id IS NULL
5. データ監査（Audit Log）
WITH snapshots AS (
SELECT 'T-0' AS snapshot_time, * FROM `project.dataset.inventory`
UNION ALL
SELECT 'T-1h', * FROM `project.dataset.inventory@-3600000`
UNION ALL
SELECT 'T-2h', * FROM `project.dataset.inventory@-7200000`
UNION ALL
SELECT 'T-4h', * FROM `project.dataset.inventory@-14400000`
)
SELECT
snapshot_time,
product_id,
quantity,
LAG(quantity) OVER (PARTITION BY product_id ORDER BY snapshot_time) AS previous_quantity,
quantity - LAG(quantity) OVER (PARTITION BY product_id ORDER BY snapshot_time) AS change
FROM snapshots
WHERE product_id = 'PROD-123'
ORDER BY snapshot_time, product_id
6. 集計値の時系列比較
WITH hourly_snapshots AS (
SELECT
0 AS hours_ago,
SUM(amount) AS total_sales
FROM `project.dataset.sales`
UNION ALL
SELECT 4, SUM(amount)
FROM `project.dataset.sales@-14400000`
UNION ALL
SELECT 8, SUM(amount)
FROM `project.dataset.sales@-28800000`
UNION ALL
SELECT 12, SUM(amount)
FROM `project.dataset.sales@-43200000`
UNION ALL
SELECT 24, SUM(amount)
FROM `project.dataset.sales@-86400000`
)
SELECT
hours_ago,
total_sales,
total_sales - LAG(total_sales) OVER (ORDER BY hours_ago DESC) AS growth
FROM hourly_snapshots
ORDER BY hours_ago