Funkcje analityczne w TSQL – ROLLUP, CUBE, GROUPING_SETS

FunkcjeAnalityczneWtsql_00

Transact-SQL daje nam dosyć dużo możliwości związanych z agregacją danych. Większość deweloperów namiętnie używa standardowych wyrażeń grupowanych, funkcji okna i tym podobnych, ale niewielu zdaje sobie sprawę z funkcji analitycznych wbudowanych w nasz ulubiony język zapytań. Wyrażenia te możemy zgrupować pod pojęciem funkcji analitycznych – w ramach niniejszego artykułu powiemy sobie o trzech niezwykle ciekawych funkcjach wchodzących w ich skład tj. ROLLUP, CUBE oraz GROUPING SETS.

Na sam początek zajmiemy się funkcją ROLLUP. Pierwszą niezwykle istotną informacją jest to, że ROLLUP jest rozszerzeniem klauzuli GROUP BY- podstawowa składnia przedstawia się następująco:

Jak czytać coś takiego? W rezultacie otrzymamy sumę pola D na następujących poziomach:

    • ABC
    • AB
    • A

Ponadto wyświetlona zostanie sumę całkowita pola D.  Zapowiada się fajnie co? Sprawdźmy to na realnym przykładzie na podstawie AdventureWorksDW2012:

Rezultatem powyższego zapytania były podsumowania pola Sales Amount na następujących poziomach:

  • kategorii, podkategorii i koloru produktu
  • kategorii, podkategorii:

result

  • kategorii:

result

  •  wszystkich pól(suma całkowita):

result

Fajne prawda? Część raportów można obsłużyć właśnie w ten sposób bez konieczności pisania bardziej skomplikowanych wyrażeń w narzędziu raportowym. Kolejną funkcjonalnością, którą się zajmiemy jest CUBE, która jest bardzo podobna do ROLLUP ale daje trochę więcej możliwości ponieważ w rezultacie daje wszystkie możliwe kombinacje dla przekazanych jako parametr pól. Składnia dla tego polecenia przedstawia się następująco:

Powyższe zapytanie pozwoli nam otrzymać sumę na następujących poziomach: ABC,AB,AC,A,BC,B,C. Sprawdźmy na analogicznym do poprzedniego przykładzie:

Po wykonaniu zapytania otrzymaliśmy następujące poziomy:

  • kategorii,podkategorii, koloru
  • podkategorii, koloru
  • koloru

result

  • kategorii, koloru

result

  • kategorii

result

  • podkategorii

result

  • kategorii, podkategorii

result

Jak widać CUBE daje nam wszystkie możliwe poziomy co w niektórych przypadkach może być niezwykle użyteczne – jednak powoduje, iż nasz rezultat zwraca naprawdę dużo wierszy co można wywnioskować po powyższych przykładach gdzie przedstawiłem tylko wybrane z nich.

Zarówno CUBE jak i ROLLUP w dużej mierze polegają na wartości NULL co natomiast gdy, któryś z naszych poziomów naprawdę ma wartość NULL? Możemy sobie z tym problemem radzić dwojako – po pierwsze możemy zastąpić wartości niezidentyfikowane używając ISNULL czy też COALESCE lub tez użyć specjalnie dedykowanej  do tego celu funkcji GROUPING_ID. Jak to zrobić? Wystarczy, że w sekcji SELECT dodamy funkcję GROUPING_ID i jako parametr podać poziom, który nas interesuje. Dzięki temu otrzymamy dodatkową kolumnę, która zwróci nam liczbę porządkową naszego podsumowania jeżeli dany wiersz jest podsumowaniem na poziomie, który podaliśmy jako argument:

result

Numer w naszej nowej kolumnie wskazuje poziom agregacji gdzie 0 odpowiada w naszej notacji zapisowi ABC, 1 AB itd.

Na koniec chciałbym wspomnieć o funkcji GROUPING_SETS, która daje nam możliwość jawnego wskazania jakie podsumowania będą obliczone:

Podsumowanie otrzymamy oczywiście na poziomie przekazanym jako parametr czyli:

oraz agregację na poziomie wszystkich wierszy co symbolizują nawiasy (). Tak więc dzięki GROUPING SETS mamy pełną kontrolę nad tym co będzie sumowane.

