在 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 |+------------+

最理想的情况是，下一步也是获取两个日期之间的所有周数，即:

+---------+|一周 |+---------+|2015-23 ||2015-24 ||2015-25 ||... ||2016-28 |+---------+

我一直在摆弄我找到的以下答案，但我无法让它们工作，主要是因为不支持核心功能，而且我找不到合适的方法来替换它们.

使用介于 2 个日期参数之间(包括 2 个日期参数)的日期填充临时表的最简单方法

在日期范围之间生成日期

非常感谢您的帮助！

最好的，最大

解决方案

所有日期从 2015-06-01 到 CURRENT_DATE()

SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS DAY从 (SELECT ROW_NUMBER() OVER() AS pos, *来自(扁平化((SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS hFROM (SELECT NULL)),h)))

<块引用>

两个日期之间的所有周

SELECT YEAR(DAY) AS y, WEEK(DAY) AS w从 (SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS DAY从 (SELECT ROW_NUMBER() OVER() AS pos, *来自(扁平化((SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS hFROM (SELECT NULL)),h))))按 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屋！