Логическая обработка запросов: 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 обрабатывается на третьем шаге логической обработки запросов, а необязательное предложение 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) <= 3 к группам фильтра. Подходят только две из пяти входных групп, поэтому выходные данные этого шага состоят только из двух групп (см. экран 3).

Экран 3. Результат фазы HAVING

Приведенный в листинге 2 программный код содержит фрагменты GROUP BY и HAVING нашего сложного тестового запроса.

Фаза WHERE возвращает результат, содержащий 27 строк, он показан на экране 4.

Экран 4. Результаты фазы WHERE

Данный набор используется как входной в фазе GROUP BY, которая в этом запросе упорядочивает строки по наборам группирования (C.custid, A.custlocation). Шаг организует строки из входного набора в шесть групп, приведенные на экране 5.

Экран 5. GROUP BY упорядочивает строки по наборам группирования

Затем запрос фильтрует группы на основе предиката HAVING COUNT (DISTINCT O.orderid) <= 3, возвращая пять из шести первоначальных групп (см. экран 6).

Экран 6. Фильтр группы на основе предиката HAVING COUNT

Возникает вопрос, почему предикат HAVING определяет число различных идентификаторов заказа. Дело в том, что запрос объединяет таблицу Sales.Orders с таблицей Sales.OrderDetails, что приводит к строке на строку заказа, а не строке на заказ. Предполагается, что фильтр HAVING в нашем запросе возвращает только группы, имеющие число заказов, а не число строк заказов, меньшее или равное трем.

Удаление подробностей

Фаза GROUP BY упорядочивает строки в группах, и с этого этапа логической обработки запроса выражения не имеют прямого доступа к подробному состоянию данных. Это относится ко всем последующим фазам, на которых выполняются предложения HAVING (шаг 4), SELECT (шаг 5) и ORDER BY (шаг 6) запроса. Если в этих предложениях нужно ссылаться на столбец во входных таблицах, сделать это напрямую можно только в том случае, если данный столбец представляет собой часть набора группирования запроса. Чтобы сослаться на столбец, который не является частью набора группирования запроса, этот столбец должен содержаться в групповой агрегатной функции. Например, следующий запрос недействителен, так как в списке SELECT вы ссылаетесь на столбец orderid, а этот столбец не является частью набора группирования и не содержится в групповой агрегатной функции:

SELECT custid, orderid
FROM Sales.Orders
GROUP BY custid;

Выдается сообщение об ошибке, приведенное на экране 7.

Экран 7. Сообщение об ошибке при ссылке на столбец

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

SELECT custid, MAX (orderid) AS lastorder
FROM Sales.Orders
GROUP BY custid;

Теоретически в SQL возможны исключения из приведенного выше правила, если вследствие существующих ограничений можно заключить, что определенные столбцы могут иметь только одно неповторяющееся значение для группы, даже если они не являются частью набора группирования. Однако SQL не поддерживает такие неявные выводы. В качестве примера рассмотрим запрос, приведенный в листинге 3.

Ограничение первичного ключа, определенное для столбца custid в таблице Sales.Customers, обеспечивает уникальность значений custid. Это означает, что все остальные столбцы в таблице функционально зависимы от столбца custid. Поэтому в результате соединения Sales.Customers и Sales.Orders все строки с одним значением custid всегда имеют одинаковые значения во всех остальных столбцах. Поэтому в приведенном запросе SQL может теоретически заключить, что, поскольку C.custid является частью набора группирования, всегда будет существовать только одно значение C.companyname, связанное с каждой группой. Однако, как уже отмечалось, SQL (то же относится и к T-SQL) не поддерживает такого рода вмешательство, и при попытке выполнить этот запрос вы получаете сообщение об ошибке, приведенное на экране 8.

Экран 8. Сообщение об ошибке при выполнении запроса из листинга 3

Существует два обходных приема, поддерживаемых SQL. Первый — добавить столбец C.companyname к набору группирования, например как в листинге 4 (назовем этот запрос Query 1).

Второй способ — применить искусственную агрегатную функцию, такую как MIN или MAX, к столбцу C.companyname, например как в листинге 5 (назовем этот запрос Query 2).

