Ομαδοποίηση SQL κατά και διαμερισμός κατά σενάρια: Πότε και πώς να συνδυάσετε δεδομένα στην επιστήμη δεδομένων – KDnuggets

Εικόνα από Freepik

Η SQL (Structured Query Language) είναι μια γλώσσα προγραμματισμού που χρησιμοποιείται για τη διαχείριση και το χειρισμό δεδομένων. Αυτός είναι ο λόγος για τον οποίο τα ερωτήματα SQL είναι πολύ απαραίτητα για την αλληλεπίδραση με βάσεις δεδομένων με δομημένο και αποτελεσματικό τρόπο.

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

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

Ο όρος GROUP BY στην SQL χρησιμοποιείται για

1. ομαδοποίηση δεδομένων σε ορισμένες στήλες
2. μειώνοντας την ομάδα σε μία μόνο σειρά
3. εκτέλεση εργασιών συγκέντρωσης σε άλλες στήλες των ομάδων.

Ομαδοποίηση στήλης = Η τιμή στη στήλη Ομαδοποίηση πρέπει να είναι ίδια για όλες τις σειρές της ομάδας

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

Η στήλη Συνάθροιση δεν πρέπει να είναι η Στήλη Ομαδοποίησης.

Σενάριο 1: Ομαδοποίηση για να βρείτε το άθροισμα του Συνόλου

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

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

select category, sum(amount) as sales
from sales
group by category;

Ομαδοποίηση στήλη = κατηγορία

Αθροιστική στήλη = ποσό

Συνάρτηση συνάθροισης = άθροισμα ()

Σενάριο 2: Ομαδοποίηση για εύρεση καταμέτρησης

Ας υποθέσουμε ότι θέλουμε να υπολογίσουμε τον αριθμό των εργαζομένων σε κάθε τμήμα.

Σε αυτήν την περίπτωση, θα ομαδοποιήσουμε ανά τμήμα και θα υπολογίσουμε τον αριθμό των εργαζομένων σε κάθε τμήμα.

select department, count(empid) as emp_count
from employees
group by department;

Ομαδοποίηση στήλη = τμήμα

στήλη συνάθροισης = κενό

Συνάρτηση συγκέντρωσης = μέτρηση

Σενάριο 3: Ομαδοποίηση για εύρεση του μέσου όρου

Ας υποθέσουμε ότι θέλουμε να υπολογίσουμε τον μέσο μισθό των εργαζομένων σε κάθε τμήμα

Αντίστοιχα, θα τα ομαδοποιήσουμε ξανά ανά τμήμα και θα υπολογίσουμε τους μέσους μισθούς των εργαζομένων σε κάθε τμήμα ξεχωριστά.

select department, avg(salary) as avg_salary
from employees
group by department;

Ομαδοποίηση στήλη = τμήμα

Συγκεντρωτική στήλη = μισθός

Συνάρτηση συνάθροισης = μ.ο

Σενάριο 4: Ομαδοποίηση για εύρεση Μέγιστου / Ελάχιστου

Ας υποθέσουμε ότι θέλουμε να υπολογίσουμε τον υψηλότερο μισθό των εργαζομένων σε κάθε τμήμα.

Θα ομαδοποιήσουμε τα τμήματα και θα υπολογίσουμε τον μέγιστο μισθό σε κάθε τμήμα.

select department, max(salary) as max_salary
from employees
group by department;

Ομαδοποίηση στήλη = τμήμα

Συγκεντρωτική στήλη = μισθός

Συνάρτηση συγκέντρωσης = μέγ

Σενάριο 5: Ομαδοποίηση για εύρεση διπλότυπων

Ας υποθέσουμε ότι θέλουμε να βρούμε διπλότυπα ή ίδια ονόματα πελατών στη βάση δεδομένων μας.

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

select name, count(*) AS duplicate_count
from customers
group by name
having count(*) > 1;

Ομαδοποίηση στήλης = όνομα

στήλη συνάθροισης = *

