Še posebej je uporabno, ko morate izračunati združevanja ali uvrstitve za skupine vrstic, ne da bi dejansko strnili celoten nabor rezultatov.
Pridružite se nam v tej vadnici, saj bomo izvedeli vse, kar morate vedeti, da boste lahko začeli delati s klavzulo OVER.
Zahteve:
Preden se poglobimo v funkcionalnost in delovanje klavzule OVER, se prepričajte, da ste se naučili osnov SQL. Predvidevamo tudi, da imate dostop do baze podatkov, ki jo lahko uporabite za preverjanje svojega znanja.
V našem primeru bomo uporabili bazo podatkov MySQL z vzorčno bazo podatkov Sakila. Prepričajte se le, da imate ustrezna dovoljenja in da vaš motor baze podatkov podpira okenske funkcije.
Sintaksa:
Kot smo že omenili, v večini primerov klavzulo OVER uporabljamo predvsem v povezavi z okenskimi funkcijami.
Kot tako lahko sintakso klavzule izrazimo na naslednji način:
[PARTICIJA PO particijskem_izrazu, ...]
[ORDER BY izraz_razvrščanja [ASC | DESC], ...]
[specifikacija_okvirja]
)
V dani sintaksi lahko vsako komponento razdelimo na naslednji način:
-
– Nanaša se na okensko funkcijo, ki jo želimo uporabiti v določenem oknu vrstic, kot so SUM(), AVG(), ROW_NUMBER(), RANK itd. - Izraz – To podaja stolpec ali izraz, za katerega se uporablja okenska funkcija.
- PARTITION BY – To je neobvezna klavzula, ki razdeli niz rezultatov na particije, kjer je vsaka particija kot ločena enota, kjer se uporablja funkcija. Vrstice znotraj iste particije si delijo enake vrednosti v navedenih stolpcih.
- ORDER BY – To določa vrstni red, v katerem so obdelane vrstice v vsaki particiji.
- frame_specification – To je neobvezna klavzula, ki definira okvir vrstic znotraj particije. Skupne specifikacije okvirja vključujejo ROWS BETWEEN
IN ali RANGE BETWEEN AND .
Ko smo tega končali, poglejmo nekaj praktičnih primerov, kako ga uporabiti.
primer:
Pokažimo, kako uporabiti klavzulo z uporabo vzorčne baze podatkov Sakila. Razmislite o primeru, kjer moramo določiti skupni prihodek za vsako filmsko kategorijo.
Uporabimo lahko okensko funkcijo vsote s klavzulo OVER in množico stavkov za združevanje, kot je prikazano v naslednjem primeru:
IZBERIime_kategorije AS ime_kategorije,
film.title AS film_title,
film.rental_rate,
SUM(plačilo.znesek) NAD (PARTICIJA PO kategoriji.ime) KOT skupni_prihodek
OD
film
PRIDRUŽI SE
film_category ON
film.film_id = film_category.film_id
PRIDRUŽI SE
kategorija ON
film_category.category_id = category.category_id
PRIDRUŽI SE
inventar VKLOP
film.film_id = popis.film_id
PRIDRUŽI SE
najem ON
inventory.inventory_id = najem.inventory_id
PRIDRUŽI SE
plačilo ON
rental.rental_id = payment.rental_id
NAROČI PO
category.name,
film.naslov;
V dani poizvedbi začnemo z izbiro naslova filma, cene najema in uporabimo izraz vsota (plačilo.znesek nad particijo po kategoriji.ime), da določimo vsoto vsake particije kategorije po imenu kategorije.
Uporabiti moramo klavzulo PARTITION BY, da zagotovimo, da se izračun vsote znova začne pri vsaki edinstveni kategoriji.
Rezultat je naslednji:
Tukaj imaš!
Zaključek
V tem primeru smo raziskali osnove dela s klavzulo OVER v SQL. To ni osnovna klavzula in zahteva predhodno poznavanje drugih funkcij SQL.