poniedziałek, 4 sierpnia 2008

Usuwanie duplikatów

Czasami zdarza się tak, że w naszej bazie dancyh występują duplikaty. Nie powinno się to zdarzyc jeżeli są dobre klucze podstawowe w tabelach. Jedkaże kiedy ich nie mamy, lub kiedy dane są importowane z pliku płaskiego, który nie ma żadnej kontroli poprawności danych, musimy sie pozbyć tych nadmiernych informacji.

Istnieje kilka sposobów na usuwanie duplikatów. W zależności od tego czy uważamy za duplikat wiersz, który ma wszystkie wartości w kolumnach takie same jak inny wiersz w tabeli czy duplikatem będzie dla nas wiersz którego jedna kolumna ma taką samą wartość w innym wierszu, trzeba rozważyć różne techiniki usuwania nadmiernych danych.

Na początek stworzę przykładową tabelę, którą wypełnię danymi.

USE TEMPDB
GO


IF OBJECT_ID('tempdb..#KLIENCI') IS NOT NULL
DROP TABLE #KLIENCI

CREATE TABLE #KLIENCI (
ID INT,
NAZWA VARCHAR(50),
TELEFON VARCHAR(9)
)


INSERT INTO #KLIENCI (ID, NAZWA, TELEFON)
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '987456321'
UNION ALL
SELECT '3', 'NOWY SWIAT', '564789321'
UNION ALL
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '111111111'


Pobierzmy teraz zawartość tabeli.

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
-------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111


Jak widać z powyższego listingu mamy dwa rodzaje duplikatów. Pierwszy dla klienta Contoso Ltd ma wszystkie wartości w kolumnach w obu wierszah identyczne. Drugi typ to klient Mercury Ltd (id = 2), który ma dwie różne wartości w kolumnie telefon.

Pierwszy typ duplikatów możemy usunąć korzystając z tabeli tymczasowej, którą wypełniamy unikalnymi wierszami. Następnie usuwamy wszystkie dane z naszej orginalnej tabeli a później kopiujemy zawartość tabeli tymczasowej do naszej tabeli pierwotnej.

SELECT DISTINCT * INTO #DUPLIKATY FROM #KLIENCI

TRUNCATE TABLE #KLIENCI

INSERT INTO #KLIENCI(ID, NAZWA, TELEFON) SELECT ID, NAZWA, TELEFON FROM #DUPLIKATY
DROP TABLE #DUPLIKATY


Pobierając zawartość naszej tabeli otrzymamy teraz następujący rezultat:

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321


Jak widać pozbyliśmy się jednego wiersza dla klienta Contoso Ltd. Technika ta będzie działać zarówno na SQL Server 2000 jak i nowszych wersjach.

Znacznie bardziej efektywną metodą i pozwalającą nam usuwać duplikaty, gdzie tylko kilka kolumn ma takie same wartości dla różnych wierszy jest technika z wykorzystaniem nowej funkcji, dostępnej od SQL Server 2005, ROW_NUMBER oraz CTE.

Korzystając z funkcji ROW_NUMBER tworzymy numery kolejnych wierszy, jednocześnie partycjonując nasze wyniki. W naszym przypadku uznajemy za duplikat wiersz który ma taką samą wartość identyfikatora klienta w dwóch lub więcej wierszach. Skorzystamy więc z funkcji ROW_NUMBER oraz bedziemy partycjonować nasze wyniki w oparciu o kolumnę id.

WITH DUPLIKATY AS (
SELECT ID, NAZWA, TELEFON, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #KLIENCI
)


Jeżeli byśmy wyświetlili zawartość naszego CTE, otrzymalibyśmy następujące rezultaty

ID NAZWA TELEFON RN
----------- ------------ --------- --
1 CONTOSO LTD 123456789 1
2 MERCURY LTD 111111111 1
2 MERCURY LTD 987456321 2
3 NOWY SWIAT 564789321 1


Teraz jedyne co musimy zrobić to usunąć nadmiarowe wiersze.

DELETE DUPLIKATY WHERE RN > 1

Pobieramy zawartość naszej tabeli

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
3 NOWY SWIAT 564789321


Teraz mając usunięte duplikaty, możemy założyć klucz podstawowy w naszej tabeli.

niedziela, 3 sierpnia 2008

Importowanie danych z XML do bazy danych.

WstępAplikacje biznesowe coraz częściej wykorzystują dane pochodzących z róznych źródeł. Coraz częsniej dane te dostarczane są w formacie XML. SQL Server 2005 oferuje wiele funkcji, które umożliwiają przetwarzanie danych w formacie XML.
SQL Server 2005 potrafi przechowywać dane jako dokumenty XML, lub też przetwarzać je i przechowywac zachowując model relacyjnej bazy danych.


Praktyczne zastosowanie
Nie zanudzając dalej teorią pora przejść do konkretnego zastowanie XML w powiązaniu z SQL Server 2005. Poniższy przykład pokaże z jaką łatwością dane mogą zostać zaimportowane z pliku, lub łancucha znaków w formacie XML do tabeli bazy danych.
Poniższy przykład jako dokument XMl będzie wykorzystywał tabelę kursów walut publikowaną na stronach Narodowego Banku Polskiego.
Fragment dokumentu XML, z którego będę korzystał w dalszej częsci tego artykułu:


