21  Βάσεις Δεδομένων

21.1 Εισαγωγή

Ένας τεράστιος όγκος δεδομένων βρίσκετε σε βάσεις δεδομένων, επομένως είναι σημαντικό να γνωρίζετε με ποιο τρόπο μπορείτε να έχετε πρόσβαση σε αυτά. Μερικές φορές μπορεί να ζητήσετε από κάποιον να κατεβάσει για εσάς ένα .csv αρχείο, αλλά αυτό μπορεί να γίνει επώδυνο γρήγορα: κάθε φορά που χρειάζεται να κάνετε μία αλλαγή θα πρέπει να επικοινωνείτε με κάποιον άλλο. Αυτό που Θέλετε είναι να μπορείτε να μεταβείτε απευθείας στη βάση δεδομένων για να λάβετε τα δεδομένα που χρειάζεστε, όταν τα χρειάζεστε.

Σε αυτό το κεφάλαιο, θα μάθετε πρώτα τα βασικά του πακέτου DBI: πώς να το χρησιμοποιήσετε για να συνδεθείτε σε μία βάση δεδομένων και στη συνέχεια να ανακτήσετε δεδομένα με ένα ερώτημα SQL1. Η SQL, συντομογραφία του structured query language, είναι η κοινή γλώσσα των βάσεων δεδομένων και είναι μία σημαντική γλώσσα για να μάθουν όλοι οι επιστήμονες δεδομένων. Δεν πρόκειται να ξεκινήσουμε με την SQL, αλλά αντ’ αυτού θα σας διδάξουμε το πακέτο dbplyr, το οποία μπορεί να μεταφράσει τον κώδικά σας, γραμμένο με dplyr, σε SQL. Θα την χρησιμοποιήσουμε ως τρόπο για να σας διδάξουμε μερικές από τις πιο σημαντικές δυνατότητες της SQL. Δεν θα γίνετε άριστοι στην SQL μέχρι το τέλος του κεφαλαίου, αλλά θα μπορείτε να είστε σε θέση να προσδιορίσετε τα πιο σημαντικά στοιχεία της και να κατανοήσετε τι κάνουν.

21.1.1 Προαπαιτούμενα

Σε αυτό το κεφάλαιο, θα παρουσιάσουμε την DBI και το πακέτο dbplyr. Η DBI είναι μία διεπαφή χαμηλού επιπέδου που συνδέεται με βάσεις δεδομένων και εκτελεί SQL. Το dbplyr είναι μία διεπαφή υψηλού επιπέδου που μεταφράζει τον κώδικα dplyr σε ερωτήματα SQL και στη συνέχεια τα εκτελεί με την DBI.

21.2 Τα βασικά των βάσεων δεδομένων

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

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

  • Οι πίνακες βάσεων δεδομένων έχουν σχεδόν πάντα ευρετήρια (indexes). Όπως το ευρετήριο ενός βιβλίου έτσι και ένα ευρετήριο βάσης δεδομένων καθιστά δυνατή τη γρήγορη εύρεση γραμμών ενδιαφέροντος χωρίς να χρειάζεται να κοιτάξετε κάθε μεμονωμένη γραμμή. Τα πλαίσια δεδομένων και τα tibbles δεν έχουν ευρετήρια, τα data.tables όμως έχουν και είναι ένας από τους λόγους που είναι τόσο γρήγορα.

  • Οι περισσότερες κλασικές βάσεις δεδομένων είναι βελτιστοποιημένες για γρήγορη συλλογή δεδομένων και όχι για ανάλυση δεδομένων. Αυτές οι βάσεις δεδομένων ονομάζονται row-oriented επειδή τα δεδομένα αποθηκεύονται σειρά προς σειρά, αντί στήλη προς στήλη όπως στην R. Στις μέρες μας, υπάρχει μεγάλη ανάπτυξη column-oriented βάσεων δεδομένων που κάνουν την ανάλυση δεδομένων πολύ πιο γρήγορη.

Οι βάσεις δεδομένων εκτελούνται από συστήματα διαχείρισης βάσεων δεδομένων (ΣΔΒΔ εν συντομία), τα οποία διατίθενται σε τρεις βασικές μορφές:

  • Τα ΣΔΒΔ πελάτη-διακομιστή εκτελούνται σε έναν ισχυρό κεντρικό διακομιστή, στον οποίο συνδέετε από τον υπολογιστή σας (τον πελάτη). Είναι εξαιρετικά για την κοινή χρήση δεδομένων με πολλά άτομα μέσα σε μία ομάδα. Κάποια δημοφιλή ΣΔΒΔ πελάτη-διακομιστή είναι τα PostgreSQL, MariaDB, SQL Server και Oracle.
  • Τα cloud ΣΔΒΔ, όπως το Snowflake, το RedShift της Amazon και το BigQuery της Google, είναι παρόμοια με τα ΣΔΒΔ πελάτη-διακομιστή, αλλά εκτελούνται στο cloud. Αυτό σημαίνει ότι μπορούν εύκολα να χειριστούν εξαιρετικά μεγάλα σύνολα δεδομένων και μπορούν να παρέχουν αυτόματα περισσότερους υπολογιστικούς πόρους, όποτε αυτοί απαιτούνται.
  • Τα in-process ΣΔΒΔ, όπως το SQLite, ή το duckdb, εκτελούνται εξ ολοκλήρου στον υπολογιστή σας. Είναι ιδανικά για εργασία με μεγάλα σύνολα δεδομένων, όπου ο κύριος χρήστης είστε εσείς.

21.3 Σύνδεση σε μία βάση δεδομένων

