SQLを学ぼう（Rで）

※同じ処理のdplyrでの書き方を追記しました。(2022.01.15.)

はじめに

この記事は「Rは使っている、もしくは学習中であるが、SQLにはなじみがないデータサイエンティスト志望」の方を主な読者と想定しています。

データサイエンティストに求められるスキルとして、RやPythonによる分析技能と並んで、SQLによるデータ処理技能がよく取り上げられます。

データベースに蓄積された大規模データから、分析に必要なデータをSQLを使って集計、抽出するところからデータサイエンティストの仕事は始まります。 そのためSQLが使えないとそもそも仕事が始まらないのです。

しかし、SQLはRのように簡単に独習できるものではありません。（個人の感想です。） というのは、SQLを学ぶ前にデータベースの構築という壁が立ちはだかるからです。

多くのSQL入門と題した書籍やサイトでは、まずデータベースの構築から始まります。

MySQLなりPostgreSQLなりをインストールして、環境を設定して、テーブルを作って…… といった具合で、なかなかデータの抽出までたどり着きません。

そこでRの出番です。 RとRStudioを使えば簡単にSQLの学習環境を構築でき、すぐにSQLを学べます。

今回扱うこと、扱わないこと

今回扱うのはRStudioでSQLを走らせる方法とSQLによる分析のためのデータ抽出の基礎です。 Rとデータベースの連携では、dbplyrなどによってSQLを書かずにRからデータベースを操作し、結果をそのままRで処理できるのが真骨頂なのですが、今回は扱いません。またの機会に。

RとRStudioを使ったSQL学習環境の構築

RとRStudioはインストール済みであるものとします。

RSQLiteパッケージのインストール

まず、RにRSQLiteパッケージをインストールします。

install.packages("RSQLite")

RSQLiteパッケージはパブリックドメインのデータベースであるSQLiteをRから操作するためのパッケージです。 このパッケージをRに入れるだけであっという間に学習環境の完成です。

練習用サンプルデータの準備

データベースができても中身が無ければ練習できません。 ここでは公開されているデータベースのサンプルデータchinook（ちぬーく）を使います。 これもRから簡単にダウンロードして使える状態になります。

これはSQLite Tutorialというサイトで配布されているものです。

download.file("https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip",
              destfile = "chinook.zip")
unzip("chinook.zip")

これでワーキング・ディレクトリにサンプルデータが展開されます。 このデータセットは音楽のダウンロード販売サイトを模した人工データセットです。

RStudioでSQLを書く

あと一歩で完了です。 RStudioの左上の新規作成ボタンから SQL Scriptを選びます。

SQLスクリプトの新規作成

そうするとこんなファイルが開きます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite())

SELECT 1

この1行目のお尻に , "chinook.db"を書き加えます。 今回のサンプルデータベースのファイル名です。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")

SELECT 1

これで準備完了です。

試しにSQLを実行してみましょう。 以下のように書いたら適当な名前を付けて保存します。 次に右上のPreviewボタンを押します。

プレビューボタン

そうすると、下のSQL Resultsにデータの抽出結果が現れます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
FROM
  tracks
WHERE
  Composer = "Queen"

SQL実行結果

このように表示されれば成功です。

SELECT文を学ぼう

さて、ここからがSQLの勉強です。 今回はデータベースから必要なデータを抽出する命令であるSELECT文の基礎を学びましょう。

サンプルデータについて

今回扱うサンプルデータchinookについて簡単に説明します。 このデータは音楽のダウンロード販売サイトを模した人工データセットで、13個のテーブルが含まれます。 SQLにおけるテーブルとはRのデータフレームのようなものと理解してください。

以下が各テーブルの簡単な説明です。フィールドとはデータフレームにおける変数、カラムのことです。

1. "albums":
   • 音楽のアルバムのリスト。
   • フィールド：AlbumId, Title, ArtistId
2. "artists"
   • アーティスト名のリスト
   • フィールド：ArtistId, Name
3. "customers"
   • 顧客のデータ
   • フィールド：CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId
4. "employees"
   • 従業員のデータ
   • フィールド：EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email
5. "genres"
   • 音楽ジャンル
   • フィールド：GenreId, Name
