BigQuery中DATE_TRUNC报错排查：从Legacy表获取月度聚合数据

问题重现 #

你在BigQuery的Legacy SQL模式下，尝试对字符串类型的date_create列做月度聚合，示例数据如下：

执行以下查询时触发了语法错误：

SELECT DATE_TRUNC(DATE date_create, MONTH) as month, sum(amount) as amount_m from table group by 1

错误信息：

Error: Syntax error: Expected ")" but got identifier "date_create"

错误原因分析 #

这个报错的核心是你混淆了BigQuery Legacy SQL 和 Standard SQL 的语法规则：

1. 日期转换语法错误：在Legacy SQL中，将字符串转换为日期类型必须使用带括号的函数调用 DATE(date_create)，而你写的 DATE date_create 是Standard SQL的类型转换写法，Legacy SQL的语法解析器无法识别，因此抛出了预期)却遇到标识符的错误。
2. DATE_TRUNC参数顺序差异：即使修正了日期转换的写法，Legacy SQL的DATE_TRUNC函数参数顺序和Standard SQL完全相反——Standard SQL是DATE_TRUNC(日期表达式, 时间单位)，但Legacy SQL是DATE_TRUNC(时间单位, 日期表达式)，而且时间单位需要用字符串形式（比如'MONTH'）。

解决方法 #

方法1：切换到Standard SQL（推荐）

BigQuery官方现在更推荐使用功能更完善、语法更直观的Standard SQL，修改后的查询语句如下：

-- 方式1：使用CAST明确转换类型
SELECT DATE_TRUNC(CAST(date_create AS DATE), MONTH) AS month, SUM(amount) AS amount_m
FROM `your-project.your-dataset.table` -- 注意Standard SQL用反引号包裹表名
GROUP BY 1

-- 方式2：利用DATE函数直接转换（因为date_create是YYYY-MM-DD格式）
SELECT DATE_TRUNC(DATE(date_create), MONTH) AS month, SUM(amount) AS amount_m
FROM `your-project.your-dataset.table`
GROUP BY 1

方法2：修正Legacy SQL查询语句

如果必须保留Legacy SQL模式，需要调整日期转换和DATE_TRUNC的写法：

-- 正确使用Legacy SQL的DATE_TRUNC语法
SELECT DATE_TRUNC('MONTH', DATE(date_create)) AS month, SUM(amount) AS amount_m
FROM [your-project:your-dataset.table] -- Legacy SQL用方括号包裹表名
GROUP BY 1

-- 另一种替代写法：直接格式化日期为月度第一天
SELECT FORMAT_DATE('%Y-%m-01', DATE(date_create)) AS month, SUM(amount) AS amount_m
FROM [your-project:your-dataset.table]
GROUP BY 1

内容的提问来源于stack exchange，提问作者Ilja