将BigQuery日数据按周一为周起始聚合为周数据时遇DATE_TRUNC报错求助

报错原因 #

报错信息明确指出：Date字段是STRING类型，但DATE_TRUNC函数仅支持DATE、DATETIME或TIMESTAMP类型作为第一个参数，因此需要先将字符串格式的日期转换为DATE类型。

修正后的脚本 #

方法1：转换日期类型并指定周一为周起始 #

将Date字符串转换为DATE类型后，使用DATE_TRUNC(..., WEEK(MONDAY))设置周起始为周一：

INSERT INTO `table name` 

SELECT
  DATE_TRUNC(PARSE_DATE('%Y-%m-%d', Date), WEEK(MONDAY)) AS PARTITION_WEEK,
  COUNT(DISTINCT(clientId)) AS _DistClients
FROM `table name`
GROUP BY PARTITION_WEEK
ORDER BY PARTITION_WEEK DESC

注意：如果你的Date字段格式是YYYYMMDD（GA常见格式），请将PARSE_DATE的格式符改为'%Y%m%d'。

方法2：使用ISO周（默认周一为起始） #

ISO周的定义就是以周一为一周的第一天，也可以直接用ISO_WEEK作为日期截断单位：

INSERT INTO `table name` 

SELECT
  DATE_TRUNC(PARSE_DATE('%Y-%m-%d', Date), ISO_WEEK) AS PARTITION_WEEK,
  COUNT(DISTINCT(clientId)) AS _DistClients
FROM `table name`
GROUP BY PARTITION_WEEK
ORDER BY PARTITION_WEEK DESC

验证结果 #

修正后查询返回的PARTITION_WEEK会是以周一为起始的日期，与你期望的结果格式一致：

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