sql - 如何在 Google Bigquery 中按小时分组

Question

我的 Google Bigquery 表有日期和时间列。新数据每 10 分钟就会发布在表中，因此日期字段将包含例如“2018-10-26”和时间字段“19:05:00”。下一条记录的日期和时间字段将类似于“2018-10-26”和“19:15:00”。如何以一小时为单位聚合每天的数据(每天24条记录)？

SQL 请求是使用 Apps 脚本从 Google 表格发送的。以下是 google bigquery.gs 脚本的一部分:( complete script in GitHub )

...
var sheet = SpreadsheetApp.getActiveSheet();

var sql = 'SELECT  date, time, SUM(col1) AS Col1, SUM(col2) AS Col2 GROUP BY 
time, date ORDER BY time ASC';

var queryResults; 

// Inserts a Query Job
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
}
....

Answer 1

以下适用于 BigQuery 标准 SQL

#standardSQL
SELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 
FROM `project.dataset.table`
GROUP BY date, hour 
ORDER BY date, hour

您可以在问题中使用虚拟数据来测试、玩玩上面的内容:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE "2018-10-26" date, TIME "19:05:00" time, 1 col1, 2 col2 UNION ALL
  SELECT "2018-10-26", "19:15:00", 3, 4
) 
SELECT date, TIME_TRUNC(time, HOUR) hour, SUM(col1) AS Col1, SUM(col2) AS Col2 
FROM `project.dataset.table`
GROUP BY date, hour 
ORDER BY date, hour  

结果

Row date        hour        Col1    Col2     
1   2018-10-26  19:00:00    4       6