Συνάρτηση συγκέντρωσης = μέτρηση

Έχοντας = συνθήκη φίλτρου που θα εφαρμοστεί πάνω από τη συνάρτηση συνάθροισης

Ο όρος PARTITION BY στην SQL χρησιμοποιείται για

1. ομαδοποίηση/διαμερισμός δεδομένων σε ορισμένες στήλες
2. Διατηρούνται μεμονωμένες σειρές και δεν συνδυάζονται σε ένα
3. εκτέλεση λειτουργιών κατάταξης και συγκέντρωσης σε άλλες στήλες της ομάδας/διαμερίσματος.

Κατάτμηση στήλης = επιλέγουμε μια στήλη στην οποία ομαδοποιούμε τα δεδομένα. Τα δεδομένα στη στήλη του διαμερίσματος πρέπει να είναι τα ίδια για κάθε ομάδα. Εάν δεν προσδιορίζεται, ο πλήρης πίνακας θεωρείται ως ενιαίο διαμέρισμα.

Στήλη παραγγελίας = Με κάθε ομάδα που δημιουργείται με βάση τη στήλη κατάτμησης, θα ταξινομήσουμε/ταξινομήσουμε τις σειρές στην ομάδα

Συνάρτηση κατάταξης = Μια συνάρτηση κατάταξης ή μια συνάρτηση συνάθροισης θα εφαρμοστεί στις σειρές στο διαμέρισμα

Σενάριο 6: Διαμερισμός για την εύρεση της Υψηλότερης εγγραφής σε μια Ομάδα

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

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

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

select book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales;

Διαχωριστική στήλη = κατηγορία

Στήλη παραγγελίας = ποσό

Συνάρτηση κατάταξης = row_number()

Αυτό το ερώτημα μας δίνει όλες τις σειρές στον πίνακα book_sales και οι σειρές ταξινομούνται σε κάθε κατηγορία βιβλίων, με το βιβλίο με τις υψηλότερες πωλήσεις ως τη σειρά 1.

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

select category, book_name, amount from (
select category, book_name, amount
row_number() over (partition by category order by amount) as sales_rank
from book_sales
) as book_ranked_sales
where sales_rank = 1;

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

Σενάριο 7: Διαμερισμός για την εύρεση αθροιστικών συνόλων σε μια ομάδα

Ας υποθέσουμε ότι θέλουμε να υπολογίσουμε το τρέχον σύνολο (αθροιστικό σύνολο) της πώλησης καθώς πωλούνται. Χρειαζόμαστε ένα ξεχωριστό αθροιστικό σύνολο για κάθε προϊόν.

Θα χωρίσουμε κατά product_id και θα ταξινομήσουμε το διαμέρισμα κατά ημερομηνία

select product_id, date, amount,
sum(amount) over (partition by product_id order by date desc) as running_total
from sales_data;

Στήλη κατάτμησης = product_id

Στήλη παραγγελίας = ημερομηνία

Συνάρτηση κατάταξης = άθροισμα ()

Σενάριο 8: Διαμερισμός για σύγκριση αξιών σε μια ομάδα

Ας πούμε ότι θέλουμε να συγκρίνουμε τον μισθό κάθε υπαλλήλου με τον μέσο μισθό του τμήματός του.

Έτσι θα χωρίσουμε τους υπαλλήλους με βάση το τμήμα και θα βρούμε τον μέσο μισθό κάθε τμήματος.

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

select employee_id, salary, department,
avg(salary) over (partition by department) as avg_dept_sal
from employees;

Διαχωριστική στήλη = τμήμα

Στήλη παραγγελίας = χωρίς παραγγελία

Συνάρτηση κατάταξης = avg()

Σενάριο 9: Διαμερισμός για τη διαίρεση των αποτελεσμάτων σε ίσες ομάδες

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

Έτσι θα εξαγάγουμε μια άλλη λογική στήλη tile_id, η οποία θα έχει το αριθμητικό id κάθε ομάδας υπαλλήλων.

