Είναι ιδιαίτερα χρήσιμο όταν χρειάζεται να υπολογίσετε τις συναθροίσεις ή τις ταξινομήσεις για ομάδες σειρών χωρίς στην πραγματικότητα να συμπτύξετε ολόκληρο το σύνολο αποτελεσμάτων.
Ελάτε μαζί μας σε αυτό το σεμινάριο καθώς μαθαίνουμε τα πάντα για να ξεκινήσετε να εργάζεστε με την ρήτρα OVER.
Απαιτήσεις:
Πριν βουτήξουμε στη λειτουργικότητα και τη λειτουργία της ρήτρας OVER, βεβαιωθείτε ότι έχετε ξεφύγει από τα βασικά της SQL. Υποθέτουμε επίσης ότι έχετε πρόσβαση σε μια βάση δεδομένων που μπορείτε να χρησιμοποιήσετε για να δοκιμάσετε τις γνώσεις σας.
Στην περίπτωσή μας, θα χρησιμοποιήσουμε τη βάση δεδομένων MySQL με το δείγμα βάσης δεδομένων Sakila. Απλώς βεβαιωθείτε ότι έχετε επαρκή δικαιώματα και ότι η μηχανή της βάσης δεδομένων σας υποστηρίζει τις λειτουργίες παραθύρου.
Σύνταξη:
Όπως αναφέραμε προηγουμένως, στις περισσότερες περιπτώσεις, χρησιμοποιούμε κυρίως τον όρο OVER σε συνδυασμό με τις συναρτήσεις παραθύρου.
Ως εκ τούτου, μπορούμε να εκφράσουμε τη σύνταξη της πρότασης ως εξής:
<συνάρτηση παραθύρου>(έκφραση) OVER ([ΔΙΑΜΕΡΙΣΜΑ ΚΑΤΑ partition_expression, ...]
[ORDER BY sort_expression [ASC | DESC], ...]
[frame_specification]
)
Στη δεδομένη σύνταξη, μπορούμε να αναλύσουμε κάθε στοιχείο ως εξής:
-
– Αναφέρεται στη συνάρτηση παραθύρου που θέλουμε να εφαρμόσουμε σε ένα συγκεκριμένο παράθυρο σειρών όπως SUM(), AVG(), ROW_NUMBER(), RANK, κ.λπ. - Έκφραση – Καθορίζει μια στήλη ή μια έκφραση για την οποία εφαρμόζεται η συνάρτηση παραθύρου.
- PARTITION BY – Αυτή είναι μια προαιρετική ρήτρα που διαιρεί το σύνολο αποτελεσμάτων σε διαμερίσματα όπου κάθε διαμέρισμα είναι σαν μια ξεχωριστή μονάδα όπου εφαρμόζεται η συνάρτηση. Οι γραμμές μέσα στο ίδιο διαμέρισμα μοιράζονται τις ίδιες τιμές στις καθορισμένες στήλες.
- ORDER BY – Αυτό καθορίζει τη σειρά με την οποία γίνεται η επεξεργασία των σειρών σε κάθε διαμέρισμα.
- frame_specification – Αυτή είναι μια προαιρετική ρήτρα που ορίζει το πλαίσιο των σειρών μέσα στο διαμέρισμα. Οι κοινές προδιαγραφές πλαισίου περιλαμβάνουν ΣΕΙΡΕΣ ΜΕΤΑΞΥ <έναρξη> ΚΑΙ <τέλος> ή εύρος μεταξύ <έναρξη> ΚΑΙ <τέλος.
Με αυτό το πρόβλημα, ας διερευνήσουμε μερικά πρακτικά παραδείγματα για το πώς να το χρησιμοποιήσετε.
Παράδειγμα:
Ας δείξουμε πώς να χρησιμοποιήσετε τη ρήτρα χρησιμοποιώντας το δείγμα βάσης δεδομένων Sakila. Εξετάστε ένα παράδειγμα όπου πρέπει να προσδιορίσουμε τα συνολικά έσοδα για κάθε κατηγορία ταινιών.
Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση παραθύρου αθροίσματος με τον όρο OVER και μια δέσμη εντολών σύνδεσης όπως φαίνεται στο ακόλουθο παράδειγμα:
ΕΠΙΛΕΓΩcategory.name AS name_category,
film.title AS film_title,
film.rental_rate,
SUM(payment.amount) OVER (ΔΙΑΜΕΡΙΣΜΑ ΚΑΤΑ κατηγορία.όνομα) AS total_revenue
ΑΠΟ
ταινία
ΣΥΜΜΕΤΟΧΗ
ταινία_κατηγορία ΕΝΕΡΓΟΠΟΙΗΣΗ
film.film_id = film_category.film_id
ΣΥΜΜΕΤΟΧΗ
κατηγορία ΟΝ
film_category.category_id = κατηγορία.category_id
ΣΥΜΜΕΤΟΧΗ
απόθεμα ΟΝ
film.film_id = inventory.film_id
ΣΥΜΜΕΤΟΧΗ
ενοικίαση ON
inventory.inventory_id = rental.inventory_id
ΣΥΜΜΕΤΟΧΗ
ΕΝΕΡΓΟΠΟΙΗΣΗ πληρωμής
rental.rental_id = πληρωμή.rental_id
ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ
όνομα κατηγορίας,
ταινία.τίτλος;
Στο συγκεκριμένο ερώτημα, ξεκινάμε επιλέγοντας τον τίτλο της ταινίας, την τιμή ενοικίασης και χρησιμοποιούμε την έκφραση sum (payment.amount over partition by category.name) για να προσδιορίσουμε το άθροισμα κάθε διαμερίσματος κατηγορίας με το όνομα της κατηγορίας.
Πρέπει να χρησιμοποιήσουμε την ρήτρα PARTITION BY για να διασφαλίσουμε ότι ο υπολογισμός του αθροίσματος ξεκινά ξανά σε κάθε μοναδική κατηγορία.
Η έξοδος που προκύπτει είναι η εξής:
Ορίστε το!
συμπέρασμα
Σε αυτό το παράδειγμα, διερευνήσαμε τις βασικές αρχές της εργασίας με τον όρο OVER στην SQL. Αυτή δεν είναι βασική ρήτρα και απαιτεί προηγούμενη εξοικείωση με άλλες δυνατότητες της SQL.