NARZĘDZIA INFORMATYCZNE/SQL

Kurs SQL-a dla analityków

Wojciech Gardziński

właściciel firmy AFIN;

Krzysztof Rumiński

właściciel firmy SYSTEM 2001;

Pytania: czytelnicy.controlling@infor.pl

Każdy analityk biznesowy przechodzi ten proces: od zafascynowania intuicyjnym interfejsem i sposobem obsługi Excela aż do… znużenia koniecznością powtarzania tych samych czynności i pokonywania tych samych kłopotów, coraz częstszych i coraz poważniejszych. Nabieramy doświadczenia, wiedzy i umiejętności, model analityczny komplikuje się. A pracochłonność i kłopoty jego obsługi wydają się narastać i nie mieć końca.

Jeżeli nadchodzi czas zmiany podejścia, widzimy konieczność zdobycia wiedzy i umiejętności, których nie da się zdobyć przez klikanie na nieznane ikonki i studiowanie helpa na własną rękę.

Pierwszy etap, trzeba przejść od architektury tradycyjnej (otworzę plik Excela, a potem zobaczę, co się da zrobić) do architektury klient-serwer (nie zaglądam do pliku, pytam się go o interesujące mnie informacje).

Program MS Query, nieodłączny, a rzadko używany przez analityków program zintegrowany z Excelem ma też kilka ikonek, przy pomocy których można zapytać bazę o informacje i stworzyć, imponujące swą formą i łatwością pielęgnacji, raporty. Ale, aby zacząć klikać na te ikonki, trzeba właśnie uzyskać trochę wiedzy nowego rodzaju.

Ci, co dotarli aż tutaj, zapewne znają nasze publikacje na ten temat. Tu już trzeba coś wiedzieć o strukturze danych, sposobach manipulowania tablicami, tworzenia relacji, filtrowania wierszy, wybierania kolumn i tworzeniu nowych, własnych. I, wiedzieć to wszystko właśnie w kontekście Excela.

Jeśli to wiemy, w nagrodę, przy pomocy kilku kliknięć myszą, możemy otrzymać raport, niemożliwy do uzyskania inną metodą. Zawierający wyniki, aktualne dla danych, przed chwilą wprowadzonych do systemu transakcyjnego, odległego o tysiące kilometrów albo do systemu budżetowego, opartego o setki skoroszytów – wszystko po jednym kliknięciu!

Smak pierwszych korzyści innego podejścia do danych – wytwarza potrzebę poznania „języka strukturalnych zapytań” (Structured Query Language, SQL), a, z naszego punktu widzenia, tak naprawdę – języka analizy poprzez zapytania.

Niniejszy artykuł jest skierowany do analityków, którzy dojrzeli już do potrzeby poznania samego języka SQL. To niewątpliwie nowe i trudne, ale możliwe. Zawarta tutaj wiedza jest nieznana zdecydowanej większości analityków, więc, jeśli zdecydujesz się czytać dalej (a może i ćwiczyć), niewątpliwie sam zapisujesz się do elity.

Instalujemy „Edytor zapytań SQL” (śródtytuł poziom I)

Pobierz plik: http://afin.net/excel/SQL_KompletnyEdytorZapytanPlusDanePrzykladowe.zip. Rozpakuj na dysku, najlepiej bezpośrednio na dysku C:. Powstanie folder SQL_w_Excelu, w którym znajdą się wszystkie niezbędne pliki.

Jeżeli rozpakujesz w innym folderze lub na innym dysku, wszystkie ścieżki będziesz musiał poprawić ręcznie z C:\SQL_w_Excelu\... na TwójDysk:\TwójFolder\SQL_w_Excelu…. Jeżeli chcesz ograniczyć ewentualne problemy ze ścieżkami i błędami ODBC (zdarzają się), radzimy instalację w ścieżce domyślnej, a jeżeli nie jest to możliwe, w jak najprostszej ścieżce bez spacji i „dziwnych” znaków, szczególnie kropek, przecinków, nawiasów itp.

Zapinamy pasy i startujemy:

1. Otwórz plik C:\SQL_w_Excelu\ADO4Excel.xls, a w nim przejdź do pierwszego arkusza o nazwie IntoASheetFromExcel. Uwaga: makra muszą być włączone.

2. W [B3] wpisz Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\SQL_w_Excelu\Dane\Sprzedaz.xls”.

4. W [B4] wpisz SELECT * FROM [faktura$].

5. Kliknij przycisk Insert data.

Jeżeli w arkuszu pojawiły się nowe dane, życzymy Ci powodzenia w ćwiczeniach z SQL! Jeśli nie, skontaktuj się z firmowym informatykiem.

Ogólne zasady stosowane tekście (śródtytuł poziom I)

Ćwiczymy na bazie Excelowej – pliku Excela jako domyślnym źródle danych. Wszelkie różnice są szczegółowo opisane.

Wielkość liter:

· Wszystkie słowa kluczowe (klauzule, ale też funkcje) języka SQL piszemy wielkimi literami. Nie ma to technicznego znaczenia, ale powoduje czytelność zapytania.

· Nazwy pól piszemy małymi literami (z wyjątkami).

· Aliasy nazw pól piszemy tak, jak potrzebujemy. Pamiętajmy jednak, że alias nazwy pola staje się nazwą pola przy zapytaniu do stworzonej przez nas tabeli.

Konwencja zapisu zapytania:

SELECT *

FROM [faktura$]

WHERE wart_net>50

Klauzula SELECT w innej linii niż FROM, potem WHERE itd. Ma to na celu wyłącznie zwiększenie czytelności zapytania. Jeżeli chcemy dane zapytanie uruchomić, to w implementacji Excelowej, gdy wymagany jest jeden ciąg zapytania, z powyższego należy zrobić tekst: SELECT * FROM [faktura$] WHERE wart_net>50 (znak interlinii należy zastąpić spacją; tekst nie może być przełamany interlinią), a gdy chcemy użyć go w edytorze zapytań w MS Query, możemy pozostawić wieloliniową postać zapytania, ale każda linia (oprócz ostatniej) musi kończyć się spacją (w przykładzie poniżej oznaczoną podkreślnikiem „_”), np.:

SELECT *_

FROM [faktura$]_

WHERE vat>10

Notacja znaków zastrzeżonych:

Stosujemy ją odpowiednio do sterownika ODBC Excela (czyli dostępu do plików Excela), ale również odpowiednio do systemu, z poziomu którego zapytanie jest realizowane – ma to niekiedy znaczenie. Dla przykładu, symbol wieloznaczny (wildcard) „dowolny ciąg znaków” w SQL to %, ale w SQL, uruchamianym z Accessa – jest to klasyczna, DOS-owa *, operator konkatenacji to &, ale nie w PostgreSQL – tam || itp. Może się zdarzyć, że nie przewidzieliśmy tutaj wszystkich wyjątków – odsyłamy wtedy po wiedzę do wszystkowiedzącej wyszukiwarki internetowej.

Słowniczek:

· BD – baza danych, SQL – Structered Query Language.

· Znak $ na końcu nazwy tabeli – oznaczenie arkusza w skoroszycie Excela. Jeżeli zapytania odnoszą się do plików Excela – stosujemy Excelowy zapis arkuszowy [faktura$] – wszystkie inne zapytania mają zapis tabelowy – faktura.

· Konkatenacja = łączenie tekstów.

· Iloczyn kartezjański tabel – tzw. CROSS JOIN, zjawisko występujące przy powstaniu zapytania do więcej, niż jednej tabeli, przy czym nie wszystkie tabele są połączone sprzężeniami (relacjami), polegający na zwielokrotnieniu ilości wierszy wyniku zapytania.

· BD MS JET – baza danych Microsoft Jet – „Silnik bazodanowy” Microsoft’a, obejmujący BD jego produkcji: MS SQL Server, MS Access, MS Excel. Poza liberalniejszym traktowaniem złej składni SQL-a, nie ma to większego znaczenia praktycznego.

Ćwiczenia SQL (śródtytuł poziom I)

Wybór pól z tabeli (śródtytuł poziom II)

Wszystkie pola:

SELECT *

FROM [faktura$]