Οι ομάδες θα δημιουργηθούν με βάση τον μισθό – η πρώτη ομάδα πλακιδίων θα έχει τον υψηλότερο μισθό κ.ο.κ.

select employee_id, salary,
ntile(4) over (order by salary desc) as tile_id
from employees;

Στήλη κατάτμησης = χωρίς κατάτμηση – ο πλήρης πίνακας βρίσκεται στο ίδιο διαμέρισμα

Στήλη παραγγελίας = μισθός

Συνάρτηση κατάταξης = ntile()

Σενάριο 10: Διαμερισμός για τον εντοπισμό νησιών ή κενών στα δεδομένα

Ας υποθέσουμε ότι έχουμε μια διαδοχική στήλη product_id και θέλουμε να εντοπίσουμε κενά σε αυτό.

Έτσι θα εξαγάγουμε μια άλλη λογική στήλη island_id, η οποία θα έχει τον ίδιο αριθμό εάν το product_id είναι διαδοχικό. Όταν ένα διάλειμμα προσδιορίζεται στο product_id, τότε το island_id αυξάνεται.

select product_id,
row_number() over (order by product_id) as row_num,
product_id - row_number() over (order by product_id) as island_id,
from products;

Στήλη κατάτμησης = χωρίς κατάτμηση – ο πλήρης πίνακας βρίσκεται στο ίδιο διαμέρισμα

Στήλη παραγγελίας = product_id

Συνάρτηση κατάταξης = row_number()

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

Συνοπτικές πληροφορίες: Η ομαδοποίηση σάς επιτρέπει να συγκεντρώνετε δεδομένα και να συνοψίζετε πληροφορίες σε κάθε ομάδα.

Ανάλυση προτύπων: Βοηθά στον εντοπισμό προτύπων ή τάσεων σε υποσύνολα δεδομένων, παρέχοντας πληροφορίες για διάφορες πτυχές του συνόλου δεδομένων.

Στατιστική ανάλυση: Επιτρέπει τον υπολογισμό στατιστικών μετρήσεων όπως μέσοι όροι, μετρήσεις, μέγιστα, ελάχιστα και άλλες συγκεντρωτικές συναρτήσεις εντός των ομάδων.

Καθαρισμός δεδομένων: Βοηθά στον εντοπισμό διπλότυπων, ασυνεπειών ή ανωμαλιών εντός των ομάδων, κάνοντας τον καθαρισμό δεδομένων και τη βελτίωση της ποιότητας πιο διαχειρίσιμη.

Ανάλυση κοόρτης: Χρήσιμο για ανάλυση βάσει κοόρτης, παρακολούθηση και σύγκριση ομάδων οντοτήτων με την πάροδο του χρόνου κ.λπ.
 
 

Hanu τρέχει το Ιστολόγιο HelperCodes που ασχολείται κυρίως με Φύλλα εξαπάτησης SQL. Είμαι προγραμματιστής full stack και ενδιαφέρομαι να δημιουργήσω επαναχρησιμοποιήσιμα στοιχεία.

• SEO Powered Content & PR Distribution. Ενισχύστε σήμερα.
• PlatoData.Network Vertical Generative Ai. Ενδυναμώστε τον εαυτό σας. Πρόσβαση εδώ.
• PlatoAiStream. Web3 Intelligence. Ενισχύθηκε η γνώση. Πρόσβαση εδώ.
• PlatoESG. Ανθρακας, Cleantech, Ενέργεια, Περιβάλλον, Ηλιακός, Διαχείριση των αποβλήτων. Πρόσβαση εδώ.
• PlatoHealth. Ευφυΐα βιοτεχνολογίας και κλινικών δοκιμών. Πρόσβαση εδώ.
• πηγή: https://www.kdnuggets.com/sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science?utm_source=rss&utm_medium=rss&utm_campaign=sql-group-by-and-partition-by-scenarios-when-and-how-to-combine-data-in-data-science