Obliczanie różnych wartości w zbiorze danych jest elementem często występującym np. w zestawieniach. Często spotykamy się z takimi zadaniami np. w przypadku systemów obsługi zamówień, gdzie do zamówienia dodajemy różne produkty o różnych ilościach i cenach a na końcu chcemy podsumować całkowitą wartość (podaną najlepiej razem z samym rekordem je odzwierciedlającym w bazie danych).

Uważam, iż przedstawiony powyżej problem jest na tyle prosty do wyobrażenia, iż możemy go wykorzystać do zaprezentowania kilku sposobów rozwiązania. Nie zwracajcie proszę uwagi na sensowność niektórych konstrukcji gdyż moim zamiarem jest tylko pokazanie pewnych technik, które nie zawsze będą adekwatne do prezentowanego przykładu (np. na zasadzie "strzelania z armaty do wróbla"). Chciałbym jednak mieć coś wspólnego co łączyłoby prezentowane rozwiązania a wg mnie ów problem świetnie się do tego nadaje. Skoro już wszystko zostało wyjaśnione to przygotujmy sobie środowisko do dalszej "zabawy".

-- Tabela klientów
create table dbo.klienci  
(
    klient_id int primary key identity(1,1),  
    nazwisko nvarchar(50) not null,
    imie nvarchar(50) not null
); 
 
-- Tabela przechowująca produktu z cenami jednostkowymi
create table dbo.produkty
(
	produkt_id int primary key identity(1,1),
	nazwa nvarchar(100) not null,
	cena money
);

-- Tabela przechowująca dane o zamówieniach (kto i kiedy zamówił)
create table dbo.zamowienia  
(
    zamowienie_id int primary key identity(1,1),  
    klient_id int not null,
    data_zamowienia date not null
);

-- Tabela łącząca zamowienia z ilościami produktów
create table dbo.zamowienia_produkty  
(
    zamowienie_produkt_id int primary key identity(1,1),  
    zamowienie_id int not null,
	produkt_id int not null,
    ilosc int not null
);

Powyższe tabele możemy zapełnić przykładowymi danymi (ewentualna zbieżność osób i nazwisk jest zupełnie przypadkowa 😉 ).

insert into dbo.klienci 
(nazwisko, imie) values
(N'Wiśniewski', N'Bartłomiej'),
(N'Skowrońska', N'Beata'),
(N'Leśniak', N'Józef');
 
insert into dbo.produkty
(nazwa, cena) values
 (N'Śrubka', 1.30),
 (N'Wkręt', 0.92),
 (N'Nakrętka', 0.54);

insert into dbo.zamowienia 
(klient_id, data_zamowienia) values
(1, '20170103'),
(2, '20170104'),
(3, '20170110');

insert into dbo.zamowienia_produkty 
(zamowienie_id, produkt_id, ilosc) values
(1, 1, 10), (1, 3, 10),
(2, 2, 50),
(3, 1, 130), (3, 2, 55), (3, 3, 85);

Gdybyśmy teraz chcieli pobrać wszystkie zamówienia wraz z całkowitymi kwotami moglibyśmy zrobić to albo agregując dane "w kodzie" aplikacji albo wykorzystać np. następujące zapytanie

select z.zamowienie_id, 
k.nazwisko, 
k.imie, 
z.data_zamowienia, 
sum(zp.ilosc * p.cena) as kwota
from zamowienia as z
inner join klienci as k on k.klient_id = z.klient_id
inner join zamowienia_produkty as zp on zp.zamowienie_id = z.zamowienie_id
inner join produkty as p on p.produkt_id = zp.produkt_id
group by z.zamowienie_id, k.nazwisko, k.imie, z.data_zamowienia;

Nie jest ono może zbyt piękne ale zwraca nam całkiem przyzwoite zestawienie zamówień

Zwroćmy uwagę na to co dzieje się w samym zapytaniu. Musimy wykonać złączenie kilku tabel, wykonać jakieś obliczenia (suma z iloczynów) a chcemy tylko wiedzieć - kto, kiedy i za jaką kwotę zamówił. Czy da się to zrobić jakoś inaczej? Mi osobiście do głowy przychodzi kilka pomysłów.

Najbardziej banalne wg mnie będzie przygotowanie widoku z powyższego zapytania.

create view dbo.v_zamowienia as 
select z.zamowienie_id, 
k.nazwisko, 
k.imie, 
z.data_zamowienia, 
sum(zp.ilosc * p.cena) as kwota
from zamowienia as z
inner join klienci as k on k.klient_id = z.klient_id
inner join zamowienia_produkty as zp on zp.zamowienie_id = z.zamowienie_id
inner join produkty as p on p.produkt_id = zp.produkt_id
group by z.zamowienie_id, k.nazwisko, k.imie, z.data_zamowienia;

Teraz pobranie zestawienia wygląda co prawda lepiej ale tak na prawdę "w środku" nic specjalnie się nie zmieniło

