Kas tie window functions ir kodėl turėtumėte apie juos žinoti
Jei dirbate su MySQL ir dar nenaudojate window functions, tai tarsi važiuotumėte Ferrari pirmąja pavara – technika yra, bet jos potencialo neišnaudojate. Nuo MySQL 8.0 versijos šie analitiniai įrankiai tapo prieinami ir MySQL naudotojams, nors kitos duomenų bazės jais džiaugėsi jau seniai.
Window functions leidžia atlikti skaičiavimus per kelias eilutes, kurios yra susijusios su dabartine eilute. Skirtingai nei GROUP BY, kuris suglaudina eilutes į vieną rezultatą, window functions išlaiko visas originalias eilutes ir prie kiekvienos prideda apskaičiuotą reikšmę. Tai kaip turėti papildomą perspektyvą į duomenis, neprarandant detalumo.
Paprasčiausias pavyzdys – įsivaizduokite, kad turite darbuotojų atlyginimų sąrašą ir norite prie kiekvieno darbuotojo parodyti, koks yra vidutinis atlyginimas jo skyriuje. Be window functions tektų rašyti subquery ar kurti laikinę lentelę. Su window functions tai tampa viena eilute kodo.
ROW_NUMBER, RANK ir DENSE_RANK: numeravimo trejetas
Pradėkime nuo trijų panašių, bet skirtingų funkcijų. Jos visos numeruoja eilutes, bet daro tai skirtingai, kai susiduria su vienodomis reikšmėmis.
ROW_NUMBER() tiesiog priskiria unikalų numerį kiekvienai eilutei. Nesvarbu, ar reikšmės kartojasi – numeracija eina iš eilės:
„`sql
SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;
„`
Tarkime, du darbuotojai uždirba po 5000 eurų. ROW_NUMBER() jiems priskirs 1 ir 2, nors jų atlyginimai identiški. Kas gaus 1, o kas 2? Tai priklauso nuo fizinės eilučių tvarkos lentelėje, todėl rezultatas gali būti nenuspėjamas.
RANK() elgiasi protingiau su dublikatais. Jei du darbuotojai uždirba vienodai, abu gaus tą patį rangą, bet po to bus praleistas numeris:
„`sql
SELECT
employee_name,
salary,
RANK() OVER (ORDER BY salary DESC) as rank_position
FROM employees;
„`
Jei du darbuotojai dalijasi pirma vieta, abu gaus 1, o kitas darbuotojas gaus 3 (ne 2!). Tai kaip olimpiadoje – du aukso medaliai, bet sidabro niekas negauna.
DENSE_RANK() yra kaip RANK(), tik be spragų numeracijoje:
„`sql
SELECT
employee_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_position
FROM employees;
„`
Du darbuotojai su vienodu atlyginimu gaus 1, o kitas darbuotojas gaus 2. Jokių praleistų numerių.
Praktikoje DENSE_RANK() dažniausiai yra naudingiausias, nes jis duoda kompaktiškiausią numeraciją. Tačiau jei jums svarbu parodyti, kiek iš tikrųjų yra darbuotojų prieš tam tikrą poziciją, RANK() bus tikslesnis pasirinkimas.
PARTITION BY: duomenų segmentavimas
Čia prasideda tikroji magija. PARTITION BY leidžia padalinti duomenis į grupes ir atlikti skaičiavimus kiekvienoje grupėje atskirai. Tai tarsi GROUP BY, tik nesuglaudinantis rezultatų.
Tarkime, norite rasti geriausiai apmokamą darbuotoją kiekviename skyriuje:
„`sql
SELECT
department,
employee_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
„`
Šis užklausas parodys visus darbuotojus, bet kiekviename skyriuje numeracija prasidės iš naujo. Pirmasis rezultatas gali atrodyti taip:
– IT skyrius: Jonas (5000€, rangas 1), Petras (4500€, rangas 2)
– Pardavimų skyrius: Ona (4800€, rangas 1), Marija (4200€, rangas 2)
Jei norite gauti tik geriausiai apmokamus darbuotojus, tiesiog įvyniokite šį užklausą į subquery:
„`sql
SELECT * FROM (
SELECT
department,
employee_name,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees
) ranked
WHERE dept_rank = 1;
„`
Tai vienas iš dažniausių window functions panaudojimo atvejų praktikoje. Anksčiau tokiam užklausui reikėjo JOIN su subquery, kuris apskaičiuodavo maksimalų atlyginimą kiekvienam skyriui. Dabar viskas daug paprasčiau ir skaitomiau.
LAG ir LEAD: kelionė laike
LAG() ir LEAD() funkcijos leidžia pasiekti kitų eilučių reikšmes – atitinkamai ankstesnių ir vėlesnių. Tai neįtikėtinai naudinga, kai reikia palyginti dabartinę reikšmę su praeita ar būsima.
Klasikinis pavyzdys – pardavimų augimo skaičiavimas:
„`sql
SELECT
sale_date,
revenue,
LAG(revenue) OVER (ORDER BY sale_date) as previous_revenue,
revenue – LAG(revenue) OVER (ORDER BY sale_date) as revenue_change
FROM daily_sales;
„`
LAG() funkcija paima reikšmę iš ankstesnės eilutės. Pirmoje eilutėje ji grąžins NULL, nes nėra ankstesnės eilutės. Galite nurodyti, kiek eilučių atgal žiūrėti (numatytoji reikšmė yra 1) ir kokią reikšmę grąžinti, jei eilutės nėra:
„`sql
LAG(revenue, 1, 0) OVER (ORDER BY sale_date)
„`
LEAD() veikia analogiškai, tik žiūri į priekį:
„`sql
SELECT
employee_name,
hire_date,
LEAD(hire_date) OVER (ORDER BY hire_date) as next_hire_date,
DATEDIFF(
LEAD(hire_date) OVER (ORDER BY hire_date),
hire_date
) as days_until_next_hire
FROM employees;
„`
Praktinis patarimas: jei tą pačią window funkciją naudojate kelis kartus su tais pačiais parametrais, galite apibrėžti window kaip atskirą elementą:
„`sql
SELECT
sale_date,
revenue,
LAG(revenue) OVER w as previous_revenue,
revenue – LAG(revenue) OVER w as revenue_change,
LEAD(revenue) OVER w as next_revenue
FROM daily_sales
WINDOW w AS (ORDER BY sale_date);
„`
Tai ne tik sutrumpina kodą, bet ir pagerina našumą, nes MySQL gali optimizuoti užklausą efektyviau.
Agreguojančios funkcijos su OVER: slenkantys vidurkiai ir sumos
Standartines agregavimo funkcijas (SUM, AVG, COUNT, MAX, MIN) galite naudoti kaip window functions. Tai atveria galimybes slenkančioms sumoms, vidurkiams ir kitiems dinaminėms analizėms.
Slenkančio vidurkio pavyzdys:
„`sql
SELECT
sale_date,
revenue,
AVG(revenue) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7days
FROM daily_sales;
„`
ROWS BETWEEN apibrėžia, kurias eilutes įtraukti į skaičiavimą. „6 PRECEDING AND CURRENT ROW” reiškia šešias ankstesnes eilutes plius dabartinę – iš viso 7 dienas.
Kumuliatyvi suma (running total) dar paprastesnė:
„`sql
SELECT
sale_date,
revenue,
SUM(revenue) OVER (ORDER BY sale_date) as cumulative_revenue
FROM daily_sales;
„`
Kai nenurodysite ROWS BETWEEN, numatytasis elgesys yra „nuo pradžios iki dabartinės eilutės”, todėl gausite kumuliatyvią sumą.
Galite derinti PARTITION BY su agregavimo funkcijomis. Pavyzdžiui, kiekvieno skyriaus kumuliatyvi atlyginimų suma:
„`sql
SELECT
department,
employee_name,
hire_date,
salary,
SUM(salary) OVER (
PARTITION BY department
ORDER BY hire_date
) as dept_cumulative_salary
FROM employees;
„`
Dar vienas praktiškas pavyzdys – procentinė dalis nuo visumos:
„`sql
SELECT
product_name,
sales_amount,
SUM(sales_amount) OVER () as total_sales,
ROUND(sales_amount * 100.0 / SUM(sales_amount) OVER (), 2) as percentage
FROM product_sales;
„`
Pastebėkite tuščią OVER () – tai reiškia, kad funkcija taikoma visam rezultatų rinkiniui, o ne tam tikram langui.
FIRST_VALUE ir LAST_VALUE: kraštutinumų paieška
Šios funkcijos leidžia gauti pirmąją ar paskutinę reikšmę apibrėžtame lange. Skamba paprastai, bet yra subtilybių.
FIRST_VALUE() paprastai veikia intuityviai:
„`sql
SELECT
employee_name,
department,
salary,
FIRST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY salary DESC
) as highest_paid_in_dept
FROM employees;
„`
Kiekvienas darbuotojas matys, kas jo skyriuje uždirba daugiausiai.
LAST_VALUE() yra klastingesnė. Daugelis pradedančiųjų tikisi, kad ji grąžins paskutinę reikšmę visame partition, bet numatytasis langas yra „nuo pradžios iki dabartinės eilutės”, todėl LAST_VALUE() paprasčiausiai grąžins dabartinę reikšmę.
Norint gauti tikrąją paskutinę reikšmę, reikia aiškiai nurodyti langą:
„`sql
SELECT
employee_name,
department,
salary,
LAST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as lowest_paid_in_dept
FROM employees;
„`
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING reiškia „visas eilutes nuo pradžios iki pabaigos”. Taip LAST_VALUE() matys visą partition ir grąžins tikrąją paskutinę reikšmę.
Alternatyva – naudoti FIRST_VALUE() su atvirkštine rikiavimo tvarka:
„`sql
FIRST_VALUE(employee_name) OVER (
PARTITION BY department
ORDER BY salary ASC
) as lowest_paid_in_dept
„`
Tai paprasčiau ir intuityviau, todėl praktikoje LAST_VALUE() naudojama retai.
NTH_VALUE ir NTILE: pažangesnė segmentacija
NTH_VALUE() leidžia gauti bet kurią N-tą reikšmę lange:
„`sql
SELECT
employee_name,
department,
salary,
NTH_VALUE(salary, 2) OVER (
PARTITION BY department
ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as second_highest_salary
FROM employees;
„`
Tai naudinga, kai reikia ne tik maksimalios ar minimalios reikšmės, bet ir kažko tarpinio – antros, trečios ir pan.
NTILE() padalija eilutes į N lygių grupių ir priskiria kiekvienai eilutei grupės numerį. Tai puikus būdas sukurti kvantilius:
„`sql
SELECT
employee_name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) as salary_quartile
FROM employees;
„`
Darbuotojai bus padalinti į keturias grupes pagal atlyginimą. Pirmoje grupėje bus 25% geriausiai apmokamų, paskutinėje – 25% prasčiausiai apmokamų.
Praktinis panaudojimas – klientų segmentavimas pagal pirkimų vertę:
„`sql
SELECT
customer_id,
total_purchases,
NTILE(5) OVER (ORDER BY total_purchases DESC) as customer_segment,
CASE NTILE(5) OVER (ORDER BY total_purchases DESC)
WHEN 1 THEN ‘VIP’
WHEN 2 THEN ‘Premium’
WHEN 3 THEN ‘Regular’
WHEN 4 THEN ‘Occasional’
WHEN 5 THEN ‘Rare’
END as segment_name
FROM customer_totals;
„`
Vėlgi, galite apibrėžti window kaip atskirą elementą, kad nereikėtų kartoti:
„`sql
SELECT
customer_id,
total_purchases,
NTILE(5) OVER w as customer_segment,
CASE NTILE(5) OVER w
WHEN 1 THEN ‘VIP’
WHEN 2 THEN ‘Premium’
WHEN 3 THEN ‘Regular’
WHEN 4 THEN ‘Occasional’
WHEN 5 THEN ‘Rare’
END as segment_name
FROM customer_totals
WINDOW w AS (ORDER BY total_purchases DESC);
„`
Našumas ir ką reikia žinoti prieš naudojant gamyboje
Window functions yra galingos, bet ne nemokamos našumo prasme. Jos reikalauja rikiavimo ir dažnai skaito duomenis kelis kartus, todėl dideliuose duomenų kiekiuose gali sulėtinti užklausus.
Keletas praktinių patarimų optimizavimui:
Indeksai yra kritiškai svarbūs. Jei naudojate ORDER BY ar PARTITION BY su tam tikrais stulpeliais, įsitikinkite, kad tie stulpeliai yra indeksuoti. Pavyzdžiui, jei dažnai rašote PARTITION BY department ORDER BY hire_date, sukurkite indeksą:
„`sql
CREATE INDEX idx_dept_hire ON employees(department, hire_date);
„`
Apribokite duomenų kiekį prieš taikydami window functions. Jei galite filtruoti duomenis WHERE sąlyga prieš taikydami window funkciją, tai padarykite. Geriau apdoroti 1000 eilučių nei 100000.
Vengkite kelių skirtingų window specifikacijų tame pačiame užklause. Jei galite, naudokite tą patį PARTITION BY ir ORDER BY visoms funkcijoms. MySQL gali optimizuoti tokį užklausą efektyviau.
Naudokite EXPLAIN. Kaip ir su bet kuriuo sudėtingu užklausu, patikrinkite execution plan:
„`sql
EXPLAIN SELECT
employee_name,
RANK() OVER (ORDER BY salary DESC) as rank_position
FROM employees;
„`
Ieškokite „Using filesort” – tai reiškia, kad MySQL turi rikiuoti duomenis, kas gali būti brangu. Tinkamas indeksas gali tai pašalinti.
Atsargiai su RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Tai verčia MySQL skaityti visas partition eilutes kiekvienai eilutei, kas gali būti labai lėta dideliuose duomenų kiekiuose.
Dar vienas aspektas – skaitomumas. Window functions gali padaryti užklausus gana sudėtingus. Jei rašote ypač ilgą užklausą su daugeliu window funkcijų, apsvarstykite galimybę jį suskaidyti į kelis žingsnius naudojant CTE (Common Table Expressions):
„`sql
WITH ranked_employees AS (
SELECT
employee_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees
),
top_earners AS (
SELECT * FROM ranked_employees WHERE dept_rank <= 3
)
SELECT
department,
employee_name,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg_top3
FROM top_earners;
```
Tai ilgiau, bet daug aiškiau nei vienas milžiniškas užklausas su įdėtais subqueries.
Realūs scenarijai ir kaip visa tai sujungti
Teorija teorija, bet kaip tai atrodo realiuose projektuose? Štai keletas scenarijų, su kuriais tikrai susidursite.
Dublikatų pašalinimas su prioritetais. Tarkime, turite klientų duomenis iš kelių šaltinių ir norite palikti tik naujausią įrašą kiekvienam klientui:
„`sql
DELETE FROM customers
WHERE id IN (
SELECT id FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY updated_at DESC
) as rn
FROM customers
) ranked
WHERE rn > 1
);
„`
Sesijų identifikavimas. Norite grupuoti vartotojo veiksmus į sesijas, kur nauja sesija prasideda, jei praėjo daugiau nei 30 minučių nuo paskutinio veiksmo:
„`sql
SELECT
user_id,
action_time,
action_type,
SUM(CASE
WHEN TIMESTAMPDIFF(MINUTE,
LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time),
action_time
) > 30 OR LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) IS NULL
THEN 1
ELSE 0
END) OVER (PARTITION BY user_id ORDER BY action_time) as session_id
FROM user_actions;
„`
Šis užklausas palygina kiekvieną veiksmą su ankstesniu ir, jei skirtumas didesnis nei 30 minučių, pradeda naują sesiją. Kumuliatyvi suma sukuria unikalų sesijos ID.
Pagrindinių metrikų dashboard. Tipinis verslo ataskaitos užklausas su keliomis metrikomis:
„`sql
SELECT
DATE(order_date) as date,
COUNT(*) as orders_count,
SUM(order_total) as revenue,
AVG(order_total) as avg_order_value,
— Palyginimas su praėjusia diena
LAG(COUNT(*)) OVER (ORDER BY DATE(order_date)) as prev_day_orders,
LAG(SUM(order_total)) OVER (ORDER BY DATE(order_date)) as prev_day_revenue,
— 7 dienų slenkantis vidurkis
AVG(SUM(order_total)) OVER (
ORDER BY DATE(order_date)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as revenue_7day_avg,
— Kumuliatyvi mėnesio suma
SUM(SUM(order_total)) OVER (
PARTITION BY YEAR(order_date), MONTH(order_date)
ORDER BY DATE(order_date)
) as month_cumulative_revenue
FROM orders
GROUP BY DATE(order_date)
ORDER BY date DESC;
„`
Pastebėkite, kad čia derinamos agregavimo funkcijos su window functions – pirmiausia grupuojame pagal datą, tada taikome window funkcijas rezultatams.
Produktų rekomendacijos. Rasti produktus, kuriuos klientai dažniausiai perka kartu:
„`sql
WITH order_pairs AS (
SELECT
o1.product_id as product_a,
o2.product_id as product_b,
COUNT(DISTINCT o1.order_id) as times_bought_together
FROM order_items o1
JOIN order_items o2 ON o1.order_id = o2.order_id AND o1.product_id < o2.product_id
GROUP BY o1.product_id, o2.product_id
)
SELECT
product_a,
product_b,
times_bought_together,
RANK() OVER (PARTITION BY product_a ORDER BY times_bought_together DESC) as recommendation_rank
FROM order_pairs
WHERE times_bought_together >= 5;
„`
Šis užklausas randa produktų poras, kurios buvo nupirktos kartu bent 5 kartus, ir kiekvienam produktui surankuoja rekomendacijas pagal dažnumą.
Window functions tikrai keičia tai, kaip rašome SQL užklausus. Tai, kas anksčiau reikalavo sudėtingų subqueries, JOIN’ų ir laikinų lentelių, dabar tampa gerokai paprastesniu ir skaitomu kodu. Taip, reikia laiko įprasti prie sintaksės ir suprasti, kaip veikia PARTITION BY bei įvairūs frame specifiers, bet investicija atsipirksta greitai.
Pradėkite nuo paprastų dalykų – ROW_NUMBER() ir RANK() dublikatams rasti, LAG() ir LEAD() palyginimams. Kai įgausite pasitikėjimo, pereikite prie sudėtingesnių scenarijų su slenkančiais vidurkiais ir kumuliatyviomis sumomis. Ir nepamirškite indeksų – jie yra skirtumas tarp greito ir lėto užklauso.
Paskutinis patarimas: eksperimentuokite su mažais duomenų kiekiais testuodami naujus užklausus. Window functions gali grąžinti netikėtus rezultatus, jei neteisingai suprasite frame specifiers ar rikiavimo tvarką. Geriau praleisti 10 minučių su testiniais duomenimis nei debuginti gamyboje, kodėl skaičiai neatitinka lūkesčių.
