我在 BigQuery 中有這樣的表：
| ID | 開始時間戳 | 結束時間戳 |
|---|---|---|
| 1 | 2021-02-05 21:45:57 UTC | 2021-02-05 21:48:17 UTC |
| 2 | 2021-02-05 23:12:01 UTC | 2021-02-05 23:13:42 UTC |
我需要為 start_timestamp 和 end_timestamp 之間的時間范圍內的每一分鐘創建行。
預期結果：
| ID | 開始時間戳 | 結束時間戳 |
|---|---|---|
| 1 | 2021-02-05 21:45:00 UTC | 2021-02-05 21:46:00 UTC |
| 1 | 2021-02-05 21:46:00 UTC | 2021-02-05 21:47:00 UTC |
| 1 | 2021-02-05 21:47:00 UTC | 2021-02-05 21:48:00 UTC |
| 1 | 2021-02-05 21:48:00 UTC | 2021-02-05 21:49:00 UTC |
| 2 | 2021-02-05 23:12:00 UTC | 2021-02-05 23:13:00 UTC |
| 2 | 2021-02-05 23:13:00 UTC | 2021-02-05 23:14:00 UTC |
如何使用 BigQuery 執行此操作？
示例輸入資料：
WITH
example_table AS (
SELECT
1 AS id,
TIMESTAMP('2021-02-05 21:45:57') AS start_timestamp,
TIMESTAMP('2021-02-05 21:48:17') AS end_timestamp
UNION ALL
SELECT
2 AS id,
TIMESTAMP('2021-02-05 23:12:01') AS start_timestamp,
TIMESTAMP('2021-02-05 23:13:42') AS end_timestamp)
SELECT * FROM example_table;
謝謝你的幫助。
uj5u.com熱心網友回復：
考慮以下方法
select id,
ts as start_timestamp,
ts interval 1 minute as end_timestamp
from example_table,
unnest(generate_timestamp_array(
timestamp_trunc(start_timestamp, minute),
timestamp_trunc(end_timestamp, minute),
interval 1 minute
) ) ts
如果應用于您問題中的樣本資料 - 輸出是
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/325504.html
標籤：sql 约会时间 谷歌-bigquery