在BigQuery中多次将时间戳数据相乘 [英] Multiplying a timestamp data for several times in BigQuery

问题描述

我的时间序列从2017年1月1日00:00:00到2017年12月31日23:00:00结束，间隔时间为1小时.我需要在同一列中将此1年时间戳记重复2400次.我需要有关这方面的帮助.

I have a time-series starting from 2017-01-01 00:00:00 to the end of 2017-12-31 23:00:00 for 1-hour interval. I need to duplicate this 1-year timestamp for 2400 times in the same column. I need help about this one..

Row  Date_time  
1   2017-01-01 00:00:00 UTC
2   2017-01-01 01:00:00 UTC
3   2017-01-01 02:00:00 UTC
4   2017-01-01 03:00:00 UTC
5   2017-01-01 04:00:00 UTC
6   2017-01-01 05:00:00 UTC
7   2017-01-01 06:00:00 UTC
8   2017-01-01 07:00:00 UTC
...........................
...........................

推荐答案

您将在BigQuery中通过生成时间戳数组然后取消嵌套来做到这一点:

You would do this in BigQuery by generating a timestamp array and then unnesting:

 select ts
 from unnest(generate_timestamp_array('2017-01-01 00:00:00', '2017-12-31 23:00:00', interval 1 hour)) ts

然后您可以使用相似的结构获得多行:

You can then get multiple rows with a similar construct:

 select ts
 from unnest(generate_timestamp_array('2017-01-01 00:00:00', '2017-12-31 23:00:00', interval 1 hour)
            ) ts cross join
      unnest(generate_series(1, 2400)) n

这篇关于在BigQuery中多次将时间戳数据相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！