Για να συνδεθείτε στη βάση δεδομένων από την R, θα χρησιμοποιήσετε δύο πακέτα:

  • Θα χρησιμοποιείτε πάντα το DBI (database interface) επειδή παρέχει ένα σύνολο γενικών συναρτήσεων που συνδέονται με τη βάση δεδομένων, ανεβάζουν δεδομένα, εκτελούν ερωτήματα SQL κ.λπ.

  • Θα χρησιμοποιήσετε επίσης ένα πακέτο προσαρμοσμένο για το ΣΔΒΔ στο οποίο συνδέεστε. Αυτό το πακέτο μεταφράζει τις γενικές DBI εντολές στις λεπτομέρειες που απαιτούνται για ένα συγκεκριμένο ΣΔΒΔ. Συνήθως υπάρχει ένα πακέτο για κάθε ΣΔΒΔ, π.χ. RPostgres για PostgreSQL και RMariaDB για MySQL.

Εάν δεν μπορείτε να βρείτε ένα συγκεκριμένο πακέτο για το ΣΔΒΔ σας, μπορείτε συνήθως να χρησιμοποιήσετε το πακέτο odbc. Αυτό χρησιμοποιεί το πρωτόκολλο ODBC που υποστηρίζεται από αρκετά ΣΔΒΔ. Το odbc απαιτεί λίγο περισσότερο χρόνο στην εγκατάστασή του, γιατί θα χρειαστεί επίσης να εγκαταστήσετε ένα πρόγραμμα οδήγησης ODBC και να πείτε στο πακέτο odbc πού να το βρει.

Συγκεκριμένα, δημιουργείτε μία σύνδεση βάσης δεδομένων χρησιμοποιώντας την DBI::dbConnect(). Το πρώτο όρισμα επιλέγει το ΣΔΒΔ2 και, στη συνέχεια, το δεύτερο και τα επόμενα ορίσματα περιγράφουν τον τρόπο σύνδεσης σε αυτό (δηλαδή την τοποθεσία και τα διαπιστευτήρια που χρειάζεστε για την πρόσβαση σε αυτό). Ο παρακάτω κώδικας δείχνει μερικά παραδείγματα:

con <- DBI::dbConnect(
  RMariaDB::MariaDB(), 
  username = "foo"
)
con <- DBI::dbConnect(
  RPostgres::Postgres(), 
  hostname = "databases.mycompany.com", 
  port = 1234
)

Οι ακριβείς λεπτομέρειες της σύνδεσης διαφέρουν πολύ από ΣΔΒΔ σε ΣΔΒΔ, οπότε δυστυχώς δεν μπορούμε να καλύψουμε όλες τις λεπτομέρειες εδώ. Αυτό σημαίνει ότι θα χρειαστεί να κάνετε λίγη έρευνα μόνοι σας. Συνήθως μπορείτε να ρωτήσετε άλλους επιστήμονες δεδομένων στην ομάδα σας ή να μιλήσετε με τον διαχειριστή της βάσης δεδομένων σας (database administrator, DBA). Η αρχική ρύθμιση θα χρειαστεί συχνά λίγο ψάξιμο (και ίσως λίγο γκουγκλάρισμα) για να γίνει σωστά, αλλά γενικά θα χρειαστεί να το κάνετε μόνο μία φορά.

21.3.1 Σε αυτό το βιβλίο

Η εγκατάσταση ενός ΣΔΒΔ πελάτη-διακομιστή ή cloud θα ήταν οδυνηρή για αυτό το βιβλίο, επομένως θα χρησιμοποιήσουμε αντ ’αυτού ένα in-process ΣΔΒΔ που υπάρχει εξ ολοκλήρου σε ένα πακέτο της R: το duckdb. Χάρη στη μαγεία του DBI, η μόνη διαφορά μεταξύ της χρήσης του duckdb και οποιουδήποτε άλλου ΣΔΒΔ είναι ο τρόπος με τον οποίο θα συνδεθείτε στη βάση δεδομένων. Αυτό το καθιστά υπέροχο να διδαχτεί, καθώς και ο κώδικας είναι εύκολος ως προς την εκτέλεσή του, ενώ μπορείτε να πάρετε εύκολα ό,τι μαθαίνετε και να το εφαρμόσετε αλλού.

Η σύνδεση στο duckdb είναι ιδιαίτερα απλή, επειδή οι προεπιλογές δημιουργούν μία προσωρινή βάση δεδομένων που διαγράφεται όταν τερματίσετε την R. Αυτό είναι εξαιρετικό για εκμάθηση, επειδή εγγυάται ότι θα ξεκινάτε από κάτι καθαρό κάθε φορά που κάνετε επανεκκίνηση της R:

con <- DBI::dbConnect(duckdb::duckdb())

Το duckdb είναι μία βάση δεδομένων υψηλής απόδοσης που έχει σχεδιαστεί για τις ανάγκες ενός επιστήμονα δεδομένων. Το χρησιμοποιούμε εδώ καθώς είναι πολύ εύκολο για να ξεκινήσετε, αλλά είναι επίσης ικανό να χειρίζεται gigabyte δεδομένων με μεγάλη ταχύτητα. Εάν θέλετε να χρησιμοποιήσετε το duckdb για ένα πραγματικό έργο ανάλυσης δεδομένων, θα χρειαστεί επίσης να δώσετε μία τιμή στο όρισμα dbdir για να δημιουργήσετε μία μόνιμη βάση δεδομένων και να πείτε στο duckdb πού να την αποθηκεύσει. Υποθέτοντας ότι χρησιμοποιείτε ένα project (Κεφάλαιο 6), είναι λογικό να το αποθηκεύσετε στον κατάλογο duckdb του αντίστοιχου project:

con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")

21.3.2 Φορτώστε μερικά δεδομένα

