在SQL中对列中的时间戳行值进行迭代并执行计数

Question

现在我有一个名为dates的视图，看起来像这样：

|first_day|last_day|
|---------|--------|
|2020-08-17T00:00:00+00:00|2020-12-04T00:00:00+00:00|
|2020-11-23T00:00:00+00:00|2020-12-07T00:00:00+00:00|
|2020-09-14T00:00:00+00:00|2020-12-04T00:00:00+00:00|
|2020-09-14T00:00:00+00:00|2020-12-04T00:00:00+00:00|
|2020-09-14T00:00:00+00:00|2020-12-04T00:00:00+00:00|

大约300行。。。

我创建了另一个视图，用这个SQL查询生成了一行日期，从2020年6月1日到今天(无论那天是什么(：

SELECT GENERATE_TIMESTAMP_ARRAY('2020-06-01', CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AS date

它生成这样的表(我们称之为dateseries(：

|date|
|----|
|2020-06-01 00:00:00 UTC|
|2020-06-02 00:00:00 UTC|
|2020-06-03 00:00:00 UTC|
|2020-06-04 00:00:00 UTC|

从2020年6月1日开始，直到当前时间戳，间隔1天，直到当前日期。

现在我想做的是迭代dateseries中的每一行值，并在dates视图中检查该时间戳值(来自dateseries(是否大于first_day列and小于last_day列。如果是，则计数1。所以它几乎是两个循环。第一个循环迭代dateseries中的每个值，然后将其与dates视图中的每一行进行比较，比较两列first_day和last_day。我想另一种思考方式是，如果dateseries的值在dates视图中的first_day和last_day列之间，那么计算1，否则为0。

最后，我想要一个这样的表(日期列与June1的时间序列相同->current_timestamp(((：

|date|count|
|----|-----|
|2020-06-01 00:00:00 UTC|32|
|2020-06-02 00:00:00 UTC|31|
|2020-06-03 00:00:00 UTC|22|
|2020-06-04 00:00:00 UTC|5|
|2020-06-05 00:00:00 UTC|16|

等等。

在BigQuery SQL中我该如何做到这一点？

编辑：不确定为什么表语法不起作用。。。

Answer 1

下面是BigQuery标准SQL

#standardSQL
with dateseries as (
select date
from unnest(GENERATE_TIMESTAMP_ARRAY('2020-06-01', CURRENT_TIMESTAMP(), INTERVAL 1 DAY)) AS date
)
select date, count(1) `count` 
from `project.dataset.dates`
join dateseries
on date between first_day and last_day
group by date

注：假设project.dataset.dates中的first_day和last_day列为时间戳数据类型。否则(如果它们是字符串(-使用线下

on date between timestamp(first_day) and timestamp(last_day)

Answer 2

您应该能够JOINdate上的两个表都在first_day和last_day之间，并计数行数：

SELECT ds.date, COUNT(d.first_day) AS count
FROM dateseries ds
JOIN dates d ON ds.date BETWEEN d.first_day AND d.last_day
GROUP BY ds.date
ORDER BY ds.date

请注意，你想要的日期结束条件并不是100%清楚；你可能想使用

JOIN dates d ON ds.date > d.first_day AND ds.date < d.last_day