Как группировать по часам в Google Bigquery
В моей таблице Google Bigquery есть столбец даты и времени. Новые данные публикуются в таблице каждые 10 минут, поэтому поле даты будет иметь, например, «2018-10-26» и поле времени «19:05:00». Следующая запись будет выглядеть как «2018-10-26» и «19:15:00» для поля даты и времени. Как агрегировать данные за каждый день по одному часу (24 записи в день)?
Запрос SQL отправляется из Google Таблиц с использованием скрипта приложений. Вот часть скрипта google bigquery.gs: (полный скрипт в 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);
}
....
Ответы 2
Вы можете использовать extract():
SELECT date, extract(hour from time) as hh,
SUM(col1) AS Col1, SUM(col2) AS Col2
FROM ?
GROUP BY date, hh
ORDER BY date, hh ASC;
Почему вам нужно хранить значения datetime в двух отдельных столбцах, а не в одном столбце datetime или timestamp?
@Intars_K. . . extract() у меня отлично работает: select extract(hour from cast('10:00:00' as time)).
Я использую этот SQL-запрос в скрипте bigquery.gs, и теперь он выглядит так: var sql = 'SELECT date, extract(HOUR FROM time) as hour, SUM(col1.... И это генерирует GoogleJsonResponseException: обнаружено "" FROM "" FROM "" в строке 1, столбец 28. Ожидалось: ")" .... Вероятно, этот подход не будет работать для Google Таблиц с использованием скрипта приложений.
Вам необходимо включить стандартный SQL.
@Elliott Brossard, в том числе: var sql = '#standardSQL SELECT date, extract(hour from cast("10:10:10" as time)), генерирует ошибку: GoogleJsonResponseException: 1.1134 - 1.1134: запрос не найден.
Ниже приведен стандартный SQL BigQuery.
#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
Ошибка создания функции Time_Trunc: GoogleJsonResponseException: 1.25 - 1.36: нераспознанная функция time_trunc
Вы не включили часть #standardSQL, которая включила бы стандартный SQL в качестве диалекта.
@Elliott Brossard, я включил его, и мой скрипт приложения выглядит так: var sql = '#standardSQL SELECT date, TIME_TRUNC(time, HOUR) as hour, SUM(... , но теперь у меня ошибка: GoogleJsonResponseException: 1.1118 - 1.1118: запрос не найден. Должен ли я включить его как-то иначе?
Вы должны поставить новую строку между префиксом и самим запросом. cloud.google.com/bigquery/docs/reference/standard-sql/…
Хорошо, теперь это работает, спасибо за подсказку! Ставил так - queryRequest.useLegacySql = false; Теперь мой скрипт выглядит так: // Inserts a Query Job try { var queryRequest = BigQuery.newQueryRequest(); queryRequest.useLegacySql = false; queryRequest.setQuery(sql).setTimeoutMs(100000); queryResults = BigQuery.Jobs.query(queryRequest, projectNumber); }...
extract () генерирует ошибку: GoogleJsonResponseException: 1.22 - 1.27: Нераспознанная функция extract Да, можно сохранить в одном столбце, но помогает ли это в решении этой проблемы?