Common Table Expressions

Common Table Expressions

Τα Common Table Expressions (CTEs) είναι ένα νέο χαρακτηριστικό της T-SQL το οποίο καθορίζεται στο SQL-99 πρότυπο. Τι είναι αυτά; Είναι ο τρόπος με τον οποίο καθορίζουμε προσωρινά result sets μέσα σε ένα statement. Φέρτε στο μυαλό σας τα derived και temporary tables… Ε, κάτι παρόμοιο αλλά όχι ακριβώς το ίδιο όπως θα δούμε παρακάτω. Συνήθως χρησιμοποιούνται για δύο λόγους. Προκειμένου να απλουστεύσουμε τη δομή περίπλοκων queries (εντάξει, αυτό δεν μας ενδιαφέρει γιατί δεν έχουμε πρόβλημα με τα περίπλοκα queries) αλλά και προκειμένου να μπορούμε να υλοποιήσουμε εύκολα αναδρομικά (recursive) queries! 

Η σύνταξη ενός CTE έχει ως εξής:

[WITH <common_table_expression> [,...n] ]
<common_table_expression>::=
   expression_name
   [(column_name [,...n])]
   AS
   (<CTE_query_definition>)

Βέβαια, είναι κομματάκι δύσκολο να αντιληφθεί κανείς αμέσως περί τίνος πρόκειται, γι αυτό ας δούμε ένα παράδειγμα:

WITH TopOrders (ProductID, TotQty) AS
( SELECT ProductID, Sum(Quantity) 
  FROM [Order Details] GROUP BY ProductID)
SELECT * FROM TopOrders
WHERE TotQty>1000
ORDER BY TotQty DESC

Δεν είναι τρομερό ως παράδειγμα με την έννοια του ότι θα μπορούσαμε να κάνουμε το ίδιο πράγμα χωρίς CTE, ωστόσο ας δούμε πως σχετίζεται με τον ορισμό. Καταρχήν, ορίζουμε ένα CTE που ονομάζεται TopOrders, αποτελείται από δύο πεδία, το ProductID και το TotQty. To CTE_query_definition είναι το SELECT που μας επιστρέφει το result set του οποίου τα πεδία γίνονται match με αυτά που έχω καθορίσει στο CTE.

Αν χρησιμοποιούσαμε temporary table θα έπρεπε να το δηλώναμε και να το κάναμε populate. Εδώ τα γλυτώνουμε αυτά, ωστόσο ο CTE μπορεί να χρησιμοποιηθεί μόνο μια φορά, στο query που ακολουθεί, ενώ το temporary table μπορεί να επαναχρησιμοποιηθεί.

Φυσικά, δεν μας εμποδίζει τίποτα να κάνουμε παιχνίδι όπως εδώ:

WITH TopOrders (ProductID, TotQty) AS
( SELECT ProductID, Sum(Quantity) 
  FROM [Order Details] GROUP BY ProductID)
SELECT P.ProductName, TPO.TotQty FROM TopOrders TPO
INNER JOIN Products P ON P.ProductID=TPO.ProductID
WHERE TotQty>1000
ORDER BY TotQty DESC

Εκεί που φαίνεται η αξία των CTE είναι στα λεγόμενα recursive queries, δηλαδή queries σε πίνακες με self-join. Για παράδειγμα, στη Northwind database, υπάρχει ο πίνακας Employees, με το πεδίο EmployeeID ως κλειδί και για κάθε employee, το πεδίο ReportsTo δείχνει στον manager που αναφέρεται αυτός ο υπάλληλος. Έτσι λοιπόν, αν θέλουμε να πάρουμε ένα report με τους managers και τους υφισταμένους τους, τότε μπορούμε να γράψουμε το εξής:

WITH Managers (ManagerID, EmployeeID) AS 
( SELECT ReportsTo, EmployeeID
  FROM Employees
  WHERE ReportsTo IS NULL
 UNION ALL
 SELECT E.ReportsTo, E.EmployeeID
  FROM Employees E  
  INNER JOIN Managers M ON 
  E.ReportsTo = M.EmployeeID)
SELECT * FROM Managers

Αυτό το CTE είναι ελαφρώς διαφορετικό σε σχέση με το προηγούμενο. Κατ’ αρχήν θυμηθείτε ας πούμε τα recursive functions σε οποιαδήποτε γλώσσα προγραμματισμού. Θα πρέπει να υπάρχει ένας τρόπος να σταματούν. Εδώ, δεν έχουμε IF και η λογική δεν είναι procedural και για αυτόν λόγο, έχουμε ένα πρώτο SELECT που ονομάζεται «anchor member query» - αποτελεί δε την κορυφή της ιεραρχίας.

Κατόπιν, υποχρεωτικά έχουμε το UNION ALL (το μοναδικό operator που επιτρέπεται) το οποίο συνδέει τα δύο result set. Τέλος έχουμε το «recursive member query» το οποίο συνδέει τον πίνακα με το CTE και εκτελείται συνεχώς μέχρι  να μην επιστρέφει result set.

Τέλος, μπορούμε να καθορίσουμε το μέγιστο αριθμό από recursions με το MAXRECURSION option (τι άλλο περιμένατε, ε;), ο οποίος αν ξεπεραστεί, τότε το query σταματά με error message.

Έγραψα προηγουμένως ότι η αξία των CTE φαίνεται στα recursive queries. Ορίστε λοιπόν μια άσκηση για γερούς αναγνώστες… Δοκιμάστε να γράψετε το παραπάνω χωρίς CTE για να δούμε αν αξίζει ή όχι η χρήση τους. Αν δεν έχω απάντηση (μιας και δεν βλέπω ποτέ κανένα σχόλιο στα posts μου) θα σας δείξω έναν τρόπο σε προσεχές post.