Agregaty kroczące

Rozważmy następującą sytuację: mamy w bazie danych rekordy zużycia jakiegoś zasobu oraz pulę do wykorzystania.

Na przykład rozliczamy jeden okres rozliczeniowy abonenta telefonu, ma on w ofercie jakąś ilość darmowych minut w każdym cyklu, więc gdzieś w bazie, w danych bilingowych w kolejnych rekordach ilości minut poszczególnych rozmów wchodzących w skład cyklu rozliczeniowego.

Interesująca nas część rekordu może się składać z dwóch kolumn: (id, minuty), gdzie większa wartość pola id oznacza późniejszy rekord. Chcemy napisać procedurę składowaną czy też funkcję, która w wyniku da nam zestawienie darmowych minut wykorzystanych w kolejnych rekordach, co z kolei w realnym systemie przełoży się na prawdziwy koszt kolejnych rozmów. Poniżej przykładowy wynik. Jego postać jest uzależniona od ilości minut w puli, tutaj pokazano ją  dla puli zawierającej 200 darmowych minut:

Id	Minuty	Darmowe	Płatne
1	17	17	0
2	19	19	0
3	16	16	0
4	12	12	0
5	28	28	0
6	30	30	0
7	9	9	0
8	4	4	0
9	19	19	0
10	25	25	0
11	17	17	0
12	6	4	2
13	18	0	18
14	13	0	13
15	8	0	8
16	14	0	14
17	21	0	21
18	16	0	16
19	7	0	7
20	22	0	22
21	24	0	24
22	18	0	18
23	21	0	21
24	29	0	29

Zdefiniujmy w T-SQL prostą tabelę,  i wstawmy do niej dane tych przykładowych 24 rekordów:

CREATE TABLE Rozmowy
(
  Id int identity(1,1),
  Minuty int
 )

 INSERT INTO Rozmowy
 VALUES (17),(19),(16),(12),(28),(30),(9),(4),(19),(25),(17),(6),(18),(13),(8),(14),(21),(16),(7),(22),(24),(18),(21),(29)

Pierwsze podejście do problemu jest naturalne dla programisty języka wyższego poziomu. Załatwiamy sprawę w pętli – przy pomocy kursorów. W funkcji, której parametrem będzie pula minut, rekord po rekordzie generujemy wynikowy zbiór danych.

Czyli:

CREATE FUNCTION ZestawienieMinut
(@pula int)
RETURNS @tabela TABLE (Id int, Minuty int, Darmowe int, Płatne int)
AS
BEGIN
 DECLARE @id int
 DECLARE @minuty int
 DECLARE @suma int = 0  -- suma częściowa
 DECLARE @darmowe int = 0 -- wynikowa ilość darmowych minut

 -- Kursor przechodzący całą tabelę z rozmowami wg id
 DECLARE kursor CURSOR LOCAL FORWARD_ONLY FOR
	SELECT id, minuty FROM Rozmowy ORDER BY id

 OPEN kursor
 FETCH NEXT FROM kursor INTO @id, @minuty

 -- dopóki mamy co czytać
 WHILE @@FETCH_STATUS = 0
	 BEGIN

		IF @suma > @pula
			SET @darmowe = 0 -- już przekroczyliśmy pulę
		ELSE
			IF @minuty <= @pula - @suma  -- w bieżącym rekordzie jej nie przekraczamy
                SET @darmowe = @minuty
			ELSE						 -- bądź przekraczamy
				SET @darmowe = @pula - @suma

		-- tworzymy rekord wynikowy
		INSERT INTO @tabela values(@id, @minuty, @darmowe, @minuty - @darmowe)

		-- uaktualniamy sumę
		SET @suma = @suma + @minuty

		-- czytamy następny rekord
		FETCH NEXT FROM kursor INTO @id, @minuty
	END
  -- sprzątamy
  CLOSE kursor
  DEALLOCATE kursor
  RETURN
END

Działa? Działa. Ale mądry znajomy powiedział nam, że kursory to ZUO i że zawsze (no, może poza nielicznymi wyjątkowymi sytuacjami) da się zamiast kursora zastosować łączenie tabel i osiągnąć ten sam wynik na ogół z dużo większą wydajnością. Więc – jak tutaj sobie poradzić? Jeśli mamy charyzmę na takim poziomie, żeby przekonać wszystkich, że to na pewno jedna z tych wyjątkowych sytuacji, nie musimy robić nic. Jednak w przeciwnym wypadku, trzeba przedstawić inne rozwiązanie. Będą nim running totals, co podobno (tak mówi nasz lokalny firmowy ekspert baz danych) tłumaczy się na agregaty kroczące, moim zdaniem ładnie brzmi, nawet trochę mniej pośpiesznie niż oryginał (czyżby w Polsce stosowano zasadę A gentleman will walk, but never run?); jeśli chodzi o to, co mówią słowniki – moje poszukiwania zakończyły się znalezieniem banalnych sum częściowych.

