Sql sum με συνθήκη. Συγκεντρωτικές συναρτήσεις SQL - SUM, MIN, MAX, AVG, COUNT. Παράμετροι ή επιχειρήματα
Πώς μπορώ να μάθω τον αριθμό των μοντέλων Η/Υ που παράγονται από έναν συγκεκριμένο προμηθευτή; Πώς να καθορίσετε τη μέση τιμή των υπολογιστών που έχουν το ίδιο Προδιαγραφές? Αυτές και πολλές άλλες ερωτήσεις που σχετίζονται με ορισμένες στατιστικές πληροφορίες μπορούν να απαντηθούν χρησιμοποιώντας τελικές (συγκεντρωτικές) συναρτήσεις. Το πρότυπο παρέχει τις ακόλουθες συγκεντρωτικές λειτουργίες:
Όλες αυτές οι συναρτήσεις επιστρέφουν μία μόνο τιμή. Ταυτόχρονα, οι λειτουργίες COUNT, MINΚαι ΜΕΓΙΣΤΗισχύει για κάθε τύπο δεδομένων, ενώ ΑΘΡΟΙΣΜΑΚαι AVGχρησιμοποιούνται μόνο για αριθμητικά πεδία. Διαφορά μεταξύ λειτουργίας ΜΕΤΡΩ(*)Και ΜΕΤΡΩ(<имя поля>) είναι ότι η δεύτερη δεν λαμβάνει υπόψη τις τιμές NULL κατά τον υπολογισμό.
Παράδειγμα. Βρείτε την ελάχιστη και τη μέγιστη τιμή για προσωπικούς υπολογιστές:
Παράδειγμα. Βρείτε τον διαθέσιμο αριθμό υπολογιστών που παράγονται από τον κατασκευαστή Α:
Παράδειγμα. Εάν μας ενδιαφέρει ο αριθμός των διαφορετικών μοντέλων που παράγονται από τον κατασκευαστή Α, τότε το ερώτημα μπορεί να διατυπωθεί ως εξής (χρησιμοποιώντας το γεγονός ότι στον πίνακα Προϊόν κάθε μοντέλο καταγράφεται μία φορά):
Παράδειγμα. Βρείτε τον αριθμό των διαθέσιμων διαφορετικών μοντέλων που παράγονται από τον κατασκευαστή A. Το ερώτημα είναι παρόμοιο με το προηγούμενο, στο οποίο έπρεπε να προσδιοριστεί ο συνολικός αριθμός μοντέλων που παράγονται από τον κατασκευαστή A. Εδώ πρέπει επίσης να βρείτε τον αριθμό των διαφορετικών μοντέλων στο το τραπέζι υπολογιστή (δηλαδή, αυτά που είναι διαθέσιμα προς πώληση).
Για να διασφαλιστεί ότι χρησιμοποιούνται μόνο μοναδικές τιμές κατά τη λήψη στατιστικών δεικτών, όταν όρισμα συγκεντρωτικών συναρτήσεωνμπορεί να χρησιμοποιηθεί DISTINCT παράμετρος. Αλλο παράμετρος ALLείναι η προεπιλογή και προϋποθέτει ότι μετρώνται όλες οι επιστρεφόμενες τιμές στη στήλη. Χειριστής,
Εάν πρέπει να λάβουμε τον αριθμό των μοντέλων Η/Υ που παράγονται Ολοικατασκευαστή, θα χρειαστεί να χρησιμοποιήσετε ΟΜΑΔΑ ΑΝΑ ρήτρα, ακολουθώντας συντακτικά WHERE ρήτρες.
ΟΜΑΔΑ ΑΝΑ ρήτρα
ΟΜΑΔΑ ΑΝΑ ρήτραχρησιμοποιείται για τον καθορισμό ομάδων γραμμών εξόδου στις οποίες μπορούν να εφαρμοστούν συγκεντρωτικές συναρτήσεις (COUNT, MIN, MAX, AVG και SUM). Εάν λείπει αυτή η ρήτρα και χρησιμοποιούνται συγκεντρωτικές συναρτήσεις, τότε όλες οι στήλες με ονόματα που αναφέρονται ΕΠΙΛΕΓΩ, θα πρέπει να περιλαμβάνονται σε αθροιστικές συναρτήσεις, και αυτές οι συναρτήσεις θα εφαρμοστούν σε ολόκληρο το σύνολο σειρών που ικανοποιούν το κατηγόρημα του ερωτήματος. Διαφορετικά, όλες οι στήλες της λίστας SELECT Δεν περιλαμβάνονταιπρέπει να καθοριστούν συνολικά οι συναρτήσεις στην ρήτρα GROUP BY. Ως αποτέλεσμα, όλες οι σειρές ερωτημάτων εξόδου χωρίζονται σε ομάδες που χαρακτηρίζονται από τους ίδιους συνδυασμούς τιμών σε αυτές τις στήλες. Μετά από αυτό, θα εφαρμοστούν συγκεντρωτικές συναρτήσεις σε κάθε ομάδα. Λάβετε υπόψη ότι για το GROUP BY όλες οι NULL τιμές αντιμετωπίζονται ως ίσες, δηλ. κατά την ομαδοποίηση με ένα πεδίο που περιέχει τιμές NULL, όλες αυτές οι σειρές θα εμπίπτουν σε μία ομάδα.Αν εάν υπάρχει ρήτρα GROUP BY, στον όρο SELECT χωρίς συγκεντρωτικές συναρτήσεις, τότε το ερώτημα θα επιστρέψει απλώς μια σειρά από κάθε ομάδα. Αυτή η δυνατότητα, μαζί με τη λέξη-κλειδί DISTINCT, μπορεί να χρησιμοποιηθεί για την εξάλειψη των διπλότυπων σειρών σε ένα σύνολο αποτελεσμάτων.
Ας δούμε ένα απλό παράδειγμα:
| SELECT μοντέλο, COUNT(model) AS Qty_model, AVG(price) AS Avg_price ΑΠΟ Η/Υ GROUP BY μοντέλο? |
Σε αυτό το αίτημα, για κάθε μοντέλο Η/Υ καθορίζεται ο αριθμός και το μέσο κόστος τους. Όλες οι σειρές με την ίδια τιμή μοντέλου σχηματίζουν μια ομάδα και η έξοδος του SELECT υπολογίζει τον αριθμό των τιμών και τις μέσες τιμές τιμής για κάθε ομάδα. Το αποτέλεσμα του ερωτήματος θα είναι ο παρακάτω πίνακας:
| μοντέλο | Qty_model | Μέση_τιμή |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Εάν το SELECT είχε στήλη ημερομηνίας, τότε θα ήταν δυνατός ο υπολογισμός αυτών των δεικτών για κάθε συγκεκριμένη ημερομηνία. Για να το κάνετε αυτό, πρέπει να προσθέσετε την ημερομηνία ως στήλη ομαδοποίησης και, στη συνέχεια, θα υπολογιστούν οι συγκεντρωτικές συναρτήσεις για κάθε συνδυασμό τιμών (μοντέλο-ημερομηνία).
Υπάρχουν αρκετά συγκεκριμένα κανόνες για την εκτέλεση συγκεντρωτικών λειτουργιών:
- Εάν ως αποτέλεσμα του αιτήματος δεν ελήφθησαν σειρές(ή περισσότερες από μία σειρές για μια δεδομένη ομάδα), τότε δεν υπάρχουν δεδομένα πηγής για τον υπολογισμό οποιασδήποτε από τις συγκεντρωτικές συναρτήσεις. Σε αυτήν την περίπτωση, το αποτέλεσμα των COUNT συναρτήσεων θα είναι μηδέν και το αποτέλεσμα όλων των άλλων συναρτήσεων θα είναι NULL.
- Διαφωνίααθροιστική συνάρτηση δεν μπορεί να περιέχει συγκεντρωτικές συναρτήσεις(συνάρτηση από συνάρτηση). Εκείνοι. σε ένα ερώτημα είναι αδύνατο, ας πούμε, να ληφθεί το μέγιστο των μέσων τιμών.
- Το αποτέλεσμα της εκτέλεσης της συνάρτησης COUNT είναι ακέραιος αριθμός(ΑΚΕΡΑΙΟΣ ΑΡΙΘΜΟΣ). Άλλες συγκεντρωτικές συναρτήσεις κληρονομούν τους τύπους δεδομένων των τιμών που επεξεργάζονται.
- Εάν η συνάρτηση SUM παράγει ένα αποτέλεσμα που είναι μεγαλύτερο από τη μέγιστη τιμή του χρησιμοποιούμενου τύπου δεδομένων, λάθος.
Έτσι, εάν το αίτημα δεν περιέχει ΟΜΑΔΑ ΑΝΑ ρήτρες, Οτι αθροιστικές συναρτήσειςσυμπεριλαμβανεται σε ρήτρα SELECT, εκτελούνται σε όλες τις σειρές ερωτημάτων που προκύπτουν. Εάν το αίτημα περιέχει ΟΜΑΔΑ ΑΝΑ ρήτρα, κάθε σύνολο γραμμών που έχει τις ίδιες τιμές μιας στήλης ή μιας ομάδας στηλών που καθορίζονται σε ΟΜΑΔΑ ΑΝΑ ρήτρα, συγκροτεί μια ομάδα και αθροιστικές συναρτήσειςεκτελούνται για κάθε ομάδα ξεχωριστά.
ΕΧΕΙ προσφορά
Αν ρήτρα WHEREορίζει ένα κατηγόρημα για το φιλτράρισμα σειρών, στη συνέχεια ΕΧΕΙ προσφοράισχύει μετά την ομαδοποίησηγια να ορίσετε ένα παρόμοιο κατηγόρημα που φιλτράρει ομάδες κατά τιμές αθροιστικές συναρτήσεις. Αυτή η ρήτρα είναι απαραίτητη για την επικύρωση των τιμών που λαμβάνονται χρησιμοποιώντας αθροιστική συνάρτησηόχι από μεμονωμένες σειρές της πηγής εγγραφής που ορίζεται στο Ρήτρα ΑΠΟ, και από ομάδες τέτοιων γραμμών. Επομένως, ένας τέτοιος έλεγχος δεν μπορεί να περιληφθεί ρήτρα WHERE.
Η συνάρτηση SUM στη γλώσσα SQL, παρά την απλότητά της, χρησιμοποιείται αρκετά συχνά κατά την εργασία με μια βάση δεδομένων. Με τη βοήθειά του, είναι βολικό να αποκτήσετε κάποια ενδιάμεσα ή τελικά αποτελέσματα χωρίς να καταφύγετε στη βοήθεια βοηθητικών εργαλείων DBMS.
Σύνταξη συνάρτησης
Στην πλειοψηφία Γλώσσες SQLη σύνταξη του αθροίσματος είναι η ίδια - μόνο το όνομα του πεδίου ή κάποια αριθμητική πράξη πολλών από αυτά χρησιμοποιείται ως όρισμα, για το οποίο απαιτείται το άθροισμα.
Σε εξαιρετικές περιπτώσεις, είναι δυνατή η μετάδοση μιας συγκεκριμένης τιμής ως αριθμός ή μεταβλητή, αλλά τέτοια "σχήματα" πρακτικά δεν χρησιμοποιούνται, καθώς δεν έχουν μεγάλη αξία. Παρακάτω είναι η σύνταξη συνάρτησης στην SQL:
sum(a) - εδώ κάποια αριθμητική τιμή ή έκφραση χρησιμοποιείται ως παράμετρος a
Αξίζει να σημειωθεί ότι πριν από την παράμετρο μπορείτε να ορίσετε λέξεις-κλειδιά, για παράδειγμα, DISTINCT ή ALL, οι οποίες θα λαμβάνουν μόνο μοναδικές ή όλες τις τιμές, αντίστοιχα.
Παράδειγμα χρήσης SUM σε SQL
Για να κατανοήσετε πλήρως πώς λειτουργεί η συνάρτηση, αξίζει να εξετάσετε πολλά παραδείγματα. Στην SQL, το SUM μπορεί να χρησιμοποιηθεί τόσο ως αποτέλεσμα επιστροφής όσο και ως ενδιάμεση τιμή, για παράδειγμα, για τον έλεγχο μιας συνθήκης.
Για την πρώτη περίπτωση, εξετάστε την επιλογή όταν πρέπει να επιστρέψετε το ποσό των πωλήσεων για κάθε προϊόν, λαμβάνοντας υπόψη ότι ο αριθμός των αγορών που πραγματοποιήθηκαν μπορεί να είναι στον πληθυντικό. Για να λάβετε το αποτέλεσμα, αρκεί να εκτελέσετε το ακόλουθο ερώτημα:
SELECT Product, sum (PurchaseAmount) FROM Sales GroupBy Product;
Σε απόκριση αυτή την εντολήθα υπάρχει μια μοναδική λίστα προϊόντων με το συνολικό ποσό αγοράς για καθένα από αυτά.
Για το δεύτερο παράδειγμα, πρέπει να λάβετε μια λίστα προϊόντων των οποίων το ποσό πωλήσεων υπερβαίνει μια συγκεκριμένη τιμή, για παράδειγμα, 100. Μπορείτε να λάβετε το αποτέλεσμα για αυτήν την εργασία με διάφορους τρόπους, ο βέλτιστος από τους οποίους είναι να εκτελέσετε ένα αίτημα:
ΕΠΙΛΕΞΤΕ Προϊόν ΑΠΟ (ΕΠΙΛΟΓΗ Προϊόντος, άθροισμα (Ποσό Αγοράς) ως Ποσό ΑΠΟ Πωλήσεις) ΟΠΟΥ Άθροισμα > 100.
SQL - Μάθημα 11. Σύνολο συναρτήσεων, υπολογισμένες στήλες και προβολές
Οι συνολικές συναρτήσεις ονομάζονται επίσης συναρτήσεις στατιστικών, συγκεντρωτικών ή αθροιστικών. Αυτές οι συναρτήσεις επεξεργάζονται ένα σύνολο συμβολοσειρών για να μετρήσουν και να επιστρέψουν μια μεμονωμένη τιμή. Υπάρχουν μόνο πέντε τέτοιες λειτουργίες:- Η συνάρτηση AVG() επιστρέφει τη μέση τιμή μιας στήλης.
- Η συνάρτηση COUNT() επιστρέφει τον αριθμό των γραμμών σε μια στήλη.
- Η συνάρτηση MAX() επιστρέφει τη μεγαλύτερη τιμή σε μια στήλη.
- Η συνάρτηση MIN() επιστρέφει τη μικρότερη τιμή στη στήλη.
- SUM() Η συνάρτηση επιστρέφει το άθροισμα των τιμών της στήλης.
SELECT MIN(τιμή), MAX(τιμή), AVG(τιμή) FROM τιμές.
Τώρα, θέλουμε να μάθουμε πόσο μας έφερε τα εμπορεύματα από τον προμηθευτή "House of Printing" (id=2). Το να κάνεις ένα τέτοιο αίτημα δεν είναι τόσο εύκολο. Ας σκεφτούμε πώς να το συνθέσουμε:
1. Αρχικά, από τον πίνακα Προμήθειες (εισερχόμενα), επιλέξτε τα αναγνωριστικά (id_incoming) εκείνων των παραδόσεων που πραγματοποιήθηκαν από τον προμηθευτή "Εκτυπωτήριο" (id=2):
2. Τώρα από τον πίνακα Supply Journal (magazine_incoming) πρέπει να επιλέξετε τα εμπορεύματα (id_product) και τις ποσότητες τους (ποσότητα), που πραγματοποιήθηκαν στις παραδόσεις που βρέθηκαν στο σημείο 1. Δηλαδή, το ερώτημα από το σημείο 1 γίνεται ένθετο:
3. Τώρα πρέπει να προσθέσουμε στον πίνακα που προκύπτει τις τιμές για τα προϊόντα που βρέθηκαν, οι οποίες αποθηκεύονται στον πίνακα Τιμές. Δηλαδή, θα χρειαστεί να συμμετάσχουμε στους πίνακες Supply Magazine (magazine_incoming) και Prices χρησιμοποιώντας τη στήλη id_product:
4. Στον πίνακα που προκύπτει λείπει σαφώς η στήλη Amount, δηλαδή υπολογιζόμενη στήλη. Η δυνατότητα δημιουργίας τέτοιων στηλών παρέχεται στη MySQL. Για να το κάνετε αυτό, απλά πρέπει να καθορίσετε στο ερώτημα το όνομα της υπολογιζόμενης στήλης και τι πρέπει να υπολογίζει. Στο παράδειγμά μας, μια τέτοια στήλη θα ονομάζεται άθροισμα και θα υπολογίζει το γινόμενο των στηλών ποσότητας και τιμής. Το όνομα της νέας στήλης διαχωρίζεται με τη λέξη AS:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, Prices WHERE magazine_incoming.id_product= Prices.id_product AND id_SELECTorHIN_incoming;
5. Ωραία, το μόνο που έχουμε να κάνουμε είναι να προσθέσουμε τη στήλη άθροισης και τελικά να μάθουμε πόσο μας έφερε τα αγαθά ο προμηθευτής "House of Printing". Η σύνταξη για τη χρήση της συνάρτησης SUM() είναι η εξής:
SELECT SUM(column_name) FROM table_name;
Γνωρίζουμε το όνομα της στήλης - summa, αλλά δεν έχουμε το όνομα του πίνακα, αφού είναι αποτέλεσμα ερωτήματος. Τι να κάνω? Για τέτοιες περιπτώσεις, η MySQL έχει Προβολές. Μια προβολή είναι ένα ερώτημα επιλογής στο οποίο δίνεται ένα μοναδικό όνομα και μπορεί να αποθηκευτεί σε μια βάση δεδομένων για μελλοντική χρήση.
Η σύνταξη για τη δημιουργία μιας προβολής είναι η εξής:
CREATE VIEW view_name AS αίτημα
Ας αποθηκεύσουμε το αίτημά μας ως αναλυτική προβολή με το όνομα report_vendor:
ΔΗΜΙΟΥΡΓΙΑ ΠΡΟΒΟΛΗ report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, Prices.price, magazine_incoming.quantity*prices.price AS άθροισμα ΑΠΟ magazine_incoming, τιμές WHERE magazine_incoming.id_productincoming. ΕΔΩ id_vendor=2 )
6. Τώρα μπορείτε να χρησιμοποιήσετε την τελική συνάρτηση SUM():
SELECT SUM(summa) FROM report_vendor;
Έτσι πετύχαμε το αποτέλεσμα, αν και για αυτό έπρεπε να χρησιμοποιήσουμε ένθετα ερωτήματα, συνδέσεις, υπολογισμένες στήλες και προβολές. Ναι, μερικές φορές πρέπει να σκεφτείς για να έχεις αποτέλεσμα, χωρίς αυτό δεν μπορείς να φτάσεις πουθενά. Αλλά θίξαμε δύο πολύ σημαντικά θέματα - υπολογισμένες στήλες και προβολές. Ας μιλήσουμε για αυτούς με περισσότερες λεπτομέρειες.
Υπολογιζόμενα πεδία (στήλες)
Χρησιμοποιώντας ένα παράδειγμα, εξετάσαμε ένα μαθηματικό υπολογισμένο πεδίο σήμερα. Εδώ θα ήθελα να προσθέσω ότι μπορείτε να χρησιμοποιήσετε όχι μόνο την πράξη πολλαπλασιασμού (*), αλλά και την αφαίρεση (-), την πρόσθεση (+) και τη διαίρεση (/). Η σύνταξη έχει ως εξής:ΕΠΙΛΟΓΗ_όνομα_στήλης 1, όνομα_στήλης 2, όνομα_στήλης 1 * όνομα_στήλης 2 ΩΣ υπολογισμένο_όνομα_στήλης ΑΠΟ το όνομα_του πίνακα;
Η δεύτερη απόχρωση είναι η λέξη-κλειδί AS, τη χρησιμοποιήσαμε για να ορίσουμε το όνομα της υπολογιζόμενης στήλης. Στην πραγματικότητα, αυτή η λέξη-κλειδί χρησιμοποιείται για να ορίσει ψευδώνυμα για οποιεσδήποτε στήλες. Γιατί είναι απαραίτητο αυτό; Για μείωση κώδικα και αναγνωσιμότητα. Για παράδειγμα, η άποψή μας θα μπορούσε να μοιάζει με αυτό:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, τιμές AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM WHERE id_vendor=2);
Συμφωνήστε ότι αυτό είναι πολύ πιο σύντομο και σαφές.
Αναπαράσταση
Έχουμε ήδη εξετάσει τη σύνταξη για τη δημιουργία προβολών. Μόλις δημιουργηθούν προβολές, μπορούν να χρησιμοποιηθούν με τον ίδιο τρόπο όπως και οι πίνακες. Δηλαδή, εκτελέστε ερωτήματα εναντίον τους, φιλτράρετε και ταξινομήστε δεδομένα και συνδυάστε ορισμένες προβολές με άλλες. Από τη μία πλευρά, αυτός είναι ένας πολύ βολικός τρόπος αποθήκευσης που χρησιμοποιείται συχνά σύνθετα ερωτήματα(όπως στο παράδειγμά μας).Αλλά να θυμάστε ότι οι προβολές δεν είναι πίνακες, δηλαδή δεν αποθηκεύουν δεδομένα, αλλά τα ανακτούν μόνο από άλλους πίνακες. Ως εκ τούτου, πρώτον, όταν αλλάξουν τα δεδομένα στους πίνακες, θα αλλάξουν και τα αποτελέσματα της παρουσίασης. Και δεύτερον, όταν υποβάλλεται αίτημα σε μια προβολή, γίνεται αναζήτηση των απαιτούμενων δεδομένων, δηλαδή μειώνεται η απόδοση του DBMS. Επομένως, δεν πρέπει να τα καταχραστείτε.
Σε αυτό εγχειρίδιοθα μάθετε πώς να χρησιμοποιείτε Συνάρτηση SUM V SQL Server(Transact-SQL) με σύνταξη και παραδείγματα.
Περιγραφή
Σε SQL Server (Transact-SQL) Συνάρτηση SUMεπιστρέφει τη συνολική τιμή μιας έκφρασης.
Σύνταξη
Η σύνταξη για τη συνάρτηση SUM στον SQL Server (Transact-SQL) είναι:
Ή η σύνταξη για τη συνάρτηση SUM κατά την ομαδοποίηση των αποτελεσμάτων κατά μία ή περισσότερες στήλες είναι:
Παράμετροι ή επιχειρήματα
express1 , expression2 , ... express_n είναι εκφράσεις που δεν περιλαμβάνονται στη συνάρτηση SUM και πρέπει να συμπεριληφθούν στον όρο GROUP BY στο τέλος της πρότασης SQL.
aggregate_expression είναι η στήλη ή η έκφραση που θα συγκεντρωθεί.
πίνακες - πίνακες από τους οποίους θέλετε να λάβετε εγγραφές. Πρέπει να υπάρχει τουλάχιστον ένας πίνακας στη λίστα FROM.
Όπου προϋποθέσεις - προαιρετικό. Αυτές είναι οι προϋποθέσεις που πρέπει να πληρούνται για τις επιλεγμένες εγγραφές.
Εφαρμογή
Η συνάρτηση SUM μπορεί να χρησιμοποιηθεί στις ακόλουθες εκδόσεις του SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Παράδειγμα με ένα πεδίο
Ας δούμε μερικά παραδείγματα συναρτήσεων SUM Server για να κατανοήσουμε πώς να χρησιμοποιήσετε τη συνάρτηση SUM στον SQL Server (Transact-SQL).
Για παράδειγμα, μπορείτε να μάθετε τη συνολική ποσότητα όλων των προϊόντων των οποίων η ποσότητα είναι μεγαλύτερη από 10.
Σε αυτό το παράδειγμα της συνάρτησης SUM, έχουμε ονομάσει την έκφραση SUM(quantity) "Total Quantity". Όταν επιστρέφετε ένα σύνολο αποτελεσμάτων - ως όνομα πεδίου θα εμφανίζεται η "Συνολική Ποσότητα".
Παράδειγμα χρήσης DISTINCT
Μπορείτε να χρησιμοποιήσετε τον τελεστή DISTINCT στη συνάρτηση SUM. Για παράδειγμα το παρακάτω Δήλωση SQLεπιστρέφει το συνολικό μισθό με μοναδικές αξίες μισθού όπου ο μισθός είναι κάτω από $29.000 ετησίως.
Εάν οι δύο μισθοί ήταν 24.000 $ ετησίως, μόνο μία από αυτές τις τιμές θα χρησιμοποιηθεί στη συνάρτηση SUM.
Παράδειγμα χρήσης του τύπου
Η έκφραση που περιέχεται στη συνάρτηση SUM δεν χρειάζεται να είναι ένα μόνο πεδίο. Μπορείτε επίσης να χρησιμοποιήσετε τον τύπο. Για παράδειγμα, μπορείτε να υπολογίσετε τη συνολική προμήθεια.
Transact-SQL
ΕΠΙΛΕΞΤΕ ΠΟΣΟ (πωλήσεις * 0,03) ΩΣ "Συνολική προμήθεια" ΑΠΟ παραγγελίες.
SELECT SUM (πωλήσεις * 0,03 ) ΩΣ "Συνολική προμήθεια" ΑΠΟ παραγγελίες? |
Παράδειγμα χρήσης GROUP BY
Σε ορισμένες περιπτώσεις, θα χρειαστεί να χρησιμοποιήσετε τον τελεστή GROUP BY με τη συνάρτηση SUM.
Ας μάθουμε να συνοψίζουμε. Όχι, αυτά δεν είναι τα αποτελέσματα της μελέτης της SQL, αλλά τα αποτελέσματα των τιμών των στηλών των πινάκων της βάσης δεδομένων. Οι συγκεντρωτικές συναρτήσεις SQL λειτουργούν στις τιμές μιας στήλης για να παράγουν μια ενιαία τιμή που προκύπτει. Οι πιο συχνά χρησιμοποιούμενες συναρτήσεις συγκεντρωτικών SQL είναι SUM, MIN, MAX, AVG και COUNT. Είναι απαραίτητο να γίνει διάκριση μεταξύ δύο περιπτώσεων χρήσης συγκεντρωτικών συναρτήσεων. Πρώτον, οι αθροιστικές συναρτήσεις χρησιμοποιούνται μόνες τους και επιστρέφουν μία μόνο τιμή που προκύπτει. Δεύτερον, οι συγκεντρωτικές συναρτήσεις χρησιμοποιούνται με τον όρο SQL GROUP BY, δηλαδή ομαδοποίηση κατά πεδία (στήλες) για να ληφθούν οι προκύπτουσες τιμές σε κάθε ομάδα. Ας εξετάσουμε πρώτα περιπτώσεις χρήσης συγκεντρωτικών συναρτήσεων χωρίς ομαδοποίηση.
Λειτουργία SQL SUM
Η συνάρτηση SQL SUM επιστρέφει το άθροισμα των τιμών σε μια στήλη πίνακα βάσης δεδομένων. Μπορεί να εφαρμοστεί μόνο σε στήλες των οποίων οι τιμές είναι αριθμοί. Ερωτήματα SQLγια να πάρετε το άθροισμα που προκύπτει ξεκινήστε ως εξής:
ΕΠΙΛΕΞΤΕ ΣΥΝΟΛΟ (COLUMN_NAME) ...
Αυτή η έκφραση ακολουθείται από FROM (TABLE_NAME) και στη συνέχεια μπορεί να καθοριστεί μια συνθήκη χρησιμοποιώντας τον όρο WHERE. Επιπλέον, το όνομα της στήλης μπορεί να προηγείται από DISTINCT, που σημαίνει μόνο αυτό μοναδικές αξίες. Από προεπιλογή, λαμβάνονται υπόψη όλες οι τιμές (για αυτό μπορείτε να καθορίσετε συγκεκριμένα όχι DISTINCT, αλλά ALL, αλλά δεν απαιτείται η λέξη ALL).
Εάν θέλετε να εκτελέσετε ερωτήματα βάσης δεδομένων από αυτό το μάθημα στον MS SQL Server, αλλά αυτό το DBMS δεν είναι εγκατεστημένο στον υπολογιστή σας, τότε μπορείτε να το εγκαταστήσετε χρησιμοποιώντας τις οδηγίες σε αυτόν τον σύνδεσμο .
Πρώτα θα εργαστούμε με τη βάση δεδομένων της εταιρείας - Company1. Το σενάριο για τη δημιουργία αυτής της βάσης δεδομένων, των πινάκων της και της πλήρωσης των πινάκων με δεδομένα βρίσκεται στο αρχείο σε αυτόν τον σύνδεσμο .
Παράδειγμα 1.Υπάρχει μια βάση δεδομένων της εταιρείας με δεδομένα για τα τμήματα και τους υπαλλήλους της. Ο πίνακας Προσωπικού έχει επίσης μια στήλη με στοιχεία για τους μισθούς των εργαζομένων. Η επιλογή από τον πίνακα μοιάζει με αυτό (για να μεγεθύνετε την εικόνα, κάντε κλικ πάνω της με το αριστερό κουμπί του ποντικιού):
Για να λάβουμε το άθροισμα όλων των μισθών, χρησιμοποιούμε το ακόλουθο ερώτημα (στον MS SQL Server - με την προηγούμενη κατασκευή USE company1;):
ΕΠΙΛΕΞΤΕ ΠΟΣΟ (Μισθός) ΑΠΟ Προσωπικό
Αυτό το ερώτημα θα επιστρέψει την τιμή 287664,63.
Και τώρα . Στις ασκήσεις αρχίζουμε ήδη να περιπλέκουμε τις εργασίες, φέρνοντάς τις πιο κοντά σε αυτές που συναντάμε στην πράξη.
Λειτουργία SQL MIN
Η συνάρτηση SQL MIN λειτουργεί επίσης σε στήλες των οποίων οι τιμές είναι αριθμοί και επιστρέφει το ελάχιστο όλων των τιμών στη στήλη. Αυτή η συνάρτηση έχει σύνταξη παρόμοια με αυτή της συνάρτησης SUM.
Παράδειγμα 3.Η βάση δεδομένων και ο πίνακας είναι τα ίδια όπως στο παράδειγμα 1.
Πρέπει να γνωρίζετε το ελάχιστο μισθοίυπάλληλοι του τμήματος με αριθμό 42. Για να το κάνετε αυτό, γράψτε το ακόλουθο ερώτημα (στον MS SQL Server - με την προηγούμενη κατασκευή USE company1;):
Το ερώτημα θα επιστρέψει την τιμή 10505,90.
Και ξανα άσκηση για ανεξάρτητη απόφαση . Σε αυτήν και σε ορισμένες άλλες ασκήσεις, θα χρειαστείτε όχι μόνο τον πίνακα Staff, αλλά και τον πίνακα Org, που περιέχει δεδομένα σχετικά με τα τμήματα της εταιρείας:
Παράδειγμα 4.Ο πίνακας Org προστίθεται στον πίνακα Staff, που περιέχει δεδομένα για τα τμήματα της εταιρείας. Εκτυπώστε τον ελάχιστο αριθμό ετών που εργάστηκε από έναν υπάλληλο σε ένα τμήμα που βρίσκεται στη Βοστώνη.
Λειτουργία SQL MAX
Η συνάρτηση SQL MAX λειτουργεί παρόμοια και έχει παρόμοια σύνταξη, η οποία χρησιμοποιείται όταν πρέπει να προσδιορίσετε τη μέγιστη τιμή μεταξύ όλων των τιμών σε μια στήλη.
Παράδειγμα 5.
Πρέπει να μάθουμε τον μέγιστο μισθό των υπαλλήλων στον αριθμό τμήματος 42. Για να το κάνετε αυτό, γράψτε το ακόλουθο ερώτημα (στον MS SQL Server - με το πρόθεμα USE company1;):
Το ερώτημα θα επιστρέψει την τιμή 18352,80
Είναι ώρα ασκήσεις για ανεξάρτητη λύση.
Παράδειγμα 6.Δουλεύουμε και πάλι με δύο τραπέζια - Προσωπικό και Οργαν. Εμφανίστε το όνομα του τμήματος και τη μέγιστη αξία της προμήθειας που έλαβε ένας υπάλληλος στο τμήμα που ανήκει στην ομάδα τμημάτων (Division) Eastern. Χρήση JOIN (σύνδεση τραπεζιών) .
Λειτουργία SQL AVG
Αυτό που αναφέρεται σχετικά με τη σύνταξη για τις προηγούμενες συναρτήσεις που περιγράφηκαν ισχύουν και για τη συνάρτηση SQL AVG. Αυτή η συνάρτηση επιστρέφει τον μέσο όρο όλων των τιμών σε μια στήλη.
Παράδειγμα 7.Η βάση δεδομένων και ο πίνακας είναι τα ίδια όπως στα προηγούμενα παραδείγματα.
Ας υποθέσουμε ότι θέλετε να μάθετε τη μέση προϋπηρεσία των υπαλλήλων στο τμήμα 42. Για να το κάνετε αυτό, γράψτε το ακόλουθο ερώτημα (στον MS SQL Server - με την προηγούμενη κατασκευή USE company1;):
Το αποτέλεσμα θα είναι 6,33
Παράδειγμα 8.Δουλεύουμε με ένα τραπέζι - Προσωπικό. Εμφάνιση του μέσου μισθού των εργαζομένων με 4 έως 6 χρόνια εμπειρίας.
Λειτουργία SQL COUNT
Η συνάρτηση SQL COUNT επιστρέφει τον αριθμό των εγγραφών σε έναν πίνακα βάσης δεδομένων. Εάν καθορίσετε SELECT COUNT(COLUMN_NAME) ... στο ερώτημα, το αποτέλεσμα θα είναι ο αριθμός των εγγραφών χωρίς να ληφθούν υπόψη εκείνες οι εγγραφές στις οποίες η τιμή της στήλης είναι NULL (μη καθορισμένη). Εάν χρησιμοποιήσετε έναν αστερίσκο ως όρισμα και ξεκινήσετε ένα ερώτημα SELECT COUNT(*) ..., το αποτέλεσμα θα είναι ο αριθμός όλων των εγγραφών (σειρών) του πίνακα.
Παράδειγμα 9.Η βάση δεδομένων και ο πίνακας είναι τα ίδια όπως στα προηγούμενα παραδείγματα.
Θέλετε να μάθετε τον αριθμό όλων των υπαλλήλων που λαμβάνουν προμήθειες. Ο αριθμός των υπαλλήλων των οποίων οι τιμές στηλών Comm δεν είναι NULL θα επιστραφεί με το ακόλουθο ερώτημα (στον MS SQL Server - με το πρόθεμα USE company1;):
SELECT COUNT (Comm) FROM Staff
Το αποτέλεσμα θα είναι 11.
Παράδειγμα 10.Η βάση δεδομένων και ο πίνακας είναι τα ίδια όπως στα προηγούμενα παραδείγματα.
Εάν θέλετε να μάθετε τον συνολικό αριθμό των εγγραφών στον πίνακα, χρησιμοποιήστε ένα ερώτημα με έναν αστερίσκο ως όρισμα στη συνάρτηση COUNT (στον MS SQL Server - με την προηγούμενη κατασκευή USE company1;):
ΕΠΙΛΕΞΤΕ ΑΡΙΘΜΟ (*) ΑΠΟ Προσωπικό
Το αποτέλεσμα θα είναι 17.
Στην επόμενη άσκηση για ανεξάρτητη λύσηθα χρειαστεί να χρησιμοποιήσετε ένα δευτερεύον ερώτημα.
Παράδειγμα 11.Δουλεύουμε με ένα τραπέζι - Προσωπικό. Εμφάνιση του αριθμού των εργαζομένων στο τμήμα προγραμματισμού (Plains).
Συγκεντρωτικές συναρτήσεις με SQL GROUP BY
Ας δούμε τώρα τη χρήση συναρτήσεων συγκεντρωτικών συναρτήσεων μαζί με την πρόταση SQL GROUP BY. Η πρόταση SQL GROUP BY χρησιμοποιείται για την ομαδοποίηση των τιμών των αποτελεσμάτων κατά στήλες σε έναν πίνακα βάσης δεδομένων. Η ιστοσελίδα έχει ένα μάθημα αφιερωμένο ξεχωριστά σε αυτόν τον χειριστή .
Θα εργαστούμε με τη βάση δεδομένων "Ads Portal 1". Το σενάριο για τη δημιουργία αυτής της βάσης δεδομένων, τον πίνακά της και τη συμπλήρωση του πίνακα δεδομένων βρίσκεται στο αρχείο σε αυτόν τον σύνδεσμο .
Παράδειγμα 12.Έτσι, υπάρχει μια βάση δεδομένων της πύλης διαφημίσεων. Διαθέτει έναν πίνακα διαφημίσεων που περιέχει δεδομένα σχετικά με τις διαφημίσεις που υποβλήθηκαν για την εβδομάδα. Η στήλη Κατηγορία περιέχει δεδομένα σχετικά με μεγάλες κατηγορίες διαφημίσεων (για παράδειγμα, Real Estate) και η στήλη Ανταλλακτικά περιέχει δεδομένα σχετικά με μικρότερα τμήματα που περιλαμβάνονται στις κατηγορίες (για παράδειγμα, τα τμήματα Διαμερίσματα και εξοχικές κατοικίες είναι τμήματα της κατηγορίας Real Estate). Η στήλη Μονάδες περιέχει δεδομένα για τον αριθμό των διαφημίσεων που υποβλήθηκαν και η στήλη Χρήματα περιέχει δεδομένα σχετικά με το χρηματικό ποσό που ελήφθη για την υποβολή διαφημίσεων.
| Κατηγορία | Μέρος | Μονάδες | Χρήματα |
| Μεταφορά | Αυτοκίνητα | 110 | 17600 |
| Ακίνητα | Διαμερίσματα | 89 | 18690 |
| Ακίνητα | Ντάχας | 57 | 11970 |
| Μεταφορά | Μοτοσικλέτες | 131 | 20960 |
| ΚΑΤΑΣΚΕΥΑΣΤΙΚΑ ΥΛΙΚΑ | σανίδες | 68 | 7140 |
| Ηλεκτρολόγος Μηχανικός | τηλεοράσεις | 127 | 8255 |
| Ηλεκτρολόγος Μηχανικός | Ψυγεία | 137 | 8905 |
| ΚΑΤΑΣΚΕΥΑΣΤΙΚΑ ΥΛΙΚΑ | Regips | 112 | 11760 |
| Ελεύθερος χρόνος | Βιβλία | 96 | 6240 |
| Ακίνητα | Στο σπίτι | 47 | 9870 |
| Ελεύθερος χρόνος | ΜΟΥΣΙΚΗ | 117 | 7605 |
| Ελεύθερος χρόνος | Παιχνίδια | 41 | 2665 |
Χρησιμοποιώντας τη δήλωση SQL GROUP BY, βρείτε το χρηματικό ποσό που κερδίσατε αναρτώντας διαφημίσεις σε κάθε κατηγορία. Γράφουμε το ακόλουθο ερώτημα (στον MS SQL Server - με την προηγούμενη κατασκευή USE adportal1;):
ΕΠΙΛΕΞΤΕ Κατηγορία, ΠΟΣΟ (Χρήματα) ΩΣ ΧΡΗΜΑΤΑ ΑΠΟ ΔΙΑΦΗΜΙΣΕΙΣ ΟΜΑΔΑ ΑΝΑ Κατηγορία
Παράδειγμα 13.Η βάση δεδομένων και ο πίνακας είναι τα ίδια όπως στο προηγούμενο παράδειγμα.
Χρησιμοποιώντας τη δήλωση SQL GROUP BY, μάθετε ποιο τμήμα κάθε κατηγορίας είχε τις περισσότερες καταχωρίσεις. Γράφουμε το ακόλουθο ερώτημα (στον MS SQL Server - με την προηγούμενη κατασκευή USE adportal1;):
ΕΠΙΛΕΞΤΕ Κατηγορία, Μέρος, ΜΕΓΙΣΤΟ (Μονάδες) AS Μέγιστο ΑΠΟ ΟΜΑΔΑ ΔΙΑΦΗΜΙΣΕΩΝ ΑΝΑ Κατηγορία
Το αποτέλεσμα θα είναι ο παρακάτω πίνακας:
Οι συνολικές και μεμονωμένες τιμές μπορούν να ληφθούν σε έναν πίνακα συνδυάζοντας αποτελέσματα ερωτημάτων χρησιμοποιώντας τον τελεστή UNION .
Σχεσιακές βάσεις δεδομένων και γλώσσα SQL
