Ranking Functions

Πολλές φορές έχουμε να αντιμετωπίσουμε requests του τύπου «θέλω όλες τις σημερινές παραγγελίες ταξινομημένες κατά ώρα αλλά με μία πρώτη στήλη extra που να έχει αύξοντα αρίθμηση». Εφόσον εκείνη την ώρα δεν χτυπήσει ο κεραυνός του Codd τον βάρβαρο που τόλμησε να ξεστομίσει τέτοιο πράγμα, εμείς θα πρέπει να το ικανοποιήσουμε. Για να πούμε την αλήθεια, η πρακτική αξία της δυνατότητας να μπορεί να παράγει κανείς row numbers είναι μεγάλη. Φανταστείτε για παράδειγμα πόσο πιο απλός θα ήταν ο κώδικας για την υλοποίηση paging tables σε web-pages αν είχαμε αυτή τη δυνατότητα.

Μέχρι σήμερα, για να φτιάξουμε κάτι τέτοιο, θα έπρεπε να καταφύγουμε σε διάφορες τεχνικές. Κάποιος θα μπορούσε να σκαρώσει γρήγορα-γρήγορα ένα query του τύπου:

SELECT (SELECT COUNT(*)
  FROM Orders AS O2
  WHERE O2.orderid <= O1.orderid) AS rownum,
  orderid,
  orderdate
FROM Orders AS O1
ORDER BY orderid

το οποίο είναι τρομερά inefficient καθώς για κάθε εγγραφή, κάνει ένα ξεχωριστό query προκειμένου να βρει τον αριθμό της (περισσότερα τέτοια εδώ). Άλλοι, θα επιλέξουν να γίνει αυτή η στήλη αρίθμησης client-side, να την παράγουν server side με cursors ή με temporary tables.

Στον SQL Server 2005 υπάρχει μια σειρά από functions που μας βοηθάνε ακριβώς σε αυτόν τον τομέα. Ονομάζονται “Ranking Functions” και όπως θα διαπιστώσετε έχουν πολύ μεγαλύτερη ευελιξία από οποιαδήποτε λύση του παρελθόντος.

Ας πούμε ότι θέλουμε το query να μας επιστρέφει το όνομα του υπαλλήλου που έχει θέσει την παραγγελία, την ημερομηνία και αρίθμηση των παραγγελιών ανά υπάλληλο σε φθίνουσα σειρά ημερομηνίας. Το query μας είναι το παρακάτω:

SELECT  Employees.LastName,
  RANK() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Εδώ, το ranking function είναι το RANK() το οποίο συνοδεύεται από ένα OVER clause το οποίο περιέχει το PARTITION BY clause που καθορίζει ένα column με επαναλαμβανόμενες τιμές – το «ανά υπάλληλο» στο ζητούμενο query μας – και ένα ORDER BY clause που καθορίζει τη σειρά ταξινόμησης.

Και ιδού το αποτέλεσμα:

Buchanan 1 1998-04-22 00:00:00.000
Buchanan 2 1998-03-17 00:00:00.000
Buchanan 3 1998-03-03 00:00:00.000
...
Callahan 1 1998-05-06 00:00:00.000
Callahan 2 1998-05-04 00:00:00.000
Callahan 3 1998-05-01 00:00:00.000
...
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 5 1998-05-01 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
...
Dodsworth 1 1998-04-29 00:00:00.000
Dodsworth 2 1998-04-14 00:00:00.000
Dodsworth 3 1998-04-13 00:00:00.000
Dodsworth 4 1998-04-10 00:00:00.000
Dodsworth 5 1998-03-26 00:00:00.000
...

Παρατηρήστε οι εγγραφές της αγαπημένης μας Davolio έχουν rank numbers που επαναλαμβάνονται. Αυτό συμβαίνει γιατί υπάρχουν πολλαπλές παραγγελίες την ίδια μέρα (και ώρα).  Παρατηρήστε επίσης ότι οι τιμές σε αυτή τη στήλη είναι 3,3,5,6,6,8 κλπ. Δηλαδή όταν δύο ή παραπάνω rows έχουν ίδιο rank, τότε το επόμενο rank «πηδάει» ανάλογες θέσεις. Αν δεν το θέλουμε αυτό, τότε αλλάζουμε το ranking function από RANK σε DENSE_RANK, δηλαδή:

SELECT  Employees.LastName,
  DENSE_RANK() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Το αποτέλεσμα:

…
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 4 1998-05-01 00:00:00.000
Davolio 5 1998-04-21 00:00:00.000
Davolio 5 1998-04-21 00:00:00.000
Davolio 6 1998-04-16 00:00:00.000
Davolio 7 1998-04-14 00:00:00.000
Davolio 8 1998-04-09 00:00:00.000
Davolio 9 1998-04-02 00:00:00.000
Davolio 10 1998-04-01 00:00:00.000
Davolio 10 1998-04-01 00:00:00.000
…