6. "invoice_items"
   • 請求書の項目
   • フィールド：InvoiceLineId, InvoiceId, TrackId, UnitPrice, Quantity
7. "invoices"
   • 請求書の詳細
   • フィールド：InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total
8. "media_types"
   • メディアのタイプ
   • フィールド：MediaTypeId, Name
9. "playlist_track"
   • プレイリストに含まれる楽曲
   • PlaylistId, TrackId
10. "playlists"
    • プレイリスト
    • PlaylistId, Name
11. "tracks"
    • 楽曲のリスト
    • TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice,

テーブル間の関係についてはこちらのpdfを参照してください。

chinook_er

SELECTとFROM

SELECT文のもっとも基本的な形は SELECT フィールド名 FROM テーブル名 です。 FROMでデータが収められているテーブルを指定し、SELECTで抽出するフィールドを指定します。 dplyrならテーブル名 %>% select(フィールド名) と書くところです。 楽曲のリストであるテーブルtracksから、フィールドName（曲名）とComposer（作曲者）を抜き出すにはこう書きます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
FROM
  tracks

tracksをデータフレームだとするとdplyrだとこう書く処理です。

tracks %>%
  select(Name, Composer)

これを実行すると、SQL Resultsに曲名と作曲者のリストが出力されます。 フィールド名の部分には特定のフィールド名を指定しない*（ワイルドカード）も使えます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  *
FROM
  tracks

これを実行するとテーブルtracksに含まれるすべてのフィールドが出力されます。

WHEREによる条件付け

通常のデータ抽出では何らかの条件にそって、それに合ったデータのみを抜き出すことがほとんどでしょう。 そのような条件を指定する場合はWHEREを使います。 dplyrでいうところのfilterと同じ役割です。 ここでは作曲者がQueenである楽曲のみを抽出します。 Rと異なり、「等しい」を表す比較演算子は=です。==ではありません。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
FROM
  tracks
WHERE
  Composer = "Queen"

dplyrだとこう。

tracks %>%
  select(Name, Composer) %>%
  filter(Composer == "Queen")

数値の条件も指定できます。 テーブルtracksには演奏時間がMillisecondsとしてミリ秒（1/1000秒）単位で格納されています。 これを使って、作曲者がQueenで5分以上の曲を抜き出してみましょう。 複数の条件はANDでつなぐことができます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer = "Queen"
  AND Milliseconds >= 5 * 60 * 1000

dplyrだとこう。

tracks %>%
  select(Name, Composer, Milliseconds) %>%
  filter(Composer == "Queen", Milliseconds >= 5 * 60 * 1000)

4分以上、5分以下ならこう……

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer = "Queen"
  AND Milliseconds <= 5 * 60 * 1000
  AND Milliseconds >= 4 * 60 * 1000

ですが、この場合区間で指定するBETWEENも使えます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer = "Queen"
  AND Milliseconds BETWEEN 4 * 60 * 1000 AND 5 * 60 * 1000

文字列を条件に使う場合は完全一致だけでなく、LIKEを使うことで任意の文字を含むもの（部分一致）という指定もできます。 %は「０文字以上の任意の文字列」を表し、_は「任意の１文字」を表します。 WHERE Composer LIKE "%Queen%" とすると、今度はQueenとDavid Bowieとの共作曲も出てきました。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer LIKE "%Queen%"

dplyrだとこう。

tracks %>%
  select(Name, Composer, Milliseconds) %>%
  filter(grepl("Queen", Composer))
# greple()の代わりにstringr::str_detect()を使う手もある。

また、複数条件をORでつなげることもできます。 Queenの各メンバーが作曲した曲も抽出できるようにしましょう。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer == "Queen"
  OR Composer == "Brian May"
  OR Composer == "Roger Taylor"
  OR Composer == "Freddie Mercury"
  OR Composer == "John Deacon"

ちょっと長ったらしいですね。 この場合、INを使うと簡潔に書けます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds
FROM
  tracks
WHERE
  Composer IN ("Queen", "Brian May", "Roger Taylor", "Freddie Mercury", "John Deacon")

dplyrだとこう。

tracks %>%
  select(Name, Composer, Milliseconds) %>%
  filter(Composer %in% c("Queen", "Brian May", "Roger Taylor", "Freddie Mercury", "John Deacon"))