Zatem, będziemy korzystać z agregatów, grupowania, sumowania. W rozwiązaniu z kursorami korzystaliśmy ze zmiennej @suma przechowującej sumę dotychczasowych przetworzonych wartości w rekordach i od niej uzależnialiśmy to, czy jeszcze darmowe minuty są, czy już minęły, czy może została ich tylko część ze wszystkich minut bieżącego rekordu. To właśnie ona będzie naszym agregatem kroczącym. Będziemy chcieli dla każdego rekordu zapytania w podzapytaniu taką sumę policzyć. Spróbujmy: dołączmy do naszej danej tabeli tę samą tabelę, tak żeby do każdego rekordu była dołączona suma wartości poprzednich rekordów oraz wartości z bieżącego rekordu. (Czemu dodajemy bieżący? Wolę dodać do sumy poprzednich wartość bieżącą niż się zastanawiać, co będzie, jeśli ja jestem pierwszym rekordem i w związku z tym moich suma poprzednich jest równa NULL). Czyli warunek łączenia będzie miał postać:

LEFT JOIN Rozmowy Rozmowy2 ON Rozmowy2.id<=Rozmowy.id

Grupować będziemy po całej wartości rekordu, do którego dołączamy, w końcu chcemy, żeby występował on raz. Czy oprócz sumy będziemy czegoś potrzebować? Tak. Jakiejś możliwości uzależnienia naszej wartości wynikowej od tej sumy i całkowitej puli minut. Dokładniej chodzi o zapisanie takiego warunku:

wynik = {jeśli suma poprzednich rekordów i mnie jest mniejsza od puli  ->    minuty;

jeśli suma poprzednich rekordów (beze mnie) jest większa lub równa puli -> 0;

a w przypadku pośrednim – pula – suma poprzednich rekordów (beze mnie)}

W podejściu iteracyjnym nie mieliśmy problemu, zmienne tymczasowe i jedna zagnieżdżona instrukcja if załatwiły sprawę. Tutaj kolejne instrukcje muszą być zastąpione wywołaniem jednej funkcji, jednak, jak się okazuje samej funkcji nie musimy pisać. T-SQL dysponuje konstrukcją case, która robi dokładnie to, o co nam chodzi – uzależnia wartość pola od wartości wyrażeń boolowskich.
W naszym przypadku dla pierwszego z wyliczanych pól konstrukcja będzie wyglądać tak:

CASE
	WHEN SUM(Rozmowy2.minuty) <= @pula
		THEN Rozmowy.minuty
	WHEN SUM(rozmowy2.minuty) - Rozmowy.Minuty >= @pula
		THEN 0
	ELSE
		@pula - (SUM(rozmowy2.Minuty) - Rozmowy.Minuty)
END as Darmowe

Drugie pole będzie wyglądało podobnie, jeśli chodzi o warunki, natomiast wartości mają dopełniać się z pierwszym polem do minut w rekordzie. Dzięki temu cała nasza funkcja będzie wyglądała następująco:

CREATE FUNCTION ZestawienieMinutBezKursorów
(
	@pula int
)
RETURNS TABLE
AS
RETURN
(
	SELECT Rozmowy.id, Rozmowy.minuty
	,CASE
		WHEN SUM(Rozmowy2.minuty) <= @pula
			THEN Rozmowy.minuty
		WHEN SUM(Rozmowy2.minuty) - Rozmowy.Minuty >= @pula
			THEN 0
		ELSE
			@pula - (SUM(Rozmowy2.Minuty) - Rozmowy.Minuty)
	 END AS Darmowe
	,CASE
		WHEN SUM(Rozmowy2.minuty) <= @pula
			THEN 0
		WHEN SUM(Rozmowy2.minuty) - Rozmowy.Minuty >= @pula
			THEN Rozmowy.Minuty
		ELSE
			 SUM(Rozmowy2.Minuty) - @pula
	END AS Płatne
	FROM Rozmowy
	 LEFT JOIN Rozmowy Rozmowy2
		ON Rozmowy2.id<=Rozmowy.id
	GROUP BY Rozmowy.id, Rozmowy.minuty
 )

Jej działanie możemy sprawdzić, pisząc:

SELECT Id, Minuty, Darmowe, Płatne FROM dbo.ZestawienieMinutBezKursorów(200) ORDER BY ID

(ORDER BY jest konieczne przy wywołaniu tego typu funkcji, ze względu na to, że serwer nie gwarantuje w żaden sposób kolejności rekordów zwracanych przez funkcję).

Jak widać, przy niewielkim wysiłku intelektualnym udało nam się zastąpić skomplikowaną i mało wydajną formułę iteracyjną na jedno zwięzłe zapytanie.

Przykład został zainspirowany walką z bardzo podobnym zapytaniem w Systemie Planowania Urlopów i Nieobecności (SPUiN).