Πέντε κορυφαίες συναρτήσεις παραθύρου SQL που πρέπει να γνωρίζετε για συνεντεύξεις στο Data Science

Η SQL είναι η καθολική γλώσσα στον κόσμο των δεδομένων και είναι η πιο σημαντική δεξιότητα που μπορείς να βρεις ως επαγγελματίας δεδομένων.

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

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

Με αυτό είπε, ας βουτήξουμε σε αυτό!

1. Δέλτα με LEAD() και LAG()

 
Τα LEAD() και LAG() χρησιμοποιούνται κυρίως όταν συγκρίνουμε μια χρονική περίοδο με την προηγούμενη χρονική περίοδο για μια δεδομένη μέτρηση. Για να δώσω μερικά παραδείγματα…

• Μπορείτε να λάβετε το δέλτα μεταξύ των πωλήσεων κάθε έτους και των πωλήσεων του προηγούμενου έτους
• Μπορείτε να λάβετε το δέλτα ως προς τον αριθμό των εγγραφών/μετατροπών/επισκέψεων ιστότοπου σε μηνιαία βάση
• Μπορείτε να συγκρίνετε την ανατροπή των χρηστών σε μηνιαία βάση

Παράδειγμα:

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

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Αθροιστικά αθροίσματα με SUM() ή COUNT()

 
Ο υπολογισμός των τρεχόντων συνόλων μπορεί απλά να γίνει μέσω μιας συνάρτησης των Windows που ξεκινά με SUM() ή COUNT(). Αυτό είναι ένα ισχυρό εργαλείο όταν θέλετε να δείξετε την ανάπτυξη μιας συγκεκριμένης μέτρησης με την πάροδο του χρόνου. Πιο συγκεκριμένα, είναι χρήσιμο στις ακόλουθες περιπτώσεις:

• Λάβετε ένα τρέχον σύνολο εσόδων και κόστους με την πάροδο του χρόνου
• Λάβετε ένα τρέχον σύνολο χρόνου που δαπανάται στην εφαρμογή ανά χρήστη
• Λάβετε ένα τρέχον σύνολο μετατροπών με την πάροδο του χρόνου

Παράδειγμα:

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

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Κινούμενοι μέσοι όροι με AVG()

 
Η AVG() είναι πολύ ισχυρή στις λειτουργίες των Windows καθώς σας επιτρέπει να υπολογίζετε κινητούς μέσους όρους πάροδο του χρόνου.

Οι κινητοί μέσοι όροι είναι ένας απλός, αλλά αποτελεσματικός τρόπος πρόβλεψης των τιμών βραχυπρόθεσμα. Είναι επίσης εξαιρετικά χρήσιμα για την εξομάλυνση των πτητικών καμπυλών σε ένα γράφημα. Γενικά, οι κινούμενοι μέσοι όροι χρησιμοποιούνται για τη μέτρηση της γενικής κατεύθυνσης του πού κινούνται τα πράγματα.

Πιο συγκεκριμένα…

• Μπορούν να χρησιμοποιηθούν για να λάβετε τη γενική τάση των εβδομαδιαίων πωλήσεων (αυξάνεται ο μέσος όρος με την πάροδο του χρόνου;). Αυτό θα υποδηλώνει ανάπτυξη ως εταιρεία.
• Μπορούν επίσης να χρησιμοποιηθούν για να δείτε τη γενική τάση των εβδομαδιαίων μετατροπών ή των επισκέψεων στον ιστότοπο.

Παράδειγμα:

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

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
Το ROW_NUMBER() είναι ιδιαίτερα χρήσιμο όταν θέλετε να λάβετε την πρώτη ή την τελευταία εγγραφή. Για παράδειγμα, εάν έχετε έναν πίνακα με το πότε ήρθαν τα μέλη του γυμναστηρίου στο γυμναστήριο και θέλετε να λάβετε την ημερομηνία της πρώτης ημέρας που ήρθαν στο γυμναστήριο, μπορείτε να κάνετε ΔΙΑΜΕΡΙΣΜΟ ΚΑΤΑ πελάτη (όνομα/αναγνωριστικό) και ΠΑΡΑΓΓΕΛΙΑ ΑΝΑ ημερομηνία αγοράς. Στη συνέχεια, για να λάβετε την πρώτη σειρά, μπορείτε απλώς να φιλτράρετε τις σειρές με rowNumber ίσο με ένα.

Παράδειγμα:

Αυτό το παράδειγμα δείχνει πώς μπορείτε να χρησιμοποιήσετε το ROW_NUMBER() για να λάβετε την πρώτη ημερομηνία της επίσκεψης κάθε μέλους (χρήστης).

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Για να ανακεφαλαιώσουμε, εάν χρειαστεί ποτέ να αποκτήσετε την πρώτη ή την τελευταία εγγραφή, το ROW_NUMBER() είναι ένας πολύ καλός τρόπος για να το πετύχετε.

5. Εγγραφή κατάταξης με DENSE_RANK()

 
Το DENSE_RANK() είναι παρόμοιο με το ROW_NUMBER() εκτός από το ότι επιστρέφει την ίδια κατάταξη για ίσες τιμές. Η πυκνή κατάταξη είναι πολύ χρήσιμη όταν πρόκειται για την ανάκτηση των κορυφαίων αρχείων, για παράδειγμα:

• Αν θέλετε να συγκεντρώσετε τις 10 πιο δημοφιλείς εκπομπές του Netflix αυτή την εβδομάδα
• Εάν θέλετε να βρείτε τους 100 κορυφαίους χρήστες με βάση τα δολάρια που δαπανήθηκαν
• Αν θέλετε να δείτε τη συμπεριφορά των 1000 λιγότερο ενεργών χρηστών

Παράδειγμα:

Εάν θέλατε να κατατάξετε τους κορυφαίους πελάτες σας με βάση τις συνολικές πωλήσεις, η DENSE_RANK() θα ήταν η κατάλληλη συνάρτηση για χρήση.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Ευχαριστώ για την ανάγνωση!

 
Και αυτό είναι όλο! Ελπίζω ότι αυτό θα σας βοηθήσει στην προετοιμασία της συνέντευξής σας — είμαι βέβαιος ότι αν γνωρίζετε αυτές τις 5 έννοιες από μέσα προς τα έξω, θα τα πάτε πολύ καλά όταν πρόκειται για τα περισσότερα προβλήματα λειτουργίας παραθύρου SQL εκεί έξω.

Όπως πάντα, σας εύχομαι ότι καλύτερο στη μαθησιακή σας προσπάθεια!

 
 
Τερένς Σιν είναι λάτρης των δεδομένων με 3+ χρόνια εμπειρίας στην SQL και 2+ χρόνια εμπειρίας στην Python, και blogger στο Towards Data Science και στο KDnuggets.

Πρωτότυπο. Αναδημοσιεύτηκε με άδεια.

Πηγή: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html