go backarticles

Articles of SQLschool.gr Team

Getting Started: Writing T-SQL (Part 4)

Fivi Panopoulou - Sotiris Karras

Σε συνέχεια του προηγούμενου 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

Fivi Panopoulou

Fivi Panopoulou

System Engineer • Speaker

Το 2007 ξεκίνησα τις σπουδές μου στη σχολή Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών στο Εθνικό Μετσόβιο Πολυτεχνείο.Κατά την διάρκεια των σπουδών μου εκεί αγάπησα τον προγραμματισμό και τα συστήματα πληροφορικής, καθώς επίσης απέκτησα το ιδιαίτερο ενδιαφέρον μου για τις βάσεις δεδομένων. Κατά την διάρκεια της διπλωματικής μου ασχολήθηκα με ζητήματα ανωνυμοποίησης δεδομένων και την ανάπτυξη σχετικού εργαλείου. Τα τελευταία χρόνια των σπουδών μου, μου δόθηκε η ευκαιρία να ασχοληθώ περισσότερο και να διευρύνω τους ορίζοντές μου ως Microsoft Student Partner και μέσω της κοινότητας Student Guru. Στα πλαίσια των κοινοτήτων αυτών, ξεκίνησα να ασχολούμαι με παρουσιάσεις αλλά και να γνωρίζω τον SQL Server. Από την πρώτη στιγμή που ασχολήθηκα μαζί του, συνειδητοποίησα πόσο ήθελα να εμβαθύνω τις γνώσεις μου σχετικά με αυτόν και τα συστήματα διαχείρισης βάσεων δεδομένων γενικότερα, πράγμα που προσπαθώ να κάνω έκτοτε. Πριν χρόνια είχα την τύχη να συμμετέχω στο πρόγραμμα mentoring, μέσω του οποίου γνώρισα τον κ. Χατζηπαυλή. Από τότε συμμετέχω στην ομάδα του SQLschool.gr.


Sotiris Karras

Sotiris Karras

System Engineer • Speaker

Είμαι απόφοιτος της σχολής Ηλεκτρολόγων Μηχανικών και Μηχανικών Υπολογιστών του Εθνικού Μετσόβιου Πολυτεχνείου και στα ενδιαφέροντά μου συμπεριλαμβάνεται o τομέας του Knowledge and Data Engineering. Πιο συγκεκριμένα, έχω ασχοληθεί ακαδημαϊκά και ερευνητικά με τον τομέα του data privacy και data anonymity, ενώ πάθος μου είναι ό,τι έχει να κάνει με relational databases και data management. Στο παρελθόν, έχω συνεργαστεί με την Microsoft Hellas ως Microsoft Student Partner για ακαδημαϊκές δραστηριότητες και ήμουν μέρος του MVP mentoring προγράμματός της, στο οποίο είχα την τύχη να γνωρίσω τον κ. Χατζηπαυλή.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.