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.

Advertisements


Σχολιάστε

Εισάγετε τα παρακάτω στοιχεία ή επιλέξτε ένα εικονίδιο για να συνδεθείτε:

Λογότυπο WordPress.com

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό WordPress.com. Αποσύνδεση / Αλλαγή )

Φωτογραφία Twitter

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Twitter. Αποσύνδεση / Αλλαγή )

Φωτογραφία Facebook

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Facebook. Αποσύνδεση / Αλλαγή )

Φωτογραφία Google+

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Google+. Αποσύνδεση / Αλλαγή )

Σύνδεση με %s