Using Window Functions in SQL Server 2012

Εισαγωγή

Πάρα πολλά νέα χαρακτηριστικά έχουν μπει στον SQL Server 2012. Όπως έχω πει και σε προηγούμενα post αλλά και στην πρόσφατη παρουσίαση μου στο 39o Autoexec event κάποια είναι δημοφιλή και ακούγονται συχνά όπως τα AlwaysOn Availability Groups, Auditing Enhancements, Security Enhancements κλπ. Δικαίως και είναι δημοφιλή. Υπάρχουν όμως τόσα άλλα που δεν ακούγονται, κακώς κατά την γνώμη μου. Σε μερικά μάλιστα δεν έχει δοθεί η δέουσα προσοχή.

Ομολογώ ότι και εγώ σε κάποια δεν είχα δώσει και τόσο μεγάλη σημασία μέχρι που δύο γεγονότα ήρθαν να με κάνουν να ασχοληθώ μαζί τους σε βάθος. Το ένα ήταν είναι η ενασχόληση μου με το νέο σεμινάριο που υπάρχει για την T-SQL στον SQL Server 2012 με τίτλο «Querying SQL Server 2012» και κωδικό 10774 το οποίο πρόσφατα είχα την δυνατότητα να διδάξω, και το άλλο ότι αγόρασα το νέο βιβλίο του Itzik Ben-Gan με τίτλο «Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions».

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions, The Book.

Το δεύτερο ήταν αυτό που με έβαλε για τα καλά μέσα στο παιχνίδι να ασχοληθώ με το θέμα που θα σας παρουσιάσω στο post αυτό. Ποτέ μέχρι τώρα όσο και αν με έχουν πιέσει δεν έχω πει κάτι για κάποιο βιβλίο καθώς πιστεύω ότι το βιβλίο είναι καθαρά προσωπική εκτίμηση για το αν είναι καλό ή όχι. Είναι όμως η πρώτη φορά που θα παραβιάσω την αρχή μου αυτή και θα το προτείνω καθώς είναι ίσως το μοναδικό που διαπραγματεύεται άριστα κατά την γνώμη μου το θέμα. Είναι ένα βιβλίο που αξίζει τα λεφτά του μέχρι την τελευταία του σελίδα. Είναι ένα μικρό σε όγκο βιβλίο αλλά τόσο αναλυτικό και το κάθε κεφάλαιο του (πέντε συνολικά) είναι καλύτερο από το προηγούμενο. Είναι από τα βιβλία τα οποία στο τέλος σου αφήνουν την γεύση που θέλεις να έχει από κάθε βιβλίο και δεν είναι άλλη από αυτή του ότι έχω μάθει πολλά από αυτό. Σε αυτό ο Itzik έχει κάνει μια εξαιρετική δουλειά στην περιγραφή των window functions, που σημειωτέον είναι ANSI & ISO SQL και στο οποίο ο συγγραφέας εξηγεί πως ο SQL Server 2012 υλοποιεί τα περισσότερα από όσα λέει το ANSI και το ISO.

Τι είναι όμως τα SQL Window functions;

Με αφορμή τα παραπάνω σε αυτό το post θέλω να σας δείξω μερικές χρήσεις των SQL Window Functions καθώς η άποψη μου είναι πλέον ότι κάθε db developer θα πρέπει να γνωρίζει. Ξεκινώντας θα πρέπει να δούμε τι είναι αυτά. Θα δανειστώ την πρώτη παράγραφο που έχει γράψει ο Itzik στο βιβλίο του στο πρώτο κεφάλαιο αυτού

«Window functions are functions applied to sets of rows defined by a clause called OVER. They are used mainly for analytical purposes allowing you to calculate running totals, calculate moving averages, identify gaps and islands in your data, and perform many other computations. These functions are based on an amazingly profound concept in standard SQL (which is both an ISO and ANSI standard)—the concept of windowing. The idea behind this concept is to allow you to apply various calculations to a set, or window, of rows and return a single value. Window functions can help to solve a wide variety of querying tasks by helping you express set calculations more easily, intuitively, and efficiently than ever before.»

Καλύτερα δεν θα μπορούσε να αποδοθεί το τι αυτά είναι, καθώς είναι μια έννοια η οποία τουλάχιστον στην αρχή είναι δύσκολο κάποιος να την καταλάβει και αυτό διότι είναι κάτι το οποίο απαιτεί να έχει καταλάβει κανείς το πώς εκτελείται λογικά ένα query όπου σε κάθε βήμα του υπάρχει ένα virtual table. Πρέπει να καταλάβει το σημείο που αυτά εφαρμόζονται και ίσως είναι το δυσκολότερο σημείο για την κατανόηση τους. Ακόμα ένα δύσκολο σημείο είναι να καταλάβει κανείς το γεγονός ότι στα δεδομένα αυτά εφαρμόζεται κάποιο είδος φίλτρου το οποίο μου δημιουργεί ένα παράθυρο στο οποίο έχω τα δεδομένα τα οποία θα αποτελέσουν το input για την εφαρμογή των window functions.

