MySQL Συναρτήσεις συνάθροισης: SUM, COUNT, AVG & ΜΕΓΙΣΤΟ

Τι είναι οι συναρτήσεις συνάθροισης στο MySQL?

An αθροιστική συνάρτηση διαβάζει πολλές γραμμές μιας μόνο στήλης και τις συμπτύσσει σε μία τιμή. Οι συναρτήσεις συγκέντρωσης αφορούν:

• Εκτέλεση υπολογισμών σε πολλαπλές γραμμές
• Μιας στήλης ενός πίνακα
• Και επιστρέφοντας μια ενιαία τιμή.

Το πρότυπο ISO ορίζει πέντε (5) συναρτήσεις αθροίσματος, δηλαδή:

1. COUNT
2. ΑΘΡΟΙΣΜΑ
3. AVG
4. MIN
5. MAX

Ένας κανόνας ισχύει και για τα πέντε: Οι συναρτήσεις συγκέντρωσης αγνοούν τις τιμές NULLΗ COUNT(*) είναι η μοναδική εξαίρεση και θα εξετάσουμε το γιατί παρακάτω.

Γιατί να χρησιμοποιήσετε συγκεντρωτικές συναρτήσεις

Τα διαφορετικά επίπεδα οργάνωσης έχουν διαφορετικές απαιτήσεις πληροφόρησης. Τα ανώτερα στελέχη συνήθως ενδιαφέρονται για ολόκληρα στοιχεία και όχι για μεμονωμένες λεπτομέρειες.

Οι συγκεντρωτικές συναρτήσεις μας επιτρέπουν να παράγουμε εύκολα συνοπτικά δεδομένα από τη βάση δεδομένων μας.

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

• Ταινίες με λιγότερα νοικιασμένα.
• Οι περισσότερες ενοικιαζόμενες ταινίες.
• Ο μέσος αριθμός φορών που κάθε ταινία ενοικιάζεται σε έναν μήνα.

Όλες οι παραπάνω αναφορές προέρχονται από συναρτήσεις συγκεντρωτικών αποτελεσμάτων. Ας εξετάσουμε την καθεμία λεπτομερώς.

COUNT συνάρτηση

Η συνάρτηση COUNT επιστρέφει τον συνολικό αριθμό τιμών στο καθορισμένο πεδίο, τόσο σε αριθμητικούς όσο και σε μη αριθμητικούς τύπους δεδομένων. Όπως κάθε συνάρτηση συγκεντρωτικών αποτελεσμάτων, η συνάρτηση COUNT(στήλη) εξαιρεί τις τιμές NULL.

Η COUNT(*) είναι μια ειδική φόρμα που επιστρέφει τον αριθμό όλων των γραμμών σε έναν πίνακα. Επίσης, μετράει NULL και διπλότυπα, επειδή μετράει γραμμές και όχι τιμές.

Ο πίνακας movierentals περιέχει τα εξής δεδομένα:

Ας υποθέσουμε ότι θέλουμε να βρούμε τον αριθμό των φορών που η ταινία με αναγνωριστικό 2 έχει ενοικιαστεί.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Εκτελώντας αυτό σε MySQL Πάγκος εργασίας Η συνάρτηση against myflixdb επιστρέφει 3, επειδή τρεις γραμμές φέρουν το movie_id 2.

DISTINCT Λέξη-κλειδί

Η COUNT απαντά «πόσα». Η επόμενη ερώτηση είναι συνήθως «πόσα» διαφορετικές αυτά», και γι' αυτό υπάρχει το DISTINCT.

Η λέξη-κλειδί DISTINCT παραλείπει διπλότυπα από τα αποτελέσματά μας ανά ομάδαping ίδιες τιμές μαζί, ακριβώς όπως υποδηλώνει η παραπάνω εικόνα.

Αρχικά, ας εκτελέσουμε ένα απλό ερώτημα.

SELECT `movie_id` FROM `movierentals`;

Τώρα το ίδιο ερώτημα με τη λέξη-κλειδί DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Η συνάρτηση DISTINCT παραλείπει τις διπλότυπες εγγραφές:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Ποιο πρέπει να χρησιμοποιήσετε;

Μπορεί επίσης να τοποθετηθεί DISTINCT μέσα μια συγκεντρωτική συνάρτηση, και εδώ είναι που οι περισσότεροι αρχάριοι χάνουν track από τις οποίες οι γραμμές καταμετρώνται στην πραγματικότητα. Οι τέσσερις παρακάτω φόρμες εκτελούνται όλες στον ίδιο πίνακα πέντε γραμμών movierentals που παρουσιάστηκε νωρίτερα, ωστόσο δεν επιστρέφουν όλες τον ίδιο αριθμό. Η διαφορά καταλήγει σε δύο ερωτήματα: καταμετρά η φόρμα γραμμές ή τιμές και διατηρεί διπλότυπα;

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Συμβουλές: Χρησιμοποιήστε COUNT(*) για τον αριθμό γραμμών, COUNT(στήλη) όταν ένα NULL σημαίνει "δεν ισχύει" και COUNT(στήλη DISTINCT) για μοναδικές τιμές. Το αντίθετο του DISTINCT είναι το ALL — η προεπιλεγμένη τιμή και επομένως σπάνια γράφεται.