Δεδομένου ότι πρόκειται για μία νέα βάση δεδομένων, πρέπει να ξεκινήσουμε προσθέτοντας κάποια δεδομένα. Εδώ θα προσθέσουμε τα σύνολα δεδομένων mpg και diamonds από το πακέτο ggplot2 χρησιμοποιώντας την DBI::dbWriteTable(). Η απλούστερη χρήση της dbWriteTable() χρειάζεται τρία ορίσματα: μία σύνδεση βάσης δεδομένων, το όνομα του πίνακα που θα δημιουργηθεί στη βάση δεδομένων και ένα πλαίσιο δεδομένων που περιέχει κάποια δεδομένα.

dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)

Εάν χρησιμοποιείτε το duckdb σε πραγματικό project, συνιστούμε ανεπιφύλακτα να μάθετε για τις συναρτήσεις duckdb_read_csv() και duckdb_register_arrow(). Σας δίνουν ισχυρούς και αποδοτικούς τρόπους για να φορτώνετε γρήγορα δεδομένα απευθείας στο duckdb, χωρίς να χρειάζεται να τα φορτώσετε πρώτα στην R. Θα δείξουμε επίσης μία χρήσιμη τεχνική για τη φόρτωση πολλών αρχείων σε μία βάση δεδομένων στην Ενότητα 26.4.1.

21.3.3 Βασικά στοιχεία DBI

Μπορείτε να ελέγξετε ότι τα δεδομένα έχουν φορτωθεί σωστά χρησιμοποιώντας μερικές άλλες συναρτήσεις DBI: Η dbListTables() παραθέτει όλους τους πίνακες στη βάση δεδομένων3 και η dbReadTable() ανακτά τα περιεχόμενα ενός πίνακα.

dbListTables(con)
#> [1] "diamonds" "mpg"

con |> 
  dbReadTable("diamonds") |> 
  as_tibble()
#> # A tibble: 53,940 × 10
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ 53,934 more rows

Η dbReadTable() επιστρέφει ένα data.frame επομένως χρησιμοποιούμε την as_tibble() για να το μετατρέψουμε σε tibble, έτσι ώστε να εκτυπώνεται όμορφα.

Εάν γνωρίζετε ήδη SQL, μπορείτε να χρησιμοποιήσετε την dbGetQuery() για να λάβετε τα αποτελέσματα της εκτέλεσης ενός ερωτήματος στη βάση δεδομένων:

sql <- "
  SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000
"
as_tibble(dbGetQuery(con, sql))
#> # A tibble: 1,655 × 5
#>   carat cut       clarity color price
#>   <dbl> <fct>     <fct>   <fct> <int>
#> 1  1.54 Premium   VS2     E     15002
#> 2  1.19 Ideal     VVS1    F     15005
#> 3  2.1  Premium   SI1     I     15007
#> 4  1.69 Ideal     SI1     D     15011
#> 5  1.5  Very Good VVS2    G     15013
#> 6  1.73 Very Good VS1     G     15014
#> # ℹ 1,649 more rows

Εάν δεν έχετε ξαναδεί SQL, μην ανησυχείτε! Θα μάθετε περισσότερα σύντομα. Αλλά αν διαβάσετε προσεκτικά, μπορεί να μαντέψετε ότι το παραπάνω ερώτημα επιλέγει πέντε στήλες του συνόλου δεδομένων diamonds και όλες τις γραμμές στις οποίες η price είναι μεγαλύτερη από 15.000.

21.4 Βασικά στοιχεία του πακέτου dbplyr

Τώρα που συνδεθήκαμε σε μία βάση δεδομένων και φορτώσαμε μερικά δεδομένα, μπορούμε να αρχίσουμε να μαθαίνουμε για το πακέτο dbplyr. Το dbplyr είναι ένα σύστημα υποστήριξης της dplyr, που σημαίνει ότι συνεχίζετε να γράφετε κώδικα dplyr, αλλά το σύστημα υποστήριξης τον εκτελεί διαφορετικά. Σε αυτή την περίπτωση, το dbplyr μεταφράζεται σε SQL. Άλλα συστήματα υποστήριξης περιλαμβάνουν το πακέτο dtplyr που μεταφράζεται σε data.table και το multidplyr που εκτελεί τον κώδικά σας σε πολλαπλούς πυρήνες.

Για να χρησιμοποιήσετε το dbplyr, πρέπει πρώτα να χρησιμοποιήσετε την tbl() για να δημιουργήσετε ένα αντικείμενο που αντιπροσωπεύει έναν πίνακα βάσης δεδομένων:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # Source:   table<diamonds> [?? x 10]
#> # Database: DuckDB v0.10.1 [unknown@Linux 6.5.0-1018-azure:R 4.3.3/:memory:]
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ more rows

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

diamonds_db <- tbl(con, in_schema("sales", "diamonds"))
diamonds_db <- tbl(con, in_catalog("north_america", "sales", "diamonds"))

Άλλες φορές μπορεί να θέλετε να χρησιμοποιήσετε το δικό σας ερώτημα SQL ως σημείο εκκίνησης:

diamonds_db <- tbl(con, sql("SELECT * FROM diamonds"))

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

big_diamonds_db <- diamonds_db |> 
  filter(price > 15000) |> 
  select(carat:clarity, price)

big_diamonds_db
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB v0.10.1 [unknown@Linux 6.5.0-1018-azure:R 4.3.3/:memory:]
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ more rows

Μπορείτε να πείτε ότι αυτό το αντικείμενο αντιπροσωπεύει ένα ερώτημα βάσης δεδομένων, επειδή εκτυπώνει το όνομα του ΣΔΒΔ στο επάνω μέρος και ενώ σας λέει τον αριθμό των στηλών, συνήθως δεν γνωρίζει τον αριθμό των γραμμών. Αυτό συμβαίνει επειδή η εύρεση του συνολικού αριθμού γραμμών απαιτεί συνήθως την εκτέλεση του πλήρους ερωτήματος, κάτι που προσπαθούμε να αποφύγουμε.

