Bazy danych – wykład trzeci
Konrad Zdanowski
Konrad Zdanowski
SQL - przypomnienie
Podstawowa forma kwerendy SQL:
s e l e c t A1 ,
. . . , Ak
from R1 ,
. . . , Rn
where <warunek > ;
Odpowiada jej w algebrze relacji operacja
π
A1,...,Ak
(σ
<warunek>
(
R1 × · · · × Rn))
Konrad Zdanowski
SQL – semantyka
s e l e c t R . A , T . B from R, T where <warunek > ;
Tak ˛
a kwerend ˛e mo˙zna zrealizowa´c nast ˛epuj ˛
aco:
1
Utwór˙z iloczyn kartezja ´nski R × T ,
2
Wypisz pary atrybutów z tych wierszy R × T , które
spełniaj ˛
a <warunek>.
albo
for all t ∈ R do
for all t
0
∈ T do
if t, t
0
spełniaj ˛
a <warunek>
then
print t.A,t’.B
end if
end for
end for
Konrad Zdanowski
SQL – semantyka
Je´sli jedna z tablic wymienionych po “
from” jest pusta, to
iloczyn kartezja ´nski R × T b ˛edzie pusty.
W takim przypadku zapytanie nie zwróci ˙zadnych krotek.
Niech R(A, B), T (C, D) tabele takie, ˙ze T jest pusta.
s e l e c t A , B from R ;
zwróci nam tabel ˛e R.
s e l e c t A , B from R, T ;
nie zwróci ˙zadnej krotki.
Konrad Zdanowski
SQL – semantyka
Domy´slnie tabele w bazie danych to zbiory. Nie mo˙zna
trzyma´c w jednej tabeli dwóch krotek o tych samych
warto´sciach wszystkich atrybutów.
W wyniku zapytania mog ˛
a zosta´c zwrócone wielozbiory.
Dzi ˛eki temu w nie tracimy informacji (nawet je´sli jest ona
nam niepotrzebna).
Przykład. Osoby (imie, nazwisko, id ). Zapytanie
s e l e c t count ( i m i e )
from Osoby as o
where o . i m i e = ’Ewa ’ ;
zwraca liczb ˛e osób o imieniu Ewa.
Konrad Zdanowski
SQL – semantyka
Je´sli chcemy usun ˛
a´c powtarzaj ˛
ace si ˛e krotki mo˙zemy u˙zy´c
s e l e c t d i s t i n c t i m i e , nazwisko
from Osoby ;
Taka operacja wymaga posortowania wyniku. Usuni ˛ecie
powtórze ´n mo˙ze by´c kosztowniejsze ni˙z obliczenie
zapytania.
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Przy pomocy
union, intersect, except mo˙zemy obliczy´c
sum ˛e, cz ˛e´s´c wspóln ˛
a (przeci ˛ecie) oraz ró˙znic ˛e dwóch
tabel.
Operacje te domy´slnie usuwaj ˛
a duplikaty krotek!
Aby zachowa´c duplikaty trzeba u˙zy´c słowa kluczowego
all.
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Rozwa˙zmy tabel ˛e GRAF(poczatek, koniec)
POCZATEK
KONIEC
1
2
2
3
3
4
4
5
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Rozwa˙zmy
s e l e c t poczatek , k o n i e c
from g r a f
union
s e l e c t poczatek , poczatek
from g r a f ;
Wyniki:
POCZATEK
KONIEC
1
1
1
2
2
2
2
3
3
3
3
4
4
4
4
5
Wyniki s ˛
a posortowane.
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Rozwa˙zmy
s e l e c t poczatek , k o n i e c
from g r a f
union a l l
s e l e c t poczatek , poczatek
from g r a f ;
Wyniki:
POCZATEK
KONIEC
1
2
2
3
3
4
4
5
1
1
2
2
3
3
4
4
Zauwa˙zmy, ˙ze w tym przypadku wyniki s ˛
a nieposortowane.
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Zapytania
s e l e c t poczatek
from g r a f ;
s e l e c t poczatek
from g r a f
union
s e l e c t poczatek
from g r a f ;
s e l e c t poczatek
from g r a f
union a l l
s e l e c t poczatek
from g r a f ;
mog ˛
a zwróci´c inne wyniki!
Konrad Zdanowski
SQL – operacje teoriomnogo´sciowe
Oracle 11g stosuje
minus zamiast except.
Oracle 11g nie implementuje
intersect all oraz minus all.
Konrad Zdanowski
SQL – zł ˛
aczenia
Operacj ˛e ł ˛
aczenia z algebry relacji mo˙zemy wykona´c przy
u˙zyciu
join.
Iloczyn kartezja ´nski dwóch tabel R × T otrzymamy za
pomoc ˛
a R
cross join T .
Konrad Zdanowski
SQL – zł ˛
aczenia
Je´sli chcemy ograniczy´c krotki w zł ˛
aczeniu do spełniaj ˛
acych
pewien warunek mo˙zemy napisa´c:
Zapytanie:
s e l e c t ∗
from g r a f g j o i n g r a f t
on g . k o n i e c = t . poczatek ;
Wynik:
POCZATEK
KONIEC
POCZATEK1
KONIEC1
1
2
2
3
2
3
3
4
3
4
4
5
Otrzymali´smy tylko te krotki, które spełniaj ˛
a warunek po
on.
Z grafów g i t “zgineły” krotki!
Konrad Zdanowski
SQL – zł ˛
aczenia
Mo˙zemy zachowa´c wszystkie zaginione krotki.
Zapytanie:
s e l e c t ∗
from g r a f g f u l l outer j o i n g r a f t
on g . k o n i e c = t . poczatek ;
Wynik:
POCZATEK
KONIEC
POCZATEK1
KONIEC1
(null)
(null)
1
2
1
2
2
3
2
3
3
4
3
4
4
5
4
5
(null)
(null)
Konrad Zdanowski
SQL – zł ˛
aczenia
Mo˙zemy zachowa´c zaginione (wisz ˛
ace) krotki z grafu g.
Zapytanie:
s e l e c t ∗
from g r a f g l e f t outer j o i n g r a f t
on g . k o n i e c = t . poczatek ;
Wynik:
POCZATEK
KONIEC
POCZATEK1
KONIEC1
1
2
2
3
2
3
3
4
3
4
4
5
4
5
(null)
(null)
Konrad Zdanowski
SQL – zł ˛
aczenia
Mo˙zemy zachowa´c wisz ˛
ace krotki z grafu t.
Zapytanie:
s e l e c t ∗
from g r a f g r i g h t outer j o i n g r a f t
on g . k o n i e c = t . poczatek ;
Wynik:
POCZATEK
KONIEC
POCZATEK1
KONIEC1
1
2
2
3
2
3
3
4
3
4
4
5
(null)
(null)
1
2
Konrad Zdanowski
SQL – zł ˛
aczenia
Niech tabela graf posiada dwa wiersze (1, null) oraz (null, 2).
Zapytanie
s e l e c t ∗
from g r a f g j o i n g r a f t
on g . k o n i e c = t . poczatek ;
nie zwróci ˙zadnej krotki.
Warto´s´c porównania null = null nie jest zdefiniowana.
Konrad Zdanowski
SQL – podzapytania
Po słowach
from, where mo˙ze wyst ˛
api´c nowe zapytanie.
s e l e c t poczatek , k o n i e c
from g r a f g
where ( g . poczatek ∗2 , g . k o n i e c +2) i n
(
s e l e c t ∗ from g r a f ) ;
To zapytanie zwróci nam jedn ˛
a krotk˛e (2, 3).
Po
where nie mo˙zemy u˙zy´c samej tabeli. Napisanie
where ( g . poczatek ∗2 , g . k o n i e c +2) i n ( g r a f )
byłoby niepoprawne składniowo (Oracle).
Konrad Zdanowski
SQL – podzapytania
W warunkach wykorzystuj ˛
acych relacje R i krotk˛e s
mo˙zemy u˙zywa´c:
1
EXISTS R,
2
s
in R,
3
s > ALL R,
4
s > ANY R,
5
s<> ALL R.
Wyra˙zenia mo˙zemy poprzedza´c negacj ˛
a, np. NOT EXISTS
R.
Pewne wyra˙zenia s ˛
a równowa˙zne:
1
s = ANY R jest równowa˙zne s
in R,
2
NOT s > ALL S jest równowa˙zne s <= ANY R.
Konrad Zdanowski
SQL – podzapytania
Je´sli wiemy, ˙ze zapytanie zwróci tylko jedn ˛
a warto´s´c,
mo˙zemy u˙zy´c jego wyniku do porównania:
where n = ( s e l e c t numer
from R, T
where R . numer = T . i d
)
Podobnie, mo˙zemy porówna´c całe krotki
where ( n , k o l o r ) = ( s e l e c t R . numer , T . k o l o r
from R, T
where R . numer = T . i d
)
Konrad Zdanowski
SQL – podzapytania skorelowane
Wyniki pewnych podzapyta ´n zale˙z ˛
a od aktualnie
testowanych krotek na zewn ˛
atrz danego podzapytania.
Wtedy trzeba oblicza´c je za ka˙zdym przypisaniem warto´sci
tym krotkom.
Przykład. Tabela Filmy(tytul, rok, dlugosc).
s e l e c t t y t u l
from f i l m y s t a r y
where r o k < ANY
(
s e l e c t r o k
from f i l m y
where t y t u l = s t a r y . t y t u l
) ;
Konrad Zdanowski
SQL – agregowanie
W SQL mo˙zemy oblicza´c warto´sci dla wszystkich wierszy
w tabeli.
Dost ˛epne funkcje: SUM(A), AVG(A), MIN(A), MAX(A),
COUNT(*), COUNT(DISTINCT A).
Ich argumetny mog ˛
a by´c wyra˙zeniami, np. SUM(A ∗ 2).
Przykład Filmy(tytul, rok, dlugosc).
s e l e c t count ( d i s t i n c t t y t u l )
from F i l m y ;
s e l e c t sum( d l u g o s c )
from F i l m y
where r o k =2010;
Konrad Zdanowski
SQL – agregowanie
Je´sli chcemy obliczy´c warto´s´c funkcji agreguj ˛
acej
oddzielnie dla ró˙znych grupi wyniku u˙zywamy
group by.
Przykład Filmy(tytul, rok, rezyser, dlugosc).
s e l e c t sum( d l u g o s c )
from F i l m y
group by r o k ;
s e l e c t
rok , r e z y s e r ,
sum( d l u g o s c )
from F i l m y
group by rok , r e z y s e r ;
Konrad Zdanowski
SQL – agregowanie
Mo˙zemy te˙z ograniczy´c wynik zapytania u˙zywaj ˛
ac funkcji
agreguj ˛
acej:
having.
Przykład Filmy(tytul, rok, rezyser, dlugosc).
Je´sli interesuje nas długo´s´c filmów, wyprodukowanych przez
re˙zyserów, którzy zaczeli pracowa´c po roku 2000 napiszemy:
s e l e c t r e z y s e r , sum( d l u g o s c )
from F i l m y
group by r e z y s e r
having min ( r o k ) >=2000;
Albo sami re˙zyserzy:
s e l e c t
r e z y s e r
from F i l m y
group by r e z y s e r
having min ( r o k ) >=2000;
Konrad Zdanowski
SQL – perspektywy
SQL daje nam mo˙zliwo´s´c tworzenia perspektyw.
Perspektywy nie s ˛
a przechowywane jako oddzielne tabele
w bazie danych.
Mo˙zemy je interpretowa´c jako skróty dla zapyta ´n, które je
definiuj ˛
a - w zapytaniu perspektywa zamieniana jest na
podzapytanie SQL.
Mo˙zemy korzysta´c z perspektyw w zapytaniach jak z tabel
ale ich warto´s´c jest za ka˙zdym razem obliczalna.
Zapewniaj ˛
a lepsz ˛
a izolacj ˛e danych i czytelno´s´c zapyta ´n.
cr ea te view s t o p i e n _ w i e r z c h o l k a as
s e l e c t poczatek w i e r z c h o l e k , count ( ∗ ) s t o p i e n
from g r a f
group by poczatek ;
Konrad Zdanowski
SQL – perspektywy
W poprzednim przykładzie tracili´smy wierchołki o stopniu
wychodz ˛
acym 0 ale raczej nie chcemy te˙z, ˙zeby obliczenie
perspektywy trwało długo.
cr ea te view s t o p i e n _ w i e r z c h o l k a as
s e l e c t poczatek w i e r z c h o l e k , count ( ∗ ) s t o p i e n
from g r a f
group by poczatek
union a l l
s e l e c t
k o n i e c w i e r z c h o l e k , 0 s t o p i e n
from g r a f
where g r a f . k o n i e c not i n
(
s e l e c t poczatek from g r a f ) ;
Konrad Zdanowski
SQL – perspektywy
Je´sli perspektywa jest utworzona z jednej tabeli (lub
perspektywy) i wykorzystane w niej atrybuty mo˙zna
uzupełni´c warto´sciami domy´slnymi do pełnego wiersza
wyj´sciowej tabeli do do perspektywy mo˙zemy wstawia´c
krotki.
Wynikiem jest wtedy wstawienie krotek do wyj´sciowej
tabeli.
Usuni ˛ecie krotki z perspektywy (je´sli jest to mo˙zliwe)
skutkuje usuni ˛eciem
wszystkich pasuj ˛
acych do niej krotek
z tabeli.
Konrad Zdanowski
SQL – definiowanie relacji w zapytaniu
Potrzebn ˛
a nam relacj ˛e mo˙zemy zdefiniowa´c te˙z przed
wła´sciwym zapytaniem.
Słu˙zy do tego konstrukcja
with R( A1 ,
. . . , Ak )
as
(
s e l e c t
. . . .
)
s e l e c t
. . . .
from R,
. . .
where . . . . ;
Konrad Zdanowski
SQL – definiowanie relacji w zapytaniu
Rozwa˙zmy tabel ˛e GRAF(poczatek, koniec)
POCZATEK
KONIEC
1
2
2
3
3
4
4
5
Konrad Zdanowski
SQL – definiowanie relacji w zapytaniu
with GrafOdwrotny ( poczatek , k o n i e c ) as
(
s e l e c t koniec , poczatek from g r a f
)
s e l e c t go . poczatek , g . k o n i e c
from g r a f g , GrafOdwrotny go
where go . k o n i e c = g . poczatek ;
POCZATEK
KONIEC
2
2
3
3
4
4
5
5
Konrad Zdanowski
SQL – zapytania rekurencyjne
Chcemy obliczy´c tranzytywne domkni ˛ecie relacji GRAF,
czyli relacj ˛e osi ˛
agalno´sci.
Obliczenie par wierzchołków (p,k) takich, ˙ze z p mo˙zna
osi ˛
agn ˛
a´c k w
dwóch krokach:
s e l e c t g1 . poczatek , g2 . k o n i e c
from g r a f g1 , g r a f g2
where g1 . k o n i e c = g2 . poczatek ;
Obliczenie par wierzchołków (p,k) takich, ˙ze z p mo˙zna
osi ˛
agn ˛
a´c k w
trzech krokach:
s e l e c t g1 . poczatek , g3 . k o n i e c
from g r a f g1 , g r a f g2 , g r a f g3
where g1 . k o n i e c = g2 . poczatek and
g2 . k o n i e c = g3 . poczatek ;
Konrad Zdanowski
SQL – zapytania rekurencyjne
with t r a n s _ g r a f ( poczatek , k o n i e c ) as
(
s e l e c t poczatek , k o n i e c
from g r a f
union a l l
s e l e c t t 1 . poczatek , g . k o n i e c
from t r a n s _ g r a f t1 , g r a f g
where t 1 . k o n i e c = g . poczatek
)
s e l e c t ∗
from t r a n s _ g r a f
order by poczatek , k o n i e c ;
Uwaga. Relacja trans_graf pojawia si ˛e po from w jednym z
członów sumy, która j ˛
a sam ˛
a definiuje.
Konrad Zdanowski
SQL – zapytania rekurencyjne
Wyniki zapytania:
POCZATEK
KONIEC
1
2
1
3
1
4
1
5
2
3
2
4
2
5
3
4
3
5
4
5
Konrad Zdanowski
SQL – zapytania rekurencyjne
Silnik bazy danych Oracle sprawdza czy obliczenie
zapytania nie doprowadzi do niesko ´nczonej p ˛etli.
W powy˙zszym przykładzie zapytanie
with t r a n s _ g r a f ( poczatek , k o n i e c ) as
(
s e l e c t poczatek , k o n i e c
from g r a f
union a l l
s e l e c t t 1 . poczatek , g . k o n i e c
from t r a n s _ g r a f t1 , t r a n s _ g r a f t 2
where t 1 . k o n i e c = t 2 . poczatek
)
s e l e c t ∗
from t r a n s _ g r a f
order by poczatek , k o n i e c ;
nie zwróciłoby wyniku.
Konrad Zdanowski
SQL – zapytania rekurencyjne
Podobnie doło˙zenie kraw ˛edzi (1, 1) do grafu sprawia, ˙ze
Oracle “rozpoznaje” p ˛etle podczas obliczania odpowiedzi.
Warto wi ˛ec zastanowi´c si ˛e czy naprawd ˛e potrzebujemy
rekursji.
Konrad Zdanowski
SQL – funkcje analityczne
Funkcje agreguj ˛
ace pozwalaj ˛
a obliczy´c warto´s´c zale˙zn ˛
a od
wszystkich (pogrupowanych) wierszy w odpowiedzi.
Funkcje analityczne pozwalaj ˛
a oblicza´c funkcje zale˙zne
tylko od cz ˛e´sci wyniku.
Np. pozwalaj ˛
a oblicza´c post ˛epuj ˛
ac ˛
a sum ˛e, N najwi ˛ekszych
(najmniejszych) warto´sci, wyznacza´c ranking, oblicza´c
funkcje na podstawie danego rekordu i jego N s ˛
asiadów
Funkcje analityczne wykonywane s ˛
a po instrukcjach
join,
where, group by, having a przed instrukcj ˛
a
order by.
Konrad Zdanowski
SQL – funkcje analityczne
Składnia funkcji analitycznej:
f u n c t i o n ( A2 ,
. . . , Ak )
over ( < p a r t i t i o n by argument >
<
order by argument >
<windowing−clause > )
Konrad Zdanowski
SQL – funkcje analityczne
partition by ... dzieli wynik zapytania na cz ˛e´sci, dla
których bed ˛
a, oddzielnie, obliczane warto´sci funkcji,
order by ... definiuje porz ˛
adek w jakim b ˛ed ˛
a podawane
wiersze przy obliczaniu funkcji, nie musi by´c to porz ˛
adek
wypisywania wyników,
windowing-clause definiuje, od których wierszy z tabeli
zale˙zy wynik funkcji.
Konrad Zdanowski
SQL – funkcje analityczne
Przykład. Osoby(imie, nazwisko, id).
IMIE
NAZWISKO
ID
Jan
Kowalski
1
Ewa
Kowalska
2
Marian
Kowalski
3
Roman
Kowalski
4
Ewa
Kowalska
5
Chcemy obliczy´c kolejno´s´c osób zgodn ˛
a z imieniem.
Konrad Zdanowski
SQL – funkcje analityczne
Chcemy obliczy´c kolejno´s´c osób zgodn ˛
a z imieniem.
s e l e c t i m i e , nazwisko , rank ( ) over
(
order by i m i e ) as m i e j s c e
from osoby ;
IMIE
NAZWISKO
MIEJSCE
Ewa
Kowalska
1
Ewa
Kowalska
1
Jan
Kowalski
3
Marian
Kowalski
4
Roman
Kowalski
5
Konrad Zdanowski
SQL – funkcje analityczne
Chcemy obliczy´c kolejno´s´c osób zgodn ˛
a z imieniem.
s e l e c t i m i e , nazwisko , dense_rank ( ) over
(
order by i m i e ) as m i e j s c e
from osoby ;
IMIE
NAZWISKO
MIEJSCE
Ewa
Kowalska
1
Ewa
Kowalska
1
Jan
Kowalski
2
Marian
Kowalski
3
Roman
Kowalski
4
Konrad Zdanowski
SQL – funkcje analityczne
Mo˙zemy skorzysta´c te˙z z funkcji agreguj ˛
acych.
Bed ˛
a obliczane dla ka˙zdego wiersza oddzielnie, nie tylko
dla całej grupy.
s e l e c t i m i e , nazwisko , i d
sum( i d ) over
(
p a r t i t i o n by i m i e order by i d )
as suma
from osoby ;
IMIE
NAZWISKO
ID
SUMA
Ewa
Kowalska
2
2
Ewa
Kowalska
5
7
Jan
Kowalski
1
1
Marian
Kowalski
3
3
Roman
Kowalski
4
4
Konrad Zdanowski
SQL – funkcje analityczne
Porz ˛
adek generowania wyników mo˙ze zosta´c zmieniony,
przez dodanie na ko ´ncu zapytania polecenia
order by.
Nie zmienia to warto´sci funkcji analitycznej.
s e l e c t i m i e , nazwisko , i d
sum( i d ) over
(
p a r t i t i o n by i m i e order by i d )
as suma
from osoby order by i d ;
IMIE
NAZWISKO
ID
SUMA
Jan
Kowalski
1
1
Ewa
Kowalska
2
2
Marian
Kowalski
3
3
Roman
Kowalski
4
4
Ewa
Kowalska
5
7
Konrad Zdanowski
SQL – funkcje analityczne
Mo˙zemy modyfikowa´c porz ˛
adek:
asc, desc.
Warto´sci null mo˙zemy ustawia´c na ko ´ncu lub pocz ˛
atku:
nulls [first|last].
Inne funkcje:
ntile(N), percent_rank(), cume_dist()
Je´sli chcemy tylko przypisa´c wierszom unikatowe numery
mo˙zemy u˙zy´c funkcji
row_number().
To pozwala łatwo znale´z´c pierwszych N krotek.
Je´sli chcemy powtarzalnych wyników musimy sortowa´c po
unikatowym kluczu.
Konrad Zdanowski
SQL – funkcje analityczne
Przykład. U˙zycie row_number().
s e l e c t i m i e , nazwisko , i d , row_number ( ) over
(
order by i d desc ) as r n
from osoby ;
IMIE
NAZWISKO
ID
RN
Ewa
Kowalska
5
1
Roman
Kowalski
4
2
Marian
Kowalski
3
3
Ewa
Kowalska
2
4
Jan
Kowalski
1
5
Uwaga. U˙zycie rn w klauzuli where jest nielegalne - funkcje
analityczne s ˛
a obliczane po
where, group by, having a przed
order by.
Konrad Zdanowski
SQL – funkcje analityczne
Przykład. U˙zycie row_number(). Aaaaaby otrzyma´c wiersze z
pewnego zakresu mo˙zemy zagnie´zdzi´c zapytanie u˙zywaj ˛
ace
row_number().
s e l e c t i m i e , nazwisko , r n from
(
s e l e c t i m i e , nazwisko , i d , row_number ( ) over
(
order by i d desc ) as r n
from osoby
)
where 1< r n and r n < 5 ;
Konrad Zdanowski
SQL – funkcje analityczne
Funkcje SUM, COUNT, AVG, MIN, MAX nie zale˙z ˛
a od
porz ˛
adku wierszy.
Je´sli obliczenie zale˙zy od porz ˛
adku generowania wierszy
powinni´smy go poda´c w
over (...) przez order by.
Forma:
order by expr [asc | desc] nulls [first | last].
Funkcje zale˙z ˛
ace od porz ˛
adku: LEAD, LAG, RANK,
DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE,
LAST, LAST VALUE.
Konrad Zdanowski
SQL – funkcje analityczne
first_value(sql-expr) oblicza warto´s´c sql-expr na pierwszej
krotce w grupie zdefiniowanej przez
partition.
s e l e c t i m i e , nazwisko , i d ,
i d − f i r s t _ v a l u e ( i d )
over
(
p a r t i t i o n by nazwisko
order by
nazwisko , i m i e , i d )
as d i f f
from osoby ;
IMIE
NAZWISKO
ID
DIFF
Ewa
Kowalska
2
0
Ewa
Kowalska
5
3
Jan
Kowalski
1
0
Marian
Kowalski
3
2
Roman
Kowalski
4
3
Konrad Zdanowski
SQL – funkcje analityczne
lead(sql-expr,offset, default) oblicza warto´s´c sql-expr na
krotce wyst ˛epuj ˛
acej w grupie offset pozycji
po aktualnej.
s e l e c t i m i e , nazwisko , i d ,
i d + l e a d ( i d ,1 , −10)
over
(
p a r t i t i o n by nazwisko
order by
nazwisko , i m i e , i d )
as sum
from osoby ;
IMIE
NAZWISKO
ID
SUM
Ewa
Kowalska
2
7
Ewa
Kowalska
5
-5
Jan
Kowalski
1
4
Marian
Kowalski
3
7
Roman
Kowalski
4
-6
Warto´s´c domy´slna dla offset to 1. Je´sli dana krotka nie istnieje,
to zostanie zwrócona warto´s´c default.
Konrad Zdanowski
SQL – funkcje analityczne
Funkcja analogiczna do
first_value to last_value.
Funkcja analogiczna do
lead to lag(sql-expr, offset,
default).
Konrad Zdanowski
SQL – funkcje analityczne
s e l e c t i m i e , nazwisko , i d , sum( i d ) over
(
order by
nazwisko , i m i e , i d
rows between 1 p r e c e d i n g and
i d f o l l o w i n g )
as sum
from osoby ;
IMIE
NAZWISKO
ID
SUM
Ewa
Kowalska
2
8
Ewa
Kowalska
5
15
Jan
Kowalski
1
9
Marian
Kowalski
3
8
Roman
Kowalski
4
7
Konrad Zdanowski
SQL – dygresja
Czasem zamiast row_number() wystarczy u˙zy´c
pseudokolumny Oracle
rownum.
Dla ka˙zdej tabeli i wyniku zapytania mo˙zemy posłu˙zy´c si ˛e
rownum, która przechowuje kolejne numery wierszy tabeli
(licz ˛
ac od 1).
s e l e c t i m i e , nazwisko , rownum
from osoby
where rownum <4;
IMIE
NAZWISKO
ROWNUM
Jan
Kowalski
1
Ewa
Kowalska
2
Marian
Kowalski
3
Konrad Zdanowski
SQL – dygresja
s e l e c t i m i e , nazwisko , rownum
from osoby
where rownum <4;
zwraca tylko pierwsze trzy wiersze
s e l e c t i m i e , nazwisko , rownum
from osoby
where rownum >1;
nie zwróci ˙zadnego wiersza, bo
rownum przypisuje
pierwszemu wierszowi numer 1. Ka˙zdy kolejno testowany
wiersz b ˛edzie miał domy´slnie przypisany
rownum = 1.
U˙zycie
rownum mo˙ze wył ˛
aczy´c pewne optymalizacje w
obliczaniu zapyta ´n.
Konrad Zdanowski
