如何提取BigQuery中两个时间戳之间的独特日期？

问题描述

对于两个不同的时间戳，比如说timestamp（'2015-02-01'）和时间戳（'2015-02-12'），我想要一个包含所有日期的列。像这样（12行）
2015-02-01
2015-02-02
。
。
。
2015-02-12

解决方案

您可以在公共数据集上使用交叉连接（ fh-bigquery：geocode.numbers_65536 ）其中您的数字最大为：65536

  SELECT date（DATE_ADD（DAY，i，DAY））DAY 
 FROM 
（SELECT'2015-01-01'AS DAY）CROSS 
 JOIN 
（SELECT i 
 FROM [fh-bigquery：geocode.numbers_65536] 
 WHERE i< = abs（DATEDIFF（'2015-01-01'，'2015-01-15'）））b 
 ORDER BY DAY ASC 
  

输出：

  + ----- + ------------ + --- + 
 |行|一天| | 
 + ----- + ------------ + --- + 
 | 1 | 2015-01-01 | | 
 | 2 | 2015-01-02 | | 
 | 3 | 2015-01-03 | | 
 | 4 | 2015-01-04 | | 
 | 5 | 2015-01-05 | | 
 | 6 | 2015-01-06 | | 
 | 7 | 2015-01-07 | | 
 | 8 | 2015-01-08 | | 
 | 9 | 2015-01-09 | | 
 | 10 | 2015-01-10 | | 
 | 11 | 2015-01-11 | | 
 | 12 | 2015-01-12 | | 
 | 13 | 2015-01-13 | | 
 | 14 | 2015-01-14 | | 
 | 15 | 2015-01-15 | | 
 + ----- + ------------ + --- + 
  

您可以使用项目菜单添加项目 fh-bigquery ，将这些数据添加到BigQuery UI中的视图中项目名称，切换到项目➪显示项目）。或者，您可以导航到BigQuery UI链接
添加项目后，示例数据集（fh-bigquery）将出现在导航面板中。

for two different timestamps, let's say timestamp('2015-02-01') and timestamp ('2015-02-12'), I want a column with all the dates in between. Like this (12 Rows)2015-02-012015-02-02...2015-02-12

解决方案

You can do that with a cross join on a public dataset (fh-bigquery:geocode.numbers_65536) where you have numbers up to: 65536

SELECT date(DATE_ADD(DAY, i, "DAY")) DAY
FROM
  (SELECT '2015-01-01' AS DAY) a CROSS
JOIN
  (SELECT i
   FROM [fh-bigquery:geocode.numbers_65536]
   WHERE i<=abs(DATEDIFF('2015-01-01','2015-01-15'))) b
ORDER BY DAY ASC

this outputs:

+-----+------------+---+
| Row |    day     |   |
+-----+------------+---+
|   1 | 2015-01-01 |   |
|   2 | 2015-01-02 |   |
|   3 | 2015-01-03 |   |
|   4 | 2015-01-04 |   |
|   5 | 2015-01-05 |   |
|   6 | 2015-01-06 |   |
|   7 | 2015-01-07 |   |
|   8 | 2015-01-08 |   |
|   9 | 2015-01-09 |   |
|  10 | 2015-01-10 |   |
|  11 | 2015-01-11 |   |
|  12 | 2015-01-12 |   |
|  13 | 2015-01-13 |   |
|  14 | 2015-01-14 |   |
|  15 | 2015-01-15 |   |
+-----+------------+---+

You can add this data to your view in the BigQuery UI by adding the project fh-bigquery using the project menu (the drop-down next to the project name, Switch to Project ➪ Display Project). Alternately, you can navigate to the BigQuery UI link https://bigquery.cloud.google.com/project/fh-bigqueryAfter you add the project, the sample dataset (fh-bigquery) appears in the navigation panel.

这篇关于如何提取BigQuery中两个时间戳之间的独特日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！