Μπορείτε να δείτε τον κώδικα SQL που δημιουργείται από τη συνάρτηση της dplyr show_query(). Το να γνωρίζετε πως να εφαρμόζετε συναρτήσεις της dplyr, είναι ένας πολύ καλός τρόπος για να μάθετε και SQL! Γράψτε λίγο κώδικα dplyr, πείτε στο dbplyr να τον μεταφράσει σε SQL και, στη συνέχεια, προσπαθήστε να καταλάβετε πώς ταιριάζουν οι δύο γλώσσες.

big_diamonds_db |>
  show_query()
#> <SQL>
#> SELECT carat, cut, color, clarity, price
#> FROM diamonds
#> WHERE (price > 15000.0)

Για να επαναφέρετε όλα τα δεδομένα στην R, καλείτε την collect(). Στο παρασκήνιο, αυτό δημιουργεί την SQL, καλεί την dbGetQuery() για να πάρει τα δεδομένα και μετά μετατρέπει το αποτέλεσμα σε tibble:

big_diamonds <- big_diamonds_db |> 
  collect()
big_diamonds
#> # A tibble: 1,655 × 5
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ 1,649 more rows

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

21.5 SQL

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

Θα εξερευνήσουμε τη σχέση μεταξύ dplyr και SQL χρησιμοποιώντας μερικούς παλιούς φίλους από το πακέτο nycflights13: τα σύνολα δεδομένων flights και planes. Αυτά τα σύνολα δεδομένων είναι εύκολο να εισαχθούν στην δοκιμαστική βάση δεδομένων μας, καθώς το πακέτο dbplyr συνοδεύεται από μία συνάρτηση που αντιγράφει τους πίνακες από το nycflights13 στη βάση:

dbplyr::copy_nycflights13(con)
#> Creating table: airlines
#> Creating table: airports
#> Creating table: flights
#> Creating table: planes
#> Creating table: weather
flights <- tbl(con, "flights")
planes <- tbl(con, "planes")

21.5.1 Βασικά στοιχεία SQL

Τα στοιχεία ανώτατου επιπέδου της SQL ονομάζονται δηλώσεις. Οι κοινές δηλώσεις περιλαμβάνουν την CREATE για τον ορισμό νέων πινάκων, την INSERT για προσθήκη δεδομένων και την SELECT για ανάκτηση δεδομένων. Θα επικεντρωθούμε στις δηλώσεις SELECT που ονομάζονται επίσης ερωτήματα, καθώς είναι σχεδόν αποκλειστικά αυτό που θα χρησιμοποιήσετε ως επιστήμονας δεδομένων.

Ένα ερώτημα αποτελείται από συνθήκες. Υπάρχουν πέντε σημαντικές συνθήκες: SELECT, FROM, WHERE, ORDER BY, και GROUP BY. Κάθε ερώτημα πρέπει να έχει τις SELECT4 και FROM5 και το απλούστερο ερώτημα είναι το SELECT * FROM table, το οποίο επιλέγει όλες τις στήλες από τον καθορισμένο πίνακα . Το αποτέλεσμα του dbplyr για έναν καθαρό πίνακα είναι :

flights |> show_query()
#> <SQL>
#> SELECT *
#> FROM flights
planes |> show_query()
#> <SQL>
#> SELECT *
#> FROM planes

Οι WHERE και ORDER BY ελέγχουν ποιες γραμμές θα περιέχονται στην έξοδο και πως αυτές θα είναι ταξινομημένες.

flights |> 
  filter(dest == "IAH") |> 
  arrange(dep_delay) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH')
#> ORDER BY dep_delay

Η GROUP BY μετατρέπει το ερώτημα σε μία σύνοψη, προκαλώντας μία συνάθροιση των δεδομένων:

flights |> 
  group_by(dest) |> 
  summarize(dep_delay = mean(dep_delay, na.rm = TRUE)) |> 
  show_query()
#> <SQL>
#> SELECT dest, AVG(dep_delay) AS dep_delay
#> FROM flights
#> GROUP BY dest

Υπάρχουν δύο σημαντικές διαφορές μεταξύ των συναρτήσεων της dplyr και των συνθηκών SELECT:

  • Στην SQL, τα πεζά και κεφαλαία δεν έχουν σημασία: μπορείτε να γράψετε select, SELECT, ή ακόμα και SeLeCt. Σε αυτό το βιβλίο θα επιμείνουμε στη συχνή σύνταξη λέξεων-κλειδιών SQL με κεφαλαία για να τις ξεχωρίζουμε από τα ονόματα πινάκων ή μεταβλητών.
  • Στην SQL, η σειρά έχει σημασία: πρέπει πάντα να γράφετε τις συνθήκες με την εξής σειρά SELECT, FROM, WHERE, GROUP BY, ORDER BY. Η σειρά αυτή όμως δεν είναι ίδια με τον τρόπο όπου πραγματικά, οι συνθήκες, αξιολογούνται. Ξεκινά η FROM, μετά η WHERE, GROUP BY, SELECT και η ORDER BY.

Οι ακόλουθες ενότητες εξερευνούν κάθε συνθήκη με περισσότερες λεπτομέρειες.

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

21.5.2 SELECT

Η συνθήκη SELECT είναι “η μηχανή” των ερωτημάτων και εκτελεί την ίδια δουλειά με τις select(), mutate(), rename(), relocate() και, όπως θα μάθετε στην επόμενη ενότητα, την summarize().

