По материалам статьи Craig Freedman: PIVOT Query Plans

В предыдущей статье был дан обзор оператора PIVOT. В этой статье рассмотрим планы запросов при использовании оператора PIVOT. Как мы далее увидим, SQL Server создаёт простой план запроса, который, по сути, представляет собой вариацию плана с агрегацией.

Давайте воспользуемся запросами из предыдущей статьи:

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
SELECT [2005], [2006], [2007]
FROM (SELECT Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

Для этого запроса создаётся следующий план:

   |--Compute Scalar(DEFINE:(
                 [Expr1006]=CASE WHEN [Expr1024]=(0) THEN NULL ELSE [Expr1025] END,
                 [Expr1007]=CASE WHEN [Expr1026]=(0) THEN NULL ELSE [Expr1027] END,
                 [Expr1008]=CASE WHEN [Expr1028]=(0) THEN NULL ELSE [Expr1029] END))
       |--Stream Aggregate(DEFINE:(
                      [Expr1024]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2005) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1025]=SUM(CASE WHEN [Sales].[Yr]=(2005) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1026]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2006) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1027]=SUM(CASE WHEN [Sales].[Yr]=(2006) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1028]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2007) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1029]=SUM(CASE WHEN [Sales].[Yr]=(2007) THEN [Sales].[Sales] ELSE NULL END)))
            |--Table Scan(OBJECT:([Sales]))

Это всего лишь обычный план запроса со скалярной агрегацией! Он применяет агрегат SUM для каждого года. Как это свойственно всем агрегатам SUM, вычисляется сумма и не явно подсчитывается количество строк. Если счетчик равен нулю, план запроса возвращает NULL, в противном случае он возвращает сумму (эту логику обеспечивает Compute Scalar).

Единственный нюанс заключается в том, что агрегат SUM фактически вычисляется с помощью оператора CASE, который фильтром отбирает те строки, которые соответствуют году, за который нужно суммировать продажи. Оператор CASE возвращает значение столбца Sales для тех строк, которые соответствуют заданному году, и значения NULL для всех остальных строк. Чтобы чётко себе представить, что происходит, можно просмотреть на результаты использования операторов CASE без агрегирования:

SELECT EmpId, Yr,
    CASE WHEN Yr = 2005 THEN Sales END AS [2005],
    CASE WHEN Yr = 2006 THEN Sales END AS [2006],
    CASE WHEN Yr = 2007 THEN Sales END AS [2007]
FROM Sales

EmpId       Yr          2005                  2006                  2007
----------- ----------- --------------------- --------------------- ---------------------
1           2005        12000.00              NULL                  NULL
1           2006        NULL                  18000.00              NULL
1           2007        NULL                  NULL                  25000.00
2           2005        15000.00              NULL                  NULL
2           2006        NULL                  6000.00               NULL
3           2006        NULL                  20000.00              NULL
3           2007        NULL                  NULL                  24000.00
2           2007        NULL                  NULL                  NULL

При вычислении сумм по каждому году план запроса основывается на том факте, что агрегатные функции отбрасывают значения NULL; то есть значения NULL не включаются в результат. Хотя для SUM этот момент может показаться интуитивным, это более очевидно проявляется для агрегата COUNT:

CREATE TABLE T (A INT)
INSERT T VALUES(NULL)
-- Возвращает 1: число строк в таблице T
SELECT COUNT(*) FROM T
-- Возвращает 0: количество ненулевых значений столбца A
SELECT COUNT(A) FROM T

Обратите внимание, что можно было бы легко переписать исходный запрос иначе:

SELECT
    SUM(CASE WHEN Yr = 2005 THEN Sales END) AS [2005],
    SUM(CASE WHEN Yr = 2006 THEN Sales END) AS [2006],
    SUM(CASE WHEN Yr = 2007 THEN Sales END) AS [2007]
FROM Sales

Этот запрос получает почти такой же план запроса. Единственным отличием является дополнительный Compute Scalar для оценки операторов CASE.

   |--Compute Scalar(DEFINE:(
                 [Expr1004]=CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014] END,
                 [Expr1005]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END,
                 [Expr1006]=CASE WHEN [Expr1017]=(0) THEN NULL ELSE [Expr1018] END))
       |--Stream Aggregate(DEFINE:(
                      [Expr1013]=COUNT_BIG([Expr1007]), [Expr1014]=SUM([Expr1007]),
                      [Expr1015]=COUNT_BIG([Expr1008]), [Expr1016]=SUM([Expr1008]),
                      [Expr1017]=COUNT_BIG([Expr1009]), [Expr1018]=SUM([Expr1009])))
            |--Compute Scalar(DEFINE:(
                           [Expr1007]=CASE WHEN [Sales].[Yr]=(2005) THEN [Sales].[Sales] ELSE NULL END,
                           [Expr1008]=CASE WHEN [Sales].[Yr]=(2006) THEN [Sales].[Sales] ELSE NULL END,
                           [Expr1009]=CASE WHEN [Sales].[Yr]=(2007) THEN [Sales].[Sales] ELSE NULL END))
                 |--Table Scan(OBJECT:([Sales]))

Другим важным отличием между синтаксисом PIVOT с соответствующим планом и альтернативным синтаксисом с последним показанным планом запроса заключается в том, что запрос с PIVOT не будет выводить следующее ниже предупреждение о значениях NULL:

Warning: Null value is eliminated by an aggregate or other SET operation.

Также можно избежать появление этого предупреждения, предварительно выполнив следующий оператор:

SET ANSI_WARNINGS OFF

После этого уточнения, план для следующего запроса с многострочной операции PIVOT не должен вас удивить:

SELECT EmpId, [2005], [2006], [2007]
FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

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

   |--Compute Scalar(DEFINE:(
                 [Expr1007]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END,
                 [Expr1008]=CASE WHEN [Expr1027]=(0) THEN NULL ELSE [Expr1028] END,
                 [Expr1009]=CASE WHEN [Expr1029]=(0) THEN NULL ELSE [Expr1030] END))
       |--Stream Aggregate(GROUP BY:([Sales].[EmpId]) DEFINE:(
                      [Expr1025]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2005) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1026]=SUM(CASE WHEN [Sales].[Yr]=(2005) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1027]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2006) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1028]=SUM(CASE WHEN [Sales].[Yr]=(2006) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1029]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2007) THEN [Sales].[Sales] ELSE NULL END),
                      [Expr1030]=SUM(CASE WHEN [Sales].[Yr]=(2007) THEN [Sales].[Sales] ELSE NULL END)))
            |--Compute Scalar(DEFINE:([Sales].[EmpId]=[Sales].[EmpId]))
                 |--Sort(ORDER BY:([Sales].[EmpId] ASC))
                      |--Table Scan(OBJECT:([Sales]))

Для того, чтобы убедиться в том, что этот запрос ничем не отличается от любого другого запроса с GROUP BY, а также чтобы увидеть возможные альтернативные планы запросов, попробуйте создать кластерный индекс по колонке EmpId, чтобы убрать сортировку, или добавьте подсказку оптимизатору OPTION(HASH GROUP), которая заставит оптимизатор использовать хеш-агрегат.

План запроса с оператором PIVOT