Spis treści:

Kategoria:SQL Server


Poprzedni, następny i sąsiedni rekord w SQL Server

Teraz i zawsze

Problem wyszukiwania rekordu poprzedniego i następnego w relacyjnych bazach danych jest tak stary jak same bazy. Nie dziwne, że od wersji SQL Server 2012 momy nowy sposób radzenia sobie z takimi zadaniami. Powiem więcej - jest to rozwiązanie zaproponowane przez komitet normalizacyjny, a ustalenia zostały zapisane w normie ISO/IEC 9075:2011 nazywaną w skrócie normą SQL:2011. Konsekwencje wpisania różnych konstrukcji językowych do norm są zdecydowanie pozytywne - jest duża szansa, że niedługo to samo zapytanie we wszystkich popularnych bazach danych będzie wyglądało tak samo. Zanim się tak jednak stanie, jesteśmy zmuszeni do stosowania alternatywnych metod. Nie dlatego, że twórcy baz danych tego nie zaimplementowali (najnowsze wersje Oracle też obsługują standard SQL:2001 w tym zakresie), ale dlatego, że jeszcze długo różni klienci będą stosować starsze wersje baz.

Tabela testowa

Nie ma co się rozpisywać - niech przemówi przykład:

CREATE TABLE EvaluationHistory
(
  CompanyCode varchar(4),
  EvaluationDate datetime NOT NULL,
  Research decimal(9,2) NOT NULL,
  Wages decimal(9,2) NOT NULL,
  Advertising decimal(9,2) NOT NULL,
  TotalSpending AS Research+Wages+Advertising
)

INSERT INTO EvaluationHistory
VALUES
('CARS''2000-01-01T12:00:00', 4000, 2000, 2000),
('CARS''2001-01-01T12:00:00', 3000, 3000, 3000),
('CARS''2002-01-01T12:00:00', 2000, 4000, 3000),
('CARS''2003-01-01T12:00:00', 2000, 5000, 4000),
('CARS''2004-01-01T12:00:00', 2000, 5000, 4000)

Tabela reprezentuje historyczne zapisy wydatków na różne obsdzary działalności przedsiębiorstwa w uproszczonej, przykładowej formie. Mamy kolejno:

  • CompanyCode - kod przedsiębiorstwa,
  • EvaluationDate - czas podsumowania wydatków, w przykładzie ustawiony zawsze na pierwszy stycznia,
  • Research - koszty badań,
  • Wages - koszty pensji,
  • Advertising - koszty reklamowe,
  • TotalSpending - suma kosztów w postaci kolumny wyliczanej.

Tabelę uzupełniłem przykładowymi danymi. Nie jest ich dużo, ale wystarczająco, by pokazać wyliczenia.

Zestawienie dwóch sąsiednich rekordów dawniej

Wspomniałem, że w SQL Server 2012 pojawiły sie nowe możliwości zestawień. Warto jednak poznać stare rozwiązania z dwóch powodów:

  • być może będziemy musieli pracować ze starszymi wersjami,
  • zobaczymy, czym się te dwa rozwiązania różnią.

Popatrzmy zatem na przykładowe zestawienie danych, które najprawdopodobniej zastosowalibyśmy w starszych wersjach SQL Server:

WITH Ordered AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY EvaluationDate) Num, *
  FROM EvaluationHistory
)
SELECT CONVERT(nvarchar(30), O1.EvaluationDate, 111)+' - '+CONVERT(nvarchar(30), O2.EvaluationDate, 111) EvaluationPeriod,
O2.Research - O1.Research Research,
O2.Wages - O1.Wages Wages,
O2.Advertising - O1.Advertising Advertising,
O2.TotalSpending - O1.TotalSpending TotalSpending
FROM Ordered O1
JOIN Ordered O2 ON O1.Num=O2.Num-1;

Rozwiązanie opiera się na prostym algorytmie: numerujemy wszystkie wiersze (sposób numerowania został opisany tutaj: Numerowanie rekordów zwracanych z SQL Server), a następnie łączymy je w pary na podstawie sąsiadujących identyfikatorów. Takie podejście można stosować dla wszystkich typów kolumn, nawet tych nieciągłych. Można się pokusić o wykorzystanie już istniejących identyfikatorów (IDENTITY), ale muszę zainterweniować: po usunięciu rekordu w sekwencji mogą powstać dziury, które uniemożliwią poprawne złączenie sąsiadów.

Wykonanie powyższej instrukcji pozwoli uzyskać taki oto rezultat:

EvaluationPeriodResearchWagesAdvertisingTotalSpending
2000/01/01 - 2001/01/01-1000.001000.001000.001000.00
2001/01/01 - 2002/01/01-1000.001000.000.000.00
2002/01/01 - 2003/01/010.001000.001000.002000.00
2003/01/01 - 2004/01/010.000.000.000.00

