Σε συνέχεια του προηγούμενου post, θα δούμε τα Aggregate Functions που μπορούμε να χρησιμοποιήσουμε, καθώς και τα GROUP BY και HAVING clauses. Επιπλέον θα δούμε τα φίλτρα TOP και OFFSET – FETCH.
AGGREGATE FUNCTIONS
Στην T-SQL υπάρχει ένα σύνολο συναρτήσεων που εκτελούν υπολογισμούς πάνω στα δεδομένα όπως άθροισμα, μέσος όρος, υπολογισμός ελάχιστης και μέγιστης τιμής. Αυτές οι συναρτήσεις ονομάζονται aggregate functions και η λίστα με όλες όσες περιλαμβάνει η T-SQL βρίσκεται εδώ.
Αξίζει να σημειωθεί πως ο γενικός κανόνας είναι ότι οι aggregate functions αγνοούν τα nulls. Αυτό σημαίνει ότι για παράδειγμα αν έχουμε μια στήλη που περιλαμβάνει τις τιμές : 4.0, 5.0 ,NULL, 4.5, NULL ο μέσος όρος με την AVG aggregate function θα υπολογιστεί ως (4.0 + 5.0 + 4.5)/3 = 4.5 αγνοώντας το πλήθος των nulls. Εξαίρεση σε αυτό αποτελεί το COUNT(*) το οποίο επιστρέφει τον αριθμό γραμμών, συμπεριλαμβανομένων των nulls.
Παρακάτω βλέπουμε παραδείγματα για τον υπολογισμό της μέγιστης τιμής προϊόντος από τον πίνακα Products (Query 1), τον υπολογισμό της συνολικής αξίας των παραγγελιών (Query 2), ενώ στο Query 3 βλέπουμε 3 διαφορετικές χρήσεις της COUNT, μαζί με το αποτέλεσμα του συγκεκριμένου query. Στην πρώτη στήλη, με το COUNT(*) θα πάρουμε τον συνολικό αριθμό γραμμών συμπεριλαμβανομένων nulls και duplicates (αν και στο παράδειγμά μας ο πίνακας Orders δεν έχει τίποτα από τα δύο). Στην δεύτερη στήλη το COUNT(ShipRegion), ισοδυναμεί με το COUNT(ALL ShipRegion) και επιστρέφει τον αριθμό των περιοχών συμπεριλαμβάνοντας duplicate τιμές, αλλά αγνοώντας τα null. Στην τελευταία στήλη, παίρνουμε πόσες διαφορετικές μεταξύ τους περιοχές υπάρχουν στον πίνακα Orders, αγνοώντας και πάλι τα nulls.
--Query 1: max product price
SELECT MAX(UnitPrice) AS MaxPrice
FROM Products;
GO
--Query 2: total value oforders
SELECT SUM(UnitPrice*Quantity) AS TotalValue
FROM [Order Details];
GO
--Query 3: count
SELECT COUNT(*) as Orders,
COUNT(ShipRegion) as Regions ,
COUNT(DISTINCT ShipRegion) as DistinctRegions
FROM Orders
GO
/*
Orders Regions DistinctRegions
830 323 19
*/
GROYP BY και HAVING
Το GROUP BY clause χρησιμοποιείται για την ομαδοποίηση των εγγραφών με βάση την τιμή μιας ή περισσότερων στηλών. Για παράδειγμα, βλέπουμε στο Query 4 τον συνολικό αριθμό τεμαχίων που έχουν παραγγελθεί ανά προϊόν και στο Query 5 βλέπουμε τον συνολικό τζίρο κάθε παραγγελίας. Στην πρώτη περίπτωση, οι εγγραφές του πίνακα Order Details χωρίζονται σε ομάδες με βάση το ProductId και στη συνέχεια υπολογίζεται η τιμή της aggregate function για κάθε ομάδα. Παρομοίως και για το δεύτερο, χωρίζοντας τις εγγραφές με βάση το OrderId. Αξίζει να σημειωθεί ότι στη select list μπορούν να συμπεριληφθούν οι στήλες με τις οποίες κάνουμε group by και aggregations με βάση τις υπόλοιπές στήλες. Παραδείγματος χάρη, δεν θα μπορούσαμε να συμπεριλάβουμε στην select list του παραδείγματος 4 την στήλη OrderId ή Quantity.
--Query 4: total quantity by product
SELECT ProductID, SUM(Quantity) AS TotalValue
FROM [Order Details]
GROUP BY ProductID;
GO
--Query 5: total value by order
SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) AS TotalValue
FROM [Order Details]
GROUP BY OrderID;
GO
--Query 6: total quantity per product,for products with more than 1000 items ordered
SELECT ProductID, SUM(quantity) as TotalQuantity
FROM [Order Details]
GROUP BY ProductID
HAVING SUM(quantity) > 1000;
GO
--Query 7: total discounted quantity per product,for ptoducts with more than 100 items discounted.
SELECT ProductID, SUM(quantity) as TotalQuantity
FROM [Order Details]
WHERE Discount > 0
GROUP BY ProductID
HAVING SUM(quantity) > 100;
GO
Για να εφαρμόσουμε φιλτράρισμα με βάση κάποια συνθήκη στο αποτέλεσμα αυτό, θα χρειαστούμε το HAVING clause. Αυτό, όπως και το WHERE, μας επιστρέφει τις γραμμές στις οποίες ισχύει η συνθήκη. Όμως, ενώ το WHERE θα «διώξει» τις γραμμές που δεν θέλουμε πριν από την ομαδοποίηση τους, το HAVING φιλτράρει το αποτέλεσμα μετά την ομαδοποίηση. Αυτό μας επιτρέπει στη συνθήκη του HAVING να χρησιμοποιήσουμε μια aggregate function. Για παράδειγμα στο Query 6, βρίσκουμε τα προϊόντα για τα οποία ο συνολικός αριθμός τεμαχίων που έχει παραγγελθεί υπερβαίνει τα 1000 τεμάχια. Στο Query 7, λόγω του where clause, ασχολούμαστε μόνο με τις γραμμές της κάθε παραγγελίας για τις οποίες έχει γίνει κάποια έκπτωση. Έτσι βρίσκουμε το συνολικό αριθμό τεμαχίων ανά προϊόν που έχει γίνει έκπτωση και κρατάμε την πληροφορία αυτή μόνο για τα προϊόντα που αυτός ο συνολικός αριθμός είναι μεγαλύτερος από 100 τεμάχια.
TOP, OFFSET-FETCH
Έχοντας κλείσει με τα βασικά τμήματα ενός select statement, πρέπει να δούμε πώς μπορούμε να κρατήσουμε μέρος των γραμμών που επιστρέφεται από ένα query με βάση την διάταξη αυτών. Το πρώτο φίλτρο που έχουμε στην T-SQL για αυτό το σκοπό είναι το TOP. Όπως φαίνεται και από το παράδειγμα (Query 8), για να πάρουμε τις πρώτες N γραμμές ενός αποτελέσματος χρησιμοποιούμε το keyword TOP ακολουθούμενο από τον αριθμό των γραμμών που θέλουμε. Μια διαφορετική χρήση του TOP είναι, αντί να ζητήσουμε συγκεκριμένο αριθμό γραμμών, να ζητήσουμε ένα ποσοστό των γραμμών του αποτελέσματος. Για παράδειγμα στο
Query 11, με δεδομένο ότι ο πίνακας Products έχει 77 γραμμές θα μας επιστρέψει το 50% αυτών (στρογγυλοποιημένο προς τα πάνω), δηλαδή θα μας δώσει τα 39 ακριβότερα προϊόντα.
Ο συνδυασμός των OFFSET και FETCH είναι ένας άλλος τρόπος να καθορίσουμε τις γραμμές που θέλουμε να πάρουμε. Με το OFFSET ορίζουμε τον αριθμό των γραμμών που θα «αγνοηθούν», προσδιορίζοντας ουσιαστικά από πια γραμμή και κάτω θα είναι το αποτέλεσμά μας. Στο
Query 9 θα πάρουμε όλες τις γραμμές από την 11η και μετά, δηλαδή όλες τις υπόλοιπες γραμμές που δεν πήραμε στο Query 8. To FETCH προσδιορίζει πόσες γραμμές θέλουμε να πάρουμε, ξεκινώντας από το σημείο που προσδιορίζεται μέσω του OFFSET. Στο
Query 10, θα πάρουμε από την 11η έως και την 20η γραμμή του αποτελέσματος. Τo OFFSET-FETCH υποστηρίζεται από τον SQL Server 2012 και μετά και είναι πολύ καλή επιλογή για τις περιπτώσεις που θέλουμε να υλοποιήσουμε paging.
--Query 8: Top 10 most expensive products
SELECT TOP 10 ProductID,ProductName,UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
GO
--Query 9: the rest of products
SELECT ProductID,ProductName,UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OFFSET 10 ROWS;
GO
--Query 10: next 10 products
SELECT ProductID,ProductName,UnitPrice
FROM Products
ORDER BY UnitPrice DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO
--Query 11: 50% of products based on price
SELECT TOP 50 PERCENT ProductID,ProductName,UnitPrice
FROM Products
ORDER BY UnitPrice DESC;
GO