21  데이터베이스

21.1 소개

방대한 양의 데이터가 데이터베이스에 저장되어 있으므로 데이터베이스에 액세스하는 방법을 아는 것은 필수적입니다. 때때로 누군가에게 스냅샷을 .csv로 다운로드해 달라고 요청할 수 있지만, 이것은 금방 고통스러워집니다. 변경해야 할 때마다 다른 사람과 소통해야 하기 때문입니다. 필요할 때 필요한 데이터를 얻기 위해 데이터베이스에 직접 접근할 수 있어야 합니다.

이 장에서는 먼저 DBI 패키지의 기본 사항을 배웁니다. DBI 패키지를 사용하여 데이터베이스에 연결한 다음 SQL1 쿼리로 데이터를 검색하는 방법을 배웁니다. Structured Query Language의 약자인 SQL은 데이터베이스의 공용어이며 모든 데이터 과학자가 배워야 할 중요한 언어입니다. 그렇긴 하지만 우리는 SQL로 시작하지 않고 대신 dplyr 코드를 SQL로 변환할 수 있는 dbplyr을 가르칠 것입니다. 그것을 SQL의 가장 중요한 기능 중 일부를 가르치는 방법으로 사용할 것입니다. 이 장이 끝날 때까지 SQL 마스터가 되지는 않겠지만 가장 중요한 구성 요소를 식별하고 그 기능을 이해할 수 있게 될 것입니다.

21.1.1 선수 지식

이 장에서는 DBI와 dbplyr을 소개합니다. DBI는 데이터베이스에 연결하고 SQL을 실행하는 저수준 인터페이스입니다. dbplyr은 dplyr 코드를 SQL 쿼리로 변환한 다음 DBI로 실행하는 고수준 인터페이스입니다.

library(DBI)
library(dbplyr)
library(tidyverse)
#> Warning: package 'ggplot2' was built under R version 4.5.2
#> Warning: package 'readr' was built under R version 4.5.2

21.2 데이터베이스 기초

가장 단순한 수준에서 데이터베이스를 데이터베이스 용어로 테이블(tables) 이라고 하는 데이터 프레임 모음으로 생각할 수 있습니다. 데이터 프레임과 마찬가지로 데이터베이스 테이블은 열의 모든 값이 동일한 유형인 명명된 열의 모음입니다. 데이터 프레임과 데이터베이스 테이블 사이에는 세 가지 높은 수준의 차이점이 있습니다:

  • 데이터베이스 테이블은 디스크에 저장되며 임의로 클 수 있습니다. 데이터 프레임은 메모리에 저장되며 근본적으로 제한되어 있습니다(비록 그 제한이 많은 문제에 대해 여전히 충분히 크지만).

  • 데이터베이스 테이블에는 거의 항상 인덱스가 있습니다. 책의 색인과 마찬가지로 데이터베이스 인덱스를 사용하면 모든 단일 행을 볼 필요 없이 관심 있는 행을 빠르게 찾을 수 있습니다. 데이터 프레임과 티블에는 인덱스가 없지만 data.table에는 있으며, 이것이 data.table이 빠른 이유 중 하나입니다.

  • 대부분의 고전적인 데이터베이스는 기존 데이터를 분석하는 것이 아니라 데이터를 신속하게 수집하는 데 최적화되어 있습니다. 이러한 데이터베이스는 데이터가 R과 같이 열별로 저장되는 것이 아니라 행별로 저장되기 때문에 행 지향(row-oriented) 이라고 합니다. 더 최근에는 기존 데이터를 훨씬 더 빠르게 분석할 수 있는 열 지향(column-oriented) 데이터베이스가 많이 개발되었습니다.

