SQL Server Group by Month : Grupowanie danych po miesiącach i kwartałach

Month GroupCzasami dla statystyk trzeba pogrupować dane według miesięcy bądź kwartałów. Sprawę utrudnia fakt ,że kolumny przetrzymujące date (DATETIME) zawierają nie tylko miesiące ,ale także rok, dzień oraz godzinie.

Grupowanie po miesiącu  wymaga więc trochę większego wysiłku.

Naturalnie istnieje wiele  sposobów na rozwiązanie tego problemu.

Oto tymczasowa tabelka zawierająca wartość jak i datę, po której będziemy grupować.

CREATE TABLE #temp
    (
    ID INT NOT NULL PRIMARY KEY,
    DateExample DATETIME NOT NULL,
    Value INT NOT NULL
    )

    INSERT INTO #temp VALUES
    (1,'2012-01-12',1),(2,'2012-06-21',6),
    (3,'2011-09-03',9),(4,'2012-11-12',11),
    (5,'2012-12-25',21),(6,'2012-03-06',32),
    (7,'2012-01-22',22),(8,'2011-06-11',11),
    (9,'2011-09-23',23),(10,'2012-11-02',32),
    (11,'2011-12-01',41),(12,'2012-03-26',52)  

Po wykonaniu przykładów. Nie zapomnij usunąć tej tymczasowej tabelki.

DROP TABLE #temp

Na początku najlepiej byłoby wspomnieć o metodach, które mogą zwrócić zły rezultat.

SELECT Month(DateExample) AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue
    FROM #temp
    GROUP BY Month(DateExample)

Jeżeli zbiór wszystkich dat zawiera się w tym samym roku to nie ma problemu z tym rozwiązaniem. Przeciwnym wypadku dane będą grupowane po miesiącu bez uwzględnienia roku.

W przykładzie powyżej dane są pogrupowane po numerze miesiąca.

Jak widać dane z grudnia 2012 i grudnia 2011 zostały ze sobą scalone. Co może być niezamierzonym rezultatem.

SELECT DateName( month , DateAdd( month , Month(DateExample) , 0 ) - 1 ) AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue
    FROM #temp
    GROUP BY DateName( month , DateAdd( month , Month(DateExample) , 0 ) - 1 )

Naturalnie grupowanie po nazwach miesiącach tworzy dokładnie ten sam problem.

Ogólnie zwracanie nazw miesięcy po stronie klienta jest zły pomysłem. Niszczy to elastyczność systemu, ponieważ baza danych z góry wymusza format wyjściowy danych.

Jak trzeba to zrobić

Dodając rok do polecenia GROUP BY rozwiążesz problem z grupowanie po numerze miesiąca.

SELECT Year(DateExample) AS [Year], Month(DateExample) AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue
    FROM #temp
    GROUP BY Year(DateExample), Month(DateExample)

Teraz dane z różnych lat nie będą ze sobą mieszane i scalane.

Nie próbuj nawet po stronie SQL Server łączyć kolumn reprezentujących rok i miesiąc  „MM-YYYY”. Przykładowo  klient .NET-owy z łatwością poradzi sobie z zadaniem łączenia tych informacji.

Jeśli jednak to ciebie nie zadowala spokojnie istnieją jeszcze inne rozwiązania.

SELECT dateadd(month, datediff(month, 0, DateExample),0) AS [Year-Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue
    FROM #temp
    GROUP BY dateadd(month, datediff(month, 0, DateExample),0) 

Ta technika zaokrągli date do pierwszego dnia miesiąca. Co oznacza ,że grupując dane  po tych datach grupujesz je tak naprawdę po ich miesiącach.

Jest to miłe rozwiązanie, ponieważ informacja o roku i o miesiącu jest połączona w jednej kolumnie.

Jeśli z jakiegoś powodu musi jeszcze posortować dane po dacie bądź je JOIN-ować  to to rozwiązanie jest dla ciebie. Wydaje się być ono najbardziej elastycznym rozwiązaniem, ponieważ jest zwracany  naturalny typ dla daty DATETIME. Klient nie musi wykonywać żadnego formatowania.

Istnieją jeszcze inne rozwiązania.

SELECT datediff(month,0,DateExample) AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue
    FROM #temp
    GROUP BY datediff(month,0,DateExample)

Ta formuła pokazuje grupowanie po miesiącu według offsetu miesiąca z bazowej daty. Te dane dla człowieka nie mają żadnej wartości.

W praktyce jednak gdybyś dodała po stronie klienta tą liczbę miesięcy do bazowej daty otrzymałbyś rok i miesiąc, po którym dane są grupowane.

Oto jak można to zrobić po stronie SQL Servera.

SELECT dateadd(month, T.DateExample,0) as [Month], SUM(T.Value)
    FROM
    (
    SELECT datediff(month,0, DateExample) AS DateExample, Value
    FROM #temp
    ) AS T
    GROUP BY T.DateExample

Jedyną zaletą tej metody jest fakt ,że w przypadku operacji JOIN SQL Server mniej się napoci. Łączenie danych typu INTEGER wykonuje się dużo szybciej niż DATETIME.

Grupowanie po kwartałach

Grupowanie po latach, tygodniach i kwartałach opiera się na tych samych pomysłach co wcześniej. Oto grupowanie danych po kwartałach.

SELECT dateadd(quarter, datediff(quarter, 0, DateExample),0)  AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue FROM #temp
    GROUP BY dateadd(quarter, datediff(quarter, 0, DateExample),0) 

Po prostu zaokrąglij swoje dane typu DATETIME do swoich własnych  potrzeb.

Daty zaczynają się od daty startowej każdego kwartału. (2011-10-01 , 2012-01-01). Jeśli chcemy otrzymać daty z końcami kwartałów nic bardziej trudnego wystarczy tylko trochę zmodyfikować zapytanie.

SELECT dateadd(quarter, datediff(quarter, 0, DateExample) + 1, 0) -1  AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue FROM #temp
    GROUP BY dateadd(quarter, datediff(quarter, 0, DateExample) + 1, 0) -1

Oczywiście układ danych po grupowaniu nie uległ zmianie.

Sztuczka z offsetem też tutaj może być wykorzystana.

SELECT datediff(quarter,0,DateExample) AS [Month],
    SUM(VALUE) AS SUMValue,MAX(VALUE) AS MAXValue,MIN(VALUE) AS MINValue FROM #temp
    GROUP BY datediff(quarter,0,DateExample)

Jaką metodę wybierz ,zależy tylko od ciebie. Pamiętaj krótko i prosto ,a formatowanie zostaw po stronie klienta.