2021-08-31 09:20:40
Группировка и условия по времени в 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
Останется только поменять заголовок столбца со временем с помощью кляузы LABEL, чтобы не отображался стандартный вариант "hour(Часы)", в формате "название агрегирующей функции(столбец)".
Итого:
=QUERY(A1:B;
"Select hour(A), count(A)
where A>timeofday'12:00:00' and Agroup by hour(A)
pivot B
label hour(A) 'Часы'" )
Именно эту несложную задачу можно решить и с помощью сводной (там еще и итоги можно будет добавить). Сводная в таблице с примером тоже есть. Но не во всех случаях подойдет сводная. Например, кверить можно массив с несколькими внешними таблицами сразу (которые будут загружаться функциями IMPORTRANGE, объединенными в массиве {... ; ... ; ...} ). Или у вас будут сложные фильтры на другие столбцы исходных данных, например, текстовые - и вам понадобится условие, заданное с помощью регулярных выражений. Тогда пригодится условие MATCHES в кляузе WHERE в функции QUERY.
Файл с примером (Создать копию)
3.2K views06:20
G