데이터베이스는 데이터베이스 관리 시스템(줄여서 DBMS)에 의해 실행되며, 세 가지 기본 형태가 있습니다:

  • 클라이언트-서버 DBMS는 강력한 중앙 서버에서 실행되며, 여러분은 컴퓨터(클라이언트)에서 연결합니다. 조직 내의 여러 사람과 데이터를 공유하는 데 좋습니다. 널리 사용되는 클라이언트-서버 DBMS에는 PostgreSQL, MariaDB, SQL Server, Oracle이 있습니다.
  • Snowflake, Amazon RedShift, Google BigQuery와 같은 클라우드 DBMS는 클라이언트-서버 DBMS와 유사하지만 클라우드에서 실행됩니다. 즉, 매우 큰 데이터셋을 쉽게 처리할 수 있으며 필요에 따라 더 많은 컴퓨팅 리소스를 자동으로 제공할 수 있습니다.
  • SQLite 또는 duckdb와 같은 인-프로세스(In-process) DBMS는 전적으로 컴퓨터에서 실행됩니다. 여러분이 주 사용자인 대규모 데이터셋으로 작업하는 데 좋습니다.

21.3 데이터베이스 연결

R에서 데이터베이스에 연결하려면 한 쌍의 패키지를 사용합니다:

  • 데이터베이스에 연결하고, 데이터를 업로드하고, SQL 쿼리를 실행하는 등의 일반적인 함수 집합을 제공하는 DBI(database interface)를 항상 사용합니다.

  • 연결하려는 DBMS에 맞춤화된 패키지도 사용합니다. 이 패키지는 일반 DBI 명령을 해당 DBMS에 필요한 세부 사항으로 변환합니다. 일반적으로 각 DBMS마다 패키지가 하나씩 있습니다. 예를 들어 PostgreSQL용 RPostgres, MySQL용 RMariaDB입니다.

DBMS에 맞는 특정 패키지를 찾을 수 없는 경우 일반적으로 odbc 패키지를 대신 사용할 수 있습니다. 이것은 많은 DBMS에서 지원하는 ODBC 프로토콜을 사용합니다. odbc는 ODBC 드라이버도 설치하고 odbc 패키지에 드라이버 위치를 알려줘야 하므로 설정이 조금 더 필요합니다.

구체적으로 DBI::dbConnect()를 사용하여 데이터베이스 연결을 생성합니다. 첫 번째 인수는 DBMS2를 선택하고, 두 번째 및 후속 인수는 연결 방법(즉, 위치 및 액세스에 필요한 자격 증명)을 설명합니다. 다음 코드는 몇 가지 전형적인 예를 보여줍니다:

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

연결의 정확한 세부 사항은 DBMS마다 많이 다르므로 안타깝게도 여기서 모든 세부 사항을 다룰 수는 없습니다. 즉, 스스로 조사를 좀 해야 합니다. 일반적으로 팀의 다른 데이터 과학자에게 물어보거나 DBA(Database Administrator)에게 이야기할 수 있습니다. 초기 설정은 올바르게 하기 위해 약간의 조작(그리고 아마도 약간의 구글링)이 필요할 수 있지만 일반적으로 한 번만 수행하면 됩니다.

21.3.1 이 책에서

클라이언트-서버나 클라우드 DBMS를 설정하는 것은 이 책에서는 고통스러운 일이 될 것이므로 대신 R 패키지 내에 전적으로 존재하는 인-프로세스 DBMS인 duckdb를 사용할 것입니다. DBI의 마법 덕분에 duckdb와 다른 DBMS를 사용하는 유일한 차이점은 데이터베이스에 연결하는 방법뿐입니다. 따라서 이 코드를 쉽게 실행할 수 있을 뿐만 아니라 배운 내용을 쉽게 가져와 다른 곳에 적용할 수 있으므로 가르치기에 좋습니다.

duckdb에 연결하는 것은 특히 간단합니다. 기본값이 R을 종료할 때 삭제되는 임시 데이터베이스를 생성하기 때문입니다. R을 다시 시작할 때마다 깨끗한 상태에서 시작한다는 것을 보장하므로 학습에 좋습니다:

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

duckdb는 데이터 과학자의 요구에 매우 부합하도록 설계된 고성능 데이터베이스입니다. 시작하기 매우 쉽지만 기가바이트의 데이터를 매우 빠른 속도로 처리할 수도 있기 때문에 여기서 사용합니다. 실제 데이터 분석 프로젝트에 duckdb를 사용하려면 dbdir 인수를 제공하여 영구 데이터베이스를 만들고 저장할 위치를 duckdb에 알려줘야 합니다. 프로젝트(Chapter 6)를 사용하고 있다고 가정하면 현재 프로젝트의 duckdb 디렉터리에 저장하는 것이 합리적입니다:

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

