Bazy danych i SQL a ptaki polskie (Kto pyta nie błądzi)

Treści multimedialne - kodowanie,
przetwarzanie, prezentacja
Odtwarzanie treści multimedialnych
Andrzej Majkowski
informatyka +
1
TYTUŁ:
Bazy danych i SQL a ptaki polskie
(Kto pyta nie błądzi)
AUTOR: Andrzej Ptasznik
2
Bazy danych są wszędzie
• Bazy danych są wszędzie
• Gromadzimy różne dane
• Zapotrzebowanie na gromadzenie danych
stale rośnie
• W jakim celu gromadzimy dane??
• Wspieranie codziennej działalności
• Sprawozdawczość i archiwizacja
• Analiza, analiza, analiza!!!!!!!
• ?????
3
Zacznij od … bazy
• Dziedzina problemu
• Technologia
• Projekt bazy danych
• Rejestracja danych
• Wykorzystanie bazy danych - analiza
4
Bazy danych opisują różne dziedziny
• Każda baza danych jest obrazem pewnej
rzeczywistości(dziedziny problemu)
• Najczęściej wykorzystywane są bazy danych
oparte na modelu relacyjnym
• Podstawą modelu relacyjnego jest pojęcie
tabeli
• Wniosek : W relacyjnych bazach danych
odwzorowujemy dziedzinę problemu za
pomocą dwuwymiarowych tabel
5
Dziedzina problemu
Formułujemy problem :
Chcemy rejestrować obserwacje ptaków na
obszarze Polski
Zadanie :
Zaprojektować odpowiednia bazę danych
Krok pierwszy :
Rozpoznanie dziedziny problemu
6
Technologia
Do
tworzenia bazy danych o nazwie
„PtakiPolskie” wykorzystamy technologię
MS SQL Server 2012 Express Edition
Technologia dostępna darmowo nawet do
zastosowań komercyjnych
MS SQL Server 2012 jest najczęściej
wykorzystywaną technologią przy realizacji
nowych projektów
7
Projektujemy bazę danych
Fakt 1 – Z rozpoznania dziedziny problemu
wynika, że gatunki ptaków są łączone w
rodziny a rodziny w rzędy
Przykładowa
zawartość
Struktura tabeli
IdRzedu
1
2
3
4
5
6
Klucz
podstawowy
NazwaPL
blaszkodziobe
brodzace
nury
pełnopłetwe
perkozy
wróblowe
NazwaLac
Anseriformes
Ciconiiformes
Gaviiformes
Pelecaniformes
Podicipediformes
Passeriformes
Typy danych
8
Projektujemy bazę danych cd.
Wymaganie : Rodziny ptaków są gromadzone w
rzędy
Klucz obcy
IdRodziny
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
NazwaPL
bocianowate
czaplowate
drozdowate
ibisowate
jaskółkowate
jemiołuszkowate
kaczkowate
krukowate
łuszczaki
nury
perkozy
pliszkowate
sikory
skowronki
wróblowate
NazwaLac
Ciconiidae
Ardeidae
Turdidae
Threskiornithidae
Hirundinidae
Bombycillidae
Anatidae
Corvidae
Fringillidae
Gaviidae
Podicipedidae
Motacillidae
Paridae
Alaudidae
Passeridae
IdRzedu
2
4
6
4
6
6
1
6
6
3
5
6
6
6
6
Wiersz z tabeli Rzedy
9
Projektujemy bazę danych cd.
10
Projektujemy bazę danych cd.
Fragment bazy danych opisujący podział
administracyjny w Polsce
11
Projektujemy bazę danych cd.
Baza danych do rejestracji obserwacji ptaków w Polsce
12
Język SQL - zapytania
• Na etapie projektu
pomocy tabel
opisaliśmy
problem
przy
• Korzystając z bazy danych realizujemy zapytania
(przetwarzanie danych, łączenie tabel, obliczenia)
• W języku SQL do realizacji zapytań służy polecenie
SELECT
• Polecenie SELECT umożliwia trzy typy realizacji
zapytań:
• Zapytania proste
• Zapytania agregujące
• Zapytania wykorzystujące przetwarzanie
w oknie
13
Zapytania proste
SELECT
(Konstruktor wiersza wynikowego)
FROM
tabel)
(Skąd pobieramy dane – łączenie
WHERE
(Filtrowanie – warunki selekcji)
ORDER
BY
zapytania)
(Porządkowanie
wyniku
14
Zapytanie proste - przykład
Podstawową
FROM
klauzulą
From Gatunki JOIN Rodziny
polecenia
Select
jest
Opisujemy połączenie
niezbędnych tabel
ON Gatunki.IdRodziny=Rodziny.IdRodziny
Wynik połączenia
15
Zapytanie proste - przykład
Dodajemy warunek selekcji :
From Gatunki JOIN Rodziny
ON Gatunki.IdRodziny=Rodziny.IdRodziny
Where IdRzedu=5
Wynik po filtrowaniu
16
Zapytanie proste - przykład
Formułujemy wyrażenia konstruktora wiersza – budujemy
postać tabeli wynikowej
Uwaga: dla każdego wiersza otrzymanego z przetwarzania
FROM … WHERE budujemy jeden wiersz wyniku
Select Gatunki.NazwaPL+N'( łac.' +Gatunki.NazwaLac+')' as Gatunek,
Rodziny.NazwaPL as Rodzina
From Gatunki JOIN Rodziny
ON Gatunki.IdRodziny=Rodziny.IdRodziny
Where IdRzedu=5
Wynik po
przetworzeniu
17
Zapytanie proste - przykład
Dodatkowo, wynik można uporządkować według
określonego kryterium
Select Gatunki.NazwaPL+N'( łac.' +Gatunki.NazwaLac+')' as Gatunek,
Rodziny.NazwaPL as Rodzina
From Gatunki JOIN Rodziny
ON Gatunki.IdRodziny=Rodziny.IdRodziny
Where IdRzedu=5
Wynik po
uporządkowaniu
Order By Gatunek
18
Zapytania agregujące - przykład
Funkcje agregujące (wykonują obliczenia dla
tabeli wynikowej) :
COUNT - zlicza ilość wierszy
SUM - sumuje wartości wyrażenia dla tabeli
AVG - oblicza wartość średniej arytmetycznej dla tabeli
MIN - określa wartość minimalna wyrażenia
MAX - określa wartość maksymalna dla wyrażenia
Zastosowana, w konstruktorze wiersza, funkcja agregująca zwraca jeden
wiersz wynikowy niezależnie od ilości wierszy przekazanych do przetworzenia
Select COUNT(*) as IleGatunkow
From Gatunki
19
Zapytania agregujące - przykład
Select Rodziny.NazwaPL as Rodzina,
COUNT(*) As IleGatunkow
From Gatunki JOIN Rodziny ON Gatunki.IdRodziny=Rodziny.IdRodziny
Group By Rodziny.NazwaPL
Having COUNT(*) BETWEEN 4 AND 10
Order By IleGatunkow DESC
Wynik zapytania
20
Grupowanie danych
Select Rodziny.NazwaPL as Rodzina,
Gatunki.NazwaPL
From Gatunki JOIN Rodziny ON Gatunki.IdRodziny=Rodziny.IdRodziny
Rodzina
Gatunek
bocianowate
bocian biały
bocianowate
bocian czarny
sikory
bogatka
sikory
czarnogłówka
sikory
czubatka
sikory
modraszka
sikory
sikory
sikory
ibisowate
ibisowate
nury
nury
nury
nury
jaskółkowate
jaskółkowate
jaskółkowate
jaskółkowate
sikora lazurowa
sikora uboga
sosnówka
ibis kasztanowaty
warzęcha
nur białodzioby
nur czarnoszyi
nur lodowiec
nur rdzawoszyi
jaskółka brzegówka
jaskółka dymówka
jaskółka oknówka
jaskółka rudawa
Rodzina
IleGatunkow
Bocianowate
2
Sikory
7
Ibisowate
2
Nury
4
Jaskółkowate
4
Select Rodziny.NazwaPL as Rodzina,
COUNT(*) As IleGatunkow
From Gatunki JOIN Rodziny ON
Gatunki.IdRodziny=Rodziny.IdRodziny
21
Group By Rodziny.NazwaPL
Zapytania wykorzystujące przetwarzanie w oknie - analiza
Przygotowanie do analizy danych:
• Zdefiniowano dwa widoki w bazie danych
Widok „Lokalizacje”
Widok „GatunkiPtakow”
22
Zapytania – przetwarzanie w oknie
WITH DaneDoAnalizy AS
(
Select Nazwisko+' '+Imie as Obserwator,
Pesel,
Gmina,
Powiat,
Gatunek,
Rodzina,
Rzad,
Ilosc,
Month(DataObserwacji) as Miesiac,
Year(DataObserwacji) as Rok
From Obserwatorzy INNER JOIN Obserwacje
ON Obserwatorzy.idobserwatora = Obserwacje.Idobserwatora
JOIN GatunkiPtakow On Obserwacje.IdGatunku=GatunkiPtakow.IdGatunku
JOIN Lokalizacje ON Lokalizacje.IdGminy=Obserwacje.IdGminy
Where Wojewodztwo='Podlaskie'
)
Select *
From DaneDoAnalizy
23
Zapytania – przetwarzanie w oknie
Przykładowa zawartość zbioru DaneDoAnalizy
24
Przetwarzanie w oknie
Przetwarzanie w oknie – klauzula OVER
Dla każdego wiersza wyniku zapytania można stosować zbiór funkcji
działających na zdefiniowanym zbiorze danych
Funkcje :
-Agregujące (AUM, AVG, MIN, MAX, AVG …..)
- Funkcje szeregujące(rankingu) (ROW_NUMBER(), RANK,
DENSE_RANK(),NTILE() )
- Funkcje pozycji (FIRST_VALUE() , LAST_VALUE() , LEAD(), LAG
(), NEXT_VALUE())
- Funkcje analityczne (PERCENT_RANK(), PERCENTILE_CONT(),
PERCENTILE_DISC())
25
Przetwarzanie w oknie
Elementy definiowania okna
Porządkowanie :
OVER (ORDER BY Kolumna)
Partycjonowanie
OVER (PARTITION BY Kolumna)
Definicja ramy okna (element ruchomy)
OVER (ORDER BY Kolumna
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW )
26
Przykład przetwarzania w oknie
Select Obserwator,
Pesel,
Gmina,
Powiat,
Gatunek,
Rodzina,
Rzad,
Ilosc,
Miesiac,
Rok,
Sum(Ilosc) OVER (Partition By Gmina) as IlewGminie,
Count(*) OVER (Partition BY Gatunek,Powiat) as IleObsGatunkuWPowiecie,
sum(Ilosc) OVER (Partition BY Powiat,Rok,Miesiac
Order BY Miesiac
Rows Between unbounded Preceding
and current row) as Narastająco
From DaneDoAnalizy
27
Przykładowy wynik zapytania
Przejdziemy do analizy przykładu w środowisku bazy danych
28
Podsumowanie
Projekt bazy danych może opisywać dowolną
dziedzinę problemu
Zapytania w języku SQL mogą działać według
trzech podstawowych schematów
Zapytania proste – jeden wiersz wynikowy dla jednego wiersza uzyskane z
frazy FROM … WHERE
Zapytania agregujące – jeden wiersz dla grupy wierszy (tracimy precyzje
zapytania)
Przetwarzanie w oknie – jeden wiersz wynikowy dla jednego wiersza
uzyskane z frazy FROM … WHERE ale można wykorzystywać agregacje w
oknie
29