Συγκεντρωτικές συναρτήσεις σε MySQL
Οι συγκεντρωτικές συναρτήσεις αφορούν
- Εκτέλεση υπολογισμών σε πολλές σειρές
- Μιας στήλης ενός πίνακα
- Και επιστρέφοντας μια ενιαία τιμή.
Το πρότυπο ISO ορίζει πέντε (5) συγκεντρωτικές συναρτήσεις και συγκεκριμένα.
1) ΜΕΤΡΗΣΤΕ
2) ΣΥΝΟΨΗ
3) AVG
4) ΛΕΠ
5) ΜΕΓ
Γιατί να χρησιμοποιήσετε συγκεντρωτικές συναρτήσεις
Από επιχειρηματική σκοπιά, διαφορετικά επίπεδα οργάνωσης έχουν διαφορετικές απαιτήσεις πληροφοριών. Οι διευθυντές ανώτατων επιπέδων συνήθως ενδιαφέρονται να γνωρίζουν ολόκληρα στοιχεία και όχι απαραίτητα τις μεμονωμένες λεπτομέρειες.
Οι συγκεντρωτικές συναρτήσεις μας επιτρέπουν να παράγουμε εύκολα συνοπτικά δεδομένα από τη βάση δεδομένων μας.
Για παράδειγμα, από τη βάση δεδομένων myflix , η διαχείριση μπορεί να απαιτεί τις ακόλουθες αναφορές
- Ταινίες με λιγότερα νοικιασμένα.
- Οι περισσότερες ενοικιαζόμενες ταινίες.
- Ο μέσος αριθμός που νοικιάζει κάθε ταινία σε ένα μήνα.
Παράγουμε εύκολα τις παραπάνω αναφορές χρησιμοποιώντας συγκεντρωτικές συναρτήσεις.
Ας δούμε αναλυτικά τις συναρτήσεις συγκεντρωτικών στοιχείων.
COUNT συνάρτηση
Η συνάρτηση COUNT επιστρέφει τον συνολικό αριθμό τιμών στο καθορισμένο πεδίο. Λειτουργεί τόσο σε αριθμούς όσο και σε μη αριθμητικούς τύπους δεδομένων. Όλες οι συγκεντρωτικές συναρτήσεις εξ ορισμού αποκλείουν μηδενικές τιμές πριν από την επεξεργασία των δεδομένων.
Το COUNT (*) είναι μια ειδική υλοποίηση της συνάρτησης COUNT που επιστρέφει τον αριθμό όλων των σειρών σε έναν καθορισμένο πίνακα. COUNT (*) εξετάζει επίσης Μηδενικά και διπλότυπα.
Ο παρακάτω πίνακας δείχνει δεδομένα στον πίνακα ενοικιάσεων ταινιών
αριθμός αναφοράς | Ημερομηνία Συναλλαγής | ημερομηνία επιστροφής | αριθμός μέλους | movie_id | ταινία_ επέστρεψε |
---|---|---|---|---|---|
11 | 20-06-2012 | Τιμή NULL | 1 | 1 | 0 |
12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 |
13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 |
14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 |
15 | 23-06-2012 | Τιμή NULL | 3 | 3 | 0 |
Ας υποθέσουμε ότι θέλουμε να πάρουμε πόσες φορές έχει νοικιαστεί η ταινία με id 2
SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;
Εκτέλεση του παραπάνω ερωτήματος στο MySQL πάγκος εργασίας ενάντια στο myflixdb μας δίνει τα ακόλουθα αποτελέσματα.
COUNT('movie_id') |
---|
3 |
DISTINCT Λέξη-κλειδί
Η λέξη-κλειδί DISTINCT που μας επιτρέπει να παραλείπουμε διπλότυπα από τα αποτελέσματά μας. Αυτό επιτυγχάνεται με την ομαδοποίηση παρόμοιων τιμών μαζί.
Για να κατανοήσουμε την έννοια του Distinct, ας εκτελέσουμε ένα απλό ερώτημα
SELECT `movie_id` FROM `movierentals`;
movie_id |
---|
1 |
2 |
2 |
2 |
3 |
Τώρα ας εκτελέσουμε το ίδιο ερώτημα με τη διακριτή λέξη-κλειδί-
SELECT DISTINCT `movie_id` FROM `movierentals`;
Όπως φαίνεται παρακάτω, το Distinct παραλείπει διπλές εγγραφές από τα αποτελέσματα.
movie_id |
---|
1 |
2 |
3 |
MIN
Η λειτουργία MIN επιστρέφει τη μικρότερη τιμή στο καθορισμένο πεδίο πίνακα.
Για παράδειγμα, ας υποθέσουμε ότι θέλουμε να μάθουμε το έτος κατά το οποίο κυκλοφόρησε η παλαιότερη ταινία στη βιβλιοθήκη μας, μπορούμε να χρησιμοποιήσουμε MySQLΛειτουργία MIN για να λάβετε τις επιθυμητές πληροφορίες.
Το παρακάτω ερώτημα μας βοηθά να το πετύχουμε
SELECT MIN(`year_released`) FROM `movies`;
Εκτέλεση του παραπάνω ερωτήματος στο MySQL Ο πάγκος εργασίας έναντι του myflixdb μας δίνει τα ακόλουθα αποτελέσματα.
MIN('year_released') |
---|
2005 |
MAX
Όπως υποδηλώνει το όνομα, η συνάρτηση MAX είναι το αντίθετο της συνάρτησης MIN. Το επιστρέφει τη μεγαλύτερη τιμή από το καθορισμένο πεδίο πίνακα.
Ας υποθέσουμε ότι θέλουμε να έχουμε τη χρονιά που κυκλοφόρησε η τελευταία ταινία στη βάση δεδομένων μας. Μπορούμε εύκολα να χρησιμοποιήσουμε τη συνάρτηση MAX για να το πετύχουμε αυτό.
Το παρακάτω παράδειγμα επιστρέφει την τελευταία κινηματογραφική χρονιά που κυκλοφόρησε.
SELECT MAX(`year_released`) FROM `movies`;
Εκτέλεση του παραπάνω ερωτήματος στο MySQL Ο πάγκος εργασίας χρησιμοποιώντας το myflixdb μας δίνει τα ακόλουθα αποτελέσματα.
MAX('year_released') |
---|
2012 |
SUM λειτουργία
Ας υποθέσουμε ότι θέλουμε μια αναφορά που δίνει το συνολικό ποσό των πληρωμών που έχουν πραγματοποιηθεί μέχρι στιγμής. Μπορούμε να χρησιμοποιήσουμε το MySQL ΑΘΡΟΙΣΜΑ λειτουργία η οποία επιστρέφει το άθροισμα όλων των τιμών στην καθορισμένη στήλη. Το SUM λειτουργεί μόνο σε αριθμητικά πεδία. Οι μηδενικές τιμές εξαιρούνται από το αποτέλεσμα που επιστρέφεται.
Ο παρακάτω πίνακας δείχνει τα δεδομένα στον πίνακα πληρωμών-
πληρωμή_ id | αριθμός μέλους | ημερομηνία πληρωμής | περιγραφή | ποσό που καταβάλλεται | εξωτερικό_αριθμός αναφοράς |
---|---|---|---|---|---|
1 | 1 | 23-07-2012 | Πληρωμή ενοικίασης ταινίας | 2500 | 11 |
2 | 1 | 25-07-2012 | Πληρωμή ενοικίασης ταινίας | 2000 | 12 |
3 | 3 | 30-07-2012 | Πληρωμή ενοικίασης ταινίας | 6000 | Τιμή NULL |
Το ερώτημα που εμφανίζεται παρακάτω λαμβάνει όλες τις πληρωμές που πραγματοποιήθηκαν και τις συνοψίζει για να επιστρέψει ένα μόνο αποτέλεσμα.
SELECT SUM(`amount_paid`) FROM `payments`;
Εκτέλεση του παραπάνω ερωτήματος στο MySQL Ο πάγκος εργασίας έναντι του myflixdb δίνει τα ακόλουθα αποτελέσματα.
SUM('amount_paid') |
---|
10500 |
AVG λειτουργία
MySQL AVG λειτουργία επιστρέφει τον μέσο όρο των τιμών σε μια καθορισμένη στήλη. Ακριβώς όπως η συνάρτηση SUM, είναι λειτουργεί μόνο σε τύπους αριθμητικών δεδομένων.
Ας υποθέσουμε ότι θέλουμε να βρούμε το μέσο ποσό που καταβλήθηκε. Μπορούμε να χρησιμοποιήσουμε το ακόλουθο ερώτημα -
SELECT AVG(`amount_paid`) FROM `payments`;
Εκτέλεση του παραπάνω ερωτήματος στο MySQL πάγκος εργασίας, μας δίνει τα ακόλουθα αποτελέσματα.
AVG('amount_paid') |
---|
3500 |
Σπαζοκεφαλιά
Νομίζετε ότι οι αθροιστικές συναρτήσεις είναι εύκολες. Δοκιμάστε αυτό!
Το ακόλουθο παράδειγμα ομαδοποιεί τα μέλη ονομαστικά, μετράει τον συνολικό αριθμό πληρωμών, το μέσο ποσό πληρωμής και το συνολικό σύνολο των ποσών πληρωμής.
SELECT m.`full_names`,COUNT(p.`payment_id`) AS `paymentscount`,AVG(p.`amount_paid`) AS `averagepaymentamount`,SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;
Εκτελώντας το παραπάνω παράδειγμα στο MySQL Ο πάγκος εργασίας μας δίνει τα ακόλουθα αποτελέσματα.
Σύνοψη
- MySQL υποστηρίζει και τις πέντε (5) αθροιστικές συναρτήσεις προτύπων ISO COUNT, SUM, AVG, MIN και MAX.
- SUM και AVG οι λειτουργίες λειτουργούν μόνο σε αριθμητικά δεδομένα.
- Εάν θέλετε να εξαιρέσετε διπλότυπες τιμές από τα αποτελέσματα συγκεντρωτικών συναρτήσεων, χρησιμοποιήστε τη λέξη-κλειδί DISTINCT. Η λέξη-κλειδί ALL περιλαμβάνει ακόμη και διπλότυπα. Εάν δεν έχει καθοριστεί τίποτα, το ALL θεωρείται ως προεπιλογή.
- Οι συγκεντρωτικές συναρτήσεις μπορούν να χρησιμοποιηθούν σε συνδυασμό με άλλες ρήτρες SQL όπως π.χ GROUP BY