フィールドの加工

ところで、ミリ秒単位だとちょっと分かりにくいので千で割って秒単位にしたいです。 そんな時はSELECTの中でMilliseconds / 1000 と処理内容を書いて、さらにAS Secondsと新しいフィールド名を付ければOKです。 dplyrで言えばmutateやtransmuteに相当します。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds / 1000 AS Seconds
FROM
  tracks
WHERE
  Composer = "Queen"
  AND Milliseconds BETWEEN 4 * 60 * 1000 AND 5 * 60 * 1000

dplyrだとこう。

tracks %>%
  select(Name, Composer, Milliseconds) %>%
  filter(Composer == "Queen", Milliseconds >= 4 * 60 * 1000, Milliseconds <= 5 * 60 * 1000) %>%
  mutate(Seconds = Milliseconds / 1000) %>%
  select(!Milliseconds)

並び替え

また、演奏時間が長い順に並べ替えたいです。 並べ替えはORDER BYに並べ替えに使うフィールドを指定して行います。 デフォルトは昇順で、降順にしたいときはフィールド名の後ろにDESCを付けます。 dplyrで言えばarrangeですね。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  Name
  , Composer
  , Milliseconds / 1000 AS Seconds
FROM
  tracks
WHERE
  Composer = "Queen"
  AND Milliseconds BETWEEN 4 * 60 * 1000 AND 5 * 60 * 1000
ORDER BY
  Seconds DESC

dplyrだとこう。

tracks %>%
  select(Name, Composer, Milliseconds) %>%
  filter(Composer == "Queen", Milliseconds >= 4 * 60 * 1000, Milliseconds <= 5 * 60 * 1000) %>%
  mutate(Seconds = Milliseconds / 1000) %>%
  select(!Milliseconds) %>%
  arrange(desc(Seconds))

テーブルの紐づけ

さて、テーブルtracksにはアーティスト名が入っていません。 どの曲がどのアーティストの曲なのか分かるようにしてみましょう。 テーブルartistsにはアーティスト名が入っていますが、そのままではテーブルtracksと紐づけられる情報がありません。 テーブルalbumsにはテーブルtracksと共通するフィールドAlbumIdがあり、またテーブルartistsと共通するフィールドArtistIdもあります。 この三つのテーブルを上手く紐づければ楽曲ごとのアーティスト名が分かるようになりそうです。

テーブル同士を紐づけるにはJOINを使います。 JOIN には INNER JOIN、LEFT JOIN、FULL OUTER JOINなどの種類がありますが、今回は紙幅の関係で説明を割愛します。 今回使う LEFT JOIN はExcelにおけるVLOOKUPと同じようなイメージです、

どのフィールドで紐づけるかはONで、テーブル名.フィールド名という形式で指定します。

dplyrでもそのままjoin系の関数がありますね。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  tracks.Name AS track_Name
  , albums.Title AS albums_Title
  , artists.Name AS artist_Name
FROM
  tracks
LEFT JOIN
  albums
  ON tracks.AlbumId = albums.AlbumId
LEFT JOIN
  artists
  ON albums.ArtistId = artists.ArtistId

これで楽曲名、アルバム名、アーティスト名が紐づきました。

dplyrだとこう。

tracks %>%
  rename(track_Name = Name) %>%
  left_join(albums, by = "AlbumId") %>%
  rename(albums_Title = Title) %>%
  left_join(artists, by = "ArtistId") %>%
  rename(artist_Name = Name) %>%
  select(track_Name, albums_Title, artist_Name)

また、WITHを使って以下のような書き方もできます。 WITHを使うと、Rで処理結果をいったんデータフレームに収めて利用するような書き方ができます。 対象となるテーブルが多かったり、処理が複雑になる場合はWITHを使った方が可読性が高くなります。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
WITH

-- WITHで一時的なテーブルtracks_albumsを作る。
tracks_albums AS (
  SELECT
    tracks.Name AS track_Name
    , albums.Title AS albums_Title
    , albums.ArtistId
  FROM
    tracks
  LEFT JOIN
    albums
    ON tracks.AlbumId = albums.AlbumId
)

SELECT
  tracks_albums.*
  , artists.Name AS artist_Name
