Najwęższy zakres wygrywa: kod pocztowy w nakładających się przedziałach i jedno konto na osobę

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Gdy kod pocztowy pasuje do kilku nakładających się przedziałów, w T-SQL wygrywa najwęższy zakres, a o wyniku decyduje reguła sortowania. Piszemy dla programisty, który przydziela osobę albo skrzynkę do przedziału kodów pocztowych i wybiera jedno konto spośród kilku należących do tej samej osoby. Wiersz słownika ma kod początku i kod końca przedziału oraz adres w osobnym polu, a wiersze bez adresu są pomijane. Dopasowanie to BETWEEN na tekście stałej szerokości. Tę samą regułę w opisie wdrożenia pokazujemy w tekście o adresacie powiadomienia według kodu pocztowego, a tutaj zostaje sama technika.

Wpisany kod normalizujemy do stałej postaci, a spośród pasujących przedziałów wygrywa najwęższy, z remisem rozstrzyganym drugim kluczem sortowania. Podobnie jedno konto na osobę wybiera ROW_NUMBER po nazwie porównywanej bez diakrytyków, z jawnym priorytetem ról i rezerwą na login, gdy nazwy brak.

Dlaczego nakładające się przedziały potrzebują reguły wyboru

W słowniku z przykładu przedziały nakładają się i bywają zagnieżdżone: duży obszar zawiera mniejsze, a dwa z nich częściowo się pokrywają. Mamy przedział ogólny od 00-000 do 99-999, przedział regionu, przedział miasta oraz przedział sąsiedni, który zaczyna się w środku miasta. Kod 31-250 mieści się w czterech przedziałach naraz, więc samo BETWEEN zwróciłoby cztery wiersze, a skrzynkę trzeba przydzielić jedną. Szerokość w tabeli to liczba kodów w przedziale, a nazwy skrzynek są neutralne.

Przykład ilustracyjny, nazwy wymyślone.

Poniższy blok tworzy tabelę przedziałów z czterema wierszami z tabeli i tabelę kont, której użyjemy w piątej części. Kolumny kod_od i kod_do mają stałą szerokość sześciu znaków, bo kod ma postać NN-NNN, a na tej stałej szerokości opiera się porównanie tekstowe w BETWEEN. Konto w drugiej tabeli ma login i rolę, a imię z nazwiskiem bywa puste; dwa konta z przykładu nie mają żadnej nazwy.

CREATE TABLE dbo.zakres_przyklad (kod_od char(6) NOT NULL, kod_do char(6) NOT NULL, adres varchar(40) NOT NULL);
INSERT dbo.zakres_przyklad (kod_od, kod_do, adres) VALUES
    ('00-000', '99-999', 'skrzynka_ogolna'),
    ('30-000', '34-999', 'skrzynka_region'),
    ('31-100', '31-299', 'skrzynka_miasto'),
    ('31-200', '31-399', 'skrzynka_sasiad');
CREATE TABLE dbo.osoba_przyklad (login varchar(30) PRIMARY KEY, imie nvarchar(40) NULL,
                                 nazwisko nvarchar(40) NULL, rola varchar(20) NOT NULL);
INSERT dbo.osoba_przyklad (login, imie, nazwisko, rola) VALUES
    ('lk_biuro', N'Łukasz', N'Kowalski', 'biuro'),
    ('lk_mag',   N'Lukasz', N'KOWALSKI', 'magazyn'),
    ('lk_term',  N'Łukasz', N'Kowalski', 'terminal'),
    ('zw_biuro', N'Zofia',  N'Wójcik',   'biuro'),
    ('zw_term',  N'Zofia',  N'Wojcik',   'terminal'),
    ('an_term',  N'Anna',   N'Nowak',    'terminal'),
    ('sys_1', NULL, NULL, 'terminal'),
    ('sys_2', NULL, NULL, 'terminal');

Tabele przykładowe: cztery przedziały i osiem kont.

Jak znormalizować wpisany kod pocztowy do jednej postaci

