ROW_NUMBER, RANK – T-SQL ‘újdonságok’

Időnként meglepve tapasztalom, hogy emberek ignorálják az SQL szerverbe bekerült dolgokat, és nekik az SQL Server pont olyan, mint mikor megismerték. Oké, az Enterprise Manager már nincs meg, de azért más is változott. Múltkor egy fejlesztő az Oracle ROWNUM okosságát hiányolta, mert ablakozó lekérdezést akart csinálni, a fenti virtuális oszlop pedig sorszámozza a visszatérő recordsetet, és lehet azt mondani, hogy where rownum < 10. Mondtam, hogy van ilyen. Gyártsunk játszós táblát:

use tempdb

create table rankdemo (
ugyfel int,
uzletkoto int,
datum smalldatetime,
osszeg float
)

insert into rankdemo values
(1001,1,’2010-01-01′,1234),
(1002,2,’2010-01-02′,1245),
(1003,2,’2010-01-04′,6578),
(1004,3,’2010-01-03′,765),
(1005,3,’2010-01-10′,44236),
(1001,3,’2010-01-11′,75),
(1001,1,’2010-01-10′,1434),
(1002,4,’2010-01-01′,94848),
(1001,1,’2010-01-02′,44236),
(1006,1,’2010-01-31′,4876),
(1001,4,’2010-01-09′,987)

Ebben álságos szerződéskötési adatok vannak, csak a minta kedvéért, valós üzleti vállalkozáshoz a sémát nem ajánlom. És most nézzük meg az úgynevezett ranking függvényeket gyorsan:

Először is, a már emlegetett ROW_NUMBER():

select ugyfel, osszeg, ROW_NUMBER() OVER (order by osszeg desc) as sorszam
FROM rankdemo

Egyszerű, mint a szög. A hozzá hasonló RANK() a rendezés alapjául szolgáló érték “helyezését” mondja meg, azaz itt van holtverseny.

select ugyfel, osszeg, ROW_NUMBER() OVER (order by osszeg desc) as sorszam,
RANK() OVER (order by osszeg desc) as helyezes
FROM rankdemo

És mi van, ha a negyedik legnagyobb értéket szeretném tudni, az előfordulások számától függetlenül? Pont van egy DENSE_RANK() is.

select ugyfel, osszeg, ROW_NUMBER() OVER (order by osszeg desc) as sorszam,
RANK() OVER (order by osszeg desc) as helyezes,
DENSE_RANK() OVER (order by osszeg desc) as helyezes2
FROM rankdemo

Itt jól látszik, hogy a helyezes2 10-nél ért véget a holtverseny miatt.

A jó, hogy van egy PARTITION BY clause is, amit meg lehet adni. Ez akkor jó, ha halmazon belül akarunk rendezni. Pl. klasszikus feladat a “mondd meg minden üzletkötő legnagyobb értékű bizniszét”. Ez a régi világban így nézett ki:

SELECT a.ugyfel, a.uzletkoto, a.osszeg FROM
rankdemo a
JOIN
(
select MAX(osszeg) mo, uzletkoto from rankdemo
group by uzletkoto) b
on a.osszeg = b.mo
and a.uzletkoto = b.uzletkoto

Ezzel szemben ma ezt írhatjuk:

WITH  abc as(
select ugyfel, uzletkoto, osszeg, ROW_NUMBER() OVER (partition by uzletkoto order by osszeg desc) as sorszam
FROM rankdemo
) 
SELECT a.ugyfel, a.uzletkoto, a.osszeg from abc a WHERE sorszam = 1

Kicsit egyszerűbb és átláthatóbb. Ha megnézzük, pont ugyanannyi idő alatt fut le mindkettő. Akkor mi a jó az újban? Mondd meg minden üzletkötő két legnagyobb értékű üzletét ranking függvények nélkül, aztán majd megmondom… :)

Leave a comment