Логическая обработка запросов: GROUP BY и HAVING

Эта статья продолжает серию публикаций о логической обработке запросов, которая описывает логическую или концептуальную интерпретацию запросов. В первой части (см. Windows IT Pro/RE № 3 за 2016 год) был дан обзор темы, приведена тестовая база данных TSQLV4 (http://tsql.solidq.com/SampleDatabases/TSQLV4.zip) и показаны примеры запросов, простой и сложный. Те же примеры базы данных и запросов используются и в этой статье. В следующих частях были рассмотрены особенности логической обработки первого основного предложения запросов — FROM. В предыдущей статье (опубликованной в Windows IT Pro/RE № 1 за 2017 год) речь шла о втором основном предложении запросов — WHERE. В данной статье мы рассмотрим, как выполняется логическая обработка третьего и четвертого основных предложений запросов — GROUP BY и HAVING соответственно.

Необязательное предложение GROUP BY обрабатывается на третьем шаге логической обработки запросов, а необязательное предложение HAVING — на четвертом. Третий шаг, на котором обрабатывается предложение GROUP BY, работает с виртуальной таблицей, возвращаемой на втором шаге в результате обработки предложения WHERE. На третьем шаге строки из входной таблицы упорядочиваются в группы на основе набора группирования, определенного в предложении GROUP BY. Затем на четвертом шаге группы фильтруются на основе предиката, указанного в предложении HAVING. Возвращаются только те группы, для которых предикаты принимают значение true, а группы, для которых значением предиката является false или unknown, отбрасываются. На рисунке 1 показана блок-схема с логической обработкой предложений FROM, WHERE, GROUP BY и HAVING.

Рисунок 1. Блок-схема с логической обработкой предложений FROM, WHERE, GROUP BY и HAVING

В этой статье используется простой тестовый запрос и сложный тестовый запрос из первой статьи, чтобы показать входные и выходные данные шагов, на которых обрабатываются предложения GROUP BY и HAVING. Программный код листинга 1 содержит предложения GROUP BY и HAVING из простого тестового запроса.

На экране 1 показано состояние данных после обработки предложения WHERE (шаг 2) и перед обработкой предложения GROUP BY (шаг 3).

Экран 1. Состояние данных после обработки предложения WHERE (шаг 2) и перед обработкой предложения GROUP BY (шаг 3)

Этот входной набор состоит из 24 строк (на экране 1 показаны только столбцы C.custid и O.orderid). Набор группирования, определенный в нашем запросе, — (C.custid), и вследствие того, что существует пять неповторяющихся значений C.custid во входном наборе, результат фазы GROUP BY упорядочивает входные строки в пяти группах (см. экран 2).

Экран 2. Упорядочение результатов на фазе GROUP BY

Фаза HAVING применяет предикат COUNT (O.orderid) = ‘20160501’. Поскольку orderdate не является частью набора группирования запроса, к нему нельзя обращаться в предложении HAVING, если только он не содержится в агрегатной функции. Но это не лучший способ фильтрации групп в тех случаях, когда результат агрегации столбца orderdate удовлетворяет некоторому условию; требуется фильтровать строки, представляющие заказы, размещенные в определенный день или позднее. Однако после группирования нужно применить фильтр группы, который сохраняет только группы сотрудников с тремя или меньшим числом заказов. Поскольку этот фильтр предусматривает агрегатный подсчет заказов и должен применяться на групповом уровне, его необходимо указывать в предложении HAVING: HAVING COUNT (*) 0 в нашем запросе, вы можете выбрать: применить его как фильтр строк в предложении WHERE или как фильтр групп в предложении HAVING. Результат будет один и тот же. Для большинства пользователей в таком случае более естественно применить фильтр в предложении WHERE. Это известно оптимизатору SQL Server, который с большой вероятностью сформирует одинаковый план в обоих случаях.

T-SQL поддерживает нестандартную функцию GROUP BY ALL. Это довольно интересная функция, но следует помнить, что в официальной документации SQL Server есть примечание, рекомендующее воздержаться от ее использования, так как Microsoft планирует отказаться от нее в будущем. Рассмотрим особенности этой функции, относящиеся к логической обработке запросов, и рекомендуемую альтернативу.

В сущности, GROUP BY ALL сохраняет пустые группы, отфильтрованные предложением WHERE. Любой агрегат, применяемый к этим группам, работает с пустым набором. Агрегат COUNT (*) для таких групп возвращает 0. Например, рассмотрим запрос, показанный в листинге 12 (назовем его Query 3).

Фильтр в предложении WHERE полностью исключает все строки для клиентов 3, 5, 6 и 9. Ранее вы видели, что результат похожего запроса без GROUP BY ALL не возвращает группы для этих клиентов. Но поскольку GROUP BY ALL сохраняет пустые группы, выходные данные этого запроса охватывают группы для клиентов с числом заказов, равным 0 (см. экран 16).

Экран 16. Результаты запроса Query 3

Как уже отмечалось, Microsoft объявила о намерении отказаться от этой функции в будущем. Альтернатива для предыдущего запроса, которая должна быть работоспособной в дальнейшем (назовем ее запросом Query 4), приведена в листинге 13.

Вместо фильтрации строк в предложении WHERE запрос использует выражение CASE на основе того же предиката, чтобы определить, сохранить значение (столбец tokeep — 1) или игнорировать (столбец tokeep — NULL). Затем вы применяете вычисление агрегата к столбцу tokeep. В этом решении используются поддерживаемые элементы, которые, скорее всего, будут поддерживаться и в дальнейшем, кроме того, оно более эффективное, чем решение с GROUP BY ALL. На рисунке 3 показаны планы запросов Query 3 (решение GROUP BY ALL) и Query 4 (решение с выражением CASE) с использованием обозревателя планов SQL Sentry (http://www.sqlsentry.com/products/plan-explorer).

Рисунок 3. Планы запросов Query 3 (решение с GROUP BY ALL) и Query 4 (решение с выражением CASE)

План для решения GROUP BY ALL (Query 3) просматривает входные данные дважды: один раз (верхняя ветвь плана) — чтобы возвратить все строки без применения фильтра WHERE наряду в постоянным флагом NULL, и второй раз (нижняя ветвь плана) с применением фильтра WHERE наряду с постоянным флагом 0. Затем план сцепляет результаты, группирует строки по empid, вычисляет агрегаты COUNT (флаг) и, наконец, применяет фильтр HAVING. План для решения на основе выражения CASE (Query 4) просматривает входные данные только один раз.

Традиционные запросы группы вычисляют агрегаты для одного набора группирования (единственного набора выражения, по которому выполняется группирование). Но иногда необходимо составить запросы, выдающие агрегаты для нескольких наборов группирования, обычно при подготовке отчетов. Например, требуется направить запрос к таблице Sales.Orders и вычислить ежедневные, ежемесячные, ежегодные и совокупные итоги заказов. Это можно сделать, составив отдельный группированный запрос для каждого набора группирования, а затем объединить результаты, как показано в листинге 14.

Обратите внимание, что в программном коде листинга 14 используются значения NULL как заполнитель для элементов, не являющихся частью текущей группировки, но релевантные в других наборах группирования. Выражения в предложении ORDER BY обеспечивают иерархический порядок представления (за дневными показателями следуют месячные итоги, за ними годовые итоги и, наконец, совокупный итог). Этот запрос формирует выходные данные, показанные в сокращенном виде на экране 17.

Экран 17. Результаты группированного запроса в сокращенном виде

SQL (и T-SQL) поддерживает гораздо более изящное решение для наших нужд с использованием предложения GROUPING SETS, которое указывается в предложении GROUP BY и содержит несколько наборов группирования, например как в листинге 15.

Функция GROUPING возвращает 0 для входного элемента, если он является частью набора группирования, и 1 в противном случае (когда это агрегат). В нашем запросе она упрощает выражения в предложении ORDER BY, которое обеспечивает иерархический порядок представления. В ходе логической обработки запросов предложение GROUPING SETS позволяет определить несколько наборов группирования и в результате связать каждую строку, возвращенную из WHERE, возможно, с несколькими группами вместо одной.

В последнем запросе, когда нужно вычислить все наборы группирования, представляющие ведущую комбинацию в иерархии, такой как наша временная иерархия, не обязательно явно перечислять все наборы группирования в предложении GROUPING SETS; можно использовать сокращенный синтаксис с предложением ROLLUP (см. листинг 16).

Предложение ROLLUP эквивалентно предложению GROUPING SETS в предыдущем запросе. Оно определяет все наборы группирования, представляющие ведущую комбинацию входных выражений.

Итак, мы рассмотрели аспекты применения предложений GROUP BY и HAVING для логической обработки запросов. В статье была представлена блок-схема логической обработки запросов с предложениями FROM, WHERE, GROUP BY и HAVING, показано, что группирование удаляет подробности, и предложена альтернатива в виде оконной работы, при которой подробности не удаляются. Я пояснил различия между предикатами WHERE и HAVING, а также рассказал о нестандартной функции GROUP BY ALL и предоставил более эффективную альтернативу, которая в отличие от GROUP BY ALL, вероятно, будет поддерживаться в дальнейшем. Наконец, мы разобрали, как определить несколько наборов группирования в одном запросе с использованием предложений GROUPSING SETS и ROLLUP. В следующей статье цикла мы продолжим рассмотрение логической обработки запросов и речь пойдет о предложениях SELECT и ORDER BY.

Поделитесь материалом с коллегами и друзьями

Теперь расходы на сертификацию кабельных систем станут меньше на 65%!

• BPM-проект года`2022 25 января 2022
• Directum Awards 4 февраля 2022
• Топ-100 ИТ-лидеров 18 февраля 2022
• Экономичный и многофункциональный Wi-Fi для турагентств и отелей: сетевые решения Aruba 22 февраля 2022
• Форум BIG DATA&AI 2022 24 марта 2022

• Windows Server
• Windows 10
• Skype
• SharePoint
• Office 365
• Microsoft Azure
• Exchange Server
• Active Directory
• Все темы

«Открытые системы» - ведущее российское издательство, выпускающее широкий спектр изданий для профессионалов и активных пользователей в сфере ИТ, цифровых устройств, телекоммуникаций, медицины и полиграфии, журналы для детей.