MIN

Η λειτουργία MIN επιστρέφει τη μικρότερη τιμή στο καθορισμένο πεδίο πίνακα.

Ας υποθέσουμε ότι θέλουμε το έτος κυκλοφορίας της παλαιότερης ταινίας στη βιβλιοθήκη μας. MySQLΗ συνάρτηση MIN μας το δίνει αυτό.

SELECT MIN(`year_released`) FROM `movies`;

Αποτέλεσμα:

MAX

Όπως υποδηλώνει το όνομα, η συνάρτηση MAX είναι το αντίθετο της συνάρτησης MIN. Το επιστρέφει τη μεγαλύτερη τιμή από το καθορισμένο πεδίο πίνακα.

Ας υποθέσουμε ότι θέλουμε το έτος κυκλοφορίας της τελευταίας ταινίας στη βάση δεδομένων μας. Το ακόλουθο παράδειγμα την επιστρέφει.

SELECT MAX(`year_released`) FROM `movies`;

Αποτέλεσμα:

SUM λειτουργία

Τα MIN και MAX επιλέγουν μια υπάρχουσα τιμή από μια στήλη. Τα SUM και AVG Υπολογίστε έναν νέο αριθμό από ολόκληρη τη στήλη.

Ας υποθέσουμε ότι θέλουμε το συνολικό ποσό των πληρωμών που έχουν πραγματοποιηθεί μέχρι στιγμής. MySQL ΑΘΡΟΙΣΜΑ λειτουργία επιστρέφει το άθροισμα όλων των τιμών στην καθορισμένη στήλη. Το SUM λειτουργεί μόνο σε αριθμητικά πεδίακαι Οι τιμές NULL εξαιρούνται από το αποτέλεσμα.

Ο παρακάτω πίνακας δείχνει τα δεδομένα στον πίνακα πληρωμών.

Το ερώτημα που εμφανίζεται παρακάτω λαμβάνει όλες τις πληρωμές που πραγματοποιήθηκαν και τις συνοψίζει σε ένα μόνο αποτέλεσμα: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Αποτέλεσμα:

AVG λειτουργία

The MySQL AVG λειτουργία επιστρέφει τον μέσο όρο των τιμών σε μια καθορισμένη στήλη. Ακριβώς όπως η συνάρτηση SUM, είναι λειτουργεί μόνο σε τύπους αριθμητικών δεδομένων.

Ας υποθέσουμε ότι θέλουμε να βρούμε το μέσο ποσό που καταβλήθηκε. Μπορούμε να χρησιμοποιήσουμε το ακόλουθο ερώτημα, το οποίο διαιρεί το σύνολο των 10500 με τις τρεις γραμμές πληρωμής που δεν είναι NULL.

SELECT AVG(`amount_paid`) FROM `payments`;

Αποτέλεσμα:

⚠️ Προειδοποίηση: AVG διαιρεί με τον αριθμό των μη NULL γραμμών, όχι με τον αριθμό γραμμών του πίνακα. Ένα NULL ποσό παραλείπεται αντί να μετριέται ως μηδέν, γεγονός που ανεβάζει αθόρυβα τον μέσο όρο. Χρησιμοποιήστε AVG(IFNULL(`amount_paid`, 0)) όταν μια τιμή που λείπει σημαίνει μηδέν.

Πρακτικό παράδειγμα: Συνδυασμός συναρτήσεων συνάθροισης με GROUP BY

Κάθε παραπάνω συνάρτηση επέστρεψε έναν αριθμό για ολόκληρο τον πίνακα. Η προσθήκη ενός GROUP BY η ρήτρα επιστρέφει έναν αριθμό ανά ομάδα αντίθετα — και έτσι δημιουργούνται οι πραγματικές αναφορές.

Το ακόλουθο παράδειγμα ομαδοποιεί τα μέλη ονομαστικά και, στη συνέχεια, μετράει τον συνολικό αριθμό πληρωμών, το μέσο ποσό πληρωμής και το γενικό σύνολο των ποσών πληρωμής για κάθε μέλος.

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 Το Workbench μας δίνει τα ακόλουθα αποτελέσματα.

Το ερώτημα ενώνει τους δύο πίνακες στον όρο WHERE — το παλαιότερο στυλ ένωσης με κόμμα. Ο σύγχρονος κώδικας γράφει την ίδια λογική με έναν σαφή όρο. ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ … ΕΝΕΡΓΟΠΟΙΗΜΕΝΗΣημειώστε επίσης ότι κάθε μη συγκεντρωτική στήλη στη λίστα SELECT πρέπει να εμφανίζεται σε GROUP BY ή MySQL 5.7 και νεότερες εκδόσεις απορρίψτε το ερώτημα στην ενότητα ONLY_FULL_GROUP_BY. Δείτε το επίσημος ανώτερος υπάλληλος MySQL αναφορά συνάρτησης συγκέντρωσης.

Συχνές Ερωτήσεις