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:
(
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:
(
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:
EvaluationPeriod | Research | Wages | Advertising | TotalSpending |
---|---|---|---|---|
2000/01/01 - 2001/01/01 | -1000.00 | 1000.00 | 1000.00 | 1000.00 |
2001/01/01 - 2002/01/01 | -1000.00 | 1000.00 | 0.00 | 0.00 |
2002/01/01 - 2003/01/01 | 0.00 | 1000.00 | 1000.00 | 2000.00 |
2003/01/01 - 2004/01/01 | 0.00 | 0.00 | 0.00 | 0.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:
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:
(
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:
CompanyCode | EvaluationPeriod | Research | Wages | Advertising | TotalSpending |
---|---|---|---|---|---|
BEER | 2000/01/01 - 2001/01/01 | 0.00 | 100.00 | -1000.00 | -900.00 |
BEER | 2001/01/01 - 2002/01/01 | 100.00 | 0.00 | 0.00 | 100.00 |
BEER | 2002/01/01 - 2003/01/01 | 100.00 | 200.00 | 500.00 | 800.00 |
BEER | 2003/01/01 - 2004/01/01 | 100.00 | 200.00 | 0.00 | 300.00 |
CARS | 2000/01/01 - 2001/01/01 | -1000.00 | 1000.00 | 1000.00 | 1000.00 |
CARS | 2001/01/01 - 2002/01/01 | -1000.00 | 1000.00 | 0.00 | 0.00 |
CARS | 2002/01/01 - 2003/01/01 | 0.00 | 1000.00 | 1000.00 | 2000.00 |
CARS | 2003/01/01 - 2004/01/01 | 0.00 | 0.00 | 0.00 | 0.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:
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:
CompanyCode | EvaluationPeriod | Research | Wages | Advertising | TotalSpending |
---|---|---|---|---|---|
BEER | NULL | NULL | NULL | NULL | NULL |
BEER | 2000/01/01 - 2001/01/01 | 0.00 | 100.00 | -1000.00 | -900.00 |
BEER | 2001/01/01 - 2002/01/01 | 100.00 | 0.00 | 0.00 | 100.00 |
BEER | 2002/01/01 - 2003/01/01 | 100.00 | 200.00 | 500.00 | 800.00 |
BEER | 2003/01/01 - 2004/01/01 | 100.00 | 200.00 | 0.00 | 300.00 |
CARS | NULL | NULL | NULL | NULL | NULL |
CARS | 2000/01/01 - 2001/01/01 | -1000.00 | 1000.00 | 1000.00 | 1000.00 |
CARS | 2001/01/01 - 2002/01/01 | -1000.00 | 1000.00 | 0.00 | 0.00 |
CARS | 2002/01/01 - 2003/01/01 | 0.00 | 1000.00 | 1000.00 | 2000.00 |
CARS | 2003/01/01 - 2004/01/01 | 0.00 | 0.00 | 0.00 | 0.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:
- Dla kolumn, które korzystają z funkcji LAG przygotuj obszar w pamięci i powstawiaj tam NULL.
- Ustaw wskaźnik na początku tabeli.
- Jeżeli pod wskaźnikiem nie ma rekordu, zakończ.
- Pobierz rekord spod wskaźnika, dodaj do niego kolumny z obszaru LAG. Całość wyślij do strumienia wyjściowego.
- Skopiuj bieżące wartości spod wskaźnika do obszaru LAG.
- 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
Brak komentarzy - bądź pierwszy