在BigQuery中对日期进行分组时，DATE_ADD或DATE_DIFF错误 [英] DATE_ADD or DATE_DIFF error when grouping dates in BigQuery

问题描述

尽我所能尝试搜索，尽管

Try and search as I might, I still cannot figure this out and although https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_add has assisted a bit, I am still stuck. I am trying to group dates into weeks but keep getting either one of the two errors below the code.

day         bitcoin_total   dash_total
2009-01-03  1               0
2009-01-09  14              0
2009-01-10  61              0

理想的结果将是星期初的日期(可以是星期一或星期日，以任何一个为准)

The desirable outcome would be the date at the start of the week (could be Monday or Sunday, whichever)

day         bitcoin_total   dash_total
2008-12-28  1               0
2009-01-04  75              0

这似乎是一个常见问题，但是大多数答案是针对T-SQL而非标准SQL的.我的日期列是 Date 类型，但这是返回类型，所以应该没有问题.

This seems to be a common question but most of the answers are for T-SQL and not standard SQL. My date column is type Date but that is the return type so that should not be a problem.

DATE_ADD(week, DATE_DIFF(week, 0, day), 0) Date
FROM
my_table
GROUP BY
DATE_ADD(week, DATE_DIFF(week, 0, day), 0) 
ORDER BY
DATE_ADD(week, DATE_DIFF(week, 0, day), 0)

我得到一个无法识别的名称:星期几，在[2:10] 时出现上述代码错误，或者 Error:在[2:29]时出现期望的INTERVAL表达式，如果我将DATE_ADD函数中的 date_expression 更改为 DATE"2009-01-01"

I am getting a Unrecognized name: week at [2:10] error with the above code or Error: Expected INTERVAL expression at [2:29] if I change the date_expression in the DATE_ADD function to, say, DATE "2009-01-01"

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT DATE_TRUNC(day, WEEK) AS day, 
  SUM(bitcoin_total) AS bitcoin_total, 
  SUM(dash_total) AS dash_total
FROM `project.dataset.table`
GROUP BY day   

如果要应用到您的问题中的示例数据，如以下示例所示

If to apply to sample data in your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2009-01-03' day, 1 bitcoin_total, 0 dash_total UNION ALL
  SELECT '2009-01-09', 14, 0 UNION ALL
  SELECT '2009-01-10', 61, 0 
)
SELECT DATE_TRUNC(day, WEEK) AS day, 
  SUM(bitcoin_total) AS bitcoin_total, 
  SUM(dash_total) AS dash_total
FROM `project.dataset.table`
GROUP BY day   

输出将为

Row day         bitcoin_total   dash_total   
1   2008-12-28  1               0    
2   2009-01-04  75              0    

这篇关于在BigQuery中对日期进行分组时，DATE_ADD或DATE_DIFF错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！