select * from dbo.v_zamowienia

Inna propozycja to trzymanie całkowitej kwoty wprost w tabeli zestawienia. Aktualizować możemy ją na kilka sposobów - jednym z nich jest robienie tego "z poziomu kodu" aplikacji. Nie ukrywam, że jest to mało wygodne podejście. Szczególnie, że można to zrobić niejako "automatycznie" po stronie samej bazy, gdzie jedną z metod na osiągnięcie tego jest dodanie triggerów do tabeli dbo.zamowienia_produkty. Aby być w pełni niezależnym od aplikacji należy przygotować trzy triggery - osobny dla dodania rekordu, inny dla aktualizacji i ostatni dla usunięcia wpisu. Najpierw jednak musimy zmodyfikować naszą tabelę zamówień aby mogła przyjmować kwotę.

alter table dbo.zamowienia
add kwota money

Wykorzystajmy też nasz widok do wstępnego ustawienia wartości nowego pola w istniejących zamówieniach.

update z
set z.kwota = v.kwota
from dbo.zamowienia as z
inner join dbo.v_zamowienia as v on v.zamowienie_id = z.zamowienie_id;

Teraz zajmijmy się samymi triggerami, które można napisać np. tak

create trigger dbo.zamowienie_ins
on zamowienia_produkty
for insert as
begin
	declare @zId int;
	declare @ilosc int;
	declare @cena money;	

	select @zId = i.zamowienie_id,
	@ilosc = i.ilosc,
	@cena = p.cena
	from inserted as i
	inner join produkty as p on p.produkt_id = i.produkt_id;

	update zamowienia
	set kwota = kwota + @cena * @ilosc
	where zamowienie_id = @zId;
end;

create trigger dbo.zamowienie_upd
on zamowienia_produkty
for update as 
begin
	declare @zId int;
	declare @poprzedniaIlosc int;
	declare @nowaIlosc int;
	declare @cena money;

	-- Zajmujemy się tylko zmianą ilości produktów pomijając 
	-- np. sytuacje kiedy aktualizacja zmienia przypisanie 
	-- do identyfikatora zamówienia.

	select @zId = d.zamowienie_id,
	@poprzedniaIlosc = d.ilosc,
	@cena = p.cena
	from deleted as d
	inner join produkty as p on p.produkt_id = d.produkt_id;

	select @nowaIlosc = ilosc
	from inserted;

	update zamowienia
	set kwota = kwota + @cena * (@nowaIlosc - @poprzedniaIlosc)
	where zamowienie_id = @zId;
end;

create trigger dbo.zamowienie_del
on zamowienia_produkty
for delete as
begin
	declare @zId int;
	declare @ilosc int;
	declare @cena money;

	select @zId = d.zamowienie_id,
	@ilosc = d.ilosc,
	@cena = p.cena
	from deleted as d
	inner join produkty as p on p.produkt_id = d.produkt_id;

	update zamowienia
	set kwota = kwota - @cena * @ilosc
	where zamowienie_id = @zId;
end;

Wprowadźmy teraz trochę zmian do naszych zamówień i zobaczmy jak zachowają się wartości kwot w tabeli zamówień.

insert into dbo.zamowienia_produkty 
(zamowienie_id, produkt_id, ilosc) values (1, 1, 30);

update dbo.zamowienia_produkty
set ilosc = 30
where zamowienie_produkt_id = 3;

delete from dbo.zamowienia_produkty
where zamowienie_produkt_id = 6;

select *
from zamowienia;

Jak można zobaczyć poniżej, efekt jest zgodny z oczekiwaniami.

Spośród pozostałych rozwiązań jako pierwsze chciałbym przedstawić skorzystanie z własnej funkcji. W sumie brzmi to nawet sensownie - funkcja to przecież takie coś gdzie "wrzucamy" różne argumenty i otrzymujemy interesujący nas wynik. Co do argumentów to w naszym przypadku wydaje się być tylko jeden w miarę użyteczny - identyfikator zamówienia. Zatem do dzieła, napiszmy sobie funkcję.

create function dbo.policzKwoteZamowienia(@zId int)
returns money
with execute as caller 
as 
begin
	declare @kwota money;

	select @kwota = sum(zp.ilosc * p.cena)
	from dbo.zamowienia_produkty as zp
	inner join dbo.produkty as p on p.produkt_id = zp.produkt_id
	where zp.zamowienie_id = @zId;

	return @kwota;
end;

Wykonajmy teraz zapytanie bazujące na naszej funkcji. Celowo nie usunąłem pola kwoty z tabeli zamówień aby można było porównać sobie wartości.

select z.*,
dbo.policzKwoteZamowienia(z.zamowienie_id) as kwota_z_funkcji
from zamowienia as z

Jak widać poniżej nasza funkcja spisała się całkiem dobrze.

