Google Таблицы

Группировка и условия по времени в QUERY

Чтобы сгруппировать данные по часам, минутам или секундам в QUERY, используются функции hour, minute, second — в SELECT и GROUP BY. Все по аналогии с функциями year, month, day, quarter, dayofweek для группировки по дате.

А если нужно условие на столбец со временем, его нужно задавать в следующем формате:

WHERE A > timeofday'HH:MM:SS'

Например, если нужны только строки со временем после 12:00:

WHERE A > timeofday'12:00:00'

Допустим, у нас есть данные по неким заявкам четырех разных типов. В столбце A - время заявки, в столбце B - тип.
Мы хотим получить количество заявок разных типов по часам - в период с 12 до 18.

Чтобы сгруппировать по часам в строках и посчитать количество заявок:

Select hour(A), count(A) group by hour(A)

Чтобы сгруппировать по типам заявок в столбцах, добавим PIVOT:

Select hour(A), count(A) group by hour(A) pivot B

Ну и добавим условие на период времени:

=QUERY(A1:B; "Select hour(A), count(A) where A>timeofday'12:00:00' and A<timeofday'18:00:00' group by hour(A) pivot B" )

Останется только поменять заголовок столбца со временем с помощью кляузы LABEL, чтобы не отображался стандартный вариант "hour(Часы)", в формате "название агрегирующей функции(столбец)".

Итого:

=QUERY(A1:B;
"Select hour(A), count(A)
where A>timeofday'12:00:00' and A<timeofday'18:00:00'
group by hour(A)
pivot B
label hour(A) 'Часы'" )

Именно эту несложную задачу можно решить и с помощью сводной (там еще и итоги можно будет добавить). Сводная в таблице с примером тоже есть. Но не во всех случаях подойдет сводная. Например, кверить можно массив с несколькими внешними таблицами сразу (которые будут загружаться функциями IMPORTRANGE, объединенными в массиве {... ; ... ; ...} ). Или у вас будут сложные фильтры на другие столбцы исходных данных, например, текстовые - и вам понадобится условие, заданное с помощью регулярных выражений. Тогда пригодится условие MATCHES в кляузе WHERE в функции QUERY.

Файл с примером (Создать копию)

11.0K viewsedited 06:20