Treści multimedialne - kodowanie, przetwarzanie, prezentacja Odtwarzanie treści multimedialnych

Treści multimedialne - kodowanie, przetwarzanie, prezentacja

Odtwarzanie treści multimedialnych

Andrzej Majkowski

TYTUŁ:

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

AUTOR: Andrzej Ptasznik

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!!!!!!!

Zacznij od … bazy

• Dziedzina problemu

• Technologia

• Projekt bazy danych

• Rejestracja danych

• Wykorzystanie bazy danych - analiza

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

Dziedzina problemu

Formułujemy problem :

Chcemy rejestrować obserwacje ptaków na obszarze Polski

Zadanie :

Zaprojektować odpowiednia bazę danych Krok pierwszy :

Rozpoznanie dziedziny problemu

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

Projektujemy bazę danych

Fakt 1 – Z rozpoznania dziedziny problemu wynika, że gatunki ptaków są łączone w rodziny a rodziny w rzędy

Struktura tabeli Struktura tabeli

Przykładowa zawartość Przykładowa

zawartość

Klucz podstawowy

Klucz

podstawowy Typy danychTypy danych

IdRzedu NazwaPL NazwaLac 1 blaszkodziobe Anseriformes 2 brodzace Ciconiiformes

3 nury Gaviiformes

4 pełnopłetwe Pelecaniformes 5 perkozy Podicipediformes

6 wróblowe Passeriformes

Projektujemy bazę danych cd.

Wymaganie : Rodziny ptaków są gromadzone w rzędy

IdRodziny NazwaPL NazwaLac IdRzedu

1 bocianowate Ciconiidae 2

2 czaplowate Ardeidae 4

3 drozdowate Turdidae 6

4 ibisowate Threskiornithidae 4

5 jaskółkowate Hirundinidae 6

6 jemiołuszkowate Bombycillidae 6

7 kaczkowate Anatidae 1

8 krukowate Corvidae 6

9 łuszczaki Fringillidae 6

10 nury Gaviidae 3

11 perkozy Podicipedidae 5

12 pliszkowate Motacillidae 6

13 sikory Paridae 6

14 skowronki Alaudidae 6

15 wróblowate Passeridae 6

Klucz obcy Klucz obcy

Wiersz z tabeli Rzedy

Wiersz z tabeli Rzedy

Projektujemy bazę danych cd.

Projektujemy bazę danych cd.

Fragment bazy danych opisujący podział

administracyjny w Polsce

Projektujemy bazę danych cd.

Baza danych do rejestracji obserwacji ptaków w Polsce

Język SQL - zapytania

• Na etapie projektu opisaliśmy problem przy pomocy tabel

• 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

Zapytania proste

SELECT (Konstruktor wiersza wynikowego)

FROM (Skąd pobieramy dane – łączenie tabel)

WHERE (Filtrowanie – warunki selekcji)

ORDER BY (Porządkowanie wyniku

zapytania)

Zapytanie proste - przykład

Podstawową klauzulą polecenia Select jest FROM

From Gatunki JOIN Rodziny

ON Gatunki.IdRodziny=Rodziny.IdRodziny

Opisujemy połączenie niezbędnych tabel Opisujemy połączenie

niezbędnych tabel

Wynik połączenia Wynik połączenia

Zapytanie proste - przykład Dodajemy warunek selekcji :

From Gatunki JOIN Rodziny

ON Gatunki.IdRodziny=Rodziny.IdRodziny Where IdRzedu=5

Wynik po filtrowaniu Wynik po filtrowaniu

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

Wynik po przetworzeniu

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

Order By Gatunek

Wynik po uporządkowaniu

Wynik po uporządkowaniu

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

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 Wynik zapytania

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 sikora lazurowa

sikory sikora uboga

sikory sosnówka

ibisowate ibis kasztanowaty

ibisowate warzęcha

nury nur białodzioby

nury nur czarnoszyi

nury nur lodowiec

nury nur rdzawoszyi

jaskółkowate jaskółka brzegówka jaskółkowate jaskółka dymówka jaskółkowate jaskółka oknówka jaskółkowate jaskółka rudawa

Select Rodziny.NazwaPL as Rodzina, COUNT(*) As IleGatunkow From Gatunki JOIN Rodziny ON

Gatunki.IdRodziny=Rodziny.IdRodziny Rodzina IleGatunkow

Bocianowate 2

Sikory 7

Ibisowate 2

Nury 4

Jaskółkowate 4

Zapytania wykorzystujące przetwarzanie w oknie - analiza

Przygotowanie do analizy danych:

• Zdefiniowano dwa widoki w bazie danych

Widok „Lokalizacje”

Widok „Lokalizacje”

Widok „GatunkiPtakow”

Widok „GatunkiPtakow”

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 *

Zapytania – przetwarzanie w oknie

Przykładowa zawartość zbioru DaneDoAnalizy

Przetwarzanie w oknie

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())

Przetwarzanie w oknie – klauzula OVER

Dla każdego wiersza wyniku zapytania można stosować zbiór funkcji

działających na zdefiniowanym zbiorze danych

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 )

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

Przykładowy wynik zapytania

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