Ito ay partikular na kapaki-pakinabang kapag kailangan mong kalkulahin ang mga pagsasama-sama o pagraranggo para sa mga pangkat ng mga hilera nang hindi aktwal na nagko-collapse sa buong hanay ng resulta.
Samahan kami sa tutorial na ito habang nalaman namin ang lahat ng dapat malaman para makapagsimula kang magtrabaho kasama ang OVER clause.
Mga kinakailangan:
Bago tayo sumisid sa pag-andar at paggana ng sugnay na OVER, tiyaking wala ka sa mga pangunahing kaalaman sa SQL. Ipinapalagay din namin na mayroon kang access sa isang database na magagamit mo upang subukan ang iyong kaalaman.
Sa aming kaso, gagamitin namin ang MySQL database kasama ang Sakila sample database. Tiyakin lamang na mayroon kang sapat na mga pahintulot at ang iyong database engine ay sumusuporta sa mga function ng window.
Syntax:
Gaya ng nabanggit namin kanina, sa karamihan ng mga kaso, pangunahing ginagamit namin ang OVER clause kasabay ng mga function ng window.
Dahil dito, maaari nating ipahayag ang syntax ng sugnay tulad ng sumusunod:
[PARTITION BY partition_expression, ...]
[ORDER BY sort_expression [ASC | DESC], ...]
[frame_specification]
)
Sa ibinigay na syntax, maaari nating hatiin ang bawat bahagi tulad ng sumusunod:
-
– Ito ay tumutukoy sa window function na gusto naming ilapat sa isang partikular na window ng mga row gaya ng SUM(), AVG(), ROW_NUMBER(), RANK, atbp. - Expression - Tinutukoy nito ang isang column o expression kung saan inilalapat ang function ng window.
- PARTITION BY – Ito ay isang opsyonal na sugnay na naghahati sa resulta na itinakda sa mga partisyon kung saan ang bawat partisyon ay parang isang hiwalay na yunit kung saan inilalapat ang function. Ang mga row sa loob ng parehong partition ay nagbabahagi ng parehong mga halaga sa mga tinukoy na column.
- ORDER BY – Tinutukoy nito ang pagkakasunud-sunod kung saan pinoproseso ang mga row sa bawat partition.
- frame_specification - Ito ay isang opsyonal na sugnay na tumutukoy sa frame ng mga row sa loob ng partition. Kasama sa mga karaniwang detalye ng frame ang ROWS BETWEEN
AND o RANGE BETWEEN AND
Sa kawalan nito, tuklasin natin ang ilang praktikal na halimbawa kung paano ito gamitin.
Halimbawa:
Ipakita natin kung paano gamitin ang sugnay gamit ang sample na database ng Sakila. Isaalang-alang ang isang halimbawa kung saan kailangan nating matukoy ang kabuuang kita para sa bawat kategorya ng pelikula.
Maaari naming gamitin ang sum window function na may OVER clause at isang grupo ng mga joint statement tulad ng ipinapakita sa sumusunod na halimbawa:
PUMILIcategory.name AS category_name,
film.title AS film_title,
film.renta_rate,
SUM(payment.amount) OVER (PARTITION BY category.name) BILANG kabuuang_kita
MULA SA
pelikula
SUMALI
NAKA-ON ang_category ng pelikula
film.film_id = film_category.film_id
SUMALI
kategorya NAKA-ON
film_category.category_id = category.category_id
SUMALI
NAKA-ON ang imbentaryo
film.film_id = imbentaryo.film_id
SUMALI
NAKA-ON ang pagrenta
inventory.inventory_id = rental.inventory_id
SUMALI
NAKA-ON ang pagbabayad
rental.rental_id = payment.rental_id
INIUTOS NI
category.name,
pelikula.pamagat;
Sa ibinigay na query, magsisimula kami sa pamamagitan ng pagpili sa pamagat ng pelikula, rate ng pagrenta, at ginagamit ang sum (payment.amount over partition by category.name) expression upang matukoy ang kabuuan ng bawat partition ng kategorya ayon sa pangalan ng kategorya.
Dapat nating gamitin ang PARTITION BY clause upang matiyak na ang pagkalkula ng kabuuan ay magsisimula muli sa bawat natatanging kategorya.
Ang resultang output ay ang mga sumusunod:
Ayan na!
Konklusyon
Sa halimbawang ito, ginalugad namin ang mga batayan ng pagtatrabaho sa OVER clause sa SQL. Ito ay hindi isang pangunahing sugnay at nangangailangan ng dating pamilyar sa iba pang mga tampok ng SQL.