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