FROM
  tracks_albums
LEFT JOIN
  artists
  ON tracks_albums.ArtistId = artists.ArtistId

集計する

基本的な集計もSQLでできます。

dplyrでいうところのgroup_by、summariseですね。

ここではテーブルinvoicesから、取引の数と顧客の数を集計してみましょう。 数を数えるにはCOUNT関数を使います。 テーブル全体の数を数えるにはワールドカードを使ってCOUNT(*)と書きます。 顧客の数を数えるにはCOUNT(DISTINCT CustomerId)と書きます。 DISTINCT は重複した行を取り除く命令です。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  COUNT(*) AS deals
  , COUNT(DISTINCT CustomerId) AS num
FROM
  invoices

単に全体で集計するだけではつまらないので、年ごとの取引数、客数、売上を集計してみましょう。 取引の日付がフィールドInvoiceDateに2009-09-11 00:00:00という形式で入っています。 ここから文字列の一部を切り出す命令substrを使って年の部分を抽出します。 （STRFTIME関数を使う手もあるが、SQLite独自の関数なので今回はより一般的なsubstrを紹介。） さらにGROUP BYで年ごとにグループ化して集計します。 売上はSUMで合計を出します。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  substr(InvoiceDate, 1, 4) AS year
  , COUNT(*) AS deals
  , COUNT(DISTINCT CustomerId) AS num
  , SUM(Total) AS Total
FROM
  invoices
GROUP BY
  year
ORDER BY 
  year

dplyrだとこう。

invoices %>%
  mutate(year = substr(InvoiceDate, 1, 4)) %>%
  select(year, CustomerId, Total) %>%
  group_by(year) %>%
  summarise(
    deals = n(),
    num = n_distinct(CustomerId),
    Total = sum(Total)
   ) %>%
  arrange(year)

こんどは月ごとの売上を集計してみましょう。 GROUP BYやORDER BYには二つ以上のフィールドを指定できます。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
SELECT
  substr(InvoiceDate, 1, 4) AS year
  , substr(InvoiceDate, 6, 2) AS month
  , COUNT(*) AS deals
  , COUNT(DISTINCT CustomerId) AS num
  , SUM(Total) AS total
FROM
  invoices
GROUP BY
  year, month
ORDER BY 
  year, month

dplyrだとこう。

invoices %>%
  mutate(year = substr(InvoiceDate, 1, 4),
        month = substr(InvoiceDate, 6, 7)) %>%
  select(year, month, CustomerId, Total) %>%
  group_by(year, month) %>%
  summarise(
    deals = n(),
    num = n_distinct(CustomerId),
    Total = sum(Total)
   ) %>%
  arrange(year, month)

集計関数には他に平均を出すAVGや最大値を出すMAX、最小値を出すMINなどがあります。 月ごとの客数、客単価を出してみましょう。

-- !preview conn=DBI::dbConnect(RSQLite::SQLite(), "chinook.db")
WITH

t1 AS (
  SELECT
    substr(InvoiceDate, 1, 4) AS year
    , substr(InvoiceDate, 6, 2) AS month
    , CustomerId
    , SUM(Total) AS total
  FROM
    invoices
  GROUP BY
    year, month, CustomerId
)

SELECT
  year
  , month
  , COUNT(*) AS n
  , AVG(total) AS avg_total
FROM
  t1
GROUP BY
  year, month
ORDER BY 
  year, month

dplyrだとこう。

invoices %>%
  mutate(year = substr(InvoiceDate, 1, 4),
        month = substr(InvoiceDate, 6, 7)) %>%
  select(year, month, CustomerId, Total) %>%
  group_by(year, month, CustomerId) %>%
  summarise(Total = sum(Total), .groups = "drop") %>%
  group_by(year, month) %>%  
  summarise(n = n(), avg_total = mean(Total), .groups = "drop") %>%
  arrange(year, month)

Enjoy!

これでRを用いたSQLの学習環境構築とSQLの基礎の基礎は終わりです。 あとは各自でSQLの入門サイトや書籍を使って練習してみてください。

リンク集

以下、参考にしたサイトのリンク集です。

SQLiteLチュートリアル

Chinook Databaseを用いたSQLチュートリアル

RSQLite公式

Using SQL in RStudio