Człowiek wpisuje ten sam kod na kilka sposobów, a porównanie tekstowe wymaga, żeby kody miały identyczną postać NN-NNN. Funkcja z przykładu sprowadza wpis do tej postaci albo do braku wartości, a dopiero potem szuka przedziału. Jest to funkcja tabelowa inline, którą opisujemy szerzej w artykule o funkcji tabelowej inline.

CREATE FUNCTION dbo.zakres_dla_kodu_przyklad (@wpis varchar(20))
RETURNS TABLE
AS
RETURN
SELECT TOP (1) z.adres, z.kod_od, z.kod_do
FROM (SELECT REPLACE(REPLACE(REPLACE(@wpis, ' ', ''), CHAR(160), ''), '-', '') AS cyfry) AS c
CROSS APPLY (SELECT CASE WHEN c.cyfry LIKE '[0-9][0-9][0-9][0-9][0-9]'
                         THEN LEFT(c.cyfry, 2) + '-' + RIGHT(c.cyfry, 3) END AS kod) AS k
JOIN dbo.zakres_przyklad AS z ON k.kod BETWEEN z.kod_od AND z.kod_do
ORDER BY ISNULL(TRY_CONVERT(int, REPLACE(z.kod_do, '-', '')), 99999) - ISNULL(TRY_CONVERT(int, REPLACE(z.kod_od, '-', '')), 0), z.kod_od;

Funkcja zwraca najwęższy przedział dla wpisanego kodu albo nic.

Normalizacja dzieje się w pierwszych dwóch podzapytaniach i ma cztery kroki.

• Spacje: pierwszy REPLACE usuwa zwykłe spacje z całego wpisu.
• Twarda spacja: drugi REPLACE z CHAR(160) usuwa ją tak samo jak zwykłą spację.
• Myślnik: trzeci REPLACE usuwa myślnik, więc zapis z myślnikiem i bez niego daje te same cyfry.
• Pięć cyfr: wzorzec LIKE przepuszcza tylko pięć cyfr, a LEFT i RIGHT składają je w postać NN-NNN.

Wynik CASE bez gałęzi ELSE to NULL, a NULL nie spełnia warunku BETWEEN, więc funkcja nie zwraca wtedy żadnego adresu. Dwa podzapytania połączone przez CROSS APPLY budują kolejno cyfry i kod, tak że drugie korzysta z gotowego wyniku pierwszego.

Granica wzorca: kod o innej długości niż pięć znaków po usunięciu spacji i myślnika daje brak kodu i brak adresata. Normalizacja przyjmuje myślnik w dowolnym miejscu, więc zapis 31-15-0 jest czytany jak 31150, i nie obcina znaków końca linii ani tabulatora. W bazie z porównaniem UTF-8 funkcja CHAR(160) daje NULL, przez co funkcja nie zwraca adresu także dla poprawnych wpisów; twardą spację trzeba wtedy zapisać inaczej, na przykład jako NCHAR(160) po rzutowaniu na nvarchar.

Jak wybrać najwęższy przedział i rozstrzygnąć remis

Gdy kod ma już postać NN-NNN, funkcja łączy go z przedziałami warunkiem BETWEEN i sortuje pasujące wiersze rosnąco po szerokości przedziału. Szerokość to różnica końca i początku po usunięciu myślnika i zamianie wartości na liczbę całkowitą, a TOP (1) zostawia pierwszy wiersz tej kolejności, czyli najwęższy przedział. Remis rozstrzyga mniejszy kod początku, bo jest drugim kluczem w ORDER BY.

Dla kodu 31-250 pasują cztery przedziały. Przedział ogólny ma szerokość 100000, przedział regionu 5000, a przedział miasta i przedział sąsiedni po 200. O remisie 200 do 200 decyduje początek: 31-100 jest mniejszy niż 31-200, więc wygrywa skrzynka miasta. Na osi poniżej pionowa linia przecina cztery belki, a ramka wskazuje przedział, który wygrywa.

