Transcript Prezentacja 3 - INF-WLF

Treści multimedialne - kodowanie,
przetwarzanie, prezentacja
Odtwarzanie treści multimedialnych
Andrzej Majkowski
informatyka +
1
BAZY DANYCH I SQL
A PTAKI POLSKIE
(KTO PYTA NIE BŁĄDZI)
Andrzej Ptasznik
2
Ptaki polskie
3
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
4
Zapytania proste
SELECT
(Konstruktor wiersza wynikowego)
FROM
(Skąd pobieramy dane – łączenie
tabel)
WHERE
(Filtrowanie – warunki selekcji)
ORDER BY (Porządkowanie wyniku
zapytania)
5
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
6
Zapytanie proste - przykład
Dodajemy warunek selekcji:
From Gatunki JOIN Rodziny
ON Gatunki.IdRodziny=Rodziny.IdRodziny
Where IdRzedu=5
Wynik po filtrowaniu
7
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
8
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
9
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
10
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
11
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
12
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”
13
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
14
Zapytania – przetwarzanie w oknie
Przykładowa zawartość zbioru DaneDoAnalizy
15
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())
16
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 )
17
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
18
Przykładowy wynik zapytania
Przejdziemy do analizy przykładu w środowisku bazy danych
19
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
20