Określone pole:

SELECT nazwa

FROM [faktura$]

Kilka określonych pól:

SELECT nazwa, wart_net

FROM [faktura$]

Jeżeli nazwa pola zawiera znaki niedozwolone:

Stosujemy zapis [nazwa pola] lub `nazwa pola` (tzw. lewe apostrofy – nad tabulatorem na klawiaturze). Jedynym znakiem spoza liter i cyfr, traktowanym na równi z nimi, jest ‘_’ (podkreślnik). Wielkość liter, stosowanych w nazwach tabel, nie ma znaczenia.

Alias pola (śródtytuł poziom II)

Stosujemy wówczas gdy chcemy, aby nasze pole nazywało się inaczej, niż w źródle danych.

Jeżeli nie stosujemy znaków niedozwolonych dla nazw pól:

SELECT nazwa AS klient

FROM [faktura$]

Jeżeli stosujemy znaki niedozwolone:

SELECT nazwa AS [mój-klient ze spacjami i %$&]

FROM [faktura$]

Uwaga

Nawiasy kwadratowe dotyczą również aliasów tabel.

Pola obliczeniowe (śródtytuł poziom II)

Wartości liczbowe

podwojenie wartości sprzedaży:

SELECT nazwa AS [mój klient], wart_net*2 AS [podwójna sprzedaż]

FROM [faktura$]

Wartości tekstowe

pierwsza litera nazwy klienta:

SELECT LEFT(nazwa,1) AS [mój klient], wart_net*2 AS [podwójna sprzedaż]

FROM [faktura$]

Wartości dat

pobranie daty w określonym formacie tekstowym:

SELECT data, format(data,'"Y"yyyy" Q"q" M"mm "D"dd') AS 'Data sformatowana'

FROM faktura

Y-Rok, Q-kwartał, M-miesiąc, D-dzień.

Uwaga

Sterownik ODBC Excela nie obsługuje funkcji FORMAT. Działa ona wyłącznie w Accessie i SQLS. W innych bazach ma inną nazwę i składnię).

pobranie fragmentu daty:

SELECT YEAR(data) AS [Rok], MONTH(data) AS [Miesiąc], DAY(data) AS [Dzień]

FROM [faktura$]

Uwaga

Nie należy (choć jest to czasami możliwe) stosować funkcji bez szczegółowego dostosowania typu jej argumentu, np. pobór roku w taki sposób SELECT LEFT(data,4) powiedzie się w BD MS JET, ale stanowczo zostanie odrzucony w innych BD. Nie należy przyzwyczajać się do liberalności BD MS JET.

Wybór rekordów (śródtytuł poziom II)

Rekordy unikatowe

jedno pole:

SELECT DISTINCT nazwa

FROM [faktura$]

kilka pól – unikatowość dotyczy kombinacji wartości obu pól:

SELECT DISTINCT nazwa, nr_fakt

FROM [faktura$]

Kryteria na polach tekstowych

faktury klienta o konkretnej nazwie (prosty filtr):

SELECT *

FROM [faktura$]

WHERE nazwa='BRONEX'

Uwaga

Powinniśmy nazwę (wartość tekstową) pisać zawsze tak, jak jest w tabeli (dużymi lub małymi literami). W BD MS JET nie jest to wymagane, ale – ze względu na niezgodność ze standardem SQL – nie jest polecane. Czyli – wielkość znaków w klauzuli WHERE ma znaczenie!

Wybór klientów na literę A w kwerendzie w MS Access:

SELECT *

FROM [faktura$]

WHERE nazwa LIKE 'A*'

Powyższe zapytanie zadziała wyłącznie wówczas gdy zostanie uruchomione w Accessie. W naszym edytorze nie zwróci żadnych rekordów.

Wybór klientów na literę A w Excelu i większości pozostałych edytorów zapytań SQL:

SELECT *

FROM [faktura$]

WHERE nazwa LIKE 'A%'

Uwaga

W standardzie SQL symbole wieloznaczne (wildcard-y) to % (dowolny ciąg dowolnych znaków) i „_” (pojedynczy dowolny znak).

Wybór klienta z literą R na drugim miejscu:

SELECT *

FROM [faktura$]

WHERE nazwa LIKE '_R%'

Wybór zakresu liter:

SELECT *

FROM [faktura$]

WHERE nazwa LIKE '[A-C]%'

Wybór faktur klientów, których nazwa zaczyna się na J, kończy na X i o wartości faktury powyżej 50:

SELECT *

FROM [faktura$]

WHERE nazwa LIKE 'J%X' AND wart_net>50

Wybór klienta, którego nazwa nie zaczyna się na J, nie kończy na X i o wartości faktury poniżej 50 (wszyscy pozostali poza J%X i powyżej 50, negacja poprzedniego):

SELECT *

FROM [faktura$]

WHERE NOT (nazwa LIKE 'J%X' AND wart_net>50)

lub

SELECT *

FROM [faktura$]

WHERE nazwa NOT LIKE 'J%X' OR NOT wart_net>50

Wybór z listy

zadanej:

SELECT *

FROM [faktura$]

WHERE nazwa IN ('BRONEX', 'CELIMP', 'JUREX')

pobranej z innego zapytania:

wyświetla wszystko z tabeli faktura, gdzie odbiorca jest z Warszawy (dane pobiera z innej tabeli odbiorca – bez zastosowania sprzężenia między tabelami)

SELECT *

FROM [faktura$]

WHERE nazwa IN

(SELECT nazwa FROM [odbiorca$] WHERE miasto='WARSZAWA')

Zapytanie zagnieżdżone

zapytanie do podzapytania:

SELECT *

FROM

(SELECT *

FROM [faktura$]

WHERE nazwa LIKE '%X')

WHERE nazwa LIKE 'A%'

zwraca wynik – zestaw faktur klientów, zaczynających się na A z wyniku pierwszego podzapytania, które zawęziło wynik tylko do klientów, kończących się na X (graficzne wcięcie podzapytania – tylko dla czytelności).

Wybór daty

data, podana jako określona wartość (w ‘#’):

SELECT *

FROM [faktura$]

WHERE data = #2004-06-03#

daty z różnych przedziałów czasu:

SELECT *

FROM [faktura$]

WHERE data <= #2004-06-03# OR data >= #2005-01-01#

Uwaga

Sposób zapisu daty jest różny dla różnych BD. W BD Oracle, określoną datę podaje się np. tak: ‘2010-01-31’

Operatory logiczne i matematyczne:

SQL używa wszystkich znanych operatorów logicznych AND, OR, NOT oraz matematycznych: =, >, <, >=, <=, <>. W kryteriach można stosować też funkcje SQL, np. NOW, LEFT, MONTH i setki innych.

Inne operatory

‘between’, czyli ‘pomiędzy wartościami’:

SELECT *

FROM [faktura$]

WHERE data BETWEEN #2004-06-03# AND #2004-12-31#

SELECT *

FROM [faktura$]

WHERE wart_net BETWEEN 10 AND 50

SELECT *

FROM [faktura$]

WHERE nazwa BETWEEN ‘AR’ AND ‘JU’

Uwaga

‘BETWEEN’ działa nie tylko na polach numerycznych (przedział obustronnie domknięty).

Operator ‘IS [NOT] NULL’ (‘Jest puste’ lub ‘Jest niepuste’):

SELECT *

FROM [faktura$]

WHERE termin IS NOT NULL

Uwaga

Wartość ‘NULL’ nie jest tożsama z wartością 0 (zero) lub ‘’ (ciągiem pustym) lub jakąkolwiek inną. ‘NULL’ to brak wartości, innej, niż ‘NULL’.

Agregowanie danych (śródtytuł poziom II)

Wartość sprzedaży według klientów

+ suma sprzedaży ogółem:

SELECT SUM(wart_net)

FROM [faktura$]

+ zmiana nazwy kolumny:

SELECT SUM(wart_net) AS [sprzedaż ogółem]

FROM [faktura$]

+ grupowanie po polu nieagregowanym (tu: nazwie):

SELECT nazwa, SUM(wart_net) AS [sprzedaż]

FROM [faktura$]

GROUP BY nazwa

Ogólna zasada dotycząca grupowania (używania ‘GROUP BY’): grupujemy zawsze po wszystkich polach nieagregowanych.

+sortowanie po nazwie (sortowanie po wartości agregującej):

SELECT nazwa, SUM(wart_net) AS [sprzedaż]

FROM [faktura$]

GROUP BY nazwa

ORDER BY nazwa

Uwaga

Domyślny porządek sortowania – rosnąco (‘ASC’).

+ ograniczenie rankingu do klientów którzy mają faktury o wartości większej niż 50

(kryterium na wartości surowej):

SELECT nazwa, SUM(wart_net) AS [sprzedaż]

FROM [faktura$]

WHERE wart_net>50

GROUP BY nazwa

ORDER BY SUM(wart_net) DESC

Sortowanie po wartościach zagregowanych:

Aby wybierać rekordy po wartościach zagregowanych (agregatach) stosujemy klauzulę HAVING. having jest podobne WHERE, ale dotyczy wyłącznie agregatów i jest wstawiane po Group BY.

Sortowanie po wartości sprzedaży malejąco (sortowanie po agregacie)

Ranking kontrahentów według wielkości sprzedaży:

SELECT nazwa, SUM(wart_net) AS [sprzedaż]

FROM [faktura$]

GROUP BY nazwa

ORDER BY SUM(wart_net) DESC

Kryterium na agregacie

Ograniczenie rankingu do klientów, którzy mają faktury o wartości większej niż 50

Dla porównania – zobacz podobne zapytanie z kryterium na wartości surowej:

SELECT nazwa, SUM(wart_net) AS [sprzedaż]

FROM [faktura$]

GROUP BY nazwa

HAVING SUM(wart_net)>50

ORDER BY SUM(wart_net) DESC

Łączenie tabel (śródtytuł poziom II)

Łączenie pionowe tabel (konsolidacja danych):

Łączenie dwóch tabel 1 pod 2-gą z tej samej lub dwóch (lub więcej) różnych baz danych.

Łączenie pionowe – wszystkie rekordy:

(ta sama BD)

SELECT *, 'Tabela1' AS [Indeks tabeli] FROM [faktura$]

UNION ALL

SELECT *, 'Tabela2' AS [Indeks tabeli] FROM [faktura$]

(różne BD)

SELECT*, ‘Access’ AS [Indeks źródła danych] FROM faktura IN 'C:\SQL_w_Excelu\Dane\Sprzedaz.mdb'

UNION ALL

SELECT *, 'Excel' AS [Indeks źródła danych] FROM [faktura$] IN 'C:\SQL_w_Excelu\Dane\Sprzedaz.xls'[Excel 5.0;]

Łączenie pionowe – rekordy unikalne:

Łączenie dwóch tabel 1 pod 2-gą z dwóch różnych baz danych bez powtarzania tych samych rekordów.

SELECT *, ‘Access’ AS [Indeks źródła danych] FROM faktura IN 'C:\SQL_w_Excelu\Dane\Sprzedaz.mdb'

UNION

SELECT *, ‘Excel’ AS [Indeks źródła danych] FROM [faktura$] IN 'C:\SQL_w_Excelu\Dane\Sprzedaz.xls'[Excel 5.0;]

Relacje

Standardowy zapis relacji – tu: wybór rekordów, spełniających warunek, z iloczynu kartezjańskiego dwóch tabel

(relacja dwóch tabel po polu nazwa z tabel faktura i odbiorca)

SELECT *

FROM [faktura$], [odbiorca$]

WHERE faktura.nazwa=odbiorca.nazwa

Uwaga

Jeśli mamy pola z więcej niż 1 tabeli to, używając nazw pól lub ich aliasów, zawsze piszemy tzw. „nazwę kwalifikowaną”, tj. NazwaTabeli.NazwaPola (nazwa tabeli – kropka – nazwa pola).

Wersja poprawna zapisywania relacji, tzw. „INNER JOIN”:

SELECT *

FROM [faktura$] INNER JOIN [odbiorca$] ON faktura.nazwa=odbiorca.nazwa

+ aliasy tabel:

SELECT *