Gdy przedziały się nakładają, o wyniku decyduje nie lista przedziałów, tylko reguła sortowania.

Granica wzorca: remis jest rozstrzygnięty tylko wtedy, gdy przedziały mają różne początki. Dwa przedziały o tych samych granicach nadal dają wynik niejednoznaczny, więc potrzebują trzeciego klucza sortowania. Gdy zakresy nakładać się nie mogą, a trzeba wskazać najbliższy z nich, służy temu osobna technika, opisana w artykule o wyborze najbliższego zakresu.

Jak sprawdzić regułę na wszystkich możliwych kodach

Pierwsze zapytanie testowe zestawia jedenaście wpisów z oczekiwanym adresem i oznacza zgodność jako OK albo BLAD. Pierwsze cztery wpisy to ten sam kod 31-150 zapisany na cztery sposoby, kolejne cztery sprawdzają remis szerokości i trzy pozostałe przedziały po jednym, a ostatnie trzy nie są kodem i mają dać brak adresu. OUTER APPLY zachowuje wiersz testowy także wtedy, gdy funkcja niczego nie zwraca, a ISNULL zamienia brak adresu na znak minus do porównania z oczekiwaniem.

SELECT t.opis, t.wpis, a.adres, t.oczekiwany,
       CASE WHEN ISNULL(a.adres, '-') = t.oczekiwany THEN 'OK' ELSE 'BLAD' END AS wynik
FROM (VALUES
    ('31-150', N'z myślnikiem', 'skrzynka_miasto'),
    ('31150', N'bez myślnika', 'skrzynka_miasto'),
    (' 31 150 ', N'ze spacjami', 'skrzynka_miasto'),
    ('31' + CHAR(160) + '150', N'z twardą spacją', 'skrzynka_miasto'),
    ('31-250', N'remis szerokości, wcześniejszy początek', 'skrzynka_miasto'),
    ('31-350', N'zakres sąsiedni', 'skrzynka_sasiad'),
    ('30-500', N'zakres regionu', 'skrzynka_region'),
    ('40-001', N'tylko zakres ogólny', 'skrzynka_ogolna'),
    ('abc', N'nie jest kodem', '-'),
    ('', N'pusty', '-'),
    (NULL, N'brak', '-')) AS t(wpis, opis, oczekiwany)
OUTER APPLY dbo.zakres_dla_kodu_przyklad(t.wpis) AS a;

Pierwsze zapytanie testowe: jedenaście wpisów i oczekiwany adres.

Wynik pierwszego zapytania testowego.

Wszystkie jedenaście wpisów daje oczekiwany adres na poziomach zgodności 130 i 160. Jedenaście wpisów nie dowodzi jednak, że porównanie tekstowe zgadza się z liczbowym, więc drugie zapytanie generuje kody od 00-000 do 99-999 i porównuje dla czterech przedziałów z przykładu wynik BETWEEN na tekście z wynikiem porównania liczbowego. Liczba rozbieżności wynosi 0.

