Pchełki SQL: suma bieżąca

https://xpil.eu/NFWhx 📋

Trzeci najstarszy zawód świata (zaraz po złodzieju-kombinatorze, wspomnianym niedawno) to prawdopodobnie księgowy. No bo skoro już złodziej-kombinator nazłodzieił i nakombinował, to chciałby teraz wiedzieć, ile tego ma. A że tępy jest jak wojskowy nóż kuchenny, wynajmie sobie w tym celu księgowego.

A taki księgowy to nic, tylko by słupki liczył. A w tych słupkach - sumy bieżące.

Sumy bieżące, czyli wyliczenie ile mamy czegoś-tam łącznie od początku - nie wiem, roku? tygodnia? świata? W każdym razie od jakiegoś początku. Na przykład suma pensji brutto od początku roku podatkowego - żeby lepiej wyliczyć próg podatku. Albo suma zjedzonych od poniedziałku chrabąszczy, żeby lepiej wyliczyć sumę zjedzonych od poniedziałku chrabąszczy. I tak dalej.

Sumy takie liczy się - na ogół - w jakichś grupach. A więc, dajmy na to, osobno chrabąszcze, osobno dżdżownice. Ewentualnie osobno podatek dochodowy, a osobno korporacyjny. I osobno pensję pana Zdzicha, a osobno pani Reginy. Wiadomo.

Ponieważ już nadałem dzisiejszemu wpisowi status Pchełki SQL, wypadałoby teraz skończyć to teoretyzowanie i przejść do samego gęstego. Zanim jednak umieszczę kod właściwy, jeszcze króciutki wstęp, co właściwie robimy.

Otóż robimy tak: mamy kalendarz (rapotem trzy miesiące, od stycznia do marca 2015). Mamy pięciu klientów (o barwnych nazwach typu Klient 1 czy Klient 3 - można dać się wyszaleć wyobraźni). Klienci przeprowadzają transakcje. I chcemy zrobić listę wszystkich transakcji przeprowadzonych przez wszystkich klientów, ułożoną według klienta i daty, wraz z tygodniowymi sumami bieżącymi dla każdego klienta.

Żeby nam się co poniektórzy Czytelnicy nie czepiali (tak, jak to miało miejsce ostatnim razem), skorzystamy z pomocy CTE w celu wygenerowania losowych danych wejściowych.

A oto i kod:

DECLARE @Data1 DATETIME= '1-Jan-2015' , @Data2 DATETIME= '31-Mar-2015';
WITH    cte_Data
          AS ( SELECT   @Data1 data ,
                        DATEPART(WEEK, @Data1) tydzien
               UNION ALL
               SELECT   cte_Data.data + 1 ,
                        DATEPART(WEEK, cte_Data.data + 1)
               FROM     cte_Data
               WHERE    cte_Data.data <= @Data2
             ),
        cte_Klient
          AS ( SELECT   1 AS Klient_ID, 'Klient 1' AS Klient
               UNION ALL   SELECT   2 , 'Klient 2'
               UNION ALL   SELECT   3 , 'Klient 3'
               UNION ALL   SELECT   4 , 'Klient 4'
               UNION ALL   SELECT   5 , 'Klient 5'
             ),
        cte_Transakcje
          AS ( SELECT   1 AS Transakcja_ID ,
                        FLOOR(RAND() * 5 + 1) AS Klient_ID ,
                        DATEADD(DAY, RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY,@Data1, @Data2) ),@Data1) AS Data ,
                        FLOOR(RAND() * 1000) AS kwota
               UNION ALL
               SELECT   cte_Transakcje.Transakcja_ID + 1 ,
                        FLOOR(RAND(CHECKSUM(NEWID())) * 5 + 1) AS Klient_ID ,
                        DATEADD(DAY, RAND(CHECKSUM(NEWID())) * ( 1 + DATEDIFF(DAY, @Data1, @Data2) ), @Data1) AS Data ,
                        FLOOR(RAND(CHECKSUM(NEWID())) * 100) AS kwota
               FROM     cte_Transakcje
               WHERE    cte_Transakcje.Transakcja_ID <= 1000
             )
    SELECT  T.Transakcja_ID ,
            K.Klient ,
            T.Data ,
            D.tydzien ,
            T.kwota ,
            SUM(T.kwota) OVER ( PARTITION BY T.Klient_ID, D.tydzien ORDER BY T.Data ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS kwota_razem_kt,
            SUM(T.kwota) OVER ( PARTITION BY T.Klient_ID ORDER BY T.Data ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS kwota_razem_k
    FROM    cte_Transakcje T
            JOIN cte_Data D ON D.data = T.Data
            JOIN cte_Klient K ON K.Klient_ID = T.Klient_ID
    ORDER BY K.Klient ,
            T.Data
OPTION  ( MAXRECURSION 1000 );

Samo gęste widzimy w okolicach ostatniego SELECT, gdzie wykonujemy sumowanie bieżące. Magia tkwi (po raz kolejny) w operatorze PARTTION BY, który od wersji SQL Server 2012 stał się dostępny również dla operatora SUM bez grupowania (proszę zauważyć, że w całym zapytaniu nigdzie nie ma operatora GROUP BY). Dla "starych" SQL-owców może się to wydawać dziwne - jak to, suma bez grupowania? Tymczasem za grupowanie odpowiada tutaj właśnie operator PARTITION BY, który wstępnie dzieli dane na grupy według tygodni i klientów. Następnie mamy ORDER BY, który sortuje dane w każdej grupie rosnąco według daty, wreszcie najciekawsze - operator wybierający wiersze z grupy, czyli ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Oznacza to, że do operacji sumowania każdorazowo trafią wszystkie rekordy z bieżącej grupy, począwszy od pierwszego rekordu aż do bieżącego.

Przypomnę jeszcze, że dotychczas takie sumowanie robiło się na ogół w całkiem inny sposób, mianowicie wyliczało się numer kolejny każdego wiersza w grupie za pomocą operatora ROW_NUMBER(), a następnie wykonywało się tzw. SELF JOIN (czyli JOIN z kopią tej samej tabeli) z równoczesnym grupowaniem - efekt był co prawda poprawny, jednak wydajność takiego zapytania spadała proporcjonalnie do kwadratu ilości rekordów, a więc metoda kompletnie nie nadawała się do dużych zbiorów danych. Tu natomiast, ponieważ nie ma GROUP BY ani żadnych JOIN-ów do kopii tej samej tabeli, wydajność jest znacząco lepsza.

https://xpil.eu/NFWhx 📋