Οι select(), rename(), και relocate() έχουν αρκετά άμεσες μεταφράσεις στην SELECT καθώς απλώς επηρεάζουν το πού εμφανίζεται μία στήλη (αν εμφανίζεται καθόλου) μαζί με το όνομά της:

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year"
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  rename(year_built = year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year" AS year_built
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  relocate(manufacturer, model, .before = type) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, manufacturer, model, "type", "year"
#> FROM planes

Αυτό το παράδειγμα δείχνει επίσης πώς η SQL αλλάζει ονόματα σε στήλες. Στην ορολογία της SQL η μετονομασία ονομάζεται aliasing και γίνεται με την AS. Σημειώστε ότι σε αντίθεση με την mutate(), το παλιό όνομα βρίσκεται στα αριστερά και το νέο όνομα βρίσκεται στα δεξιά.

Στα παραπάνω παραδείγματα σημειώστε ότι τα "year" και "type" είναι μέσα σε διπλά εισαγωγικά. Αυτό συμβαίνει επειδή πρόκειται για δεσμευμένες λέξεις στο duckdb, κι επομένως το πακέτο dbplyr τις παραθέτει για να αποφύγει οποιαδήποτε πιθανή σύγχυση μεταξύ των ονομάτων στηλών/πινάκων και των τελεστών SQL.

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

SELECT "tailnum", "type", "manufacturer", "model", "year"
FROM "planes"

Άλλα συστήματα βάσεων δεδομένων χρησιμοποιούν backticks αντί για εισαγωγικά:

SELECT `tailnum`, `type`, `manufacturer`, `model`, `year`
FROM `planes`

Οι μεταφράσεις για την mutate() είναι εξίσου απλές: κάθε μεταβλητή γίνεται μία νέα έκφραση στην SELECT:

flights |> 
  mutate(
    speed = distance / (air_time / 60)
  ) |> 
  show_query()
#> <SQL>
#> SELECT flights.*, distance / (air_time / 60.0) AS speed
#> FROM flights

Θα επανέλθουμε στη μετάφραση μεμονωμένων στοιχείων (όπως το /) στην Ενότητα 21.6.

21.5.3 FROM

Η συνθήκη FROM ορίζει την πηγή των δεδομένων. Δεν θα έχει πολύ ενδιαφέρον για λίγο, επειδή χρησιμοποιούμε απλά μεμονωμένους πίνακες. Θα δείτε όμως πιο περίπλοκα παραδείγματα μόλις δούμε τις συναρτήσεις ένωσης (join).

21.5.4 GROUP BY

H group_by() μεταφράζεται στην συνθήκη GROUP BY6 και η summarize() μεταφράζεται στην συνθήκη SELECT:

diamonds_db |> 
  group_by(cut) |> 
  summarize(
    n = n(),
    avg_price = mean(price, na.rm = TRUE)
  ) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n, AVG(price) AS avg_price
#> FROM diamonds
#> GROUP BY cut

Θα επανέλθουμε στο τι συμβαίνει με τη μετάφραση των n() και mean() στην Ενότητα 21.6.

21.5.5 WHERE

Η filter()μεταφράζεται στη συνθήκη WHERE:

flights |> 
  filter(dest == "IAH" | dest == "HOU") |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH' OR dest = 'HOU')

flights |> 
  filter(arr_delay > 0 & arr_delay < 20) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (arr_delay > 0.0 AND arr_delay < 20.0)

Υπάρχουν μερικές σημαντικές λεπτομέρειες που πρέπει να σημειωθούν εδώ:

  • Ο τελεστής | γίνεται OR και το & γίνεται AND.
  • Η SQL χρησιμοποιεί τον τελεστή = για σύγκριση, κι όχι τον ==. Η SQL δεν έχει ανάθεση, επομένως δεν υπάρχει πιθανότητα σύγχυσης εκεί.
  • Η SQL χρησιμοποιεί μόνο '' για συμβολοσειρές, όχι "". Ακόμη, στην SQL, το "" χρησιμοποιείται για τον προσδιορισμό μεταβλητών, όπως το `` της R.

Ένας άλλος χρήσιμος τελεστής της SQL είναι ο IN, ο οποίος είναι πολύ κοντά στον %in% της R:

flights |> 
  filter(dest %in% c("IAH", "HOU")) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest IN ('IAH', 'HOU'))

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

flights |> 
  group_by(dest) |> 
  summarize(delay = mean(arr_delay))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> # Source:   SQL [?? x 2]
#> # Database: DuckDB v0.10.1 [unknown@Linux 6.5.0-1018-azure:R 4.3.3/:memory:]
#>   dest   delay
#>   <chr>  <dbl>
#> 1 IAH    4.24 
#> 2 BQN    8.25 
#> 3 ATL   11.3  
#> 4 MCO    5.45 
#> 5 LAS    0.258
#> 6 PHX    2.10 
#> # ℹ more rows

Εάν θέλετε να μάθετε περισσότερα για το πώς λειτουργεί τοNULL, μπορεί να σας φανεί χρήσιμο το “Three valued logic” του Markus Winand.

Σε γενικές γραμμές, μπορείτε να εργαστείτε με NULL τιμές χρησιμοποιώντας τις συναρτήσεις που θα χρησιμοποιούσατε για NA στην R:

flights |> 
  filter(!is.na(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (NOT((dep_delay IS NULL)))

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

WHERE "dep_delay" IS NOT NULL

Σημειώστε ότι εάν εφαρμόσετε την filter() σε μία μεταβλητή που δημιουργήσατε χρησιμοποιώντας μία σύνοψη, το dbplyr θα δημιουργήσει μία συνθήκη HAVING αντί για μία συνθήκη WHERE. Αυτή είναι μία από τις ιδιοσυγκρασίες της SQL: η WHERE αξιολογείται πριν από τη SELECT και τη GROUP BY. Επομένως η SQL χρειάζεται μία άλλη συνθήκη που θα αξιολογηθεί στη συνέχεια.

diamonds_db |> 
  group_by(cut) |> 
  summarize(n = n()) |> 
  filter(n > 100) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n
#> FROM diamonds
#> GROUP BY cut
#> HAVING (COUNT(*) > 100.0)

21.5.6 ORDER BY

Η ταξινόμηση γραμμών περιλαμβάνει μία απλή μετάφραση από την arrange() στην συνθήκη ORDER BY:

flights |> 
  arrange(year, month, day, desc(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> ORDER BY "year", "month", "day", dep_delay DESC

Παρατηρήστε πώς η desc() μεταφράζεται στην DESC: αυτή είναι μία από τις πολλές συναρτήσεις της dplyr των οποίων το όνομα εμπνεύστηκαν από την SQL.

21.5.7 Υποερωτήματα

Μερικές φορές δεν είναι δυνατό να μεταφραστεί μία ροή με συναρτήσεις dplyr σε μία μεμονωμένη δήλωση SELECT και έτσι πρέπει να χρησιμοποιήσετε ένα υποερώτημα (ή εμφολευμένο ερώτημα). Ένα υποερώτημα είναι απλώς ένα ερώτημα που χρησιμοποιείται ως πηγή δεδομένων στην συνθήκη FROM, αντί για τον συνηθισμένο πίνακα.

Το dbplyr συνήθως χρησιμοποιεί υποερωτήματα για να αντιμετωπίσει τους περιορισμούς της SQL. Για παράδειγμα, οι εκφράσεις στην συνθήκη SELECT δεν μπορούν να αναφέρονται σε στήλες που μόλις δημιουργήθηκαν. Αυτό σημαίνει ότι η ακόλουθη (απλή) ροή συναρτήσεων dplyr πρέπει να συμβεί σε δύο βήματα: το πρώτο (εσωτερικό) ερώτημα υπολογίζει την year1 και στη συνέχεια το δεύτερο (εξωτερικό) ερώτημα μπορεί να υπολογίσει το year2.

flights |> 
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |> 
  show_query()
#> <SQL>
#> SELECT q01.*, year1 + 1.0 AS year2
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01

Αυτό θα το δείτε επίσης αν επιχειρήσετε να εφαρμόσετε την filter() σε μία μεταβλητή που μόλις δημιουργήσατε. Θυμηθείτε, παρόλο που η WHERE είναι γραμμένη μετά τη SELECT, αξιολογείται πριν από αυτή, επομένως χρειαζόμαστε ένα υποερώτημα σε αυτό το (απλό) παράδειγμα:

flights |> 
  mutate(year1 = year + 1) |> 
  filter(year1 == 2014) |> 
  show_query()
#> <SQL>
#> SELECT q01.*
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01
#> WHERE (year1 = 2014.0)

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

21.5.8 Ενώσεις (Joins)

Εάν είστε εξοικειωμένοι με τις ενώσεις της dplyr, οι ενώσεις της SQL είναι αρκετά παρόμοιες. Ορίστε ένα απλό παράδειγμα:

flights |> 
  left_join(planes |> rename(year_built = year), by = "tailnum") |> 
  show_query()
#> <SQL>
#> SELECT
#>   flights.*,
#>   planes."year" AS year_built,
#>   "type",
#>   manufacturer,
#>   model,
#>   engines,
#>   seats,
#>   speed,
#>   engine
#> FROM flights
#> LEFT JOIN planes
#>   ON (flights.tailnum = planes.tailnum)

Το κύριο στοιχείο που πρέπει να προσέξετε εδώ είναι η σύνταξη: οι ενώσεις της SQL χρησιμοποιούν υποσυνθήκες της FROM για να επιστρέψουν πρόσθετους πίνακες, ενώ καθορίζουν το πώς σχετίζονται οι πίνακες με το ON.

Τα ονόματα της dplyr για αυτές τις συναρτήσεις είναι τόσο στενά συνδεδεμένα με την SQL που μπορείτε εύκολα να μαντέψετε την αντιστοίχιση σε SQL για τις inner_join(), right_join() και full_join():

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
INNER JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
RIGHT JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
FULL JOIN planes ON (flights.tailnum = planes.tailnum)

Είναι πιθανό να χρειαστείτε πολλές ενώσεις όταν εργάζεστε με δεδομένα από μία βάση δεδομένων. Αυτό συμβαίνει επειδή οι πίνακες βάσεων δεδομένων αποθηκεύονται συχνά σε μία εξαιρετικά κανονικοποιημένη μορφή, όπου κάθε “γεγονός” αποθηκεύεται σε ένα μόνο μέρος, οπότε για να διατηρήσετε ένα πλήρες σύνολο δεδομένων για ανάλυση, πρέπει να πλοηγηθείτε σε ένα σύνθετο δίκτυο πινάκων που συνδέονται με πρωτεύοντα και ξένα κλειδιά. Αν συναντήσετε αυτό το σενάριο, το πακέτο dm, των Tobias Schieferdecker, Kirill Müller και Darko Bergant, θα κάνει τη ζωή σας λίγο πιο εύκολη. Μπορεί να καθορίσει αυτόματα τις συνδέσεις μεταξύ πινάκων χρησιμοποιώντας τους περιορισμούς που τα DBA συχνά παρέχουν, να οπτικοποιήσει τις συνδέσεις και να δημιουργήσει τις συνδέσεις που χρειάζεστε για να συνδέσετε έναν πίνακα με έναν άλλο.

21.5.9 Λοιπές συναρτήσεις

Το dbplyr μεταφράζει επίσης και άλλες συναρτήσεις όπως οι distinct(), slice_*(), και intersect(), και μία ολοένα αυξανόμενη γκάμα από συναρτήσεις του πακέτου tidyr όπως οι pivot_longer() και pivot_wider(). Ο πιο εύκολος τρόπος για να δείτε το πλήρες σύνολο των διαθέσιμων, προς το παρόν, μεταφράσεων είναι να επισκεφτείτε τον ιστότοπο του dbplyr: https://dbplyr.tidyverse.org/reference/.

21.5.10 Ασκήσεις

  1. Σε τι μεταφράζεται η distinct();
    Σε τι η head();

  2. Εξηγήστε τι κάνει καθένα από τα ακόλουθα ερωτήματα SQL και δοκιμάστε να τα δημιουργήσετε ξανά χρησιμοποιώντας το πακέτο dbplyr.

    SELECT * 
FROM flights
WHERE dep_delay < arr_delay

SELECT *, distance / (air_time / 60) AS speed
FROM flights

21.6 Μεταφράσεις συναρτήσεων

Μέχρι στιγμής έχουμε επικεντρωθεί στη μεγάλη εικόνα του πώς οι συναρτήσεις της dplyr μεταφράζονται στις συνθήκες ενός ερωτήματος. Τώρα θα εμβαθύνουμε λίγο και θα μιλήσουμε για τη μετάφραση των συναρτήσεων της R που λειτουργούν με μεμονωμένες στήλες, π.χ., τι συμβαίνει όταν χρησιμοποιείτε την mean(x) σε ένα summarize();

Για να δούμε τι συμβαίνει, θα χρησιμοποιήσουμε μερικές μικρές βοηθητικές συναρτήσεις που εκτελούν μία summarize() ή μία mutate() και εμφανίζουν το SQL ερώτημα που δημιουργήθηκε. Αυτό θα κάνει λίγο πιο εύκολο το να εξερευνήσετε μερικές παραλλαγές και να δείτε πως μπορεί να διαφέρουν οι συνόψεις και οι μετασχηματισμοί.

summarize_query <- function(df, ...) {
  df |> 
    summarize(...) |> 
    show_query()
}
mutate_query <- function(df, ...) {
  df |> 
    mutate(..., .keep = "none") |> 
    show_query()
}

Ας επικεντρωθούμε σε μερικές συνόψεις! Κοιτάζοντας τον παρακάτω κώδικα θα παρατηρήσετε ότι ορισμένες συναρτήσεις σύνοψης, όπως η mean(), έχουν σχετικά απλή μετάφραση ενώ άλλες, όπως η median(), είναι πολύ πιο περίπλοκες. Η πολυπλοκότητα είναι συνήθως μεγαλύτερη για συναρτήσεις που είναι συχνές στη στατιστική, αλλά λιγότερο συχνές σε βάσεις δεδομένων.

flights |> 
  group_by(year, month, day) |>  
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
#> `summarise()` has grouped output by "year" and "month". You can override
#> using the `.groups` argument.
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) AS mean,
#>   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

Η μετάφραση των συναρτήσεων σύνοψης γίνεται πιο περίπλοκη όταν χρησιμοποιούνται μέσα σε μία mutate(), επειδή πρέπει να μετατραπούν στις λεγόμενες συναρτήσεις window. Στην SQL, μετατρέπετε μία συνηθισμένη συνάρτηση συνάθροισης σε συνάρτηση window προσθέτοντας το OVER μετά από αυτήν:

flights |> 
  group_by(year, month, day) |>  
  mutate_query(
    mean = mean(arr_delay, na.rm = TRUE),
  )
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) OVER (PARTITION BY "year", "month", "day") AS mean
#> FROM flights

Στην SQL, η έκφραση GROUP BY χρησιμοποιείται αποκλειστικά για συνόψεις, επομένως εδώ μπορείτε να δείτε ότι η ομαδοποίηση έχει μετακινηθεί από το όρισμα PARTITION BY στο OVER.

Οι συναρτήσεις window περιλαμβάνουν όλες τις συναρτήσεις που κοιτάζουν προς τα εμπρός ή προς τα πίσω, όπως η lead() και η lag(), που κοιτάζουν την τιμή “πριν” ή “μετά” αντίστοιχα:

flights |> 
  group_by(dest) |>  
  arrange(time_hour) |> 
  mutate_query(
    lead = lead(arr_delay),
    lag = lag(arr_delay)
  )
#> <SQL>
#> SELECT
#>   dest,
#>   LEAD(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lead,
#>   LAG(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lag
#> FROM flights
#> ORDER BY time_hour

Εδώ είναι σημαντικό να εφαρμόσετε την arrange() στα δεδομένα, επειδή οι πίνακες SQL δεν έχουν εγγενή σειρά. Στην πραγματικότητα, αν δεν χρησιμοποιήσετε την arrange(), μπορεί να πάρετε τις γραμμές με διαφορετική σειρά κάθε φορά! Σημειώστε ότι για τις συναρτήσεις window, οι πληροφορίες ταξινόμησης επαναλαμβάνονται: η συνθήκη ORDER BY του κύριου ερωτήματος δεν εφαρμόζεται αυτόματα στις συναρτήσεις window.

Μία άλλη σημαντική συνάρτηση της SQL είναι η CASE WHEN. Χρησιμοποιείται ως μετάφραση των if_else() και case_when(), των συναρτήσεων του πακέτου dplyr που ενέπνευσε. Ακολουθούν μερικά απλά παραδείγματα:

flights |> 
  mutate_query(
    description = if_else(arr_delay > 0, "delayed", "on-time")
  )
#> <SQL>
#> SELECT CASE WHEN (arr_delay > 0.0) THEN 'delayed' WHEN NOT (arr_delay > 0.0) THEN 'on-time' END AS description
#> FROM flights
flights |> 
  mutate_query(
    description = 
      case_when(
        arr_delay < -5 ~ "early", 
        arr_delay < 5 ~ "on-time",
        arr_delay >= 5 ~ "late"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay < -5.0) THEN 'early'
#> WHEN (arr_delay < 5.0) THEN 'on-time'
#> WHEN (arr_delay >= 5.0) THEN 'late'
#> END AS description
#> FROM flights

Η CASE WHEN χρησιμοποιείται επίσης για ορισμένες άλλες συναρτήσεις που δεν έχουν άμεση μετάφραση από την R στην SQL. Ένα καλό παράδειγμα αυτού είναι η cut():

flights |> 
  mutate_query(
    description =  cut(
      arr_delay, 
      breaks = c(-Inf, -5, 5, Inf), 
      labels = c("early", "on-time", "late")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay <= -5.0) THEN 'early'
#> WHEN (arr_delay <= 5.0) THEN 'on-time'
#> WHEN (arr_delay > 5.0) THEN 'late'
#> END AS description
#> FROM flights

Το dbplyr μεταφράζει επίσης κοινές συναρτήσεις χειρισμού συμβολοσειράς και ημερομηνίας, για τις οποίες μπορείτε να μάθετε στο vignette("translation-function", package = "dbplyr"). Οι μεταφράσεις του dbplyr σίγουρα δεν είναι τέλειες και υπάρχουν πολλές συναρτήσεις της R που δεν έχουν μεταφραστεί ακόμα, όμως το dbplyr κάνει εκπληκτικά καλή δουλειά καλύπτοντας τις λειτουργίες που θα χρησιμοποιείτε τις περισσότερες φορές.

21.7 Σύνοψη

Σε αυτό το κεφάλαιο μάθατε πώς να έχετε πρόσβαση σε δεδομένα από βάσεις δεδομένων. Εστιάσαμε στο dbplyr, ένα “σύστημα υποστήριξης” του dplyr που σας επιτρέπει να γράψετε κώδικα dplyr με τον οποίο είστε εξοικειωμένοι και να μεταφραστεί αυτόματα σε SQL. Χρησιμοποιήσαμε, ακόμα, αυτή τη μετάφραση για να σας μάθουμε λίγη SQL. Είναι σημαντικό να μάθετε κάποια κομμάτια της SQL καθώς είναι η πιο συχνά χρησιμοποιούμενη γλώσσα για εργασία με δεδομένα και η μερική γνώση θα σας διευκολύνει να επικοινωνείτε με άλλους ανθρώπους, σχετικούς με δεδομένα, που δεν χρησιμοποιούν R. Εάν έχετε ολοκληρώσει αυτό το κεφάλαιο και θέλετε να μάθετε περισσότερα για την SQL, έχουμε δύο συστάσεις:

  • Το SQL for Data Scientists από την Renée M. P. Teate είναι μία εισαγωγή στην SQL που έχει σχεδιαστεί ειδικά για τις ανάγκες των επιστημόνων δεδομένων και περιλαμβάνει παραδείγματα του είδους των δεδομένων που είναι πολύ πιθανό να συναντηθούν σε πραγματικές επιχειρήσεις.
  • Το Practical SQL από τον Anthony DeBarros είναι γραμμένο από την οπτική γωνία ενός δημοσιογράφου δεδομένων (ένας επιστήμονας δεδομένων που ειδικεύεται στην αφήγηση συναρπαστικών ιστοριών) και αναφέρεται σε περισσότερες λεπτομέρειες σχετικά με την εισαγωγή των δεδομένων σας μία βάση δεδομένων και την εγκατάσταση του δικού σας ΣΔΒΔ.

Στο επόμενο κεφάλαιο, θα μάθουμε για ένα άλλο σύστημα υποστήριξης της dplyr για εργασία με μεγάλα δεδομένα: το arrow. Το arrow έχει σχεδιαστεί για εργασία με μεγάλα αρχεία στον δίσκο και αποτελεί φυσικό συμπλήρωμα των βάσεων δεδομένων.

  1. Η SQL προφέρεται είτε “s”-“q”-“l” είτε “sequel”.↩︎

  2. Συνήθως, αυτή είναι η μόνη συνάρτηση που θα χρησιμοποιήσετε από το πακέτο πελάτη, επομένως συνιστούμε να χρησιμοποιήσετε τον τελεστή :: για να καλέστε αυτήν τη μία συνάρτηση, αντί να φορτώσετε ολόκληρο το πακέτο με το library() .↩︎

  3. Τουλάχιστον, όλοι οι πίνακες τους οποίους έχετε άδεια να δείτε.↩︎

  4. Ανάλογα με τη χρήση, η SELECT μπορεί να είναι είτε δήλωση είτε συνθήκη. Για να αποφευχθεί αυτή η σύγχυση, θα χρησιμοποιούμε το ερώτημα SELECT αντί για τη δήλωση SELECT.↩︎

  5. Τεχνικά, απαιτείται μόνο η SELECT, καθώς μπορείτε να γράψετε ερωτήματα όπως SELECT 1+1 για να εκτελέσετε βασικούς υπολογισμούς. Εαν θέλετε όμως να εργαστείτε με δεδομένα (όπως κάνετε πάντα!), θα χρειαστείτε επίσης μία συνθήκη FROM.↩︎

  6. Αυτό δεν είναι τυχαίο: το όνομα της συνάρτησης της dplyr εμπνεύστηκε από τον όρο της SQL.↩︎