Сортировка и группировка в QUERY

Фильтрация показывает нужные строки, но для настоящих отчётов нужны порядок и итоги. QUERY поддерживает и то и другое с помощью ещё двух конструкций в стиле SQL: ORDER BY и GROUP BY.

С их помощью можно ответить на вопросы вроде в каком регионе продают больше всего или вывести сделки от самых крупных к самым мелким — всё в одной формуле.

Конструкция ORDER BY сортирует результаты по одному или нескольким столбцам. Она указывается после WHERE, если он используется.

По умолчанию сортировка выполняется по возрастанию (от меньшего к большему, от А до Я). Эта формула выводит все строки, отсортированные по продажам от меньших значений к большим.

Добавьте DESC после столбца, чтобы сортировать от большего к меньшему. Используйте ASC, чтобы явно указать сортировку по возрастанию.

Так сначала отображаются самые крупные продажи — это идеально для списка лучших результатов. Объедините конструкцию с LIMIT, чтобы получить аккуратную первую тройку.

Перечислите в ORDER BY несколько столбцов через запятую, чтобы упорядочить строки с одинаковыми значениями. Google Таблицы сортируют по первому столбцу, а затем используют следующий, чтобы упорядочить совпадающие строки.

Здесь результаты упорядочены по регионам в алфавитном порядке, а внутри каждого региона сначала идут самые высокие продажи.

GROUP BY объединяет строки с одинаковым значением в одну итоговую строку. Так создаются итоги по категориям.

Для этого конструкция SELECT должна объединять столбец для группировки с агрегатной функцией, например SUM, COUNT или AVG, применённой к другому столбцу.

Эта формула подсчитывает общую сумму продаж для каждого региона. SUM(D) складывает столбец Sales, а GROUP BY A создаёт одну строку для каждого региона.

В результате получается небольшая сводная таблица: Восток с итогом и Запад с итогом — всё с помощью одной функции.

Замените функцию на COUNT, чтобы считать строки, а не суммировать их. Так Вы узнаете, сколько сделок заключил каждый регион.

Можно использовать COUNT(D), чтобы подсчитать непустые ячейки продаж и быстро получить количество строк в каждой группе.

Используйте AVG, чтобы вычислить среднее значение в каждой группе. Здесь мы получаем средний размер сделки для каждого региона.

Можно даже сочетать агрегаты: выберите одновременно SUM(D) и AVG(D) в одном запросе, чтобы показать общую сумму и среднее значение рядом.

Распространённая ошибка: каждый столбец в SELECT, который не обёрнут в агрегатную функцию, должен быть указан в GROUP BY.

SELECT A, B, SUM(D) GROUP BY A завершится ошибкой, потому что B не является ни агрегированным, ни сгруппированным. Либо сгруппируйте оба столбца, либо удалите B из списка выбираемых столбцов.

Объедините конструкции, чтобы ранжировать сводку. После группировки отсортируйте данные по агрегатному значению, чтобы вывести самую крупную группу первой.

Так каждый регион выводится с общей суммой продаж — от самой высокой суммы к самой низкой. Получается готовая таблица лидеров.

Для сгруппированных столбцов появляются неудачные заголовки вроде сумма Продажи. Добавьте конструкцию LABEL, чтобы переименовать их и сделать отчёт аккуратнее.

Здесь столбец с итогом переименовывается в Итоговые продажи. Текст названия использует одинарные кавычки, как и значения для фильтрации.

Проверьте, насколько хорошо Вы освоили сортировку и группировку.

Теперь Вы умеете формировать результаты QUERY:

• ORDER BY col [ASC|DESC] сортирует данные, а дополнительные столбцы помогают упорядочить строки с одинаковыми значениями
• GROUP BY вместе с SUM, COUNT или AVG создаёт сводки по категориям
• Выбранные неагрегированные столбцы должны быть указаны в GROUP BY
• LABEL переименовывает заголовки агрегатных столбцов

Теперь одна формула создаёт отсортированный, сгруппированный и подписанный отчёт.