Pchełki SQL: Liczy się styl

Pchełki SQL są na tym blogu od jakiegoś czasu traktowane po macoszemu - trochę z braku czasu, trochę z braku pomysłów, a trochę z najnormalniejszego, ludzkiego lenistwa.

Dziś nadrobimy tę zaległość. Napiszemy króciutko o funkcji skrótu oraz konwersji skrótu na tekst.

Jeżeli ktoś nie wie, co to jest funkcja skrótu, to może sobie zerknąć tutaj: https://pl.wikipedia.org/wiki/Funkcja_skrótu

Załóżmy, że importujemy zewnętrzną tabelę z danymi do hurtowni. Jedna z kolumn zawiera unikalny identyfikator, jednak okazuje się, że w niektórych przypadkach kolumna ta jest pusta (NULL). Ponieważ nie chcemy importować pustych wartości identyfikatora, możemy albo pominąć takie rekordy przy imporcie, albo zastąpić pustą wartość jakąś wartością wyliczoną na podstawie innych kolumn w rekordzie. Ważne, żeby dobrać taki zestaw kolumn, który gwarantuje unikalność.

Zobaczmy na przykładzie:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT testdata.id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Powyższe zapytanie zwróci nam id == NULL dla rekordu Cezarego Muki. Chcemy zamiast NULL-a dostać tam jakiś niepusty, unikalny identyfikator. W tym celu zastosujemy funkcję HASHBYTES:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT COALESCE(testdata.id, CONVERT(VARCHAR, HASHBYTES('MD5', kto+gdzie+kiedy))) id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Takie zapytanie zwróci nam, owszem, unikalny tekst w kolumnie ID dla rekordu pana Czarka, ale będzie to wartość powstała bezpośrednio z konwersji wartości binarnej (HASHBYTES) na tekst - czyli tzw "krzaczki". A chcielibyśmy zamiast tego dostać ciąg cyfr szesnastkowych.

I tu w sukurs przychodzi trzeci element funkcji CONVERT, czyli styl. Styl jest liczbą całkowitą mówiącą w jaki sposób ma nastąpić konwersja na tekst. W przypadku danych będących datą / czasem styli jest mnóstwo i oznaczają one rozmaite rodzaje formatowania daty i godziny. Natomiast w przypadku danych typu varbinary (zwracanych przez HASHBYTES) styl może przyjąć wartość 1 lub 2. Jedynka oznacza konwersję bezpośrednią na tekst (czyli "krzaczki") i jest domyślna, natomiast dwójka oznacza zamianę bajtów na ich reprezentację heksadecymalną. Czyli dokładnie to, o co nam chodzi. Ostateczny kod będzie więc wyglądać następująco:
WITH testdata
AS ( SELECT '71B3F15E5351431AB5205356DF874F10' AS id
, 'adam kowalski' AS kto
, 'lublin' AS gdzie
, 'wczoraj' AS kiedy
UNION ALL
SELECT '3FEA61713A944057991767D873DF2DB0'
, 'jan malinowski'
, 'warszawa'
, 'jutro'
UNION ALL
SELECT '2404A5A361EB4EC99F34D8072F91AAD9'
, 'anna nowak'
, 'gdzie indziej'
, 'kiedykolwiek'
UNION ALL
SELECT NULL
, 'cezary muka'
, 'nie wiadomo'
, 'przedwczoraj'
UNION ALL
SELECT 'A221740F71B043818FE0F8368EDEEF2B'
, 'melinda brooks'
, 'nowy jork'
, 'teraz' )
SELECT COALESCE(testdata.id, CONVERT(VARCHAR, HASHBYTES('MD5', kto+gdzie+kiedy), 2)) id
, testdata.kto
, testdata.gdzie
, testdata.kiedy
FROM testdata;

Mała, niepozorna dwójeczka sprawi, że zamiast "krzaczków" dostaniemy ładny, unikalny, szesnastkowy identyfiktor.

Voila!

5 komentarzy

      1. … ewentualnie zmienię MD5 na SHA-256 i wtedy prawdopodobieństwo trafienia na kolizję w bilionie rekordów i tak będzie o 45 rzędów wielkości mniejsze od tego, że wielki meteor zniszczy naszą cywilizację w ciągu najbliższej sekundy 😉

      2. Nie nie słonko. Dla tych samych danych dostaniesz tego samego hasha. Co oznacza, że unikalność idzie w maliny.
        Ponieważ dane są w hurtowni, nie możesz zagwarantować unikalności danych, na podstawie których wyliczasz tegoż #a.
        Danem, na podstawie których wyliczasz, wzbogaciłbym o jakiś element niedeterministyczny – czas, random…

        1. Masz rację. Nieprecyzyjnie wyraziłem się, pisząc o unikalnym identyfikatorze. Chodziło tam raczej o unikalny identyfikator jednego z obiektów występujących w tabeli, a nie identyfikator całego rekordu. Jako źródło HASHBYTES użyłem kilku kolumn jednoznacznie identyfikujących ów obiekt. W przypadku przykładu, który pokazuję we wpisie, kolumny te to [kto], [gdzie] oraz [kiedy] – w rzeczywistości jednak kolumn mam o wiele więcej i zależy mi na tym, żeby dostać tę samą wartość ID dla tych samych zestawów [kto], [gdzie] i [kiedy].

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]