WITH n AS (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS v
           FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
SELECT COUNT(*) AS rozbieznosci
FROM n CROSS JOIN dbo.zakres_przyklad AS z
CROSS APPLY (SELECT RIGHT('0' + CAST(n.v / 1000 AS varchar(2)), 2) + '-'
                  + RIGHT('00' + CAST(n.v % 1000 AS varchar(3)), 3) AS kod) AS k
WHERE CASE WHEN k.kod BETWEEN z.kod_od AND z.kod_do THEN 1 ELSE 0 END
   <> CASE WHEN n.v BETWEEN CAST(REPLACE(z.kod_od, '-', '') AS int)
                        AND CAST(REPLACE(z.kod_do, '-', '') AS int) THEN 1 ELSE 0 END;

Drugie zapytanie testowe: porównanie tekstowe i liczbowe dla kodów od 00-000 do 99-999.

Granica testu: to zestaw przykładowych przedziałów. Wynik jest dowodem dla czterech przedziałów z przykładu, a nie dla dowolnego słownika.

Dlaczego jedna osoba ma kilka kont i jak wybrać jedno

Druga technika dotyczy listy osób do wyboru, na której ma być jedna pozycja na osobę, choć osoba może mieć kilka kont. W przykładzie ta sama osoba bywa zapisana raz jako „Łukasz Kowalski”, a raz jako „Lukasz KOWALSKI”, i ma konta o różnych rolach. Widok z poniższego bloku nadaje kontu nazwę osoby: imię i nazwisko po przycięciu spacji, a gdy nazwy brak, login. Rezerwa na login sprawia, że konta bez nazwy nie zlewają się w jedną pozycję. Blok zawiera separator GO, który jest separatorem narzędzia, a nie instrukcją T-SQL.

CREATE VIEW dbo.osoba_wpis_przyklad AS
SELECT login, rola,
       ISNULL(NULLIF(LTRIM(RTRIM(ISNULL(imie, N'') + N' ' + ISNULL(nazwisko, N''))), N''), login) AS osoba
FROM dbo.osoba_przyklad;
GO
SELECT login, osoba, rola
FROM (SELECT w.*, ROW_NUMBER() OVER (
              PARTITION BY w.osoba COLLATE Latin1_General_CI_AI
              ORDER BY CASE WHEN CHARINDEX('|' + w.rola + '|', '|magazyn|biuro|') > 0
                            THEN CHARINDEX('|' + w.rola + '|', '|magazyn|biuro|') ELSE 9000 END,
                       w.login) AS rn
      FROM dbo.osoba_wpis_przyklad AS w) AS x
WHERE rn = 1
ORDER BY login;

Widok z nazwą osoby i zapytanie wybierające jedno konto na osobę.

ROW_NUMBER numeruje konta w partycjach po nazwie osoby, a warunek rn równe 1 zostawia po jednym koncie z partycji. Partycja używa COLLATE Latin1_General_CI_AI, czyli porównania bez rozróżniania wielkości liter i diakrytyków, więc oba zapisy nazwiska trafiają do jednej grupy. Kolejność w grupie wyznacza priorytet roli: o miejscu decyduje pozycja roli w ciągu |magazyn|biuro|, a role spoza ciągu dostają w CASE wartość 9000 i idą za konta z listy. Przy remisie wygrywa mniejszy login.

Wynik zapytania wybierającego konto.

Konto an_term jest jedynym kontem swojej osoby, więc zostaje mimo roli spoza listy. Dwa konta bez nazwy zostają osobno, bo w partycji jest u nich własny login zamiast pustej wartości.

Dwa warianty pomijające jedną z reguł dają widoczny zły skutek, co pokazuje kolejny blok. Pierwszy wariant porównuje nazwy w domyślnym zestawieniu znaków, a drugi używa CHARINDEX bez CASE.

SELECT N'domyślne porównanie' AS wariant, login
FROM (SELECT w.login, ROW_NUMBER() OVER (PARTITION BY w.osoba ORDER BY w.login) AS rn
      FROM dbo.osoba_wpis_przyklad AS w) AS x
WHERE rn = 1
UNION ALL
SELECT N'CHARINDEX bez CASE', login
FROM (SELECT w.login, ROW_NUMBER() OVER (
              PARTITION BY w.osoba COLLATE Latin1_General_CI_AI
              ORDER BY CHARINDEX('|' + w.rola + '|', '|magazyn|biuro|'), w.login) AS rn
      FROM dbo.osoba_wpis_przyklad AS w) AS x
WHERE rn = 1
ORDER BY 1, 2;

Dwa warianty pomijające jedną z reguł, wynik w jednej liście.

Wynik porównania wariantów.

Przy domyślnym porównaniu Polish_CI_AS wynik ma siedem wierszy, bo osoby z diakrytykami rozpadają się na dwie pozycje. Bez CASE wynik ma pięć wierszy, ale wygrywają konta spoza listy ról, ponieważ CHARINDEX zwraca 0, a 0 sortuje się przed 1. Rezerwę na wartość zapasową stosuje się też przy wyborze węższego zbioru, który omawiamy w artykule o wyborze węższego zbioru z rezerwą. Sam pomysł wyboru pierwszego wiersza z grupy opisujemy w tekście o pierwszym wierszu z ROW_NUMBER.

Co zmienia COLLATE bez diakrytyków i co zmienia domyślne porównanie

Zapytanie poniżej porównuje cztery pary nazw w czterech zestawieniach znaków i pokazuje, kiedy litera z diakrytykiem jest równa swojej wersji bez diakrytyku. Domyślne porównanie bazy w teście to Polish_CI_AS, więc bez jawnego COLLATE osoby z diakrytykami rozdzielają się na dwie pozycje, tak jak w poprzednim bloku.

SELECT para,
       CASE WHEN a = b COLLATE Latin1_General_CI_AI THEN 'rowne' ELSE 'rozne' END AS latin1_ci_ai,
       CASE WHEN a = b COLLATE Latin1_General_100_CI_AI THEN 'rowne' ELSE 'rozne' END AS latin1_100_ci_ai,
       CASE WHEN a = b COLLATE Polish_CI_AI THEN 'rowne' ELSE 'rozne' END AS polish_ci_ai,
       CASE WHEN a = b COLLATE Polish_CI_AS THEN 'rowne' ELSE 'rozne' END AS polish_ci_as
FROM (VALUES (N'Łukasz i Lukasz', N'Łukasz', N'Lukasz'),
             (N'Wójcik i Wojcik', N'Wójcik', N'Wojcik'),
             (N'Kowalski i KOWALSKI', N'Kowalski', N'KOWALSKI'),
             (N'ąćęńśźż i acenszz', N'ąćęńśźż', N'acenszz')) AS t(para, a, b);

Cztery pary nazw porównane w czterech zestawieniach znaków.

Wynik zapytania porównującego nazwy.

Słowo „Łukasz” i słowo „Lukasz” są równe w Latin1_General_CI_AI i w Latin1_General_100_CI_AI, a różne w Polish_CI_AI i Polish_CI_AS. Podobnie „Wójcik” i „Wojcik” są równe w Latin1_General_CI_AI, a różne w Polish_CI_AI. W Latin1_General_CI_AI równe są też pozostałe polskie litery z diakrytykami, co pokazuje ostatnia para w tabeli. Para „Kowalski” i „KOWALSKI” różni się tylko wielkością liter i wychodzi równa w czterech zestawieniach.

Granice porównania bez diakrytyków mają cztery strony.

• Różne osoby: porównanie bez diakrytyków nie identyfikuje osoby, bo łączy też różne osoby, których nazwiska różnią się tylko polskimi literami.
• Cztery zestawienia: test dotyczy czterech wymienionych zestawień znaków na jednej instancji, więc wyniku nie przenosimy na inną bazę ani inne zestawienie.
• Tabele tymczasowe: gdy tempdb ma inne porównanie niż baza, kolumny tekstowe tabel tymczasowych wymagają jawnego COLLATE DATABASE_DEFAULT, inaczej porównanie kończy się konfliktem porównań.
• Kody innych krajów: postać NN-NNN opisuje polski kod, więc nie przyjmujemy, że kod pocztowy w innym kraju ma ten sam zapis.

Pytania do analizy

Przed zastosowaniem wzorca odpowiedz na cztery pytania.

1. Nakładanie przedziałów: Czy przedziały w Twoim słowniku mogą się nakładać i kto pilnuje, żeby remis szerokości nie zależał od przypadku?
2. Postać wpisu: W jakiej postaci użytkownicy wpisują kod i czy normalizacja odrzuca wpisy o złej długości, zamiast zgadywać?
3. Porównanie nazw: Jakim zestawieniem znaków porównujesz nazwy osób i co się stanie, gdy domyślne zestawienie bazy rozróżnia litery z diakrytykami?
4. Konto bez nazwy: Co robi reguła wyboru konta dla osoby bez nazwy i dla roli, której nie ma na liście priorytetów?