Чтобы выяснить, какое решение более эффективно, ознакомьтесь с планами запросов на рисунке 2.

Рисунок 2. Планы запросов Query 1 и Query 2

Обратите внимание, что в плане для запроса Query 1 оптимизатор передает работу по группированию и агрегированию сразу после просмотра данных из таблицы Orders, перед соединением с таблицей Customers. Это соединение уравнивающее, поэтому группирование по O.custid — то же самое, что группирование по C.custid, а затем возможна передача при сохранении значения первоначального запроса. Таким образом, соединение происходит с меньшими затратами по сравнению с группированием и агрегированием после соединения. На мой взгляд, запрос Query 1 более ясный и естественный, так что я предпочитаю этот вариант.

Агрегирование данных без удаления подробностей

По сравнению с получением детального состояния данных группирование дает новые подробности в виде агрегированных вычислений. Однако одновременно при группировании отбрасываются подробности, как было показано выше. Например, рассмотрим следующий запрос:

SELECT custid, SUM (val) AS custtotal
FROM Sales.OrderValues
GROUP BY custid;

В исходном представлении имеется строка для заказа, но, поскольку запрос группирует данные по custid, выходные данные содержат одну строку для заказчика с общими значениями заказа клиента (см. экран 9).

Экран 9. Результат агрегирования данных с удалением подробностей

Но что делать, если вы не хотите избавляться от подробностей? Например, если нужно получить детальную информацию о заказе и, кроме того, вычислить процент значения текущего заказа в общей сумме заказов клиента. В процессе вычисления процента необходимо разделить подробное значение заказа на агрегат всех значений заказа для того же клиента. Это можно сделать, подготовив запрос, который группирует данные по custid и вычисляет общее значение для клиента, а затем определить табличное выражение на основе этого запроса и задействовать внешний запрос для соединения табличного выражения с представлением Sales.OrderValues, чтобы сопоставить подробности с агрегатами. Однако в результате увеличивается сложность программного кода. Альтернативный способ: вычислить агрегат с использованием оконной функции вместо групповой (см. листинг 6).

В отличие от группирования, при котором удаляются подробности, оконная функция подробностей не удаляет. Агрегат вычисляется для окна строк, полученного из набора результатов базового запроса, и определяется предложением OVER функции. Результат базового запроса определяется только тогда, когда логическая обработка запроса достигает фазы SELECT (после обработки фаз FROM, WHERE, GROUP BY и HAVING), поэтому оконные функции разрешены только в предложениях SELECT и ORDER BY запроса. Если указано пустое предложение OVER, функция работает с полным результатом базового запроса, и вы получаете общий итог. Если добавлено предложение разбиения окна, как в приведенном выше запросе, функция работает с ограниченной секцией; в нашем примере строки ограничены лишь теми, в которых значение custid такое же, как в текущей строке. Другими словами, наша оконная функция вычисляет итог клиента. Оконная функция возвращает результат, не отбрасывая детали, поэтому вы можете объединить элементы подробностей из строки с результатом функции агрегирования окна. Наш запрос делит значение текущего заказа на итог клиента, чтобы вычислить процент: val/SUM (val) OVER (PARTITION BY custid). Этот запрос выдает выходные данные, показанные на экране 10.

Экран 10. Результаты агрегирования с сохранением подробностей

Примечательно, что группирование и работа с окнами не исключают друг друга. Вы можете применять оконные функции к сгруппированным данным. Помните, что группирование выполняется на шаге 3 логической обработки запроса и работа с окнами происходит после группирования, в фазах SELECT (шаг 5) или ORDER BY (шаг 6). Основное правило: следует помнить, что, в отличие от функций агрегирования группы, которые можно применить к элементам подробностей как к входным данным, оконные функции могут применяться только к элементам, обычно разрешенным в предложении SELECT. Например, предположим, нам нужно подготовить сгруппированный запрос, в котором вычисляются итоговые значения заказов клиента и, кроме того, процент итога клиента от общего итога. Можно попробовать применить запрос как в листинге 7.

Ссылка на столбец val в функциях агрегирования группы SUM корректна, но ссылка на столбец val в функции агрегированного значения окна SUM недопустима, точно так же как такая ссылка была бы недопустимой непосредственно в предложении SELECT. В этом случае вы получите сообщение об ошибке, приведенное на экране 11.

Экран 11. Сообщение об ошибке при выполнении листинга 7

Вы можете попробовать альтернативный вариант, применяя оконную функцию к псевдониму custtotal (см. листинг 8).

Однако эта попытка также заканчивается неудачей, так как подобная ссылка была бы некорректной непосредственно в предложении SELECT из-за обработки выражений на основе наборов в той же логической фазе. Вы получите сообщение об ошибке, как на экране 12.

Экран 12. Сообщение об ошибке выполнения листинга 8

Вероятно, вам это покажется удивительным, но вы можете применить оконную функцию к функции группирования (см. листинг 9).

На этот раз запрос будет выполнен успешно, и вы получите выходные данные, показанные на экране 13.

Экран 13. Результат применения оконной функции к функции группирования

Аналогично предположим, что нужно сгруппировать данные из представления Sales.OrderValues по custid и orderdate и наряду с вычислением ежедневного итога требуется вычислять значения с нарастающим итогом от начала операций клиента до текущей даты. Как показано в предыдущем примере, можно объединить группирование и работу с окнами для выполнения этой задачи (см. листинг 10).

Этот запрос выдает выходные данные, представленные на экране 14.

Экран 14. Результаты вычисления ежедневного итога и значения с нарастающим итогом

HAVING против WHERE

Предложение HAVING служит для целей фильтрации, подобно предложению WHERE, где данные фильтруются на основе предиката. Ключевое различие между ними в том, что WHERE фильтрует строки перед группированием, а предложение HAVING фильтрует целые группы после группирования. Поэтому на уровне группы предложение HAVING определяет, следует ли сохранить группу или удалить ее в зависимости от результата предиката (если true — сохранить, если false или unknown — удалить). Например, нам нужно направить запрос к представлению Sales.OrderValues и фильтровать только заказы, размещенные 1 мая 2016 года и позже. Вы хотите группировать остальные заказы по сотрудникам и фильтровать только группы не более чем с тремя заказами. Для подходящих групп нужно возвратить идентификатор сотрудника и число заказов. Необходимо применить фильтр к столбцу orderdate в предложении WHERE, поскольку он рассматривается как фильтр строк: WHERE orderdate >= ‘20160501’. Поскольку orderdate не является частью набора группирования запроса, к нему нельзя обращаться в предложении HAVING, если только он не содержится в агрегатной функции. Но это не лучший способ фильтрации групп в тех случаях, когда результат агрегации столбца orderdate удовлетворяет некоторому условию; требуется фильтровать строки, представляющие заказы, размещенные в определенный день или позднее. Однако после группирования нужно применить фильтр группы, который сохраняет только группы сотрудников с тремя или меньшим числом заказов. Поскольку этот фильтр предусматривает агрегатный подсчет заказов и должен применяться на групповом уровне, его необходимо указывать в предложении HAVING: HAVING COUNT (*) <= 3. В листинге 11 приводится полный запрос.

Выходные данные этого запроса показаны на экране 15.

Экран 15. Результаты запроса с фильтром в HAVING

Если нужно применить фильтр на основе столбца, который является частью набора группирования запроса, например empid > 0 в нашем запросе, вы можете выбрать: применить его как фильтр строк в предложении WHERE или как фильтр групп в предложении HAVING. Результат будет один и тот же. Для большинства пользователей в таком случае более естественно применить фильтр в предложении WHERE. Это известно оптимизатору SQL Server, который с большой вероятностью сформирует одинаковый план в обоих случаях.

GROUP BY ALL

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.

SELECT ...
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON C.custid = O.custid
WHERE C.country = N'Spain'
GROUP BY C.custid
HAVING COUNT( O.orderid ) <= 3;

