Porównanie trzech sposobów na PIVOT w TSQL

Dane w relacyjnych bazach danych posiadają formę tabelaryczną tj. składają się z kolumn oraz wierszy. Zdarza się jednak dosyć często, że chcemy wiersze zamienić na kolumny lub kolumny na wiersze – operację taką zwyczajowo nazywamy piwotowaniem. W ramach tego artykułu chciałbym przedstawić trzy różne sposoby na osiągnięcie zamierzonego rezultatu. Oczywiście nie są to wszystkie możliwe sposoby – znajdą się inne metody na odwrócenie wyniku zarówno w samym kodzie TSQL, kodzie zewnętrznym czy w SSIS – jednakże w ramach tego artykułu chciałbym się ograniczyć do tych trzech metod, które mimo wszystko wydają się najprostsze i najbardziej popularne. Zapraszam do lektury.

Na samym początku przedstawmy nasz zestaw testowy którym będzie następujące zapytanie:

USE WideWorldImporters
GO
SELECT il.InvoiceID,
       il.InvoiceLineID,
       si.Size,
       c.ColorName,
       SUM(il.Quantity) AS Qty
FROM Sales.InvoiceLines il
     JOIN Warehouse.StockItems si ON si.StockItemID = il.StockItemID
     JOIN Warehouse.Colors c ON si.ColorID = c.ColorID
GROUP BY il.InvoiceID,
         il.InvoiceLineID,
         si.size,
         c.ColorName;

Przypuśćmy, że naszym celem jest stworzenie czegoś na wzór matrixa gdzie Id faktury, linii faktury oraz rozmiar będą znajdować się po stronie wierszy, w kolumnach będzie kolor, a ilość będzie odpowiednio wyliczona na przecięciach tych składowych. Cała koncepcja wygląda następująco:

Pierwszym sposobem jaki postaram się przedstawić jest chyba ten najbardziej oczywisty, a mianowicie użycie składni PIVOT dostępnej w TSQL. Składnia ta jest specyficzna i zazwyczaj trzeba się jej chwilę przyjrzeć aby dobrze ją zaimplementować. W naszym przypadku zapytanie źródłowe otoczymy CTE co zwiększy jego czytelność i dopiero na rezultacie tego działania wykonamy pożądaną operację:

WITH cte
     AS (
     SELECT il.InvoiceID,
            il.InvoiceLineID,
            si.Size,
            c.ColorName AS Color,
            SUM(il.Quantity) AS Quantity
     FROM Sales.InvoiceLines il
          JOIN Warehouse.StockItems si ON si.StockItemID = il.StockItemID
          JOIN Warehouse.Colors c ON si.ColorID = c.ColorID
     GROUP BY il.InvoiceID,
              il.InvoiceLineID,
              si.size,
              c.ColorName)
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            ISNULL(Red,0) AS Red
            ,ISNULL(Black,0) AS Black
            ,ISNULL(White,0) AS White
            ,ISNULL(Green,0) AS Green
            ,ISNULL(Purple,0) AS Purple
     FROM
     (
         SELECT InvoiceID,
                InvoiceLineID,
                Size,
                Quantity,
                Color
         FROM cte
     ) AS invoices PIVOT(SUM(Quantity) FOR Color IN([Red],
                                                    [Black],
                                                    [White],
                                                    [Green],
                                                    [Purple])) AS pvt;

Składnia wygląda tak, że rozszerzamy nasze podzapytanie (w tym przypadku o nazwie invoices) o PIVOT sumy dla konkretnych kolorów. Rezultat jest dokładnie tym czego oczekiwaliśmy i przedstawia się następująco:

Mimo, że całe zapytanie nie wydaje się zbyt skomplikowane to takowe w gruncie rzeczy jest – wystarczy rzucić okiem na plan zapytania, który prezentuje się całkiem okazale:

W każdym bądź razie cel osiągnięty stosunkowo niewielkim nakładem sił. Kolejną metodą na osiągnięcie tego samego rezultatu będzie metoda roboczo przeze mnie nazwana SUMIF. Będzie się ona opierała o sumy warunkowe tj. będziemy sumować tylko te wiersze, które są równe określonemu kolorowi. W poniższym przykładzie użyłem funkcji IIF ale w praktyce możemy również użyć CASE, które w gruncie rzeczy będzie wykonywany wewnętrznie w ten sam sposób. Zapytanie w tej metodzie wygląda następująco:

WITH cte
     AS (
     SELECT il.InvoiceID,
            il.InvoiceLineID,
            si.Size,
            c.ColorName AS Color,
            SUM(il.Quantity) AS Quantity
     FROM Sales.InvoiceLines il
          JOIN Warehouse.StockItems si ON si.StockItemID = il.StockItemID
          JOIN Warehouse.Colors c ON si.ColorID = c.ColorID
     GROUP BY il.InvoiceID,
              il.InvoiceLineID,
              si.size,
              c.ColorName)
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(IIF(Color = 'Red', Quantity, 0)) AS Red,
            SUM(IIF(Color = 'Black', Quantity, 0)) AS Black,
            SUM(IIF(Color = 'White', Quantity, 0)) AS White,
            SUM(IIF(Color = 'Green', Quantity, 0)) AS Green,
            SUM(IIF(Color = 'Purple', Quantity, 0)) AS Purple
     FROM cte
	GROUP BY
	InvoiceID,
            InvoiceLineID,
            Size;

Zapis stosunkowo prosty i w moim mniemaniu dużo czytelniejszy niż poprzednio. Plan wykonania również prezentuje się lepiej jeśli chodzi o poziom skomplikowania – co prawda mogli byśmy jeszcze w niektórych aspektach go poprawić ale póki co zostawimy go tak jak jest:

Trzecia metoda o jakiej chciałbym wspomnieć jest metoda o roboczej nazwie Monster Query(zaraz się przekonacie dlaczego), która polega na dołączaniu tego samego zbioru danych tylko tym razem z innym kolorem. Zapytanie wygląda następująco (nie przeraźcie się:)):

WITH cte
     AS (
     SELECT il.InvoiceID,
            il.InvoiceLineID,
            si.Size,
            c.ColorName AS Color,
            SUM(il.Quantity) AS Quantity
     FROM Sales.InvoiceLines il
          JOIN Warehouse.StockItems si ON si.StockItemID = il.StockItemID
          JOIN Warehouse.Colors c ON si.ColorID = c.ColorID
     GROUP BY il.InvoiceID,
              il.InvoiceLineID,
              si.size,
              c.ColorName),
     cteRed
     AS (
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(Quantity) AS Red
     FROM cte
     WHERE Color = 'Red'
     GROUP BY InvoiceID,
              InvoiceLineID,
              Size),
     cteBlack
     AS (
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(Quantity) AS Black
     FROM cte
     WHERE Color = 'Black'
     GROUP BY InvoiceID,
              InvoiceLineID,
              Size),
     cteWhite
     AS (
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(Quantity) AS White
     FROM cte
     WHERE Color = 'White'
     GROUP BY InvoiceID,
              InvoiceLineID,
              Size),
     cteGreen
     AS (
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(Quantity) AS Green
     FROM cte
     WHERE Color = 'Green'
     GROUP BY InvoiceID,
              InvoiceLineID,
              Size),
     ctePurple
     AS (
     SELECT InvoiceID,
            InvoiceLineID,
            Size,
            SUM(Quantity) AS Purple
     FROM cte
     WHERE Color = 'Purple'
     GROUP BY InvoiceID,
              InvoiceLineID,
              Size)
     SELECT cte.InvoiceID
            ,cte.InvoiceLineID
            ,cte.Size
            ,ISNULL(cteRed.Red,0) AS Red
            ,ISNULL(cteBlack.Black,0) AS Black
            ,ISNULL(cteWhite.White,0) AS White
            ,ISNULL(cteGreen.Green,0) AS Green
            ,ISNULL(ctePurple.Purple,0) AS Purple
     FROM cte
          LEFT JOIN cteRed ON cte.InvoiceId = cteRed.InvoiceID
                              AND cte.InvoiceLineID = cteRed.InvoiceLineID
          LEFT JOIN cteBlack ON cte.InvoiceId = cteBlack.InvoiceID
                                AND cte.InvoiceLineID = cteBlack.InvoiceLineID
          LEFT JOIN cteWhite ON cte.InvoiceId = cteWhite.InvoiceID
                                AND cte.InvoiceLineID = cteWhite.InvoiceLineID
          LEFT JOIN cteGreen ON cte.InvoiceId = cteGreen.InvoiceID
                                AND cte.InvoiceLineID = cteGreen.InvoiceLineID
          LEFT JOIN ctePurple ON cte.InvoiceId = ctePurple.InvoiceID
                                 AND cte.InvoiceLineID = ctePurple.InvoiceLineID;

Rezultat znów po raz kolejny jest poprawny:

Plan zapytania podobnie jak samo zapytanie jest bardzo mocno skomplikowany i rozbudowany (wiem, że jest on nieczytelny jednakże nie interesują nas jego konkretne operatory tylko pogląd co do poziomu skomplikowania)

Nie wiem jak u Was ale gdy widzę taki plan zapytania to na mojej twarzy pojawia się uśmiech. Pytacie jak to możliwe żeby zbudować coś takiego? Odpowiedź nie jest mi znana ale muszę Wam przyznać, że miałem okazję spotkać każde z tych podejść w praktyce z czego to ostatnie zmobilizowało mnie do napisania niniejszego artykułu. Porównajmy sobie każde z podejść pod kątem czasu wykonania, statystyk IO oraz kosztu.

Porównanie estymowanego kosztu przedstawia się następująco:

Zaskoczeni? Ja na początku trochę byłem ponieważ to co powinno być najlepiej zoptymalizowane do tego typu operacji czyli PIVOT okazał się najbardziej kosztowny! Kosztowniejszy nawet od monstrualnego zapytania z ostatniego przykładu. Najmniej kosztowny okazał się SUMIF.

Przejdźmy dalej i zobaczmy jak wygląda sprawa odczytu statystyk IO:

PIVOT

SUMIF

Monster Query:

Wnioski są dosyć oczywiste, nasze monstrualne zapytanie dało nam najwięcej odczytów, PIVOT oraz SUMIF miały podobne statystyki jednakże znów SUMIF okazał się najbardziej wydajnym rozwiązaniem. Ostatnią statystyką jaką sobie zbadamy jest oczywiście czas wykonania całego zapytania chociaż nie jest ona zbyt miarodajna z oczywistych względów to mogę powiedzieć, że różnica była na tyle nieznacząca pomiędzy każdym z tych zapytań, że możemy uznać ją za w przybliżeniu taką samą.

Która zatem z tych metod jest najlepsza? Odpowiedź jak zawsze jest niejednoznaczna bo zależy pod jakim kątem je porównujemy. Jeśli chodzi o wydajność to z powyższych testów i mojego osobistych doświadczeń wynika, że to SUMIF daje najlepsze rezultaty, a podejście zaprezentowane jako “Monster Query” najgorsze. Pod kątem czytelności również powiedziałbym, że ranking kształtuje się podobnie. Co ciekawe w zdecydowanej ilości przypadków funkcja PIVOT działała niewiele ale jednak gorzej od SUMIF co powiem szczerze było dla mnie małą niespodzianką. W przypadku operacji odwrotnej czyli UNPIVOT mamy już tylko dwa podstawowe podejścia, a mianowicie składnię UNPIVOT i zestaw poleceń UNION, których porównanie postaram się przedstawić w ramach osobnego artykułu. Na ten moment polecam testowanie PIVOT oraz SUMIF w przypadku operacji przestawiania kolumn i wierszy i wybrania tego podejścia, które daje nam najlepsze rezultaty i najlepszą czytelność kodu.