1 Przetwarzanie w oknie - nowa siła zapytańmgr inż. Andrzej Ptasznik

2 Trochę klasyki Zapytania w języku SQL bazowały na trzech podstawowych operacjach na modelu relacyjnym: Projekcja Selekcja Łączenie

3 Tabela wyjściowa Teza : Podstawa zapytań to ograniczenie zawartości tabeli wynikowej do zdefiniowanego zakresu i postaci danych

4 Projekcja : Wybór podzbioru dostępnych kolumnSELECT Handlowiec, DataTransakcji, Kwota FROM Tabela

5 Selekcja : Wybór wierszy spełniających zadane wyrażenie logiczneRegion =‚Azja’ Select Handlowiec, Region, Miasto, DataTransakcji, Kwota FROM Tabela WHERE Region=‚Azja’

6 Łączenie : Sprowadza problem do jednej tabeliTabela : Handlowcy Tabela : Transakcje SELECT * FROM Handlowcy as H JOIN Transakcje as T ON H.idhandlowca=T.idhandlowca Wynik łączenia

7 Przetwarzanie zapytaniaKonstruktor wiersza Przetwarzanie zapytania Select LEFT(Nazwisko,1) +’ ‚ +LEFT(IMIE,1) as Inicjały, Ocena, DATENAME(mm,DataWystawienia) as miesiac Select Pesel, Avg(Ocena) as Srednia FROM Uczniowie JOIN Oceny ON Uczniowie.Iducznia=Oceny.idUcznia Where DataWystawienia Between ‚ ’ and ‚ ’ Przygotowanie zbioru wynikowego do przetwarzania Group by Pesel Having Avg(ocena) > 3,0 Frazy wspomagające wykorzystanie funkcji agregujących Zbiór1 Zbiór2 Zbiór3 Dalej

8 Powrót Zbiór wyjściowy do przetwarzania zapytania

9 Wynik zapytania po przetworzeniu Dla każdego wiersza zbioru wejściowego powstaje jeden wiersz jako wynik działania konstruktora wiersza. Powrót

10 Zastosowanie filtru HavingGrupy wierszy - wynik operacji grupowania Dla każdej grupy konstruktor wiersza tworzy jeden wiersz wynikowy - dala każdej grupy wykonywana jest funkcja agregująca Powrót Wynik zapytania Zastosowanie filtru Having

11 Filtry zapytań: ON … WHERE … HAVING … TOP .. [ORDER BY] ORDER BYParadoks klauzuli ORDER BY : Create view Test as Select nazwisko, pesel From uczniowie Order By Nazwisko Filtry zapytań: ON … WHERE … HAVING … TOP .. [ORDER BY] ORDER BY OFFSET … FETCH …. Msg 1033, Level 15, State 1, Procedure Test4442, Line 4 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Create view Test as Select top 100 percent nazwisko, pesel From uczniowie Order By Nazwisko Definicja OK Select * From Test … a gdzie porządek ????

12 Krótki przegląd nowościSelect przedmiot, avg(ocena) as srednia from UczniowieXML outer apply ( Select k.value('Przedmiot[1]', 'varchar(50)') as Przedmiot, k.value('Ocena[1]', 'numeric(5,2)') as Ocena From WykazOcen.nodes('Dzienniczek/Ocena') t(k) Where year(k.value('DataWystawienia[1]', 'date'))=2009 ) as tmp Group by przedmiot Wynik zapytania

13 Krótki przegląd nowościwith Tmp as ( SELECT nazwisko+' '+imie AS Uczen, przedmiot, ocena FROM V_OCENY ) Select Uczen, isnull( cast(fizyka as numeric(5,2)) , 0.00) as Fizyka, isnull( cast(matematyka as numeric(5,2)) , 0.00) as Matematyka, isnull( cast(Geografia as numeric(5,2)) , 0.00) as Geografia, isnull( cast(Literatura as numeric(5,2)) , 0.00) as Literatura From tmp pivot ( avg(ocena) for przedmiot in ([Fizyka], [Matematyka], [Geografia], [Literatura])) as A