Na koniec zostawiłem jeszcze jedno ciekawe rozwiązanie. Mam na myśli tzw. pola kalkulowane. Co to takiego? Myślę, że najlepiej będzie to pokazać na przykładzie. Załóżmy na chwilę, że nasza tabela przechowująca powiązania pomiędzy ilością produktów a zamówieniem ma trochę inną strukturę i zawiera informację o jednostkowej cenie produktu.

create table dbo.zamowienia_produkty  
(
    zamowienie_produkt_id int primary key identity(1,1),  
    zamowienie_id int not null,
    produkt_id int not null,
    ilosc int not null,
    cena_jedn money
);

Jak widać można bardzo łatwo zbudować sobie zapytanie, które obliczy nam od razu kwotę cząstkową przypadającą na produkt.

select zp.*, ilosc * cena_jedn as kwota
from dbo.zamowienia_produkty as zp;

T-SQL pozwala nam uprościć nieco ten zapis wprowadzając wspomniane przeze mnie wyżej pole kalkulowane. Założenie jest takie, że skoro formuła jest niezmienna to można ją zaszyć w samej definicji tabeli. Aby to osiągnąć wystarczy zadeklarować naszą tabelę w następujący sposób

create table dbo.zamowienia_produkty
(
    zamowienie_produkt_id int primary key identity(1,1),  
    zamowienie_id int not null,
    produkt_id int not null,
    ilosc int not null,
    cena_jedn money,
    kwota as ilosc * cena_jedn
);

Działa to rewelacyjnie, nieprawdaż? Tylko, że w naszym przypadku powstaje pewna komplikacja. Rozwiązanie w tej postaci nadaje się tylko do zastosowania w jednej tabeli. My natomiast mamy dane w kilku tabelkach. Czy można sobie z tym jakoś poradzić? Otóż odpowiedź brzmi tak, można. Należy w tym celu posłużyć się funkcją (a w naszym przypadku ta napisana przez nas wcześniej będzie się nadawać idealnie). Dla ścisłości tylko dodam, iż pole przechowujące wartość jednostkową w tabeli wiążącej ilości produktów z zamówieniami nie będzie nam więcej potrzebna (posłużyła nam tylko do zilustrowania idei pól wyliczanych).

create table dbo.zamowienia_produkty
(
    zamowienie_produkt_id int primary key identity(1,1),  
    zamowienie_id int not null,
    produkt_id int not null,
    ilosc int not null,
    kwota as dbo.policzKwoteZamowienia(zamowienie_id)
);

Ktoś mógłby teraz powiedzieć - no fajnie, zaczęliśmy od zapytania łączącego kilka tabelek a skończymy na tabelce, która zawiera praktycznie te połączenia w sobie w ramach funkcji. Co w tym takiego fajnego? Pomijając fakt zaszycia obliczeń w definicji tabelki jest jeszcze jedna "wisienka na torcie", którą pozwoliłem sobie zachować na koniec. Co będzie jeśli powiem, że wartość kwoty nie musi być wyliczana przy każdym zapytaniu? Czy to możliwe? Otóż okazuje się, że tak chociaż niestety nie będzie to dotyczyło naszego przypadku z wykorzystaniem funkcji. Chodzi o to, iż wartość takiego wyliczanego pola można "zmaterializować" wprost w tabeli (zmaterializować tzn. zapisać w bazie wartość a nie tylko sam sposób jej obliczania). Jak taki efekt osiągnąć? Wystarczy trochę zmodyfikować definicję wyliczanego pola dodając do niego argument persisted (zainteresowanych odsyłam do lektury MSDN).

create table dbo.zamowienia_produkty
(
    zamowienie_produkt_id int primary key identity(1,1),  
    zamowienie_id int not null,
    produkt_id int not null,
    ilosc int not null,
    kwota as dbo.policzKwoteZamowienia(zamowienie_id) presisted
);

Niestety, jak napisałem wyżej, w tym przypadku taka próba zdefiniowana tabeli zakończy się niepowodzeniem. Przyczyną jest fakt, iż wartość pola kwota nie jest deterministyczna, co jest warunkiem koniecznym do "zmaterializowania" wartości. Dlaczego ów determinizm jest taki ważny? Ponieważ zasada działania pól z atrybutem persisted jest taka, iż wartość pola będzie wyliczana zgodnie z podaną regułą tylko wtedy, kiedy zmieni się jedna ze składowych biorących udział w obliczeniach (mówiąc inaczej jeśli żaden z takich argumentów się nie zmienia to wtedy wartość odczytywana jest wprost z tabeli). Jeśli natomiast baza nie umie określić składowych obliczenia (tak jak w naszym przypadku) wtedy niestety nie pozwoli na "materializację".

Na tym chciałbym zakończyć przegląd rozwiązań naszego problemu. Czy to co przedstawiłem wyczerpuje temat? Jestem oczywiście pewien, że nie. Mam jednak nadzieję, iż udało mi się pokazać przynajmniej te ciekawsze z możliwych oraz wykorzystywanych technik 🙂