BigQuery和标准SQL：如何按任意一天的时间间隔

问题描述：

我是一个BigQuery和SQL新手，继续处理分组问题。在BigQuery中使用标准SQL，我想按X天分组数据。这里的数据表：BigQuery和标准SQL：如何按任意一天的时间间隔

event_id | url |   timestamp 
----------------------------------------------------------- 
    xx   a.html  2016-10-18 15:55:16 UTC 
    xx   a.html  2016-10-19 16:68:55 UTC 
    xx   a.html  2016-10-25 20:55:57 UTC 
    yy   b.html  2016-10-18 15:58:09 UTC 
    yy   b.html  2016-10-18 08:32:43 UTC 
    zz   a.html  2016-10-20 04:44:22 UTC 
    zz   c.html  2016-10-21 02:12:34 UTC

我想数着每个URL发生在X天的间隔，从给定日起的每个事件的数量。例如：我如何在3天的时间间隔内对其进行分组，我的第一个时间间隔是从2016-10-18 00:00:00 UTC开始的？另外，我可以将间隔的第3天分配给每一行吗？示例输出：

event_id | url | count |  3dayIntervalLabel 
----------------------------------------------------------- 
    xx   a.html  2   2016-10-20 --> [18th thru 20th] 
    yy   b.html  2   2016-10-20 
    zz   a.html  1   2016-10-20 
    zz   c.html  1   2016-10-23 --> [21th thru 23th] 
    xx   a.html  1   2016-10-26 --> [24th thru 26th]

我添加了三个注释来阐明3dayIntervalLabel值。

一般来说，我希望能够解决：从X日开始，从Y日开始，并使用每个间隔的最后日期标记间隔。

如果需要更多说明，请让我知道。

如果你有兴趣，我也问过关于使用滚动窗口对这些数据进行分组的StackOverflow（和已得到的答案）的类似问题：initial question和follow-up。

谢谢！

不是一个完整的答案，但你可以利用['GENERATE_DATE_ARRAY'（HTTPS的：//云.google.com/bigquery/docs/reference/standard-sql/functions-and-operators＃generate_date_array）如果您需要加入的日期序列。 –

我接受的答案实际上是使用GENERATE_DATE_ARRAY（）和join！ –

答

WITH dailyAggregations AS (
    SELECT 
    DATE(ts) AS day, 
    url, 
    event_id, 
    UNIX_SECONDS(TIMESTAMP(DATE(ts))) AS sec, 
    COUNT(1) AS events 
    FROM yourTable 
    GROUP BY day, url, event_id, sec 
), 
calendar AS (
    SELECT day, DATE_ADD(day, INTERVAL 2 DAY) AS endday 
    FROM UNNEST (GENERATE_DATE_ARRAY('2016-10-18', '2016-11-06', INTERVAL 3 DAY)) AS day 
) 
SELECT 
    event_id, 
    url, 
    SUM(events) AS `count`, 
    c.endday AS `ThreedayIntervalLabel` 
FROM calendar AS c 
JOIN dailyAggregations AS a 
ON a.day BETWEEN c.day AND c.endday 
GROUP BY endday, url, event_id

这看起来很不错，但我在测试时遇到了麻烦。我认为这与查询无关。我试过运行查询20次以上，只完成一次。所有其他时间，我得到了“tableUnavailable”错误。一旦我弄清楚了，我会验证查询是否有效。 –

BigQuery方面正在发生某些事情。人数看到同样的错误！看到https://status.cloud.google.com/ - 现在是用红色标出 –

查询工作，看来，我最初得到了相关的BigQuery停运“tableUnavailable”的错误。此外，dailyAggregations查询中的“UNIX_SECONDS（TIMESTAMP（DATE（ts）））AS sec”行不需要，也不需要在此情况下使用。 –

答

如果你有一个基准日，则是这样的：

select floor(date_diff(date(timestamp), date '2016-10-18', day)/3) as days, 
     count(*) 
from t 
group by days 
order by days;

使用BIgQuery的标准SQL语言，datediff（）未定义。有一个名为DATE_DIFF（）的函数接受3个参数。不知道这是否可以重做。 –

@TheTravelingCoder。。。我正在查看文档的错误版本。 –

BigQuery和标准SQL：如何按任意一天的时间间隔

相关推荐