21.3.2 데이터 로드

이것은 새 데이터베이스이므로 데이터를 추가하는 것으로 시작해야 합니다. 여기서는 DBI::dbWriteTable()을 사용하여 ggplot2의 mpg 및 diamonds 데이터셋을 추가합니다. dbWriteTable()의 가장 간단한 사용법에는 데이터베이스 연결, 데이터베이스에 생성할 테이블 이름, 데이터 프레임의 세 가지 인수가 필요합니다.

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

실제 프로젝트에서 duckdb를 사용하는 경우 duckdb_read_csv() 및 duckdb_register_arrow()에 대해 배우는 것을 강력히 추천합니다. 이것들은 데이터를 먼저 R로 로드하지 않고도 duckdb로 직접 데이터를 빠르게 로드할 수 있는 강력하고 성능이 뛰어난 방법을 제공합니다. 또한 Section 26.4.1 에서 여러 파일을 데이터베이스로 로드하는 유용한 기술을 보여줄 것입니다.

21.3.3 DBI 기초

dbListTables()는 데이터베이스의 모든 테이블을 나열3하고 dbReadTable()은 테이블의 내용을 검색하는 등 몇 가지 다른 DBI 함수를 사용하여 데이터가 올바르게 로드되었는지 확인할 수 있습니다.

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()을 사용하여 티블로 변환합니다.

이미 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 데이터셋의 5개 열과 price가 15,000보다 큰 모든 행을 선택한다고 추측할 수 있습니다.

21.4 dbplyr 기초

이제 데이터베이스에 연결하고 일부 데이터를 로드했으므로 dbplyr에 대해 배우기 시작할 수 있습니다. dbplyr은 dplyr 백엔드입니다. 즉, dplyr 코드를 계속 작성하지만 백엔드가 이를 다르게 실행한다는 의미입니다. 여기서 dbplyr은 SQL로 변환합니다. 다른 백엔드로는 data.table로 변환하는 dtplyr과 여러 코어에서 코드를 실행하는 multidplyr이 있습니다.

dbplyr을 사용하려면 먼저 tbl()을 사용하여 데이터베이스 테이블을 나타내는 객체를 생성해야 합니다:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # Source:   table<diamonds> [?? x 10]
#> # Database: DuckDB 1.4.3 [root@Darwin 25.1.0:R 4.5.0/: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"))

이 객체는 게으릅니다(lazy). dplyr 동사를 사용해도 dplyr은 아무 작업도 수행하지 않습니다. 수행하려는 작업 순서를 기록하고 필요할 때만 수행합니다. 예를 들어 다음 파이프라인을 살펴보세요:

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

big_diamonds_db
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB 1.4.3 [root@Darwin 25.1.0:R 4.5.0/: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

상단에 DBMS 이름이 인쇄되고 열 수는 알려주지만 일반적으로 행 수는 알 수 없기 때문에 이 객체가 데이터베이스 쿼리를 나타낸다는 것을 알 수 있습니다. 총 행 수를 찾으려면 일반적으로 전체 쿼리를 실행해야 하는데, 이는 우리가 피하려고 하는 것이기 때문입니다.

show_query()라는 dplyr 함수로 생성된 SQL 코드를 볼 수 있습니다. 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()를 호출하여 데이터를 얻은 다음 결과를 티블로 변환합니다:

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을 사용하여 데이터베이스에서 원하는 데이터를 선택하고 아래 설명된 변환을 사용하여 기본 필터링 및 집계를 수행합니다. 그런 다음 R 고유의 함수로 데이터를 분석할 준비가 되면 collect()를 사용하여 메모리 내 티블을 얻고 순수 R 코드로 작업을 계속합니다.

21.5 SQL

이 장의 나머지 부분에서는 dbplyr의 렌즈를 통해 SQL을 조금 가르칠 것입니다. 다소 전통적이지 않은 SQL 소개이지만 기본 사항을 빠르게 파악하는 데 도움이 되기를 바랍니다. 다행히도 개념의 많은 부분이 동일하므로 dplyr을 이해한다면 SQL을 빠르게 배울 수 있습니다.

nycflights13 패키지의 오랜 친구인 flights와 planes를 사용하여 dplyr과 SQL 간의 관계를 탐색할 것입니다. 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의 최상위 구성 요소를 문(statements) 이라고 합니다. 일반적인 문에는 새 테이블 정의를 위한 CREATE, 데이터 추가를 위한 INSERT, 데이터 검색을 위한 SELECT가 포함됩니다. 데이터 과학자로서 거의 독점적으로 사용할 것이기 때문에 SELECT 문, 즉 쿼리(queries) 에 집중할 것입니다.

쿼리는 절(clauses) 로 구성됩니다. 중요한 5가지 절은 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은 표준이지만 매우 복잡하며 어떤 데이터베이스도 이를 정확히 따르지 않습니다. 이 책에서 중점적으로 다룰 주요 구성 요소는 DBMS 간에 매우 유사하지만 사소한 변형이 많이 있습니다. 다행히 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

Section 21.6 에서 개별 구성 요소(예: /)의 변환으로 다시 돌아올 것입니다.

21.5.3 FROM

FROM 절은 데이터 소스를 정의합니다. 지금은 단일 테이블만 사용하고 있기 때문에 당분간은 별로 흥미롭지 않을 것입니다. 조인 함수를 다루게 되면 더 복잡한 예제를 보게 될 것입니다.

21.5.4 GROUP BY

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

Section 21.6 에서 n()과 mean()의 변환에 무슨 일이 일어나고 있는지 다시 다룰 것입니다.

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으로 R의 %in%와 매우 유사합니다:

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

SQL은 NA 대신 NULL을 사용합니다. 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 1.4.3 [root@Darwin 25.1.0:R 4.5.0/:memory:]
#>   dest   delay
#>   <chr>  <dbl>
#> 1 CLT    7.36 
#> 2 MDW   12.4  
#> 3 HOU    7.18 
#> 4 SDF   12.7  
#> 5 LAS    0.258
#> 6 PHX    2.10 
#> # ℹ more rows

NULL이 작동하는 방식에 대해 더 알고 싶다면 Markus Winand의 “The Three-Valued Logic of SQL”을 읽어보세요.

일반적으로 R에서 NA에 사용하는 함수를 사용하여 NULL로 작업할 수 있습니다:

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은 WHERE 절이 아니라 HAVING 절을 생성합니다. 이것은 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로 변환되는 것을 주목하세요. 이것은 이름이 SQL에서 직접 영감을 받은 많은 dplyr 함수 중 하나입니다.

21.5.7 하위 쿼리(Subqueries)

때로는 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 조인

dplyr의 조인에 익숙하다면 SQL 조인도 매우 유사합니다. 간단한 예는 다음과 같습니다:

flights |> 
  left_join(planes |> rename(year_built = year), join_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과 밀접하게 연결되어 있어 inner_join(), right_join(), full_join()에 해당하는 SQL을 쉽게 추측할 수 있습니다:

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)

데이터베이스의 데이터로 작업할 때 많은 조인이 필요할 가능성이 큽니다. 데이터베이스 테이블은 종종 고도로 정규화된 형태로 저장되어 각 “사실”이 한 곳에 저장되고 분석을 위해 완전한 데이터셋을 유지하려면 기본 키와 외래 키로 연결된 복잡한 테이블 네트워크를 탐색해야 하기 때문입니다. 이 시나리오에 직면하면 Tobias Schieferdecker, Kirill Müller, Darko Bergant의 dm 패키지가 생명의 은인이 될 것입니다. DBA가 종종 제공하는 제약 조건을 사용하여 테이블 간의 연결을 자동으로 결정하고, 연결을 시각화하여 무슨 일이 일어나고 있는지 볼 수 있게 하며, 한 테이블을 다른 테이블에 연결하는 데 필요한 조인을 생성할 수 있습니다.

21.5.9 기타 동사

dbplyr은 또한 distinct(), slice_*(), intersect()와 같은 다른 동사와 pivot_longer() 및 pivot_wider()와 같은 점점 늘어나는 tidyr 함수 선택을 변환합니다. 현재 사용 가능한 전체 세트를 보는 가장 쉬운 방법은 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 함수의 변환에 대해 이야기해 보겠습니다. 예를 들어 summarize()에서 mean(x)를 사용하면 어떻게 될까요?

무슨 일이 일어나고 있는지 확인하는 데 도움이 되도록 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,
#>   MEDIAN(arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

요약 함수를 mutate() 내부에서 사용하면 소위 윈도우(window) 함수로 전환해야 하기 때문에 변환이 더 복잡해집니다. SQL에서는 일반 집계 함수 뒤에 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 절은 요약에만 사용되므로 여기서 그룹화가 GROUP BY 절에서 OVER로 이동했음을 볼 수 있습니다.

윈도우 함수에는 각각 “이전” 또는 “다음” 값을 보는 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()를 사용하지 않으면 매번 다른 순서로 행을 다시 얻을 수 있습니다! 윈도우 함수의 경우 정렬 정보가 반복된다는 점에 유의하세요. 기본 쿼리의 ORDER BY 절은 윈도우 함수에 자동으로 적용되지 않습니다.

또 다른 중요한 SQL 함수는 CASE WHEN입니다. 이것은 직접 영감을 준 dplyr 함수인 if_else() 및 case_when()의 변환으로 사용됩니다. 다음은 몇 가지 간단한 예입니다:

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 요약

이 장에서는 데이터베이스의 데이터에 액세스하는 방법을 배웠습니다. 익숙한 dplyr 코드를 작성하면 자동으로 SQL로 변환되는 dplyr “백엔드”인 dbplyr에 집중했습니다. 그 변환을 사용하여 SQL을 조금 가르쳤습니다. SQL은 데이터를 다루는 데 가장 일반적으로 사용되는 언어이며 SQL을 조금 알면 R을 사용하지 않는 다른 데이터 담당자와 소통하기가 더 쉬워지므로 SQL을 배우는 것이 중요합니다.

이 장을 마쳤고 SQL에 대해 더 배우고 싶다면 두 가지 추천 사항이 있습니다:

  • Renée M. P. Teate의 SQL for Data Scientists는 데이터 과학자의 요구에 맞춰 특별히 설계된 SQL 입문서이며 실제 조직에서 마주칠 가능성이 높은 고도로 상호 연결된 데이터의 예를 포함합니다.
  • Anthony DeBarros의 Practical SQL은 데이터 저널리스트(설득력 있는 이야기를 전달하는 전문 데이터 과학자)의 관점에서 작성되었으며 데이터를 데이터베이스로 가져오고 자체 DBMS를 실행하는 것에 대해 더 자세히 다룹니다.

다음 장에서는 대용량 데이터 작업을 위한 또 다른 dplyr 백엔드인 arrow에 대해 배울 것입니다. Arrow는 디스크의 대용량 파일 작업용으로 설계되었으며 데이터베이스의 자연스러운 보완재입니다.

  1. SQL은 “에스-큐-엘” 또는 “시퀄”로 발음합니다.↩︎

  2. 일반적으로 이것은 클라이언트 패키지에서 사용하는 유일한 함수이므로 library()로 전체 패키지를 로드하는 대신 ::를 사용하여 해당 함수 하나만 꺼내는 것을 권장합니다.↩︎

  3. 적어도 볼 수 있는 권한이 있는 모든 테이블입니다.↩︎

  4. 헷갈리게도 문맥에 따라 SELECT는 문이거나 절입니다. 이 혼란을 피하기 위해 일반적으로 SELECT 문 대신 SELECT 쿼리를 사용합니다.↩︎

  5. 엄밀히 말하면 SELECT 1+1과 같은 쿼리를 작성하여 기본 계산을 수행할 수 있으므로 SELECT만 필요합니다. 하지만 데이터로 작업하려면(항상 그렇듯이!) FROM 절도 필요합니다.↩︎

  6. 우연이 아닙니다. dplyr 함수 이름은 SQL 절에서 영감을 받았습니다.↩︎