Πότε εφαρμόζονται στην λογική εκτέλεση ενός query;

Δεν θέλω να μπω σε τέτοιες λεπτομέρειες καθώς ο σκοπός μου δεν είναι να αντιγράψω το βιβλίο όμως για την ευκολία στην συνέχεια της ανάγνωσης του συγκεκριμένου post θα εξηγήσω σύντομα και απλά τις παραπάνω προτάσεις.

Όπως έχω αναφέρει σε παλαιότερα post μου η λογική σειρά εκτέλεσης ενός query είναι

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
   • a. SQL Window function expression evaluation
   • b. Remove Duplicates
6. ORDER BY
7. OFFSET-FETCH/TOP

Από το παραπάνω είναι πλέον σαφές ότι τα SQL Window functions εφαρμόζονται μετά το SELECT και πριν την απόρριψη των διπλοεγγραφών στην περίπτωση που υπάρχει κάποιο DISTINCT και αυτό είναι το αρχικό μας παράθυρο πάνω στο οποίο αυτές (οι functions) θα εφαρμοστούν αν και μπορούμε να έχουμε περιορισμούς εν είδη φίλτρου εφόσον σε αυτές έχουμε βάλει κάποιο ORDER BY ή PARTITION.

Η Σύνταξη τους

Η γενικότερη μορφή της σύνταξης τους είναι

Function(<arguments>) OVER ( [window partition clause] [window order clause [window frame clause]] )

Όπου με το partitioning clause έχουμε την δυνατότητα να επιβάλουμε ένα είδος restriction σε αυτές τις γραμμές που έχουν τις ίδιες τιμές με την τρέχουσα γραμμή και αναφέρονται στα partition argumenents. Με το order clause έχουμε την δυνατότητα να επιβάλουμε ένα είδος framing με το οποίο ορίζουμε το κατώτερο και το ανώτερο όριο μέσα σε ένα partition με σκοπό μόνο αυτές οι γραμμές που είναι μέσα σε αυτό να φιλτραριστούν.

Από την σύνταξη μέσα στο window order clause έχουμε το window frame clause το οποίο αναλύεται περαιτέρω ως

<window frame units> <window frame extents> [<window frame exclusion>]

Το window frame exclusion δεν είναι ακόμα υλοποιημένο στον SQL Server 2012, τα άλλα δύο όμως είναι αλλά και πάλι όχι σε όλη τους την γκάμα δυνατοτήτων τους όπως αυτή ορίζεται από το πρότυπο.

Ειδικότερα για τα window frame units και window frame extents η υλοποίηση τους γίνεται με την χρήση των ROWS ή RANGE. Για την πρώτη υπάρχει πλήρης υλοποίηση αλλά για την RANGE δεν υπάρχει πλήρης υλοποίηση από τον SQL Server 2012.

Σε αυτές υπάρχουν τα UNBOUNDED PRECEDING/FOLLOWING, <n> PERCENDING, <n> FOLLOWING, CURRENT ROW.

Με τα παραδείγματα που ακολουθούν θα γίνουν αυτά κατανοητά. Αλλά θα τονίσω για ακόμα μια φορά ότι υπάρχει το βιβλίο!. Τα λέει όλα αναλυτικά.

Παραδείγματα

Δεν θα σας κουράσω με πολλά αλλά με μερικά δυνατά παραδείγματα τα οποία θα σας γλυκάνουν για να ασχοληθείτε με αυτές όπως εγώ όπου έχω πάθει υστερία μαζί τους.

Για πρώτο παράδειγμα θα ξεκινήσω με αυτό που αρκετές φορές ζητάτε ειδικά σε οικονομικού χαρακτήρα εφαρμογές και δεν είναι άλλο από το running total ή αλλιώς προοδευτικό σύνολο. Πριν την έλευση των window functions για να κάνει κάποιος κάτι τέτοιο θα έπρεπε να το κάνει μ είτε με κάποιο self join ή με sub-query. Για να πάρετε μια ιδέα να μερικά παραδείγματα

• Calculating Running Totals, Subtotals and Grand Total Without a Cursor
• Complicated SQL query for a running total column
• SQL Server Query, running total in view, reset when column A changes

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

 

create view dbo.OrdersView 
as 
    select o.Orderid,o.orderdate,o.CustomerID, 
           sum(od.quantity*od.unitprice) OrderTotal 
    from Orders o 
    inner join [Order Details] od on o.OrderID=od.OrderID 
    group by o.OrderID,o.orderdate,o.CustomerID; 
go 

Το αποτέλεσμα του είναι

Orderid     orderdate               CustomerID OrderTotal
----------- ----------------------- ---------- ---------------------
10248       1996-07-04 00:00:00.000 VINET      440,00
10249       1996-07-05 00:00:00.000 TOMSP      1863,40
10250       1996-07-08 00:00:00.000 HANAR      1813,00
10251       1996-07-08 00:00:00.000 VICTE      670,80
10252       1996-07-09 00:00:00.000 SUPRD      3730,00
10253       1996-07-10 00:00:00.000 HANAR      1444,80
10254       1996-07-11 00:00:00.000 CHOPS      625,20
10255       1996-07-12 00:00:00.000 RICSU      2490,50
10256       1996-07-15 00:00:00.000 WELLI      517,80
10257       1996-07-16 00:00:00.000 HILAA      1119,90

Αν μας ζητούσαν να έχω running total για όλες τις παραγγελιές το query μας θα ήταν

select OrderID,orderdate,customerid,ordertotal, 
    sum(OrderTotal) over( order by orderid 
                rows between unbounded preceding 
                and current row) as RunningTotal 
from OrdersView; 

και το αποτέλεσμα του

OrderID     orderdate               customerid ordertotal            RunningTotal
----------- ----------------------- ---------- --------------------- ---------------------
10248       1996-07-04 00:00:00.000 VINET      440,00                440,00
10249       1996-07-05 00:00:00.000 TOMSP      1863,40               2303,40
10250       1996-07-08 00:00:00.000 HANAR      1813,00               4116,40
10251       1996-07-08 00:00:00.000 VICTE      670,80                4787,20
10252       1996-07-09 00:00:00.000 SUPRD      3730,00               8517,20
10253       1996-07-10 00:00:00.000 HANAR      1444,80               9962,00
10254       1996-07-11 00:00:00.000 CHOPS      625,20                10587,20
10255       1996-07-12 00:00:00.000 RICSU      2490,50               13077,70
10256       1996-07-15 00:00:00.000 WELLI      517,80                13595,50
10257       1996-07-16 00:00:00.000 HILAA      1119,90               14715,40
10258       1996-07-17 00:00:00.000 ERNSH      2018,60               16734,00

Να εξηγήσω όμως λίγο τι έχει γραφτεί ώστε να βγει το running total. Στην ουσία ζητάμε στο σύνολο του ordertotal το οποίο όμως να είναι βασισμένο για την τρέχουσα εγγραφή στο παράθυρο με βάση το orderid και για τις γραμμές σε αυτό που είναι από την αρχή (rows between unbounded preceding) και την τρέχουσα γραμμή (and current row)

Αν το αφεντικό δεν ήταν ικανοποιημένο με αυτό και ήθελε να έρχονται οι παραγγελίες με ανά πελάτη και ημερομηνία και με την αλλαγή του πελάτη να ξαναρχίζει από την αρχή το running total τότε απλά το query μας θα γίνονταν όπως παρακάτω

select OrderID,orderdate,customerid,ordertotal, 
    sum(OrderTotal) 
        over(partition by customerid 
        order by orderdate 
        rows between unbounded preceding 
        and current row) as RunningTotal 
from OrdersView; 

και το αποτέλεσμα του θα είναι

OrderID     orderdate               customerid ordertotal           RunningTotal
----------- ----------------------- ---------- --------------------- ---------------------
10643       1997-08-25 00:00:00.000 ALFKI      1086,00               1086,00
10692       1997-10-03 00:00:00.000 ALFKI      878,00                1964,00
10702       1997-10-13 00:00:00.000 ALFKI      330,00                2294,00
10835       1998-01-15 00:00:00.000 ALFKI      851,00                3145,00
10952       1998-03-16 00:00:00.000 ALFKI      491,20                3636,20
11011       1998-04-09 00:00:00.000 ALFKI      960,00                4596,20
10308       1996-09-18 00:00:00.000 ANATR      88,80                 88,80
10625       1997-08-08 00:00:00.000 ANATR      479,75                568,55
10759       1997-11-28 00:00:00.000 ANATR      320,00                888,55
10926       1998-03-04 00:00:00.000 ANATR      514,40                1402,95
10365       1996-11-27 00:00:00.000 ANTON      403,20                403,20
10507       1997-04-15 00:00:00.000 ANTON      881,25                1284,45
10535       1997-05-13 00:00:00.000 ANTON      2156,50               3440,95
10573       1997-06-19 00:00:00.000 ANTON      2082,00               5522,95
10677       1997-09-22 00:00:00.000 ANTON      956,90                6479,85
10682       1997-09-25 00:00:00.000 ANTON      375,50                6855,35
10856       1998-01-28 00:00:00.000 ANTON      660,00                7515,35

 