Dodatnie wartości oznaczają wzrost w stosunku do poprzedniego okresu, ujemne spadek.

Podział na grupy

Poprzednie zapytanie miało do przetworzenia prosty zestaw danych. Zwykle mamy jednak do czynienia z czymś bardziej skomplikowanym. Z czymś, z czym poprzednie zapytanie nie da sobie rady. Aby pokazać o co chodzi, dodajmy do tabeli kilka dodatkowych rekordów:

INSERT INTO EvaluationHistory
VALUES
('BEER''2000-01-01T12:00:00', 1000, 2000, 1000),
('BEER''2001-01-01T12:00:00', 1000, 2100, 0),
('BEER''2002-01-01T12:00:00', 1100, 2100, 0),
('BEER''2003-01-01T12:00:00', 1200, 2300, 500),
('BEER''2004-01-01T12:00:00', 1300, 2500, 500)

Na czym polega problem? Tym razem dane z wielu grup zostaną wymieszane, bo za kryterium numeracji przyjęta została data. Można uznać, że należałoby te rekordy poustawiać względem kolumny CompanyCode, ale wtedy ostatni rekord z jednej grupy połączyłby się z pierwszym rekordem z grupy następnej. Należy zatem nałożyć dodatkowy warunek - kod grupy obu łączonych rekordów musi być taki sam. Należy jeszcze zadbać o to, aby w ramach grupy wszystki przydzielane numery były kolejnymi liczbami całkowitymi. Rozwiązania są dwa:

WITH Ordered AS
(
  SELECT ROW_NUMBER() OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate) Num, *
  FROM EvaluationHistory
)
SELECT O1.CompanyCode,
CONVERT(nvarchar(30), O1.EvaluationDate, 111)+' - '+CONVERT(nvarchar(30), O2.EvaluationDate, 111) EvaluationPeriod,
O2.Research - O1.Research Research,
O2.Wages - O1.Wages Wages,
O2.Advertising - O1.Advertising Advertising,
O2.TotalSpending - O1.TotalSpending TotalSpending
FROM Ordered O1
JOIN Ordered O2 ON O1.Num=O2.Num-1
WHERE O1.CompanyCode = O2.CompanyCode;

WITH Ordered AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY CompanyCode, EvaluationDate) Num, *
  FROM EvaluationHistory
)
SELECT O1.CompanyCode,
CONVERT(nvarchar(30), O1.EvaluationDate, 111)+' - '+CONVERT(nvarchar(30), O2.EvaluationDate, 111) EvaluationPeriod,
O2.Research - O1.Research Research,
O2.Wages - O1.Wages Wages,
O2.Advertising - O1.Advertising Advertising,
O2.TotalSpending - O1.TotalSpending TotalSpending
FROM Ordered O1
JOIN Ordered O2 ON O1.Num=O2.Num-1
WHERE O1.CompanyCode = O2.CompanyCode;

Oba rozwiązania pozwalają uzyskać następujący rezultat:

CompanyCodeEvaluationPeriodResearchWagesAdvertisingTotalSpending
BEER2000/01/01 - 2001/01/010.00100.00-1000.00-900.00
BEER2001/01/01 - 2002/01/01100.000.000.00100.00
BEER2002/01/01 - 2003/01/01100.00200.00500.00800.00
BEER2003/01/01 - 2004/01/01100.00200.000.00300.00
CARS2000/01/01 - 2001/01/01-1000.001000.001000.001000.00
CARS2001/01/01 - 2002/01/01-1000.001000.000.000.00
CARS2002/01/01 - 2003/01/010.001000.001000.002000.00
CARS2003/01/01 - 2004/01/010.000.000.000.00

Przy okazji tego prostego przykładu pozwolę sobie zadać pytanie: które z zapytań będzie działało lepiej? Od razu powiem, że rozwiązanie nie jest łatwe i pomóc nam może tylko intuicja. Instrukcje wykonują się prawie identycznie - jest tylko jedna drobna różnica. To jeden z tych obszarów, w których drobna modyfikacja pozwoliłaby nieco zwiększyć wydajność pewnej niewielkiej grupy instrukcji. W jakiś sposób w pierwszym prypadku silnik bazy danych rozpoznaje, że rekordy są posortowane. W drugim, choć rekordy są posortowane w taki sam sposób, silnikowi taka informacja gdzieś umyka.

W pierwszym przypadku do złączenia dwóch ponumerowanych rekordów wykorzystany jest wobec tego algorytm scalania (Merge, więcej na ten temat tutaj: Algorytm merge w praktyce), w drugim natomiast tablice haszujące, bardziej wymagające obliczeniowo niż wspomniany algorytm scalania.