Jak widać funkcje analityczne w TSQL nie odbiegają od standardu kodowania zapytań i są bardzo proste w użyciu – warto je stosować gdyż są przejrzyste i dają spore możliwości szczególnie pod kątem raportowania i analityki szczególnie gdy chcemy uniknąć agregacji po stronie narzędzia. Warto również wspomnieć o tym, że w powyższych przykładach zastosowałem funkcję agregacji jaką była suma jednakże nic nie stoi na przeszkodzie aby użyć innych agregatów w zależności od potrzeby.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

5 Comments

  1. Piotr Ziuziański

    Myślę, że warto dodać, że stosowanie ROLLUP ma sens wtedy, kiedy dla naszych grupowanych kolumn widoczna jest hierarchia – w takim przypadku stosowanie CUBE (wszystkie możliwe kombinacje) jest trochę bez sensu.
    Poza tym, GROUPING SETS jest w standardzie ISO/ANSI, a ROLLUP i CUBE nie 🙂 Myślę, że w podręczniku Itzika Ben-Gana jest dobrze wytłumaczone, że ROLLUP i CUBE to takie… skrócone formy zapisu konkretnych GROUPING SETS 🙂
    Zastanawiam się także nad funkcją GROUPING_ID(). Wydawało mi się, że kolejne identyfikatory nadawane są jako wyrażenie (2^n) – 1, ale zaskoczyły mnie w Twoim przykładzie identyfikatory 5, 6. Znasz może jakieś wyjaśnienie?
    Pozdrawiam!

    Reply
    1. Adrian Chodkowski (Post author)

      Funkcje te są tak naprawdę tym co mamy domyślnie w tabeli przestawnej Excela – podsumowania na wszystkich możliwych poziomach 🙂 Jakbyśmy chcieli wyrzucać do jakiegoś narzędzia czy np. do pliku płaskiego dane o różnych poziomach szczegółowości (co czasem się zdarza) to możemy ucieszyć się z gotowego rozwiązania jakim są te funkcje albo składać UNIONem różne zapytania z różną ilością kolumn GROUP BY.

      Sam osobiście musiałem coś takiego zrobić po tym jak ktoś analizował dane w Excelu i zaczytywał dane z pliku csv wygenerowanego z SAPa (było tam bardzo dużo podsumowań wierszy itp). Gdy nadszedł czas na to aby ten Excel zaczął zaczytywać dane z hurtowni danych miałem możliwość przepisania kodu VBA (sic!) albo dostarczenie pliku o takiej strukturze – wtedy CUBE był rozwiązaniem 🙂

      Jeśli chodzi o standardy to nie wspominałem o nich bo szczerze powiem, że sam już nie wiem co jest tym standardem a co nie 🙂 Kod TSQL i tak nie będzie mobilny bo i tak w każdej bazie danych masowo używane są zapisy specyficzne dla danego dostawcy bazy danych – tak więc zwyczajnie przestałem się tym przejmować:)

      Co do GROUPING_ID to numerki dodawane są standardowo N+1 – skąd u Ciebie wzięło się (2^n)-1? Dla przykładu podaje zapytanie na WideWorldImportersDW – przetestuj :):

      SELECT DSI.[Stock Item],
      DSI.Color,
      DSI.Brand,
      DSI.Size,
      GROUPING_ID(DSI.[Stock Item],
      DSI.Color,
      DSI.Brand,
      DSI.Size) AS GROUP_ID,
      SUM(F.Quantity) AS Quantity
      FROM [Fact].[Sale] AS F
      JOIN [Dimension].[Stock Item] AS DSI ON DSI.[Stock Item Key] = F.[Stock Item Key]
      GROUP BY CUBE(DSI.[Stock Item], DSI.Color, DSI.Brand, DSI.Size)
      ORDER BY GROUP_ID;

      Reply
      1. Piotr Ziuziański

        https://youtu.be/8NIXZL-EROg?list=PL08903FB7ACA1C2FB
        Tutaj jest odpowiedź na Twoje pytanie skąd mi się wzięło binarne obliczanie dla grouping_id 🙂

        Reply
        1. Adrian Chodkowski (Post author)

          No rozumiem kwestie binarne jak najbardziej ale nie rozumiem pytania skąd się u mnie wzięły poziomy 5 i 6:) Numery te również można otrzymać w takim zapisie czyż nie? Liczba 110 oraz 101.

          Reply
          1. Piotr Ziuziański

            Faktycznie, po znalezieniu tego wytłumaczeniu na YT wszystko stało się jasne 🙂 ciekawe ile jeszcze tajemnic skrywa stary poczciwy SQL Server 😉

Leave a Comment

Your email address will not be published. Required fields are marked *