使用Google BigQuery中的给定范围内的所有日期填充表格 [英] Populating a table with all dates in a given range in Google BigQuery
问题描述
是否有任何方便的方法在Google BigQuery中填入给定范围内的所有日期的表格?我需要的是从2015-06-01到CURRENT_DATE()的所有日期,所以像这样:
+ --- --------- +
|日期|
+ ------------ +
| 2015-06-01 |
| 2015-06-02 |
| 2015-06-03 |
| ... |
| 2016-07-11 |
+ ------------ +
最佳地,下一步是在两个日期之间的所有星期,即:
$ b
+ --------- +
|周|
+ --------- +
| 2015-23 |
| 2015-24 |
| 2015-25 |
| ... |
| 2016-28 |
+ --------- +
我一直在摆弄我找到了下面的答案,但我无法让他们工作,主要是因为核心功能不受支持,我找不到合适的方法来替换他们。
您的帮助非常感谢!
Best,
Max
2015-06-01 until CURRENT_DATE()
SELECT DATE(DATE_ADD(TIMESTAMP -06-01),pos - 1,DAY))AS DAY
FROM(
SELECT ROW_NUMBER()OV ER()AS pos,*
FROM(FLATTEN((
SELECT SPLIT(RPAD('',1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()),TIMESTAMP(2015-06-01)) ,'。'),'')AS h
FROM(SELECT NULL)),h
)))
两个日期之间的所有星期
SELECT YEAR(DAY)AS y,WEEK(DAY)as w
FROM(
SELECT DATE(DATE_ADD(TIMESTAMP(2015-06-01),pos - 1,DAY ))AS DAY
FROM(
SELECT ROW_NUMBER()OVER()as pos,*
FROM(FLATTEN((
SELECT SPLIT(RPAD('',1 + DATEDIFF (TIMESTAMP(CURRENT_DATE()),TIMESTAMP(2015-06-01)),'。'),'')AS h
FROM(SELECT NULL)),h
)))
)
GROUP BY y,w
Is there any convenient way to populate a table with all dates in a given range in Google BigQuery? What I need are all dates from 2015-06-01 till CURRENT_DATE(), so something like this:
+------------+
| date |
+------------+
| 2015-06-01 |
| 2015-06-02 |
| 2015-06-03 |
| ... |
| 2016-07-11 |
+------------+
Optimally, the next step would be to also get all weeks between the two dates, i.e.:
+---------+
| week |
+---------+
| 2015-23 |
| 2015-24 |
| 2015-25 |
| ... |
| 2016-28 |
+---------+
I've been fiddling around with the following answers I found, but I can't get them to work, mostly because core functions aren't supported and I can't find proper ways to replace them.
Easiest way to populate a temp table with dates between and including 2 date parameters
Generate Dates between date ranges
Your help is very much appreciated!
Best, Max
all dates from 2015-06-01 till CURRENT_DATE()
SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS DAY
FROM (
SELECT ROW_NUMBER() OVER() AS pos, *
FROM (FLATTEN((
SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
FROM (SELECT NULL)),h
)))
all weeks between the two dates
SELECT YEAR(DAY) AS y, WEEK(DAY) AS w
FROM (
SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS DAY
FROM (
SELECT ROW_NUMBER() OVER() AS pos, *
FROM (FLATTEN((
SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
FROM (SELECT NULL)),h
)))
)
GROUP BY y, w
这篇关于使用Google BigQuery中的给定范围内的所有日期填充表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!