14 Krótki przegląd nowościWith Tmp as ( Select Nazwisko, Imie,Idpracownika, cast('/' as varchar(512)) as sciezka from Pracownicy where Idpracownika=2 union all Select p.nazwisko, P.imie,P.idpracownika, cast( tmp.sciezka+tmp.nazwisko+'/' as varchar(512)) as sciezka from tmp join Pracownicy as P on tmp.idpracownika=P.idprzelozonego ) Select * from tmp

15 With Matematyka as ( Select top 5 Nazwisko, imie,Pesel,avg(ocena) as srednia from Uczniowie join Oceny on Uczniowie.iducznia=Oceny.iducznia join Przedmioty on Oceny.idprzedmiotu=Przedmioty.idprzedmiotu Where Przedmioty.Nazwa='Matematyka' group by Nazwisko, imie,Pesel order by srednia desc ), Fizyka as Where Przedmioty.Nazwa='Fizyka' ) Select nazwisko, imie,pesel From matematyka intersect Select nazwisko, imie,pesel from Fizyka

16 Przetwarzanie w oknie – klauzula OVERDla 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())

17 Elementy definiowania oknaPorzą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 )

18 PRZYKŁADY Dane sprzedaży Wyniki nauczania

19 … a jaka jest średnia ocen wszystkich???? - dla każdego wierszaSELECT *, AVG(OCENA) OVER () as SredniaSzkoly FROM Dane Select AVG(OCENA) FROM DANE

20 … a jaka jest średnia ocen dla danej klasy???? - dla każdego wierszaSELECT *, AVG(OCENA) OVER (PARTITION BY Klasa) as SredniaKlasy FROM Dane Select AVG(OCENA) FROM DANE as D WHERE Klasa=Dane.Klasa

21 … i jeszcze kilka podobnych wartości ???? - dla każdego wierszaSELECT AVG(OCENA) OVER (PARTITION BY Klasa) as SredniaKlasy, AVG(OCENA) OVER (PARTITION BY Klasa, przedmiot) as SredniaKlasyZPrzedmiotu, AVG(OCENA) OVER (PARTITION BY Uczen,Przedmiot) as SredniaUczniaZPrzedmiotu FROM Dane

22 Funkcje uporządkowanego oknaFunkcje szeregujące(rankingu) (ROW_NUMBER(), RANK, DENSE_RANK(),NTILE() ) Funkcje pozycji (FIRST_VALUE() , LAST_VALUE() , LEAD(), LAG (), NEXT_VALUE())

23 … które miejsce według średniej ocen ma dany uczeń w całej szkole i w swojej klasieSELECT Uczen, Klasa, RANK() OVER (Order by AVG(ocena) desc ) as MiejsceWSzkole, RANK() OVER (Partition by klasa Order by AVG(ocena) desc ) as MiejsceWKlasie FROM Dane GROUP BY Uczen, Klasa

24 Rama OKNA Okno musi być uporządkowane !!!!!! Rozszerzenie obszaru bieżącego wiersza o wiersze poprzedzające i(lub) następujące OVER (ORDER BY kolumna ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

25 Month(dataTransakcji) as Miesiac, With tmp as ( SELECT* , Month(dataTransakcji) as Miesiac, YEAR(DAtaTransakcji) as Rok from vtransakcje ) Select *, SUM(kwota) over (Partition By Handlowiec,Rok) as RocznaSprzedaz, SUM(Kwota) over (Partition By HAndlowiec,Rok Order By miesiac Rows between unbounded preceding and current row) as OdPoczRoku From tmp

26 Rama z wykorzystaniem ROWS

27 Month(dataTransakcji) as Miesiac, With tmp as ( SELECT* , Month(dataTransakcji) as Miesiac, YEAR(DAtaTransakcji) as Rok from vtransakcje ) Select *, SUM(kwota) over (Partition By Handlowiec,Rok) as RocznaSprzedaz, SUM(Kwota) over (Partition By HAndlowiec,Rok Order By miesiac Range between unbounded preceding and current row) as OdPoczRoku From tmp

28 Rama z wykorzystaniem RANGE

29 Klauzula OVER +funkcje przetwarzające zbiory danych – stanowi kolejne rozszerzenie potencjału zapytań SQL

30 Dziękuję za uwagę  … a może pytania ??????