T-SQL kütyü: a ROW_NUMBER()

Kedves mindenki! A menedzserkedés gépszíja rendesen berántott, de örömmel jelentem, hogy visszatértem, szebben, mint valaha. És őszebben is, de ez most mindegy. A közelmúltban még jobban rákattantam a Transact-SQL-re, az MSSQL programnyelvére, mrt rájöttem, hogy egy csomó dolgot tud, amivel én szívok szorgosan, mert nem olvasom el az asztalomon álló könyvet a SQL 2008 T-SQL kincsesbányájáról (by Itzik Ben-Gan, természetesen). Úgyhogy most T-SQL ömlengek.

A Transact-SQL nem kényeztette el azokat az embereket, akik szerettek volna olyanokat kérdezni, mint a második tíz legnagyobb ügyfél vagy csak egyszerűen minden harmadik alkalmazott, azaz olyat, ahol a recordsethez saját sorszámozás kellett. Ezt vagy nyakatekeréssel vagy temptáblába beleválogatósan, arra identity ráhelyezésesen (vagyis nyakatekeréssel) lehetett megoldani. De ez már a múlt ködös homályába vész, hiszen SQL 2005-ben megjelent a ROW_NUMBER() függvény, mely végre kiváló tulajdonságokkal vértezi fel az MSSQL-t, és kivesz egy érvet az ellendrukkerek táborában üldögélő Oracle (rownum) és MySQL (LIMIT) rajongók kezéből. Sőt.

A ROW_NUMBER() egy elég kemény eszköz, ami amellett, hogy képes sorszámot adni az egyes rekordokhoz, végre kényelmesen ÉS hatékonyan megoldhatóvá teszi az egyik visszatérő problémámat: válogassuk ki mondjuk egy értékesítés táblából minden értékesítő három legnagyobb összegű üzletkötését. Ez SQL 2000-ben tudott és akart rondán kinézni (megpróbáltam megírni, de annyira randa volt, hogy biztos elrontottam, úgyhogy nem merem felrakni), ellenben ROW_NUMBER() használatával elég átlátható lesz:

SELECT e.* FROM dbo.Ertekesites e
JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY ErtekesitoID ORDER BY Vegosszeg DESC) AS Rangsor from dbo.Ertekesites) e2
ON e.id = e2.id
WHERE e2.rangsor < =3

A lényegi rész ez: ROW_NUMBER() OVER (PARTITION BY ErtekesitoID ORDER BY Vegosszeg DESC), illetve főleg az over utáni rész. A PARTITION BY ErtekesitoID azt mondja, hogy szedjük a rekordokat csoportokba ErtekesitoID szerint, mint a GROUP BY, de ne gyúrjuk egybe a rekordokat, hanem az egyes csoportokon belül sorszámozzuk őket, tehát pontosan annyi 1 értéket fogunk látni, ahány különböző ErtekesitoID van a táblában. A sorszámozás alapja pedig a Vegosszeg oszlop lesz, amint azt az ORDER BY clause (magyarosan klóz) mutatja, mégpedig csökkenő sorrendben. A záró WHERE feltételben jelezzük, hogy csak a három legjobbat szeretnénk látni mindenkitől.

Akinek ez megtetszett, az még cifrábbakat is talál ebben a témában, a Ranking Functions cím alatt a BOL-ban. Az NTILE() segítségével a rekordokat csoportosíthatjuk azonos vagy majdnem azonos méretű halmazokba, a RANK() rangsorol, úgy, mint az olimpián: ha két első van egyforma eredménnyel, nincs második, csak harmadik; a DENSE_RANK() pedig kedvesebben, nem hagy szünetet a számsorban: ha volt két első, akkor a harmadik a második lesz, vagyis 1,1,2 jön vissza (a RANK() 1,1,3-at ad).

Leave a comment