Pokazany przykład uświadamia nam, że zawsze warto rozważyć kilka możliwych rozwiązań, o ile oczywiście zależy nam na uzyskaniu najwyższej wydajności i mamy na to czas.

Poprzednicy i następnicy w SQL Server 2012

W poprzednim akapicie rozważałem zauważalne różnice w wydajności dwóch podobnych instrukcji. Ci, którzy mają komfort pracy z SQL Server 2012, już nigdy nie będą musieli przeprowadzać podobnych testów i analiz. Popatrzmy na poniższy przykład:

SELECT CompanyCode,
CONVERT(nvarchar(30), LAG(EvaluationDate) OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate), 111)
+' - '+CONVERT(nvarchar(30), EvaluationDate, 111) EvaluationPeriod,
Research-LAG(Research) OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate) Research,
Wages-LAG(Wages) OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate) Wages,
Advertising-LAG(Advertising) OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate) Advertising,
TotalSpending-LAG(TotalSpending) OVER (PARTITION BY CompanyCode ORDER BY EvaluationDate) TotalSpending
FROM EvaluationHistory

W przykładzie wykorzystana została funkcja LAG, która zwraca poprzednika w ramach przetwarzanego okna danych (PARTITION BY). Aby uzyskać następnika wystarczy zamienić funkcję LAG na LEAD. Ot cała filozofia. Popatrzmy teraz na rezultat powyższej instrukcji:

CompanyCodeEvaluationPeriodResearchWagesAdvertisingTotalSpending
BEERNULLNULLNULLNULLNULL
BEER2000/01/01 - 2001/01/010.00100.00-1000.00-900.00
BEER2001/01/01 - 2002/01/01100.000.000.00100.00
BEER2002/01/01 - 2003/01/01100.00200.00500.00800.00
BEER2003/01/01 - 2004/01/01100.00200.000.00300.00
CARSNULLNULLNULLNULLNULL
CARS2000/01/01 - 2001/01/01-1000.001000.001000.001000.00
CARS2001/01/01 - 2002/01/01-1000.001000.000.000.00
CARS2002/01/01 - 2003/01/010.001000.001000.002000.00
CARS2003/01/01 - 2004/01/010.000.000.000.00

Okazuje się, że wystarczy tylko w zewnętrznym zapytaniu usunąć wiersze z wartościami NULL. Pozostawiam to już do samodzielnego uzupełnienia.

Fizyczne skanowanie tabeli bez powtórzeń

Niektórzy pewnie zastanawiają się, czy nowa operacja wykonywana jest jakoś lepiej od poprzednich. Odpowiedź brzmi - tak! W wersjach SQL Server wcześnijszych niż 2012 wymagane było dwukrotne przeskanowanie przetwarzanej tabeli. Mieliśmy do czynienia z takim nieco zakamuflowanym, ponumerowanym samozłączeniem. W SQL Server 2012 operacja wymaga tylko jednego skanowania. Jak to możliwe? Jakiż to tajny algorytm za tym stoi? Najciekawsze w tym wszystkim jest to, że algorytm jest bajecznie prosty:

  1. Dla kolumn, które korzystają z funkcji LAG przygotuj obszar w pamięci i powstawiaj tam NULL.
  2. Ustaw wskaźnik na początku tabeli.
  3. Jeżeli pod wskaźnikiem nie ma rekordu, zakończ.
  4. Pobierz rekord spod wskaźnika, dodaj do niego kolumny z obszaru LAG. Całość wyślij do strumienia wyjściowego.
  5. Skopiuj bieżące wartości spod wskaźnika do obszaru LAG.
  6. Przestaw wskaźnik na kolejny rekord i przejdź do punktu 3.

To tylko pseudokod. W rzeczywistości da się go jeszcze bardziej zoptymalizować unikając kopiowania rekordów, stosując buforowanie strumienia i dwa równolegle przesuwające się wskaźniki.

Co było moim celem? Po pierwsze chciałem pokazać, że na z pozoru proste instrukcje czasami trzeba czekać naprawdę długo. Czasem nawet bardzo długo. Po drugie, było już na ten temat kilka wpisów, większość stosowanych algorytmów jest powszechnie znana lub bardzo łatwa do opracowania. Kto jest wiernym w małych rzeczach, będzie i w większych. Kto zrozumie małe rzeczy, zrozumie i większe. Takim właśnie wnioskiem chciałbym zakończyć, zanim znów zanurzę się w dużym projekcie, którego nikt, nawet twórca, nie rozumie.

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !