Εισαγωγή
Πάρα πολλά νέα χαρακτηριστικά έχουν μπει στον 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 είναι
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- a. SQL Window function expression evaluation
- b. Remove Duplicates
- ORDER BY
- 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. Για να πάρετε μια ιδέα να μερικά παραδείγματα
Τώρα όμως τα πράγματα γίνονται καλύτερα, απλούστερα και με καλύτερή επίδοση. Για το παράδειγμα μας θα χρησιμοποιήσω τις παραγγελίες από την 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 να αναφέρω περισσότερα, εκτός από το να πω ακόμα μια φορά ότι η αγορά του βιβλίου αυτού αξίζει τα λεφτά του.