Αυτό που κάναμε εδώ ήταν απλά να προσθέσουμε ένα partition το οποίο λειτουργεί ως restriction που σημαίνει ότι το παράθυρο θα έχει μόνο τις εγγραφές που ο κωδικό πελάτη της τρέχουσας γραμμής ορίζει και οι οποίες θα είναι φιλτραρισμένες όπως και πριν.

Φανταστείτε τι παιχνίδι μπορείτε να κάνετε…

Για αυτό το λόγο θα σας πάω λίγο βαθύτερα με ένα λίγο πιο ανεβασμένο σενάριο. Ας υποθέσουμε ότι στο παραπάνω το αφεντικό ζητάει σε κάθε γραμμή των δεδομένων όπως φαίνεται παραπάνω θέλει να υπάρχει το σύνολο της προηγούμενης παραγγελίας, της επόμενης και ο μέσος όρος των τριών (προηγούμενης, τρέχουσας και επόμενης και αυτό πάλι ανά πελάτη. Δεν θέλω να σκεφτώ τι έκανα για πάρω ένα τέτοιο αποτέλεσμα στο παρελθόν τώρα όμως το query μου θα μπορούσε να είναι κάπως έτσι

select OrderID,orderdate,customerid, 
    max(OrderTotal) 
        over(partition by customerid 
        order by orderdate 
        rows between 1 preceding 
        and 1 preceding) as PrevOrderTotal, 
    OrderTotal as CurrentOrderTotal, 
    max(OrderTotal) 
        over(partition by customerid 
        order by orderdate 
        rows between 1 following 
        and 1 following) as NextOrderTotal, 
    avg(OrderTotal) 
        over(partition by customerid 
        order by orderdate 
        rows between 1 preceding 
        and 1 following) as AgvOrderTotal 
from OrdersView; 
go 

και το αποτέλεσμα αυτής της εκτέλεσης

OrderID     orderdate  customerid PrevOrderTotal        CurrentOrderTotal     NextOrderTotal        AgvOrderTotal
----------- ---------- ---------- --------------------- --------------------- --------------------- ---------------------
10643       25/08/1997 ALFKI      NULL                  1086,00               878,00                982,00
10692       03/10/1997 ALFKI      1086,00               878,00                330,00                764,6666
10702       13/10/1997 ALFKI      878,00                330,00                851,00                686,3333
10835       15/01/1998 ALFKI      330,00                851,00                491,20                557,40
10952       16/03/1998 ALFKI      851,00                491,20                960,00                767,40
11011       09/04/1998 ALFKI      491,20                960,00                NULL                  725,60
10308       18/09/1996 ANATR      NULL                  88,80                 479,75                284,275
10625       08/08/1997 ANATR      88,80                 479,75                320,00                296,1833
10759       28/11/1997 ANATR      479,75                320,00                514,40                438,05
10926       04/03/1998 ANATR      320,00                514,40                NULL                  417,20
10365       27/11/1996 ANTON      NULL                  403,20                881,25                642,225
10507       15/04/1997 ANTON      403,20                881,25                2156,50               1146,9833
10535       13/05/1997 ANTON      881,25                2156,50               2082,00               1706,5833
10573       19/06/1997 ANTON      2156,50               2082,00               956,90                1731,80
10677       22/09/1997 ANTON      2082,00               956,90                375,50                1138,1333
10682       25/09/1997 ANTON      956,90                375,50                660,00                664,1333
10856       28/01/1998 ANTON      375,50                660,00                NULL                  517,75
10355       15/11/1996 AROUT      NULL                  480,00                899,00                689,50
10383       16/12/1996 AROUT      480,00                899,00                453,00                610,6666
10453       21/02/1997 AROUT      899,00                453,00                2142,90               1164,9666
10558       04/06/1997 AROUT      453,00                2142,90               1704,00               1433,30

Μην μου πείτε ότι δεν είναι ωραίο;

Από performance πάλι είναι αρκετά καλύτερο από άλλες πιθανές υλοποιήσεις.

Επίλογος

Θα μπορούσα να γράψω πολλά ακόμα παραδείγματα όμως νομίζω ότι πρέπει να σταματήσω εδώ καθώς δεν θα ήταν δίκαιο για το βιβλίο και του δουλειά του Itzik να αναφέρω περισσότερα, εκτός από το να πω ακόμα μια φορά ότι η αγορά του βιβλίου αυτού αξίζει τα λεφτά του.