英文:
How do extract Big Query DAILY data into WEEKLY numbers, with Monday as the first day of the week?
问题
我正在尝试以每周格式获取聚合数据（来自GA BigQuery），以星期一作为第一天。然而，当我使用下面的脚本时，我收到以下错误消息：
预期结果：
PARTITION_WEEK _DistClients
2023-05-15 1234
2023-05-08 4321
错误消息：
> DATE_TRUNC函数没有匹配的签名，参数类型为：STRING、DATE_TIME_PART。支持的签名有：DATE_TRUNC(DATE, DATE_TIME_PART); DATE_TRUNC(DATETIME, DATE_TIME_PART); DATE_TRUNC(TIMESTAMP, DATE_TIME_PART, [STRING])
所使用的脚本：
INSERT INTO `表名`
SELECT
DATE_TRUNC(Date, WEEK(MONDAY)) AS PARTITION_WEEK,
COUNT(DISTINCT(clientId)) as _DistClients,
FROM `表名`
GROUP BY PARTITION_WEEK
ORDER BY PARTITION_WEEK DESC
非常感谢您的任何帮助。谢谢！
英文:
I'm trying to get aggregated data (from GA BigQuery) in a weekly format with Monday as the first day. However, when I use the the script below I get the following error:
Expected results:
PARTITION_WEEK _DistClients
2023-05-15 1234
2023-05-08 4321
Error message:
>No matching signature for function DATE_TRUNC for argument types: STRING, DATE_TIME_PART. Supported signatures: DATE_TRUNC(DATE, DATE_TIME_PART); DATE_TRUNC(DATETIME, DATE_TIME_PART); DATE_TRUNC(TIMESTAMP, DATE_TIME_PART, [STRING])
Script used:
INSERT INTO `table name`
SELECT
DATE_TRUNC(Date, WEEK(MONDAY)) AS PARTITION_WEEK,
COUNT(DISTINCT(clientId)) as _DistClients,
FROM `table name`
GROUP BY PARTITION_WEEK
ORDER BY PARTITION_WEEK DESC
Any help is much appreciated. Thank you!
答案1
得分: 0
For the expected output,
SELECT DATE_TRUNC(PARSE_DATE("%Y%m%d", Date), WEEK(MONDAY)) AS PARTITION_WEEK,
COUNT(DISTINCT clientId) AS _DistClients
FROM `table name`
GROUP BY 1;
And for the monthly aggregation,
SELECT FORMAT_DATE('%Y-%m', PARSE_DATE("%Y%m%d", Date)) AS Year_Mon,
COUNT(DISTINCT clientId) AS _DistClients
FROM `table name`
GROUP BY 1;
英文:
For the expected output,
PARTITION_WEEK _DistClients
2023-05-15 1234
2023-05-08 4321
You can consider below query.
SELECT DATE_TRUNC(PARSE_DATE("%Y%m%d",Date), WEEK(MONDAY)) AS PARTITION_WEEK,
COUNT(DISTINCT clientId) AS _DistClients,
FROM `table name`
GROUP BY 1;
And for the monthly aggregation,
Year_Mon DistClients
2023-5 12345
SELECT FORMAT_DATE('%Y-%m', PARSE_DATE("%Y%m%d", Date)) AS Year_Mon,
COUNT(DISTINCT clientId) AS _DistClients,
FROM `table name`
GROUP BY 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论