<tabela_kursow typ="A">
<numer_tabeli>150/A/NBP/2008</numer_tabeli>
<data_publikacji>2008-08-01</data_publikacji>
<pozycja>
<nazwa_waluty>dolar australijski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>AUD</kod_waluty>
<kurs_sredni>1,9339</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar Hongkongu</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>HKD</kod_waluty>
<kurs_sredni>0,2649</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar kanadyjski</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>CAD</kod_waluty>
<kurs_sredni>2,0138</kurs_sredni>
</pozycja>
<pozycja>
<nazwa_waluty>dolar nowozelandzki</nazwa_waluty>
<przelicznik>1</przelicznik>
<kod_waluty>NZD</kod_waluty>
<kurs_sredni>1,5061</kurs_sredni>
</pozycja>
</tabela_kursow>


Potrzebne będą jeszcze tabele które będą przechowywać dane.

USE tempdb
GO
CREATE TABLE dbo.WALUTY (
KOD_WALUTY CHAR(3) PRIMARY KEY NOT NULL,
NAZWA_WALUTY VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.KURSY_WALUT (
KOD_WALUTY CHAR(3) NOT NULL,
KURS_SREDNI MONEY NOT NULL,
DATA DATETIME NOT NULL,
PRZELICZNIK SMALLINT NOT NULL,
PRIMARY KEY (KOD_WALUTY, DATA),
FOREIGN KEY (KOD_WALUTY) REFERENCES dbo.WALUTY (KOD_WALUTY)
)


Musimy jeszcze zadeklarować zmienną, która będzie przechowywała nasz dokument XML. Zmienna ta jest typu XML.

DECLARE @XMLDATA AS XML

Aby załadować zawartość pliku XMl do naszej zmeinnej skorzystamy z funkcji OPENROWSET. W SQL Server 2005 funkcja OPENROWSET umożliwia czytanie zawartości pliku bez konieczności korzystania z tabeli. Dzięki korzystamy tylko z jednej istrukcji SELECT.

SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X).

Mając utworzone tabele oraz zadeklarowaną zmienna przechowującą zawartość pliku XML możemy przejść do przetwarzania tych danych. W tym celu wykorzystamy procedure, która jako parametr będzie przyjmowała naszą zmienną XML, a raczej dokument XML, który załadowaliśmy wcześniej do zmiennej.
Procedura przyjmuje dokument XML jako swój parametr a nie scieżkę do pliku. Jest to o tyle istotne, że procedura ta może być wykorzystana w dowolnej aplikacji, która będzie przekazyła dokument XML jako łańcuch znaków. Aplikacja taka może np codziennie pobierać dane bezpośrednio ze stron NBP, i przekazywać zawarośc dokumentu do procedury, bez konieczności materializowania pliku na dysku. O tym jak łatwo można zamienić Dataset w aplikacji .NET do łańcucha znaków kilka słów poświęcę na koniec tego dokumentu.
Nazwijmy naszą procedurę IMPORT_NBP

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS


Na początek musimy zadeklarować zmienną oraz tymczasową tabelę, które pomogą nam w przetwarzaniu danych.

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)


Data publikacji występuje w naszym dokumencie XMl tylko raz. Aby kod był bardziej czytelny będziemy tę wartość przechowywać w zmiennej DATA_PUBLIKACJI.
Tymczasowa tabela KURSY_WALUT będzie przechowywała tymczasowo zawartość naszych elementów XML.
W celu przypisania wartości zmeinnej DATA_PUBLIKACJI skorzystamy z funkcji value.

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

Proszę zwrócić uwagę na fakt, że funkcja value jak i wszystkiego pozostałe fukcje oraz elementy XML są pisane małymi literami. XML rozróżnia wielkość liter. Funkcja text() odczytuje zawartość elementu data_publikacji. Proszę zwrócić uwagę na index elementu [1]. Oznacza to, że funkcja value pobiera pierwszy element data_publikacji w dokumencie XML. Indeksowanie elementów rozpoczyna się od wartości 1, inaczej jak ma to miejsce w .NET Framework, gdzie indeksowanie rozpoczyna się domyślnie od 0. Jeżeli w naszym dokumecie występowałoby więcej elementów data_publikacji moglibyśmy użyć innej wartości indeksu. Zawartość naszego elementu data_publikacji odczytujemy jako DATETIME. Uzywając funkcji value musimy podać jakiego typu jest nasz element.
Mając już datę publikacji przejdźmy do odczytywanie kolejnych danych. Teoretycznie moglibyśmy to zrobić w ten sam sposób jak odczytaliśmy datę, zmieniając tylko element XML z /tabela_kursow/data_publikacji na /tabela_kursow/pozycja/nazwa_valuty. Z praktycznego punktu widzenia jest to jednak zadanie bardzo karkołomne gdyz najpierw musielibyśmy znać ilość elementów w naszym dokumencie XML a później pobierać każdy z nich z osobna.
Aby sprawić, żeby nasz kod był bardziej uniwersalny skorzystamy z dwóch dodatkowych funkcji, służących do przetwarzania dokumentów XML. Będą to funkcje query i nodes.
Poniższe zapytanie zwróci nam wszystkie elementy pozycja z osobna.

SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)

Teraz korzystając z kolejnego, zewnętrznego zapytania możemy pobrać wartości każdego z elementów naszego dokumentu XML. Wszystkie te dane wstawimy do naszej tabeli tymczasowej KURSY_WALUT.

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)


W tym miejscu poświęcę kilka słów średniemu kursowi wymiany. Nasz dokument XML przechowuje tę wartość formie liczby zmienno-pozcyjnej, używając jako separatora dziesiętnego przecinka. SQL Server 2005 domyślnie używa jako separatora kropki. Gdybyśmy odczytali wartośc kurs_sredni jako typ MONEY w naszej tabeli znaleźlibyśmy nie 1,9339 a 19339.00. Dlatego też odczytujemy tę wartość jako typ NVARCHAR, a funkcja REPLACE zatępuje znaki [,] na [.].
W ten sposób mamy już nasze dane z pliku XML w tabeli tymczasowej.
Na koniec pozostaje nam zapisanie wszystkich dancyh do naszych tabel docelowych.
Najpierw zapisujemy dane do tabeli WALUTY, sprawdzając przy okazji czy podane waluta już nie istnieje w naszej bazie danych.

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)


Mając uzupełnioną tabelę z walutami możemy zapisać kursy walut, przy okazji sprawdzając czy kurs waluty dla danego dnia już nie istnieje.

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)


Na koniec pozostaje nam usunięcie tabeli tymczasowej

DROP TABLE #KURSY_WALUT

A także ostatnie

GO

Pora teraz sprawidzić naszą procedurę.

DECLARE @XMLDATA AS XML
SELECT @XMLDATA = X FROM OPENROWSET(BULK 'C:\NBP.xml', SINGLE_BLOB) AS IMPORT(X)

EXEC IMPORT_NBP @XMLDATA

SELECT * FROM dbo.KURSY_WALUT
SELECT * FROM dbo.WALUTY


Jak już wspomniałem wcześniej procedura ta może zostać wykorzystana w aplikacji, która pobiera dane ze strony www, a później przekazuje cały dataset jako parametr w procedurze.
Zamiana całego dataset w .NET na łańcuch znaków w formacie XML to tylko jedna instrukcja:
Dataset1.GetXml


Podsumowanie
Jak widać na powyższym przykładzie w SQL Server 2005 bardzo łatwo możemy pzretwarzać dane w formacie XML. Dane nie muszą być przetwarzane w aplikacji, tylko wszystko przekazane zostaje na ‘barki’ serwera bazy danych.


Na koniec kompletny kod naszej procedury.

CREATE PROCEDURE IMPORT_NBP(
@XMLDATA XML
)
AS

DECLARE @DATA_PUBLIKACJI AS DATETIME

CREATE TABLE #KURSY_WALUT (
NAZWA_WALUTY NVARCHAR(100),
PRZELICZNIK SMALLINT,
KOD_WALUTY CHAR(3),
KURS_SREDNI MONEY
)

SELECT @DATA_PUBLIKACJI = X.value('(/tabela_kursow/data_publikacji/text())[1]', 'DATETIME') FROM @XMLDATA.nodes('.') AS IMPORT(X)

INSERT INTO #KURSY_WALUT (NAZWA_WALUTY, PRZELICZNIK, KOD_WALUTY, KURS_SREDNI) (
SELECT
NODE.value('(/pozycja/nazwa_waluty/text())[1]', 'NVARCHAR(100)'),
NODE.value('(/pozycja/przelicznik/text())[1]', 'SMALLINT'),
NODE.value('(/pozycja/kod_waluty/text())[1]', 'CHAR(3)'),
REPLACE(NODE.value('(/pozycja/kurs_sredni/text())[1]', 'NVARCHAR(15)'), ',', '.')
FROM (
SELECT X.query('.') AS NODE FROM @XMLDATA.nodes('tabela_kursow/pozycja') AS IMPORT(X)
) AS ELEMENTY
)

INSERT INTO dbo.WALUTY (KOD_WALUTY, NAZWA_WALUTY) (
SELECT KOD_WALUTY, NAZWA_WALUTY FROM #KURSY_WALUT
WHERE KOD_WALUTY NOT IN (SELECT KOD_WALUTY FROM dbo.WALUTY)
)

INSERT INTO dbo.KURSY_WALUT (KOD_WALUTY, KURS_SREDNI, DATA, PRZELICZNIK) (
SELECT KOD_WALUTY, KURS_SREDNI, @DATA_PUBLIKACJI, PRZELICZNIK FROM #KURSY_WALUT AS KW
WHERE NOT EXISTS (
SELECT KOD_WALUTY, DATA FROM KURSY_WALUT WHERE KOD_WALUTY = KW.KOD_WALUTY AND DATA = @DATA_PUBLIKACJI
)
)

DROP TABLE #KURSY_WALUT

GO