SELECT ...
FROM Sales.Customers AS C
  LEFT OUTER JOIN
      ( Sales.Orders AS O
          INNER JOIN Sales.OrderDetails AS OD
            ON O.orderid = OD.orderid
            AND O.orderdate >= '20160101' )
    ON C.custid = O.custid
  CROSS APPLY ( VALUES( CONCAT(C.country, N'.' + C.region, N'.' + C.city),
                        OD.qty * OD.unitprice * (1 - OD.discount) )
              ) AS A(custlocation, val)
WHERE A.custlocation IN (N'Spain.Madrid', N'France.Paris', N'USA.WA.Seattle')
GROUP BY C.custid, A.custlocation
HAVING COUNT( DISTINCT O.orderid ) <= 3;

SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid;

SELECT C.custid, C.companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid, C.companyname;

SELECT C.custid, MAX(C.companyname) AS companyname, MAX(O.orderid) AS lastorder
FROM Sales.Customers AS C
  INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
GROUP BY C.custid;

SELECT custid, orderid, val,
  val / SUM(val) OVER(PARTITION BY custid) AS pctcust
FROM Sales.OrderValues;

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(val) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(custtotal) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

SELECT custid, SUM(val) AS custtotal, SUM(val) / SUM(SUM(val)) OVER() AS pct
FROM Sales.OrderValues
GROUP BY custid;

SELECT custid, orderdate, SUM(val) AS daytotal,
  SUM(SUM(val)) OVER(PARTITION BY custid
                     ORDER BY orderdate
                     ROWS UNBOUNDED PRECEDING) AS runtotal
FROM Sales.OrderValues
GROUP BY custid, orderdate;

SELECT empid, COUNT(*) AS numorders
FROM Sales.OrderValues
WHERE orderdate >= '20160501'
GROUP BY empid
HAVING COUNT(*) <= 3;

SELECT empid, COUNT(*) AS numorders
FROM Sales.OrderValues
WHERE orderdate >= '20160501'
GROUP BY ALL empid
HAVING COUNT(*) <= 3;

SELECT empid, COUNT(tokeep) AS numorders
FROM Sales.OrderValues
  CROSS APPLY ( VALUES( CASE WHEN orderdate >= '20160501' THEN 1 END ) )
    AS A(tokeep)
GROUP BY empid
HAVING COUNT(tokeep) <= 3;

WITH C AS
(
  SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
      AS A(orderyear, ordermonth, orderday)
  GROUP BY orderyear, ordermonth, orderday

  UNION ALL

  SELECT orderyear, ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate)) )
      AS A(orderyear, ordermonth)
  GROUP BY orderyear, ordermonth

  UNION ALL

  SELECT orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
    CROSS APPLY ( VALUES(YEAR(orderdate)) ) AS A(orderyear)
  GROUP BY orderyear

  UNION ALL

  SELECT NULL AS orderyear, NULL AS ordermonth, NULL AS orderday, COUNT(*) AS numorders
  FROM Sales.Orders
)
SELECT orderyear, ordermonth, orderday, numorders
FROM C
ORDER BY
  CASE WHEN orderyear IS NOT NULL THEN 0 ELSE 1 END, orderyear,
  CASE WHEN ordermonth IS NOT NULL THEN 0 ELSE 1 END, ordermonth,
  CASE WHEN orderday IS NOT NULL THEN 0 ELSE 1 END, orderday;

SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
    AS A(orderyear, ordermonth, orderday)
GROUP BY GROUPING SETS
(
  (orderyear, ordermonth, orderday),
  (orderyear, ordermonth),
  (orderyear),
  ()
)
ORDER BY
  GROUPING(orderyear), orderyear,
  GROUPING(ordermonth), ordermonth,
  GROUPING(orderday), orderday;

SELECT orderyear, ordermonth, orderday, COUNT(*) AS numorders
FROM Sales.Orders
  CROSS APPLY ( VALUES(YEAR(orderdate), MONTH(orderdate), DAY(orderdate)) )
    AS A(orderyear, ordermonth, orderday)
GROUP BY ROLLUP(orderyear, ordermonth, orderday)
ORDER BY
  GROUPING(orderyear), orderyear,
  GROUPING(ordermonth), ordermonth,
  GROUPING(orderday), orderday;