无法从BigQuery中的时间戳中提取日期

Question

我正在尝试获取与特定实体ID相关联的最近一周内的所有行。每行都有一个时间戳。我想通过从日期时间戳中提取日期来按天分组这些实体，但是当我尝试时，我会得到以下错误信息：

错误：（L6:28）：在GROUP BY中使用表达式STRFTIME_UTC_USEC([data_timestamp]，'%Y-%m-%d')无效。

来自bigquery。看起来DATE()函数出了问题。

这是我运行的完整查询：

SELECT Count(*) FROM [myproj:mydataset.mytable] 
WHERE 
    bool_property=False 
AND 
    entity_id=5667423172689920 
AND
    DATEDIFF(CURRENT_DATE(), data_timestamp) <= 7
GROUP BY DATE(data_timestamp)

从数据来看，质量（此处称为“data_timestamp”）似乎是一个正确的时间戳；我真的不明白为什么DATE()函数会失败。

有任何帮助或提示吗？谢谢！

- bgenchel

Answer 1

你的查询存在两个问题：

1. 在BigQuery Legacy SQL中，你不能在GROUP BY语句中使用表达式，只能使用字段（顺便说一下，在BigQuery Standard SQL中不存在这种限制）
2. 当你使用GROUP BY时，不能简单地选择所有字段，而是需要使用一些聚合函数，如COUNT或SUM等（对于那些不属于GROUP VY的字段）

因此，你的查询可能如下所示：

SELECT DATE(data_timestamp) AS dt, COUNT(1) AS cnt 
FROM [myproj:mydataset.mytable] 
WHERE bool_property=FALSE 
AND entity_id=5667423172689920 
AND DATEDIFF(CURRENT_DATE(), data_timestamp) <= 7
GROUP BY dt

- Mikhail Berlyant

---

好的，看起来我正在使用标准SQL，因为我已经测试了其他使用“GROUP BY”的查询，并且它们似乎工作正常。你说的聚合函数是正确的。不过我改变了我的问题以适应这一点。我的真正问题不在于GROUP BY，而是在于DATE函数，它似乎不合逻辑地无法工作。 - bgenchel

根据您使用的语法，您正在使用传统 SQL。您尝试在我的答案中运行查询了吗？问题不在于日期函数，而在于您在传统 SQL的group by语句中使用了它。 - Mikhail Berlyant

是的，完全奏效了！不过，我不确定我为什么能成功。我猜你的理论/假设是日期函数不能正常工作，因为它在GROUP BY中？能否再详细解释一下这个查询是如何工作的？ - bgenchel

正如我之前已经解释过的那样，在BigQuery Legacy SQL中，您不能在group by语句中使用表达式，而是需要在select语句中预先计算这个表达式，例如 DATE(data_timestamp) AS dt，然后在group by中使用这个新的“字段”（dt）。这是通常的做法。 - Mikhail Berlyant

啊，我现在明白了，不是GROUP BY不起作用，而是里面的表达式。太棒了，非常感谢！ - bgenchel

Answer 2

不幸的是，传统SQL不允许使用GROUP BY表达式，只能使用列名。但是使用标准SQL可以实现：

SELECT * FROM myproj.mydataset.mytable
WHERE 
    bool_property=False AND 
    entity_id=5667423172689920 AND
    DATE_DIFF(CURRENT_DATE(), EXTRACT(DATE FROM data_timestamp), DAY) <= 7
GROUP BY EXTRACT(DATE FROM data_timestamp)

- Mosha Pasumansky

---

似乎BigQuery确实包括group by，详见https://cloud.google.com/bigquery/query-reference。 - bgenchel