Και πάλι όμως μπορεί να πει κάποιος «Εγώ θέλω οπωσδήποτε να έχω αύξουσα αρίθμηση για κάθε row». Γι αυτόν τον περίεργο που θα πει τέτοιο πράγμα, έχουμε το function ROW_NUMBER. Οπότε, το νέο query είναι ως εξής:

SELECT  Employees.LastName,
  ROW_NUMBER() OVER(PARTITION BY Employees.LastName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM    Orders INNER JOIN Employees
   ON Orders.EmployeeID = Employees.EmployeeID

Με αποτέλεσμα:

…
Davolio 1 1998-05-06 00:00:00.000
Davolio 2 1998-05-05 00:00:00.000
Davolio 3 1998-05-04 00:00:00.000
Davolio 4 1998-05-04 00:00:00.000
Davolio 5 1998-05-01 00:00:00.000
Davolio 6 1998-04-21 00:00:00.000
Davolio 7 1998-04-21 00:00:00.000
Davolio 8 1998-04-16 00:00:00.000
Davolio 9 1998-04-14 00:00:00.000
Davolio 10 1998-04-09 00:00:00.000
Davolio 11 1998-04-02 00:00:00.000
Davolio 12 1998-04-01 00:00:00.000
Davolio 13 1998-04-01 00:00:00.000
…

Φυσικά, αν θέλουμε να μην εφαρμόσουμε partitions στα δεδομένα μας, μπορούμε να πούμε κάτι σαν:

SELECT ROW_NUMBER() OVER (ORDER BY OrderID), * FROM Orders

Πάντοτε το ORDER BY clause είναι υποχρεωτικό, ακόμη κι αν έχουμε κάποιο clustered index που μας επιστρέφει ταξινομημένα τα δεδομένα.

Τέλος, υπάρχει ένα τέταρτο ranking function που ονομάζεται NTILE και χρησιμεύει προκειμένου να «σπάσουμε» ένα σύνολο από εγγραφές σε ομάδες. Δηλαδή, για παράδειγμα, αν θέλουμε μια λίστα για τη μεταφορική εταιρία «United Package», τις παραγγελίες που έχει διακινήσει, την ημερομηνία παραγγελίας και όλα αυτά σε 5 groups, τότε μπορούμε να γράψουμε το εξής query:

SELECT Shippers.CompanyName,
  Orders.OrderID,
  NTILE(5) OVER(PARTITION BY Shippers.CompanyName ORDER BY Orders.OrderDate DESC) AS OrderRank,
  Orders.OrderDate
FROM Orders INNER JOIN Shippers
   ON Orders.ShipVia = Shippers.ShipperID
WHERE Shippers.CompanyName='United Package'

Το function NTILE έχει ένα όρισμα που δείχνει πόσα groups θα φτιαχτούν. Παράλληλα, προσπαθεί να κάνει populate τα groups με ίσο αριθμό εγγραφών και αν αυτό δεν γίνει, τοποθετεί τα groups με μεγαλύτερο αριθμό εγγραφών, πρώτα.

United Package 11074 1 1998-05-06 00:00:00.000
United Package 11075 1 1998-05-06 00:00:00.000
United Package 11076 1 1998-05-06 00:00:00.000
...
United Package 10938 2 1998-03-10 00:00:00.000
United Package 10939 2 1998-03-10 00:00:00.000
United Package 10936 2 1998-03-09 00:00:00.000
...
United Package 10768 3 1997-12-08 00:00:00.000
United Package 10761 3 1997-12-02 00:00:00.000
United Package 10756 3 1997-11-27 00:00:00.000
...
United Package 10579 4 1997-06-25 00:00:00.000
United Package 10577 4 1997-06-23 00:00:00.000
United Package 10574 4 1997-06-19 00:00:00.000
...
United Package 10429 5 1997-01-29 00:00:00.000
United Package 10427 5 1997-01-27 00:00:00.000
United Package 10425 5 1997-01-24 00:00:00.000
...

Κλείνοντας, θα πρέπει να πούμε ότι τα ranking functions είναι non-deterministic, που χοντρικά σημαίνει ότι το ίδιο query σε δύο διαφορετικές χρονικές στιγμές μπορεί να δώσει διαφορετικά αποτελέσματα. Αν θα γίνει αυτό ή όχι εξαρτάται από τη σειρά ταξινόμησης. Όταν βασίζεται σε κάποιο κλειδί (και δεν αλλάζει το πλήθος των εγγραφών) τότε είναι deterministic.  Δεν μπορούμε δηλαδή να είμαστε ποτέ σίγουροι ότι η παραγγελία 10936 θα έχει rank 2.